首页 > 编程学习 > Hive学习笔记:04 SQL的窗口函数、OVER的使用

文章目录

  • 测试环境准备
  • 窗口和Over
    • OVER语法
    • PARTITION BY: 开窗字段
    • ORDER BY: 排序
    • Window Specifications :窗口定义
      • 语法解析
      • ROWS和RANGE的区别
  • 窗口函数
    • LEAD
    • LAG
    • FIRST_VALUE
    • LAST_VALUE

本文将介绍Hive SQL中窗口函数、分析函数以及Over的使用,其实不仅仅是Hive,对于很多数数据库来说同样也适用,比如Mysql8,Oracle,MSSQL等传统的关系型数据库。

测试环境准备

如有一张表stock_hq,表格中的数据如下所示:

TDATESECCODEBLOCKAMOUNTPRICE
20221113000001.szA0121010.10
20221113000002.szA022109.10
20221113000003.szA012108.10
20221114000001.szA0121010.10
20221114000002.szA022109.10
20221114000003.szA012108.10
20221115000001.szA0121010.10
20221115000002.szA022109.10
20221115000003.szA012108.10
20221116000001.szA0121010.10
20221116000002.szA022109.10
20221116000003.szA012108.10
20221117000001.szA0111010.10
20221117000002.szA021109.10
20221117000003.szA011109.10
20221118000001.szA0110010.10
20221118000002.szA0210010.10
20221118000003.szA0110010.10

窗口和Over

窗口其实就是一个数据范围,它指定了我们统计计算分些数据范围。在Spark和Flink中我们知道,窗口有全局窗口和滚动窗口之分,同样在SQL的窗口中也有类似的概念。在SQL中,窗口是通过Over来实现的。

OVER语法

OVER( [PARTITION BY xx] [ORDER BY XX] [Window specifications ])

在Over()中可以由上述3者进行不同的组合,或者3者都可以不指定。

PARTITION BY: 开窗字段

PARTITON BY 选项是可选的,它可以指定一个或者多个字段进行开窗,如果不指定开窗字段,则只有“一个窗口”。

  • 示例1:不指定开窗字段,不指定排序,不定义窗口大小,则默认表中所有的数据都在一个窗口中
SELECT SECCODE,TDATE,AMOUNT,SUM(AMOUNT) over() AS SUM_AMOUNT FROM stock_hq;

输出:

TDATESECCODEAMOUNTSUM_AMOUNT
20221113000001.sz2103150
20221113000002.sz2103150
20221113000003.sz2103150
20221114000001.sz2103150
20221114000002.sz2103150
20221114000003.sz2103150
20221115000001.sz2103150
20221115000002.sz2103150
20221115000003.sz2103150
20221116000001.sz2103150
20221116000002.sz2103150
20221116000003.sz2103150
20221117000001.sz1103150
20221117000002.sz1103150
20221117000003.sz1103150
20221118000001.sz1003150
20221118000002.sz1003150
20221118000003.sz1003150

很明显,上述数据中Sum(Amount)统计的是所有记录的和,也就是说所有数据在同一窗口中。

  • 示例2:指定一个开窗字段
SELECT SECCODE,TDATE,AMOUNT,SUM(AMOUNT) over(PARTITION BY SECCODE) AS SUM_AMOUNT FROM stock_hq;

上述语句中指定了一个开窗字段,没有指定排序和窗口大小,则分成多个窗口,每个窗口中的数据是每个SECCODE的所有数据。

SECCODETDATEAMOUNTSUM_AMOUNT
000001.sz202211132101050
000001.sz202211142101050
000001.sz202211181001050
000001.sz202211152101050
000001.sz202211171101050
000001.sz202211162101050
000002.sz202211132101050
000002.sz202211181001050
000002.sz202211171101050
000002.sz202211162101050
000002.sz202211152101050
000002.sz202211142101050
000003.sz202211152101050
000003.sz202211162101050
000003.sz202211142101050
000003.sz202211171101050
000003.sz202211132101050
000003.sz202211181001050
  • 示例3:指定多个开窗字段
select BLOCK,SECCODE,TDATE,AMOUNT,SUM(AMOUNT) over(PARTITION BY BLOCK,SECCODE) AS SUM_AMOUNT FROM stock_hq;

输出:

BLOCKSECCODETDATEAMOUNTSUM_AMOUNT
A01000001.sz202211132101050
A01000001.sz202211181001050
A01000001.sz202211142101050
A01000001.sz202211171101050
A01000001.sz202211152101050
A01000001.sz202211162101050
A01000003.sz202211181001050
A01000003.sz202211171101050
A01000003.sz202211162101050
A01000003.sz202211152101050
A01000003.sz202211142101050
A01000003.sz202211132101050
A02000002.sz202211162101050
A02000002.sz202211152101050
A02000002.sz202211171101050
A02000002.sz202211142101050
A02000002.sz202211181001050
A02000002.sz202211132101050

开窗字段值相同的数据分在同一个窗口中(类似于group by).

ORDER BY: 排序

指定了Order By后,那么窗口就不再是“静态”的了,而成了一个动态滚动的窗口,废话不多说,上菜:

  • 示例1:不指定分区,只指定Order by
select TDATE,SECCODE, AMOUNT,SUM(AMOUNT) over(order by SECCODE) AS SUM_AMOUNT FROM stock_hq;

输出:

TDATESECCODEAMOUNTSUM_AMOUNT
20221113000001.sz2101050
20221114000001.sz2101050
20221118000001.sz1001050
20221115000001.sz2101050
20221117000001.sz1101050
20221116000001.sz2101050
20221113000002.sz2102100
20221118000002.sz1002100
20221117000002.sz1102100
20221116000002.sz2102100
20221115000002.sz2102100
20221114000002.sz2102100
20221115000003.sz2103150
20221116000003.sz2103150
20221114000003.sz2103150
20221117000003.sz1103150
20221113000003.sz2103150
20221118000003.sz1003150

如果只单独指定了Order By,Order By字段相同的数据会先分成一组做一个统计,然后再到下一个组如000002.sz的数据时会将000002.sz的所有的数据先做个统计,再累加上一个分组000001.sz的统计结果。

  • 示例2:指定分区,指定排序
select TDATE,SECCODE, AMOUNT,SUM(AMOUNT) over(partition by SECCODE order by TDATE) AS SUM_AMOUNT FROM stock_hq;
TDATESECCODEAMOUNTSUM_AMOUNT
20221113000001.sz210210
20221114000001.sz210420
20221115000001.sz210630
20221116000001.sz210840
20221117000001.sz110950
20221118000001.sz1001050
20221113000002.sz210210
20221114000002.sz210420
20221115000002.sz210630
20221116000002.sz210840
20221117000002.sz110950
20221118000002.sz1001050
20221113000003.sz210210
20221114000003.sz210420
20221115000003.sz210630
20221116000003.sz210840
20221117000003.sz110950
20221118000003.sz1001050

在每一个窗口中,每一行的统计结果为上一行的统计结果加上当前行的值。

Window Specifications :窗口定义

窗口的定义主要用于指定窗口的大小,有如下几种语义进行指定:

(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING

语法解析

UNBOUNDED 无边界的
PRECEDING 当前行的前
FOLLOWING 当前行后跟多少行

示例组合:

  • ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING 当前行的前3行到前一行
  • ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING 当前行的前3行+当前行+当前行的后一行
  • ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING 第一行到当前行的前2行
  • ROWS BETWEEN CURRENT ROW AND UNBOUND FOLLWING 当前行到末尾行
  • ROWS BETWEEN 3 FOLLOWING AND UNBOUND FOLLOWING 从当前行往后数3行开始到末尾
  • ROWS BETWEEN 3 FOLLOWING AND 10 FOLLOWING 从当前行的后面第3行开始到后面第10行之间的数据。

ROWS和RANGE的区别

由于我们上面的日期是连续的,所以需要删除某一天的数据,让效果看起来更明显。

delete from stock_hq where tdate = 20221115;
  • ROWS是根据数据的物理顺序来指定窗口的大小,可以不指定排序字段(采用数据库的默认顺序)。

如:ROWS BETWEEN 3 PRECEDING AND CURRENT ROW.
在000001.sz窗口中,假如当前行的日期是20221117:到当前行时统计的是当前行前3行的值加当前行的值也就是740。统计的范围是固定的,与当前行的值无关。

示例:

select SECCODE,TDATE,AMOUNT,SUM(AMOUNT) over( partition BY SECCODE order by TDATE ROWS BETWEEN 3 PRECEDING AND CURRENT Row)   AS SUM_AMOUNT FROM stock_hq;

输出:

SECCODETDATEAMOUNTSUM_AMOUNT
000001.sz20221113210210
000001.sz20221114210420
000001.sz20221116210630
000001.sz20221117110740
000001.sz20221118100630
000002.sz20221113210210
000002.sz20221114210420
000002.sz20221116210630
000002.sz20221117110740
000002.sz20221118100630
000003.sz20221113210210
000003.sz20221114210420
000003.sz20221116210630
000003.sz20221117110740
000003.sz20221118100630
  • RANGE 必须指定一个排序字段,且排序字段必须是数字类型或时间类型,窗口的大小和当前行的排序字段的值有关

如:RANGE BETWEEN 3 PRECEDING AND CURRENT ROW.
假如当前行的日期是20221117,到当前行时统计的是日期大于等于20221114到当前行20221117这一范围上的值,如下示例中是530,是一个逻辑上的窗口设定,与当前值有关。

select SECCODE,TDATE,AMOUNT,SUM(AMOUNT) over( partition BY SECCODE order by TDATE RANGE BETWEEN 3 PRECEDING AND CURRENT Row) AS SUM_AMOUNT FROM stock_hq;

输出:

SECCODETDATEAMOUNTSUM_AMOUNT
000001.sz20221113210210
000001.sz20221114210420
000001.sz20221116210630
000001.sz20221117110530
000001.sz20221118100420
000002.sz20221113210210
000002.sz20221114210420
000002.sz20221116210630
000002.sz20221117110530
000002.sz20221118100420
000003.sz20221113210210
000003.sz20221114210420
000003.sz20221116210630
000003.sz20221117110530
000003.sz20221118100420

窗口函数

LEAD

  • 函数原型:LEAD(column,rows,default_value)
  • 用途:将窗口的数据整体上移指定的行,上移后空缺的值使用指定的default_value填充。

示例:每个窗口的数据整体往上移一行,空缺的值默认为NULL

select TDATE,SECCODE, AMOUNT,LEAD(AMOUNT,1) over(partition by SECCODE order by TDATE) AS LEAD_AMOUNT FROM stock_hq;

输出:

TDATESECCODEAMOUNTLEAD_AMOUNT
20221113000001.sz210210
20221114000001.sz210210
20221116000001.sz210110
20221117000001.sz110100
20221118000001.sz100
20221113000002.sz210210
20221114000002.sz210210
20221116000002.sz210110
20221117000002.sz110100
20221118000002.sz100
20221113000003.sz210210
20221114000003.sz210210
20221116000003.sz210110
20221117000003.sz110100
20221118000003.sz100

LAG

  • 函数原型:LAG(column,rows,default_value)
  • 用途:将窗口的数据整体下移指定的行,下移后空缺的值使用指定的default_value填充。
select TDATE,SECCODE, AMOUNT,LAG(AMOUNT,1) over(partition by SECCODE order by TDATE) AS LEAD_AMOUNT FROM stock_hq;

输出:

TDATESECCODEAMOUNTLAG_AMOUNT
20221113000001.sz210
20221114000001.sz210210
20221116000001.sz210210
20221117000001.sz110210
20221118000001.sz100110
20221113000002.sz210
20221114000002.sz210210
20221116000002.sz210210
20221117000002.sz110210
20221118000002.sz100110
20221113000003.sz210
20221114000003.sz210210
20221116000003.sz210210
20221117000003.sz110210
20221118000003.sz100110

FIRST_VALUE

  • 函数原型:FIRST_VALUE(column,Bool)

最多两个参数,第一个参数是列名,第二个参数是一个bool值,默认是false。如果设为true,则会跳过NULL值找第一个不为NULL的值。

Tips:有些关系型数据库中只有一个参数,请注意。

  • 用途:取窗口中的第一个值

LAST_VALUE

  • 函数原型:LAST_VALUE(column,Bool)

最多两个参数,第一个参数是列名,第二个参数是一个bool值,默认是false。如果设为true,则会跳过NULL值找第一个不为NULL的值。

  • 用途:取窗口中的最后一个值。
Copyright © 2010-2022 dgrt.cn 版权所有 |关于我们| 联系方式