窗口函数（window functions）对多行进行操作，并为查询中的每一行返回一个值。 OVER()子句能将窗口函数与其他分析函数（analytical functions）和报告函数（reporting functions）区分开来。

1. 常用窗口函数

下表列出了一些窗口函数以及描述信息：

窗口函数 描述
LAG() LAG()窗口函数返回分区中当前行之前行（可以指定第几行）的值。 如果没有行，则返回null。
LEAD() LEAD()窗口函数返回分区中当前行后面行（可以指定第几行）的值。 如果没有行，则返回null。
FIRST_VALUE FIRST_VALUE窗口函数返回相对于窗口中第一行的指定列的值。
LAST_VALUE LAST_VALUE窗口函数返回相对于窗口中最后一行的指定列的值。
2. 语法

LAG 和 LEAD 语法

LAG | LEAD

( <col>, <line_num>, <DEFAULT> )

OVER ( [ PARTITION BY ] [ ORDER BY ] )

FIRST_VALUE | LAST_VALUE 语法

FIRST_VALUE | LAST_VALUE

( <col>,<ignore nulls as boolean> ) OVER

( [ PARTITION BY ] [ ORDER BY ][ window_clause ] )

3.demo

原数据：

hive> select * from tmp_pv;

OK

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-10    1

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-11    5

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-12    7

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-13    3

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-14    2

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-15    4

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-16    4

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-10    2

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-11    9

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-12    3

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-13    10

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-14    1

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-15    8

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-16    2

Time taken: 0.102 seconds, Fetched: 14 row(s)

3.1 LAG()

LAG(col,n,DEFAULT)窗口函数返回分区中当前行之前第n行对应列的值。 如果没有相应的行，则返回NULL。 第一个参数为列名，第二个参数为当前行之前第n行（可选，默认为1），第三个参数为缺失时默认值（当前行之前第n行为NULL没有时，返回该默认值，如不指定，则为NULL）。

为了比较每个用户浏览次数与前一天的浏览次数进行比较，查询返回当前浏览次数以及前一天的浏览数量。由于在2017-02-10之前没有浏览行为，前一天的浏览次数设置为0（不设置默认为NULL）。

hive> select gid, dt, pv, lag(pv, 1, 0) over (partition by gid order by dt) as pre_pv from tmp_pv;

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-10    1    0

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-11    5    1

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-12    7    5

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-13    3    7

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-14    2    3

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-15    4    2

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-16    4    4

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-10    2    0

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-11    9    2

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-12    3    9

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-13    10    3

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-14    1    10

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-15    8    1

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-16    2    8

Time taken: 11.783 seconds, Fetched: 14 row(s)

3.2 LEAD()

LEAD(col,n,DEFAULT)窗口函数返回分区中当前行后面第n行对应列的值。 如果没有相应的行，则返回NULL。 第一个参数为列名，第二个参数为当前行后面第n行（可选，默认为1），第三个参数为缺失时默认值（当前行后面第n行为没有时，返回该默认值，如不指定，则为NULL）。

为了比较每个用户浏览次数与后一天的浏览次数进行比较，查询返回当前浏览次数以及后一天的浏览数量。由于在2017-02-16之后没有浏览行为，后一天的浏览次数显示为NULL（默认为NULL）

hive> select gid, dt, pv, lead(pv, 1) over (partition by gid order by dt) from tmp_pv;

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-10    1    5

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-11    5    7

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-12    7    3

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-13    3    2

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-14    2    4

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-15    4    4

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-16    4    NULL

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-10    2    9

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-11    9    3

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-12    3    10

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-13    10    1

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-14    1    8

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-15    8    2

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-16    2    NULL

Time taken: 9.509 seconds, Fetched: 14 row(s)

3.3 FIRST_VALUE()

为了比较每个用户浏览次数与第一天浏览次数进行比较，查询返回当前浏览次数以及第一天浏览次数。第一个用户第一天浏览次数为1，第二个用户第一天浏览次数为2。

hive> select gid, dt, pv, first_value(pv) over (partition by gid order by dt) from tmp_pv;

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-10    1     1

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-11    5     1

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-12    7     1

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-13    3     1

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-14    2     1

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-15    4     1

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-16    4     1

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-10    2     2

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-11    9     2

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-12    3     2

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-13    10    2

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-14    1     2

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-15    8     2

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-16    2     2

Time taken: 9.862 seconds, Fetched: 14 row(s)

备注

上面例子窗口为第一行到当前行(缺失window子句有order by ，默认为rows between unbounded preceding and current row)。所以，first_value返回窗口的第一行，即第一天浏览次数。

4.4 LAST_VALUE()

为了比较每个用户浏览次数与最新一天浏览次数进行比较，查询返回当前浏览次数以及最新一天浏览次数。第一个用户最新一天（2017-02-16）浏览次数为4，第二个用户最新一天（2017-02-16）浏览次数为2。

hive> select gid, dt, pv, last_value(pv) over (partition by gid order by dt rows between current row and unbounded following) from tmp_pv;

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-10    1    4

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-11    5    4

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-12    7    4

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-13    3    4

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-14    2    4

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-15    4    4

0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-16    4    4

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-10    2    2

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-11    9    2

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-12    3    2

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-13    10    2

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-14    1    2

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-15    8    2

993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-16    2    2

Time taken: 9.335 seconds, Fetched: 14 row(s)

备注

上面例子的窗口为当前行到最后一行（rows between current row and unbounded following）。last_value返回的是窗口最后一行，即最新一天的浏览次数。

最新文章

  1. javascript基础05
  2. mysql where 1=1和 1=0 的作用
  3. WebStorm phpStorm 注册码
  4. 【墙裂推荐】大学生如何学习WEB开发
  5. 2014年辛星Javascript解读第二节
  6. 5.6.3 String类型
  7. JavaScript高级程序设计：第一章
  8. 为什么要 urlencode()
  9. django+Python数据库利用Echarts实现网页动态数据显示
  10. 通过DbVisualizer 工具运行DB2存储过程实现INSERT语句主键自增造数
  11. AllPay(欧付宝)支付接口集成
  12. 【中间件安全】Weblogic 安全加固规范
  13. SQL Server 并发死锁解决案例备忘
  14. leetcode717&mdash;1-bit and 2-bit Characters
  15. Why I Left the .NET Framework
  16. Confluence 6 的 Crowd 设置
  17. unity3d动态加载dll的API以及限制
  18. 数据去重优化 MemoryError 内存不足
  19. JSON_EXTRACT查询mysql中的｛｝和　[{},{}中的值]
  20. 日志处理(二) 日志组件logback的介绍及配置使用方法(转)

热门文章

  1. 浅谈Excel开发：二 Excel 菜单系统(转)
  2. C++统计程序运行时间代码片段
  3. P1551 亲戚
  4. P1936 【水晶灯火灵】
  5. 【VS开发】【图像处理】ISP图像传感器处理器基础
  6. C++ 11的右值引用
  7. 写出java.lang.Object类的六个常用方法
  8. Excel使用技巧大全（超全）
  9. CAN 总线数据收发驱动
  10. Fescar使用(资料)