Hive分析窗体函数之LAG，LEAD，FIRST_VALUE和LAST_VALUE

最新推荐文章于 2024-07-29 20:37:12 发布
最新推荐文章于 2024-07-29 20:37:12 发布
阅读量166 收藏
点赞数
文章标签： 大数据
原文链接：http://www.cnblogs.com/mfmdaoyou/p/7354646.html
版权

环境信息：
Hive版本号为apache-hive-0.14.0-bin
Hadoop版本号为hadoop-2.6.0
Tez版本号为tez-0.7.0


创建表：

create table windows_ss

(

    polno string,

    eff_date string,

    userno string

)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ','

stored as textfile;

 

数据：

P066666666666,2016-04-02 09:00:02,user01

P066666666666,2016-04-02 09:00:00,user02

P066666666666,2016-04-02 09:03:04,user11

P066666666666,2016-04-02 09:50:05,user03

P066666666666,2016-04-02 10:00:00,user51

P066666666666,2016-04-02 09:10:00,user09

P066666666666,2016-04-02 09:50:01,user32

P088888888888,2016-04-02 09:00:02,user41

P088888888888,2016-04-02 09:00:00,user55

P088888888888,2016-04-02 09:03:04,user23

P088888888888,2016-04-02 09:50:05,user80

P088888888888,2016-04-02 10:00:00,user08

P088888888888,2016-04-02 09:10:00,user22

P088888888888,2016-04-02 09:50:01,user31

 

将数据导入Hive表中：

LOAD DATA LOCAL INPATH  '/home/hadoop/testhivedata/windows_ss.txt'  OVERWRITE INTO TABLE windows_ss;

 

1. LAG


LAG(col,n,DEFAULT) 用于统计窗体内往上第n行值
第一个參数为列名。第二个參数为往上第n行（可选，默觉得1），第三个參数为默认值（当往上第n行为NULL时候。取默认值，如不指定，则为NULL）

SELECT

   polno,

   eff_date,

   userno,

   ROW_NUMBER() OVER(PARTITION BY polno ORDER BY eff_date) AS rn,

   LAG(eff_date,1,'1970-01-01 00:00:00') OVER(PARTITION BY polno ORDER BY eff_date) AS last_1_time,

   LAG(eff_date,2) OVER(PARTITION BY polno ORDER BY eff_date) AS last_2_time

FROM windows_ss;

结果：

polno                        eff_date                              userno       rn    last_1_time                  last_2_time

P066666666666     2016-04-02 09:00:00      user02     1     1970-01-01 00:00:00      NULL

P066666666666     2016-04-02 09:00:02      user01     2     2016-04-02 09:00:00      NULL

P066666666666     2016-04-02 09:03:04      user11     3     2016-04-02 09:00:02      2016-04-02 09:00:00

P066666666666     2016-04-02 09:10:00      user09     4     2016-04-02 09:03:04      2016-04-02 09:00:02

P066666666666     2016-04-02 09:50:01      user32     5     2016-04-02 09:10:00      2016-04-02 09:03:04

P066666666666     2016-04-02 09:50:05      user03     6     2016-04-02 09:50:01      2016-04-02 09:10:00

P066666666666     2016-04-02 10:00:00      user51     7     2016-04-02 09:50:05      2016-04-02 09:50:01

P088888888888     2016-04-02 09:00:00      user55     1     1970-01-01 00:00:00      NULL

P088888888888     2016-04-02 09:00:02      user41     2     2016-04-02 09:00:00      NULL

P088888888888     2016-04-02 09:03:04      user23     3     2016-04-02 09:00:02      2016-04-02 09:00:00

P088888888888     2016-04-02 09:10:00      user22     4     2016-04-02 09:03:04      2016-04-02 09:00:02

P088888888888     2016-04-02 09:50:01      user31     5     2016-04-02 09:10:00      2016-04-02 09:03:04

P088888888888     2016-04-02 09:50:05      user80     6     2016-04-02 09:50:01      2016-04-02 09:10:00

P088888888888     2016-04-02 10:00:00      user08     7     2016-04-02 09:50:05      2016-04-02 09:50:01

 

分析：

last_1_time: 指定了往上第1行的值。default为'1970-01-01 00:00:00' 

            P066666666666第一行。往上1行为NULL,因此取默认值 1970-01-01 00:00:00

            P066666666666第三行，往上1行值为第二行值。2016-04-02 09:00:02

            P066666666666第六行，往上1行值为第五行值。2016-04-02 09:50:01

