开发者学堂课程【大数据分析之企业级网站流量运营分析系统开发实战（第四阶段）： Hive 高阶--分组窗口函数--取值分组函数（ LAG,LEAD,FIRST_VALUE,LAST_VALUE）】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/696/detail/12235
Hive 高阶--分组窗口函数--取值分组函数（ LAG,LEAD,FIRST_VALUE,LAST_VALUE）
内容介绍：
一、准备数据
二、LAG
三、LEAD
四、FIRST_ VALUE
五、LAST_ VALUE
六、特别注意 order by
一、准备数据
1、了解分组窗口函数中几个常见的取值函数，这些函数从名字上就可以简单了解它，FIRST_VALUE 第一个值,LAST_VALUE 最后一个值，lag 向上取整，lead 向下取整，注意这几个函数不支持 WINDOW 子句，就是 row between 控制行数。
2、cookie1,2018-04-10 10:00:02,ur12
Cookie1,2018-04-10 10:00:00,ur11
cookie1,2018-04-10 10:03 :04,lur13
cookie1,2018-04-10 10:50:05,ur16
Cookie1,2018-04-10 11:00:00,ur17
cookie1,2018-04-10 10:10:00,ur14
cookie1,2018-04-10 10:50:01,ur15
cookie2,2018-04-10 10:00:02,ur122
cookie2,2018-04-10 10:00:00,ur111
cookie2,2018-04-10 10:03:04,lur133
cookie2,2018-04-10 10:50:05 ,ur166
cookie2,2018-04-10 11:00:00,ur177
cookie2,2018-04-10 10:10:00,ur144
cookie2,2018-04-10 10:50:01,ur155
CREATE TABLE itcast_t4 (
cookieid string,
createtime string,--页面访问时间
ur1 STRING --被访问页面
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY ', '
stored as textfile;
Cookie id 用户，Cookie 创建的时间，有完整的数据，url 访问哪个页面。创建表，把结构化数据映射出来。创建完表之后当中的字段以及类型要跟数据保持对应，指定分隔符为逗号。
3、复制准备好文件，打开本地，vim 编辑，把结构化数据复制过去，粘贴，保存。
cookie1,2018-04-10 10:00:02,ur12
Cookie1,2018-04-10 10:00:00,ur11
cookie1,2018-04-10 10:03 :04,lur13
cookie1,2018-04-10 10:50:05,ur16
Cookie1,2018-04-10 11:00:00,ur17
cookie1,2018-04-10 10:10:00,ur14
cookie1,2018-04-10 10:50:01,ur15
cookie2,2018-04-10 10:00:02,ur122
cookie2,2018-04-10 10:00:00,ur111
cookie2,2018-04-10 10:03:04,lur133
cookie2,2018-04-10 10:50:05 ,ur166
cookie2,2018-04-10 11:00:00,ur177
cookie2,2018-04-10 10:10:00,ur144
cookie2,2018-04-10 10:50:01,ur155
4、创建表以及把数据给加载到表中，复制建表语句 create table，打开 hive 终端，使用 load 命令把数据从 hive 服务器的本地加载到数仓中，复制执行。
加载数据:
load data local inpath ' /root/hivedata/itcast_t4.dat' into table itcast_t4;
5、加载成功后，验证数据是否映射成功，select*，操作相当于提前保证数据不会有问题，映射解析非常完美没有出现 null 情况。
二、LAG
1、LAG(cd,n,DEFAULT)中可以指定三个字段，用于统计窗口内往上第 n 行值。
2、第一个参数为统计哪个列，取哪个列的值，,第二个参数是一个数字，表示往上第n行(可选，默认为1) , 第三个参数内默认值(当往上第 n 行为 NULL 吋候,取默认值,如不指定,则为 NULL )如果现在就是第一行，再往上取一行，第一行之前没有数据就会出现为空，如果为空的情况下，如果有默认值就用默认值填充，如果没有默认值，不指定就设置为 null。
SELECT cookieid,
createtime ,
ur1,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,在每个分组内做ROW_NUMBER()打标号方便看数据排序的步骤号，根据cookie进行分组，根据创建时间进行排序，
根据cookieid进行分组，整个数据会分成两组，一个叫做cookie1，一个叫做cookie2，每个分组内 rn 做了row number打上序号标号的操作，1234567，一二分组内。
LAG(createtime ,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY向上取一行，提取创建时间，取一行的值，如果上面一行没有字段，默认以它进行填充，当拿第一行值时，向上取一行，它本身是第一行，向上没有数据，但是有填充，所以它的值是1970-01-01。整个数据都往下了一行。
createtime) AS last_ 1_time,
LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_ 2_time向上取两行，但是没有默认值填充，当向上取两行，没有值时，默认返回 null。当取第一行，往上取两行没有值，没 null，当取第二行时依然没有值，当取第三行记录时，它往上两行正好是第一行的记录，10.00，相当于往下了两行。
FROM itcast_ t4;
last_1_ time:指定了往上第1行的值, default为'1970-01-01 00:00:00'
cookie1第一行,往上1行为NULL,因此取默认值1970-01-01 00:00:00
cookie1第三行,往上1行值为第二行值, 2015-04-10 10:00:02
cookie1第六行,往上1行值为第五行值, 2015-04-10 10:50:01
1ast_ 2_ time: 指定了往上第2行的值为指定默认值
cookie1第一行,往上2行为NULL
cookie1第二行,往上2行为NULL
cookie1第四行,往上2行为第二行值,2015-04-10 10:00:02
cookie1第七行,往上2行为第五行值,2015-04-10 10:50:01
三、LEAD
与 LAG 相反
LEAD(col,n,DEFAULT) 用于统计窗口内往下第 n 行值
三个参数，第一个参数为列名,第二个参数为往下第 n 行(可选，默认为1 ) , 第三个参数为默认值填充(当往下第 n 行为 NULL 时候,取默认值,如不指定,则为 NULL )
SELECT cookieid,
createtime ,
url,
ROW_ NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LEAD(createtime,1, '1970-01-0100:00:00') OVER(PARTITION BY cookieid ORDER BY
createtime) AS next_ 1_ time,
LEAD(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_ 2_time
FROM itcast _t4;
复制，打开 hive 终端进行执行，当取第一个 leag1 时向下取一行值，拿第一行内容时，实际上取的是第二行值，十点零二，相当于把这行内容填在第一行，第二行填充给第一行，第三行填充给第二行，当到第七行值时，它需要拿到下面这一行，这时分组已经结束，只能采用默认值进行填充，所以最后一行值是1970-01-0100:00:00，正好跟LAG是相反的操作。向下取两值也是一样，拿到第一行值时，应该拿到的是第三行，第三行提到第一行，第四行提到第二行，如果没有，返回为 null，这就是向下取法。
四、FIRST_ VALUE
取分组内排序后,截止到当前行,第一个值
SELECT cookieid,
createtime，
ur1,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
FIRST_VALUE(ur1) OVER(PARTITION BY cookieid ORDER BY createtime) AS first1
FROM itcast_ t4;
根据 cookieid 进行分组，每个分组内根据创建的时间进行排序，而且是正序，提取 url 的第一值，但是要注意截取当前值的第一个字，截取到第二行 url1，当取 FIRST_VALUE 时永远都是截取当前值的第一个值，就是整个分组的第一个值，每个都是截取当前值的第一个值，url11，三个值在一起还是取 url11。
五、LAST_VALUE
取分组内排序后，截止到当前行，最后一个值，就是取自己本身的值。SELECT cookieid,
createtime,
url,
ROW_ NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LAST_VALUE (ur1) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1
FROM itcast_t4;
第一行截止到最后一个值 url1自己本身，截止到第二行当前自己本身 url2，因此LAST_VALUE就是取自己本身。把数据进行排序，取第一个值还是最后一个值跟分组内的排序相关，正序或者倒序直接影响最后的取值结果。
如果想要取分组内排序后最后一个值，则需要变通一下:
SELECT cookieid,
createtime,
url,
ROW NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn.
LAST_ VALUE (ur1) OVER(PARTITION BY cookieid ORDER BY createtime) AS lastl,
FIRST_VALUE(ur1) OVER(PARTITION BY cookieid ORDER BY createtime DESC) AS last2
FROM itcast_ _t4
ORDER BY cookieid,createtime;
根据时间 createtime 进行倒序排序，再取第一个值相当于取每个分组内的最后一个值，在排序时把时间倒序排序，相当于主力数据库的排序，再取 FIRST_VALUE 分组内最后一个值，复制执行，发现每个值都是分组内最后一个值，ur17，相当于把时间做了倒序的排序取第一个值，倒序的排序最大排在前面，取值都是ur17。
六、特别注意 order by
如果不指定 ORDER BY ,则进行排序混乱,会出现错误的结果
SELECT cookieid,
createtime ,
url,
FIRST_ VALUE(ur1) OVER(PARTITION BY cookieid) AS first2
FROM itcast_ t4;
注意取值函数的使用在分组后排序对最终结果影响非常大。
