持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第31天，10月更文诚意加码，激发写作潜力｜掘金·日新计划 - 掘金 (juejin.cn)点击查看活动详情

上下移动

首先，新建一个node01窗口

然后进入编写文档的文件夹

[root@node01 ~]# cd /opt/testData/hive/

 

然后新建文件，编辑数据

[root@node01 hive]# vi cookie3.txt

数据准备。

cookie1,2015-04-10 10:00:02,url2

cookie1,2015-04-10 10:00:00,url1

cookie1,2015-04-10 10:03:04,url3

cookie1,2015-04-10 10:50:05,url6

cookie1,2015-04-10 11:00:00,url7

cookie1,2015-04-10 10:10:00,url4

cookie1,2015-04-10 10:50:01,url5

cookie2,2015-04-10 10:00:02,url22

cookie2,2015-04-10 10:00:00,url11

cookie2,2015-04-10 10:03:04,url33

cookie2,2015-04-10 10:50:05,url66

cookie2,2015-04-10 11:00:00,url77

cookie2,2015-04-10 10:10:00,url44

cookie2,2015-04-10 10:50:01,url55

进入hive里

创建表

0: jdbc:hive2://192.168.67.110:10000> create table cookie3(cookieid string, createtime string, url string)
. . . . . . . . . . . . . . . . . . > row format delimited fields terminated by ',';
No rows affected (0.631 seconds)

加载数据

0: jdbc:hive2://192.168.67.110:10000> load data local inpath "/opt/testData/hive/cookie3.txt" into table cookie3;
No rows affected (1.095 seconds)

查看数据

0: jdbc:hive2://192.168.67.110:10000> create table cookie3(cookieid string, createtime string, url string)
. . . . . . . . . . . . . . . . . . > row format delimited fields terminated by ',';
No rows affected (0.299 seconds)
0: jdbc:hive2://192.168.67.110:10000> load data local inpath "/opt/testData/hive/cookie3.txt" into table cookie3;
No rows affected (0.786 seconds)
0: jdbc:hive2://192.168.67.110:10000> select * from cookie3;

LAG(col,n,DEFAULT)

用于将当前列往上移n行

第一个参数为列名。

第二个参数为往上第n行（可选，默认为1）。

第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）。

LAG(createtime,1,'1970-01-01 00:00:00') over (partition by cookieid order by createtime) as last_1_time

LAG(createtime,2) over (partition by cookieid order by createtime) as last_2_time

LEAD(col,n,DEFAULT)

与上面的相似，用于将当前列往下移n行。