last_2_time: 指定了往上第2行的值，为指定默认值

            P088888888888第一行，往上2行为NULL

            P088888888888第二行，往上2行为NULL

            P088888888888第四行。往上2行为第二行值。2016-04-02 09:00:02

            P088888888888第七行，往上2行为第五行值，2016-04-02 09:50:01

 

2. LEAD

与LAG相反

LEAD(col,n,DEFAULT) 用于统计窗体内往下第n行值

第一个參数为列名。第二个參数为往下第n行（可选，默觉得1），第三个參数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

SELECT

   polno,

   eff_date,

   userno,

   ROW_NUMBER() OVER(PARTITION BY polno ORDER BY eff_date) AS rn,

   LEAD(eff_date,1,'1970-01-01 00:00:00') OVER(PARTITION BY polno ORDER BY eff_date) AS next_1_time,

   LEAD(eff_date,2) OVER(PARTITION BY polno ORDER BY eff_date) AS next_2_time

FROM windows_ss;

结果：

polno                                  eff_date                     userno   rn    next_1_time                 next_2_time

P066666666666     2016-04-02 09:00:00      user02     1     2016-04-02 09:00:02      2016-04-02 09:03:04

P066666666666     2016-04-02 09:00:02      user01     2     2016-04-02 09:03:04      2016-04-02 09:10:00

P066666666666     2016-04-02 09:03:04      user11     3     2016-04-02 09:10:00      2016-04-02 09:50:01

P066666666666     2016-04-02 09:10:00      user09     4     2016-04-02 09:50:01      2016-04-02 09:50:05

P066666666666     2016-04-02 09:50:01      user32     5     2016-04-02 09:50:05      2016-04-02 10:00:00

P066666666666     2016-04-02 09:50:05      user03     6     2016-04-02 10:00:00      NULL

P066666666666     2016-04-02 10:00:00      user51     7     1970-01-01 00:00:00      NULL

P088888888888     2016-04-02 09:00:00      user55     1     2016-04-02 09:00:02      2016-04-02 09:03:04

P088888888888     2016-04-02 09:00:02      user41     2     2016-04-02 09:03:04      2016-04-02 09:10:00

P088888888888     2016-04-02 09:03:04      user23     3     2016-04-02 09:10:00      2016-04-02 09:50:01

P088888888888     2016-04-02 09:10:00      user22     4     2016-04-02 09:50:01      2016-04-02 09:50:05

P088888888888     2016-04-02 09:50:01      user31     5     2016-04-02 09:50:05      2016-04-02 10:00:00

P088888888888     2016-04-02 09:50:05      user80     6     2016-04-02 10:00:00      NULL

P088888888888     2016-04-02 10:00:00      user08     7     1970-01-01 00:00:00      NULL

分析：

--逻辑与LAG一样，仅仅只是LAG是往上。LEAD是往下

 

3. FIRST_VALUE

取分组内排序后，截止到当前行。第一个值

SELECT

   polno,

   eff_date,

   userno,

   ROW_NUMBER() OVER(PARTITION BY polno ORDER BY eff_date) AS rn,

   FIRST_VALUE(userno) OVER(PARTITION BY polno ORDER BY eff_date) AS first1

FROM windows_ss;

 

polno                  eff_date                  userno     rn    first1

P066666666666     2016-04-02 09:00:00      user02     1     user02

P066666666666     2016-04-02 09:00:02      user01     2     user02

P066666666666     2016-04-02 09:03:04      user11     3     user02

P066666666666     2016-04-02 09:10:00      user09     4     user02

P066666666666     2016-04-02 09:50:01      user32     5     user02

P066666666666     2016-04-02 09:50:05      user03     6     user02

P066666666666     2016-04-02 10:00:00      user51     7     user02

P088888888888     2016-04-02 09:00:00      user55     1     user55

P088888888888     2016-04-02 09:00:02      user41     2     user55

P088888888888     2016-04-02 09:03:04      user23     3     user55

P088888888888     2016-04-02 09:10:00      user22     4     user55

P088888888888     2016-04-02 09:50:01      user31     5     user55

P088888888888     2016-04-02 09:50:05      user80     6     user55

P088888888888     2016-04-02 10:00:00      user08     7     user55

 

4. LAST_VALUE

取分组内排序后，截止到当前行，最后一个值

SELECT

   polno,

   eff_date,

   userno,

   ROW_NUMBER() OVER(PARTITION BY polno ORDER BY eff_date) AS rn,

   LAST_VALUE(userno) OVER(PARTITION BY polno ORDER BY eff_date) AS last1

FROM windows_ss;

结果：

polno                                  eff_date                   userno      rn    last1

P066666666666     2016-04-02 09:00:00      user02     1     user02

P066666666666     2016-04-02 09:00:02      user01     2     user01

P066666666666     2016-04-02 09:03:04      user11     3     user11

P066666666666     2016-04-02 09:10:00      user09     4     user09

P066666666666     2016-04-02 09:50:01      user32     5     user32

P066666666666     2016-04-02 09:50:05      user03     6     user03

P066666666666     2016-04-02 10:00:00      user51     7     user51

P088888888888     2016-04-02 09:00:00      user55     1     user55

P088888888888     2016-04-02 09:00:02      user41     2     user41

P088888888888     2016-04-02 09:03:04      user23     3     user23

P088888888888     2016-04-02 09:10:00      user22     4     user22

P088888888888     2016-04-02 09:50:01      user31     5     user31

P088888888888     2016-04-02 09:50:05      user80     6     user80

P088888888888     2016-04-02 10:00:00      user08     7     user08

 

假设不指定ORDER BY，则默认依照记录在文件里的偏移量进行排序，会出现错误的结果

FIRST_VALUE没有排序：

SELECT

    polno,

    eff_date,

    userno,

    FIRST_VALUE(userno) OVER(PARTITION BY polno) AS first2  

FROM windows_ss;

 

polno                             eff_date                          userno   first2

P066666666666     2016-04-02 09:00:02      user01     user01

P066666666666     2016-04-02 09:00:00      user02     user01

P066666666666     2016-04-02 09:03:04      user11     user01

P066666666666     2016-04-02 09:50:05      user03     user01

P066666666666     2016-04-02 10:00:00      user51     user01

P066666666666     2016-04-02 09:10:00      user09     user01

P066666666666     2016-04-02 09:50:01      user32     user01

P088888888888     2016-04-02 09:00:02      user41     user41

P088888888888     2016-04-02 09:00:00      user55     user41

P088888888888     2016-04-02 09:03:04      user23     user41

P088888888888     2016-04-02 09:50:05      user80     user41

P088888888888     2016-04-02 10:00:00      user08     user41

P088888888888     2016-04-02 09:10:00      user22     user41

P088888888888     2016-04-02 09:50:01      user31     user41

 

LAST_VALUE没有排序：

SELECT

   polno,

   eff_date,

   userno,

   LAST_VALUE(userno) OVER(PARTITION BY polno) AS last2  

FROM windows_ss;

结果：

polno                           eff_date                              userno last2

P066666666666     2016-04-02 09:00:02      user01     user32

P066666666666     2016-04-02 09:00:00      user02     user32

P066666666666     2016-04-02 09:03:04      user11     user32

P066666666666     2016-04-02 09:50:05      user03     user32

P066666666666     2016-04-02 10:00:00      user51     user32

P066666666666     2016-04-02 09:10:00      user09     user32

P066666666666     2016-04-02 09:50:01      user32     user32

P088888888888     2016-04-02 09:00:02      user41     user31

P088888888888     2016-04-02 09:00:00      user55     user31

P088888888888     2016-04-02 09:03:04      user23     user31

P088888888888     2016-04-02 09:50:05      user80     user31

P088888888888     2016-04-02 10:00:00      user08     user31

P088888888888     2016-04-02 09:10:00      user22     user31

P088888888888     2016-04-02 09:50:01      user31     user31

 

假设想要取分组内排序后最后一个值，则须要变通一下：

SELECT

   polno,

   eff_date,

   userno,

   ROW_NUMBER() OVER(PARTITION BY polno ORDER BY eff_date) AS rn,

   LAST_VALUE(userno) OVER(PARTITION BY polno ORDER BY eff_date) AS last1,

   FIRST_VALUE(userno) OVER(PARTITION BY polno ORDER BY eff_date DESC) AS last2

FROM windows_ss ORDER BY polno,eff_date;

 

polno                                 eff_date                     userno     rn    last1       last2

P066666666666     2016-04-02 09:00:00      user02     1     user02     user51

P066666666666     2016-04-02 09:00:02      user01     2     user01     user51

P066666666666     2016-04-02 09:03:04      user11     3     user11     user51

P066666666666     2016-04-02 09:10:00      user09     4     user09     user51

P066666666666     2016-04-02 09:50:01      user32     5     user32     user51

P066666666666     2016-04-02 09:50:05      user03     6     user03     user51

P066666666666     2016-04-02 10:00:00      user51     7     user51     user51

P088888888888     2016-04-02 09:00:00      user55     1     user55     user08

P088888888888     2016-04-02 09:00:02      user41     2     user41     user08

P088888888888     2016-04-02 09:03:04      user23     3     user23     user08

P088888888888     2016-04-02 09:10:00      user22     4     user22     user08

P088888888888     2016-04-02 09:50:01      user31     5     user31     user08

P088888888888     2016-04-02 09:50:05      user80     6     user80     user08

P088888888888     2016-04-02 10:00:00      user08     7     user08     user08

 

注意：

在使用分析函数的过程中，要特别注意ORDERBY子句，用的不恰当。统计出的结果就不是你所期望的

 

转载于:https://www.cnblogs.com/mfmdaoyou/p/7354646.html

aodiyi6351
关注
Hive 窗口函数LEAD LAG FIRST_VALUE LAST_VALUE
SmartSi
02-22 8616
窗口函数（window functions）对多行进行操作，并为查询中的每一行返回一个值。 OVER()子句能将窗口函数与其他分析函数（analytical functions）和报告函数（reporting functions）区分开来。1. 常用窗口函数下表列出了一些窗口函数以及描述信息：窗口函数描述LAG()LAG()窗口函数返回分区中当前行之前行（可以指定第几行）的值。 如果没有行，则返回
数仓工具—Hive语法之窗口函数first_value和last_value(12)
热门推荐
01-06 6万+
1. first_value和last_value 见名知意,就是计算当前窗口截止当前行的第一个值和最后一个值(或者是排名的第一名个最后一名) 2. 其实我们可以理解为取当前窗口截止当前行的最值(最大值和最小值) 3. 这两个窗口函数其实用的不多，也没什么常见的场景
Hive分析窗口函数(四) LAG,LEAD,FIRST_VALUE,LAST_VALUE
未来在这儿的专栏
02-02 474
注意： 这几个函数不支持WINDOW子句。（什么是WINDOW子句，点此查看前面的文章 ）数据准备： LAG LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值 第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL） LEAD 与LAG相反 LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值 第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认
Hive分析窗口函数 LAG,LEAD,FIRST_VALUE,LAST_VALUE
卡奥斯道的博客
11-16 730
转自：http://lxw1234.com/archives/2015/04/190.htm 数据准备： cookie1,2015-04-10 10:00:02,url2cookie1,2015-04-10 10:00:00,url1cookie1,2015-04-10 10:03:04,1url3cookie1,2015-04-10 10:50:05,url6cookie1,2015
HIVE分析窗口函数: LAG,LEAD,FIRST_VALUE,LAST_VALUE
Qi.zheng
10-19 682
本文转载自：lxw的大数据田地 » Hive分析窗口函数(四) LAG,LEAD,FIRST_VALUE,LAST_VALUE 注意： 这几个函数不支持WINDOW子句。（什么是WINDOW子句，点此查看前面的文章 ） Hive版本为 apache-hive-0.13.1 数据准备： cookie1,2015-04-10 10:00:02,url2cookie1,2015-
Hive分析窗口函数: LAG、LEAD、FIRST_VALUE、LAST_VALUE
菜如张学清的博客
07-17 2万+
目录 1.数据准备 1.1数据格式 1.2创建表 2.LAG 2.1函数说明 2.2函数使用 2.3 查询结果 2.4 结果说明 3. LEAD 3.1 函数说明 3.2 函数使用 3.3 查询结果 4.FIRST_VALUE 4.1 函数说明 4.2 函数使用 5.LAST_VALUE 5.1 函数说明 5.2 函数使用 5.3 查询结果 5.4 取...
窗口函数之LEAD,LAG, FIRST_VALUE, LAST_VALUE
蕾欧娜与26的博客
09-20 1242
这篇博客给大家介绍 Hive 中最常用的四个窗口函数，分别是：LEAD, LAG, FIRST_VALUE 以及 LAST_VALUE。 FIRST_VALUE：取分组内排序后，截止到当前行，第一个值。 LAST_VALUE：取分组内排序后，截止到当前行，最后一个值。 LEAD (col, n, default)：用于统计窗口内往下滴 n 行值。第一个参数为列名，第二个参数为往下滴 n 行（默认为 1 ），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL） LAG (col
Hive分析窗口函数之LAG，LEAD，FIRST_VALUE和LAST_VALUE
DataFlow范式
04-04 1万+
环境信息： Hive版本为apache-hive-0.14.0-bin Hadoop版本为hadoop-2.6.0 Tez版本为tez-0.7.0 创建表： create tablewindows_ss (     polno string,     eff_date string,     userno string ) ROW FORMATDELIMITED F
hive窗口函数之ntile、lag、lead、first_value、last_value
MicoOu的博客
03-17 1743
目录1.样例数据2.ntile(n)2.1实例 1.样例数据 id crtime pv cookie1,2015-04-10,1 cookie1,2015-04-11,5 cookie1,2015-04-12,7 cookie1,2015-04-13,3 cookie1,2015-04-14,2 cookie1,2015-04-15,4 cookie1,2015-04-16,4 cook...
Hive分析窗口函数(四) LAG、LEAD、FIRST_VALUE和LAST_VALUE
weixin_42177380的博客
06-04 688
数据 cookie4.txt cookie1,2015-04-10 10:00:02,url2 cookie1,2015-04-10 10:00:00,url1 cookie1,2015-04-10 10:03:04,1url3 cookie1,2015-04-10 10:50:05,url6 cookie1,2015-04-10 11:00:00,url7 cookie1,2015-04-10 ...
hive的first_value、last_value函数，可取非null值
sinat_35197112的博客
06-02 1048
介绍hive函数first_value、last_value用法
Hive--窗口函数--lead函数--lag函数--first_value函数--last_value函数
mx13156的博客
08-29 684
LAST_VALUE 取分组内排序后，截止到当前行，最后一个值。FIRST_VALUE 取分组内排序后，截止到当前行，第一个值。lag函数主用与统计窗口内向上第几行值。lead用于统计窗口内向下第几行值。
hive 开窗函数之lag,lead,first_value,last_value
鸭梨的博客
11-28 3901
概念 测试数据 -- 数据准备： 姓名,购买日期,购买数量 saml 2018-01-01 10 saml 2018-01-08 55 tony 2018-01-07 50 saml 2018-01-05 46 tony 2018-01-04 29 tony 2018-01-02 15 saml 2018-02-03 23 mart 2018-04-13 94 saml 2018-04-06 42 mart 2018-04-11 75 mart 2018-04-0
Hive--开窗函数--窗口位置函数：LAG、LEAD、FIRST_VALUE、LAST_VALUE
qq_46893497的博客
11-22 1万+
窗口函数：LAG、LEAD、FIRST_VALUE、LAST_VALUE 总结 FIRST_VALUE 功能：取每个分区内某列的第一个值 语法：FIRST_VALUE(col) over (partition by col1 order by col2) LAST_VALUE 功能：取每个分区内某列的最后一个值 语法：LAST_VALUE(col) over (partition by col1 order by col2) 注意：默认窗口是从第一条到当前条 LAG 功能：取每个分区内某列的
hive高级分析函数与优化.rar_hive_hive 函数优化_hive 分析 大数据_hive高级优化_oldwlc
09-23
高级分析函数如统计学习函数（如KMEANS、NAIVE_BAYES等）和时间序列分析函数（如LAG、LEAD跨越时间窗口的计算）则能进行更复杂的分析任务。 2. **Hive函数优化**： 函数优化主要涉及函数重写、谓词下推和计算下推...
HiveSQL中last_value和first_value函数的应用
最新发布
07-29 875
今天做一个数据分析，其中有一列数据有些有数据有些没数据，因此我们需要把每数据的进行补充进来因此我们需要使用last_value 函数和over 结合使用，但是遇到一个比较奇葩的问题不能按照预期进行处理。
Hive窗口函数
weixin_44516261的博客
10-03 1121
Hive窗口函数 1）定义 窗口函数属于sql中比较高级的函数 mysql从8.0版本才支持窗口函数，5.6,5.7都没有窗口函数 oracle 里面一直支持窗口函数 hive也支持窗口函数 以下函数才是窗口函数 窗口函数（13个）： LEAD LEAD(col,n, default_val)：往后第n行数据 col 列名 n 往后第几行 默认为1 默认值 默认null LAG LAG(col,n,default_val)：往前第n行数据 col 列名 n 往前第几行 默认为1
Hive学习——开窗函数精讲
雷神乐乐的博客
02-24 2803
Hive中开窗函数十分重要，很多业务需求都可以用开窗函数解决
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值