hive窗口函数总结

最新推荐文章于 2022-04-15 11:11:43 发布
最新推荐文章于 2022-04-15 11:11:43 发布
阅读量2.7k 收藏 16
点赞数 4
分类专栏： 大数据
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/bjl0915/article/details/111402452
版权

Hive preceding and following理解

在了解hive开窗函数前我们来看看Hive窗口函数preceding and following是怎么回事呢.
Hive窗口函数中,有一个功能是统计当前行之前或之后指定行作为一个聚合,关键字是 preceding 和 following,举例说明其使用方法.
常规的窗口函数比较简单,这里介绍一下分组的,重点是分组排序之后的rows between用法。
关键是理解rows between中关键字含义：

关键字含义
preceding往前
following往后
current row当前行
unbounded开始行
unbounded preceding表示从前面的起点
unbounded following表示到后面的终点

案例

select country,time,charge,
max(charge) over (partition by country order by time) as normal,
max(charge) over (partition by country order by time rows between unbounded preceding and current row) as unb_pre_cur,
max(charge) over (partition by country order by time rows between 2 preceding and 1 following) as pre2_fol1,
max(charge) over (partition by country order by time rows between current row and unbounded following) as cur_unb_fol 
from temp

注意:默认是在分组类的当前行之前的行中计算。
rows between unbounded preceding and current row和默认的一样
rows between 2 preceding and 1 following表示在当前行的前2行和后1行中计算
rows between current row and unbounded following表示在当前行和到最后行中计算
rows between对于avg、min、max、sum这几个窗口函数的含义基本是一致的,注意查看当前结果
注意查看分组后窗口函数统计结果
在 hive 环境中创建临时表

create table tmp_student
(
   name           string,
   class          tinyint,
   cooperator_name   string,
   score          tinyint
)row format delimited fields terminated by '|';

加载测试数据
load data local inpath ‘text.txt’ into table tmp_student;
其中text.txt中内容为:

adf|3|测试公司1|45
xx|3|测试公司2|55
cfe|2|测试公司2|74
3dd|3|测试公司5|n
fda|1|测试公司7|80
gds|2|测试公司9|92
ffd|1|测试公司10|95
dss|1|测试公司4|95
ddd|3|测试公司3|99
gf|3|测试公司9|99

查看是否加载成功

hive> select * from tmp_student;
adf	3	测试公司1	45
xx 3	测试公司2	55
cfe	2	测试公司2	74
3dd	3	测试公司5	NULL
fda	1	测试公司7	80
gds	2	测试公司9	92
ffd	1	测试公司10	95
dss	1	测试公司4	95
ddd	3	测试公司3	99
gf	3	测试公司9	99
Time taken: 1.314 seconds, Fetched: 10 row(s)

下面来了解preceding and following函数用法,执行下面sql

select
    name,
    score,
    sum(score) over(order by score range between 2 preceding and 2 following) s1, -- 当前行的score值加减2的范围内的所有行
    sum(score) over(order by score rows between 2 preceding and 2 following) s2, -- 当前行+前后2行,一共5行
    sum(score) over(order by score range between unbounded preceding and unbounded following) s3, -- 全部行,不做限制
    sum(score) over(order by score rows between unbounded preceding and unbounded following) s4, -- 全部行,不做限制
    sum(score) over(order by score) s5, -- 第一行到当前行（和当前行相同score值的所有行都会包含进去）
    sum(score) over(order by score rows between unbounded preceding and current row) s6, -- 第一行到当前行（和当前行相同score值的其他行不会包含进去,这是和上面的区别）
    sum(score) over(order by score rows between 3 preceding and current row) s7, -- 当前行+往前3行
    sum(score) over(order by score rows between 3 preceding and 1 following) s8, --当前行+往前3行+往后1行
    sum(score) over(order by score rows between current row and unbounded following) s9 --当前行+往后所有行
from
    tmp.tmp_student
order by 
    score;

注意:
当ORDER BY后面缺少窗口从句条件，窗口规范默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
当ORDER BY和窗口从句都缺失, 窗口规范默认是 ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
rows是物理窗口，是哪一行就是哪一行，与当前行的值（order by key的key的值）无关，只与排序后的行号相关，就是我们常规理解的那样。
range是逻辑窗口，与当前行的值有关（order by key的key的值）,在key上操作range范围。
得到相关结果如下
注意查看窗口函数统计结果通过上面的练习我们主要是对preceding and following有了一个比较全面的理解,所谓开窗函数其实就相当于flink中的滚动窗口,统计分析都是基于这个滚动窗口内完成的,所有的聚合计算统计都需要先根据range或者row确定窗口内的数据,然后就很容易得到正确的计算结果,在确定行数的过程中需要根据range和row确定是逻辑范围还是物理范围,最终都可以看作是第N行到第M行内数据的聚合统计.
Flink窗口说明

窗口函Windowing functions

  • FIRST_VALUE(col, bool DEFAULT)

    返回分组窗口内第一行col的值,DEFAULT默认为false,如果指定为true,则跳过NULL后再取值,对于FIRST_VALUE每个分组第一行数据的FIRST_VALUE(col, bool DEFAULT) 就等于col,接下来几行数据会参考第一行数据是否为NULL根据True/False进行取舍.

WITH tmp AS (
		SELECT 1 AS group_id, 'a' AS col
		UNION ALL
		SELECT 1 AS group_id, 'b' AS col
		UNION ALL
		SELECT 1 AS group_id, 'c' AS col
		UNION ALL
		SELECT 2 AS group_id, NULL AS col
		UNION ALL
		SELECT 2 AS group_id, 'e' AS col
	)
SELECT group_id, col, FIRST_VALUE(col) OVER (PARTITION BY group_id ORDER BY col) AS col_new
FROM tmp;
返回结果为:
group_id col col_new  
1 a a 
1 b a 
1 c a 
2 NULL NULL  
2 e NULL
如果是True
WITH tmp AS (
		SELECT 1 AS group_id, NULL AS col
		UNION ALL
		SELECT 1 AS group_id, 'b' AS col
		UNION ALL
		SELECT 1 AS group_id, 'c' AS col
		UNION ALL
		SELECT 2 AS group_id, NULL AS col
		UNION ALL
		SELECT 2 AS group_id, 'e' AS col
	)
SELECT group_id, col, FIRST_VALUE(col, true) OVER (PARTITION BY group_id ORDER BY col) AS col_new
FROM tmp;
返回结果为：  
group_id col col_new  
1 NULL NULL  
1 b b  
1 c b  
2 NULL NULL  
2 e e
  • LAST_VALUE(col, bool DEFAULT)
    返回分组窗口内第后一行col的值,DEFAULT默认为false,如果指定为true,则跳过NULL后再取值.
WITH tmp AS (
		SELECT 1 AS group_id, 'a' AS col
		UNION ALL
		SELECT 1 AS group_id, NULL AS col
		UNION ALL
		SELECT 1 AS group_id, 'c' AS col
		UNION ALL
		SELECT 2 AS group_id, 'd' AS col
		UNION ALL
		SELECT 2 AS group_id, 'e' AS col
	)
SELECT group_id, col, LAST_VALUE(col) OVER (PARTITION BY group_id ORDER BY col DESC) AS col_new FROM tmp; 
返回结果为： 
group_id col col_new  
1 c c  
1 a a  
1 NULL NULL  
2 e e  
2 d d
如果是True
WITH tmp AS (
		SELECT 1 AS group_id, 'a' AS col
		UNION ALL
		SELECT 1 AS group_id, NULL AS col
		UNION ALL
		SELECT 1 AS group_id, 'c' AS col
		UNION ALL
		SELECT 2 AS group_id, 'd' AS col
		UNION ALL
		SELECT 2 AS group_id, 'e' AS col
	)
SELECT group_id, col, LAST_VALUE(col, true) OVER (ORDER BY group_id,col DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS col_new FROM tmp;
返回结果为：  
group_id col col_new  
1 c a  
1 a a  
1 NULL e  
2 e d  
2 d d

开窗函数不同于group by函数,开窗函数能够把所有的记录都显示出来,一般select所选择的列也都与over里面的分组和排序字段相同,这样才能比较清楚地看到当前记录在聚合函数中的区别和贡献,上面两个窗口函数我们针对最后一个案例进行说明下.
使用了开窗函数首先要确定窗口的大小,根据上面的PRECEDING和FOLLOWING讲解我们可以知道在分析时候窗口大小为[前一行,当前行,后一行],那么对于第一行1 c取出last_value就是从[空值,c,a]取出集合中最后一个就是a,同理对于第二行1 a取出last_value就是从[c,a,Null]中取出最后一个Null跳过再取得到a,对于2 e从集合[Null,e,d]last_value=d*
  • LEAD(col, n, DEFAULT)

返回分组窗口内往下第n行col的值,n默认为1,往下第n没有时返回DEFAULT（DEFAULT默认为NULL）使用分组后那么分组之间就不交叉计算.

WITH tmp AS
(
 SELECT 1 AS group_id, 'a' AS col 
 UNION ALL SELECT 1 AS group_id,  'b' AS col 
 UNION ALL SELECT 1 AS group_id,  'c' AS col 
 UNION ALL SELECT 2 AS group_id,  'd' AS col 
 UNION ALL SELECT 2 AS group_id,  'e' AS col
)
SELECT group_id,
      col,
      LEAD(col) over(partition by group_id order by col) as col_new
FROM tmp;

返回结果

group_id col col_new
1 a b
1 b c
1 c NULL
2 d e
2 e NULL

等同于

WITH tmp AS
(
 SELECT 1 AS group_id, 'a' AS col 
 UNION ALL SELECT 1 AS group_id,  'b' AS col 
 UNION ALL SELECT 1 AS group_id,  'c' AS col 
 UNION ALL SELECT 2 AS group_id,  'd' AS col 
 UNION ALL SELECT 2 AS group_id,  'e' AS col
)
SELECT group_id,
      col,
      LAST_VALUE(col) over(partition by group_id order by col rows between 1 FOLLOWING and 1 FOLLOWING) as col_new
FROM tmp;

其中rows between 1 FOLLOWING and 1 FOLLOWING为从往后一行开始到往后一行结束=往后一行
返回结果

group_id col col_new
1 a b
1 b c
1 c NULL
2 d e
2 e NULL

使用LEAD默认值

WITH tmp AS
(
 SELECT 1 AS group_id, 'a' AS col 
 UNION ALL SELECT 1 AS group_id,  'b' AS col 
 UNION ALL SELECT 1 AS group_id,  'c' AS col 
 UNION ALL SELECT 2 AS group_id,  'd' AS col 
 UNION ALL SELECT 2 AS group_id,  'e' AS col
)
SELECT group_id,
      col,
      LEAD(col, 2, 'z') over(partition by group_id order by col) as col_new
FROM tmp;

返回结果

group_id col col_new
1 a c
1 b z
1 c z
2 d z
2 e z
  • LAG(col, n, DEFAULT)
    返回分组窗口内往上第n行col的值,n默认为1,往上第n没有时返回DEFAULT（DEFAULT默认为NULL）
WITH tmp AS
(
 SELECT 1 AS group_id, 'a' AS col 
 UNION ALL SELECT 1 AS group_id,  'b' AS col 
 UNION ALL SELECT 1 AS group_id,  'c' AS col 
 UNION ALL SELECT 2 AS group_id,  'd' AS col 
 UNION ALL SELECT 2 AS group_id,  'e' AS col
)
SELECT group_id,
      col,
      LAG(col) over(partition by group_id order by col) as col_new
FROM tmp;

等同于

WITH tmp AS
(
 SELECT 1 AS group_id, 'a' AS col 
 UNION ALL SELECT 1 AS group_id,  'b' AS col 
 UNION ALL SELECT 1 AS group_id,  'c' AS col 
 UNION ALL SELECT 2 AS group_id,  'd' AS col 
 UNION ALL SELECT 2 AS group_id,  'e' AS col
)
SELECT group_id,
      col,
      FIRST_VALUE(col) over(partition by group_id order by col rows BETWEEN 1 PRECEDING and 1 PRECEDING) as col_new
FROM tmp;

返回结果都是

group_id col col_new
1 a NULL
1 b a
1 c b
2 d NULL
2 e d

使用默认值

WITH tmp AS
(
 SELECT 1 AS group_id, 'a' AS col 
 UNION ALL SELECT 1 AS group_id,  'b' AS col 
 UNION ALL SELECT 1 AS group_id,  'c' AS col 
 UNION ALL SELECT 2 AS group_id,  'd' AS col 
 UNION ALL SELECT 2 AS group_id,  'e' AS col
)
SELECT group_id,
      col,
      LAG(col, 2, 'zz') over(partition by group_id order by col) as col_new
FROM tmp;

返回结果

group_id col col_new
1 a zz
1 b zz
1 c a
2 d zz
2 e zz

OVER详解 The OVER clause

** FUNCTION(expr) OVER([PARTITION BY statement] [ORDER BY statement] [window clause]) **
中括号为可选参数
FUNCTION：包括标准聚合函数(COUNT/SUM/MIN/MAX/AVG)和一些分析函数(RANK/ROW_NUMBER/DENSE_RANK等)
PARTITION BY：可以由一个或者多个列组成
ORDER BY：可以由一个或者多个列组成
window clause:(ROWS | RANGE) BETWEEN (UNBOUNDED PRECEDING | num PRECEDING | CURRENT ROW) AND (UNBOUNDED PRECEDING | num PRECEDING | CURRENT ROW)
当window clause 未指定时默认为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,即分组内第一行至当前行作为窗口
当 window clause和ORDER BY都未指定时,默认为ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
**即分组内第一行至最后一行作为窗口.**

标准聚合函数

COUNT(expr) OVER()
返回窗口内行数
WITH tmp AS
(
 SELECT 1 AS group_id, 'a' AS col 
 UNION ALL SELECT 1 AS group_id,  'b' AS col 
 UNION ALL SELECT 1 AS group_id,  'c' AS col 
 UNION ALL SELECT 2 AS group_id,  'e' AS col 
 UNION ALL SELECT 2 AS group_id,  'e' AS col
)
SELECT group_id,
      col,
      count(col) over(partition by group_id) as cnt1,
      count(col) over(partition by group_id order by col) as cnt2,
      count(col) over(partition by group_id order by col rows between CURRENT ROW and UNBOUNDED following) as cnt3,
      count(distinct col) over(partition by group_id order by col rows between CURRENT ROW and UNBOUNDED following) as cnt4
FROM tmp;
返回结果为
group_id col cnt1 cnt2 cnt3 cnt4
1 a 3 1 3 3
1 b 3 2 2 2
1 c 3 3 1 1
2 e 2 2 2 1
2 e 2 2 1 1

SUM(expr) OVER()
返回窗口内求和值
WITH tmp AS
(
 SELECT 1 AS group_id, 1 AS col 
 UNION ALL SELECT 1 AS group_id,  2 AS col 
 UNION ALL SELECT 1 AS group_id,  3 AS col 
 UNION ALL SELECT 2 AS group_id,  4 AS col 
 UNION ALL SELECT 2 AS group_id,  4 AS col
)
SELECT group_id,
      col,
      SUM(col) over(partition by group_id) as sum1,
      SUM(col) over(partition by group_id order by col) as sum2,
      SUM(col) over(partition by group_id order by col rows between CURRENT ROW and UNBOUNDED following) as sum3,
      SUM(distinct col) over(partition by group_id order by col rows between CURRENT ROW and UNBOUNDED following) as sum4
FROM tmp;
返回结果为
group_id col sum1 sum2 sum3 sum4
1 1 6 1 6 6
1 2 6 3 5 5
1 3 6 6 3 3
2 4 8 8 8 4
2 4 8 8 4 4

MIN(expr) OVER()
返回窗口内最小值
WITH tmp AS
(
 SELECT 1 AS group_id, 1 AS col 
 UNION ALL SELECT 1 AS group_id,  2 AS col 
 UNION ALL SELECT 1 AS group_id,  3 AS col 
 UNION ALL SELECT 2 AS group_id,  4 AS col 
 UNION ALL SELECT 2 AS group_id,  5 AS col
)
SELECT group_id,
      col,
      MIN(col) over(partition by group_id) as min1,
      MIN(col) over(partition by group_id order by col) as min2,
      MIN(col) over(partition by group_id order by col rows between CURRENT ROW and UNBOUNDED following) as min3
FROM tmp;
group_id col min1 min2 min3
1 1 1 1 1
1 2 1 1 2
1 3 1 1 3
2 4 4 4 4
2 5 4 4 5

MAX(expr) OVER()
返回窗口内最大值
WITH tmp AS
(
 SELECT 1 AS group_id, 1 AS col 
 UNION ALL SELECT 1 AS group_id,  2 AS col 
 UNION ALL SELECT 1 AS group_id,  3 AS col 
 UNION ALL SELECT 2 AS group_id,  4 AS col 
 UNION ALL SELECT 2 AS group_id,  5 AS col
)
SELECT group_id,
      col,
      MAX(col) over(partition by group_id) as max1,
      MAX(col) over(partition by group_id order by col) as max2,
      MAX(col) over(partition by group_id order by col rows between CURRENT ROW and UNBOUNDED following) as max3
FROM tmp;
返回结果为
group_id col max1 max2 max3
1 1 3 1 3
1 2 3 2 3
1 3 3 3 3
2 4 5 4 5
2 5 5 5 5

AVG(expr) OVER()
返回窗口内平均值
WITH tmp AS
(
 SELECT 1 AS group_id, 1 AS col 
 UNION ALL SELECT 1 AS group_id,  2 AS col 
 UNION ALL SELECT 1 AS group_id,  3 AS col 
 UNION ALL SELECT 2 AS group_id,  4 AS col 
 UNION ALL SELECT 2 AS group_id,  4 AS col
)
SELECT group_id,
      col,
      AVG(col) over(partition by group_id) as avg1,
      AVG(col) over(partition by group_id order by col) as avg2,
      AVG(col) over(partition by group_id order by col rows between CURRENT ROW and UNBOUNDED following) as avg3,
      AVG(distinct col) over(partition by group_id order by col rows between CURRENT ROW and UNBOUNDED following) as avg4
FROM tmp;
返回结果为
|group_id|col|avg1|avg2|avg3|avg4|
|1|1|2.0|1.0|2.0|2.0|
|1|2|2.0|1.5|2.5|2.5|
|1|3|2.0|2.0|3.0|3.0|
|2|4|4.0|4.0|4.0|4.0|
|2|4|4.0|4.0|4.0|4.0|

分析函数 Analytics functions
RANK() OVER()
返回分组内排名（不支持自定义窗口）
WITH tmp AS
(
 SELECT 1 AS group_id, 1 AS col 
 UNION ALL SELECT 1 AS group_id,  3 AS col 
 UNION ALL SELECT 1 AS group_id,  3 AS col 
 UNION ALL SELECT 2 AS group_id,  4 AS col 
 UNION ALL SELECT 2 AS group_id,  5 AS col
)
SELECT group_id,
      col,
      RANK() over(partition by group_id order by col desc) as r
FROM tmp;
返回结果为
|group_id|col|r|
|1|3|1|
|1|3|1|
|1|1|3|
|2|5|1|
|2|4|2|

ROW_NUMBER() OVER()
返回分组内行号（不支持自定义窗口）
WITH tmp AS
(
 SELECT 1 AS group_id, 1 AS col 
 UNION ALL SELECT 1 AS group_id,  3 AS col 
 UNION ALL SELECT 1 AS group_id,  3 AS col 
 UNION ALL SELECT 2 AS group_id,  4 AS col 
 UNION ALL SELECT 2 AS group_id,  5 AS col
)
SELECT group_id,
      col,
      ROW_NUMBER() over(partition by group_id order by col desc) as r
FROM tmp;
返回结果为
|group_id|col|r|
|1|3|1|
|1|3|2|
|1|1|3|
|2|5|1|
|2|4|2|

DENSE_RANK() OVER()
返回分组内排名（排名相等不会留下空位,不支持自定义窗口）
WITH tmp AS
(
 SELECT 1 AS group_id, 1 AS col 
 UNION ALL SELECT 1 AS group_id,  3 AS col 
 UNION ALL SELECT 1 AS group_id,  3 AS col 
 UNION ALL SELECT 2 AS group_id,  4 AS col 
 UNION ALL SELECT 2 AS group_id,  5 AS col
)
SELECT group_id,
      col,
      DENSE_RANK() over(partition by group_id order by col desc) as r
FROM tmp;
返回结果为
|group_id|col|r|
|1|3|1|
|1|3|1|
|1|1|2|
|2|5|1|
|2|4|2|

CUME_DIST() OVER()
返回分组内累计分布值,即分组内小于(或者大于)等于当前值行数/分组内总行数
WITH tmp AS
(
 SELECT 1 AS group_id, 1 AS col 
 UNION ALL SELECT 1 AS group_id,  3 AS col 
 UNION ALL SELECT 1 AS group_id,  3 AS col 
 UNION ALL SELECT 2 AS group_id,  4 AS col 
 UNION ALL SELECT 2 AS group_id,  5 AS col
)
SELECT group_id,
      col,
      CUME_DIST() over(partition by group_id order by col asc) as d1,
      CUME_DIST() over(partition by group_id order by col desc) as d2
FROM tmp;

返回结果为
|group_id|col|d1|d2|
|1|3|1.0|0.6666666666666666|
|1|3|1.0|0.6666666666666666|
|1|1|0.3333333333333333|1.0|
|2|5|1.0|0.5|
|2|4|0.5|1.0|

PERCENT_RANK() OVER()
返回百分比排序值,即分组内当前行的RANK值-1/分组内总行数-1
WITH tmp AS
(
 SELECT 1 AS group_id, 1 AS col 
 UNION ALL SELECT 1 AS group_id,  3 AS col 
 UNION ALL SELECT 1 AS group_id,  3 AS col 
 UNION ALL SELECT 2 AS group_id,  4 AS col 
 UNION ALL SELECT 2 AS group_id,  5 AS col
)
SELECT group_id,
      col,
      RANK() over(partition by group_id order by col asc) as r1,
      PERCENT_RANK() over(partition by group_id order by col asc) as p1,
      RANK() over(partition by group_id order by col desc) as r2,
      PERCENT_RANK() over(partition by group_id order by col desc) as p2
FROM tmp;

返回结果为
|group_id|col|r1|p1|r2|p2|
|1|3|2|0.5|1|0.0|
|1|3|2|0.5|1|0.0|
|1|1|1|0.0|3|1.0|
|2|5|2|1.0|1|0.0|
|2|4|1|0.0|2|1.0|

NTILE(INTEGER x) OVER()
返回分区编号（将有序分区划分为x个组,称为bucket,并为分区中的每一行分配一个bucket编号）
WITH tmp AS
(
 SELECT 1 AS group_id, 1 AS col 
 UNION ALL SELECT 1 AS group_id,  3 AS col 
 UNION ALL SELECT 1 AS group_id,  3 AS col 
 UNION ALL SELECT 1 AS group_id,  3 AS col 
 UNION ALL SELECT 2 AS group_id,  4 AS col 
 UNION ALL SELECT 2 AS group_id,  5 AS col
)
SELECT group_id,
      col,
      NTILE(2) over(partition by group_id order by col asc) as bucket_id
FROM tmp;

返回结果为
|group_id|col|bucket_id|
|1|1|1|
|1|3|1|
|1|3|2|
|1|3|2|
|2|4|1|
|2|5|2|
OVER子句也支持聚合函数
Hive 2.1.0及之后版本,OVER子句也支持聚合函数,如：
WITH tmp AS
(
 SELECT 1 AS group_id, 1 AS col 
 UNION ALL SELECT 1 AS group_id,  3 AS col 
 UNION ALL SELECT 1 AS group_id,  3 AS col 
 UNION ALL SELECT 2 AS group_id,  4 AS col 
 UNION ALL SELECT 2 AS group_id,  5 AS col
)
SELECT group_id,
      RANK() over(order by sum(col) desc) as r
FROM tmp
group by group_id;
结果为
|group_id|r|
|2|1|
|1|2|

window clause 的另一种写法
将window子句写在from后面,在over后使用别名进行引用,如下:
WITH tmp AS
(
 SELECT 1 AS group_id, 1 AS col 
 UNION ALL SELECT 1 AS group_id,  2 AS col 
 UNION ALL SELECT 1 AS group_id,  3 AS col 
 UNION ALL SELECT 2 AS group_id,  4 AS col 
 UNION ALL SELECT 2 AS group_id,  4 AS col
)
SELECT group_id,
      col,
      AVG(col) over w1 as avg1,
      AVG(distinct col) over(partition by group_id order by col rows between CURRENT ROW and UNBOUNDED following) as avg2
FROM tmp
WINDOW w1 AS (partition by group_id order by col rows between CURRENT ROW and UNBOUNDED following);

结果为
|group_id|col|avg1|avg2|
|1|1|2.0|2.0|
|1|2|2.5|2.5|
|1|3|3.0|3.0|
|2|4|4.0|4.0|
|2|4|4.0|4.0|

WITH tmp AS
(
 SELECT 1 AS group_id, 1 AS col 
 UNION ALL SELECT 1 AS group_id,  2 AS col 
 UNION ALL SELECT 1 AS group_id,  3 AS col 
 UNION ALL SELECT 2 AS group_id,  4 AS col 
 UNION ALL SELECT 2 AS group_id,  4 AS col
)
SELECT group_id,
      col,
      AVG(col) over w1 as avg1,
      AVG(distinct col) over w2 as avg2
FROM tmp
WINDOW w1 AS (partition by group_id order by col rows between CURRENT ROW and UNBOUNDED following),
w2 AS (partition by group_id order by col rows between CURRENT ROW and UNBOUNDED following);

结果为
|group_id|col|avg1|avg2|
|1|1|2.0|2.0|
|1|2|2.5|2.5|
|1|3|3.0|3.0|
|2|4|4.0|4.0|
|2|4|4.0|4.0|

本文完.
Any suggestions and criticisms will be sincerely welcomed.
资料

https://blog.csdn.net/happyrocking/article/details/105369558
https://docs.aws.amazon.com/redshift/latest/dg/redshift
https://www.jianshu.com/p/3f3cf58472ca
https://www.cnblogs.com/hyunbar/p/13524855.html
https://blog.csdn.net/weixin_42307036/article/details/112381387

windy_bober
关注
  • 4
    点赞
  • 踩
  • 16
    收藏
    觉得还不错? 一键收藏
  • 3
    评论
专栏目录
【分析函数】一文遍识开窗函数
Axlsss的博客
12-28 1149
【数据库分析函数】一文遍识开窗函数
理解over()开窗函数
瓶子里的小地盘
11-30 7569
问题： 先建一个表数据： create table test_min_max ( YearID number, Line number ); insert into test_min_max(yearID,line) values(2016,1001); insert into test_min_max(yearID,line) values(2016,1002); in
开窗函数实例
qq_39366828的博客
05-20 256
–两行之间的数据进行比较 SELECT ID,FLAG,CHANGEDT FROM TEST; 原始结果： SELECT ID ,FLAG ,MAX(CHANGEDT) OVER(PARTITION BY ID ORDER BY CHANGEDT) START_DT ,MAX(CHANGEDT) OVER(PARTITION BY ID ORDER BY CHANGEDT ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) END_DT FROM TEST 之后结果： S
sql 开窗函数小结
vex123的专栏
04-15 733
1、相关函数说明 over() -- 指定窗口 窗口大小变化(partition by & order by ) current row -- 当前行 n preceding -- 往前1行 n following -- 往后1行 unbounded -- 起点 unbounded preceding -- 前面起点 unbounded followding -- 后面起点 lag(col,n,default) -- 往前n行数据 lead(col,n,default) -- 往
敲黑板啦！开窗函数你学会了吗
qq_39783601的博客
03-24 3740
特征分析与偏移分析什么是开窗函数？学习目标：1、累计计算窗口函数（1）sum(…) over(……)（2）avg(…) over(……)（3）语法总结：2、分区排序窗口函数3、分组排序窗口函数4、偏移分析窗口函数练习总结： 什么是开窗函数？ 开窗函数用于为行定义一个窗口（这里的窗口是指运算将要操作的行的集合），它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回...
hive窗口函数
01-09
hive窗口系列函数
hive窗口函数.docx
07-08
Hive 窗口函数详解 Hive 窗口函数是 Hive 中的一种强大的分析函数，它可以对数据进行分类、排序、聚合和排名等操作。下面将详细介绍 Hive 窗口函数的语法结构、分类、应用场景和实践练习。 窗口函数语法结构 窗口...
Hive 窗口函数
01-07
over()：指定分析函数工作的数据窗口的大小，这个数据窗口大小会随着行的变而变化。over跟在聚合函数后面，只对聚合函数生效。 current row：当前行 n preceding：往前n行数据 n following：往后n行数据 unbounded：...
Hive窗口函数，定义、demo
05-14
Hive窗口函数的定义，demo等
Hive自定义函数
01-20
Hive自定义函数 一. UDF(user defined function) 背景 系统内置函数无法解决所有的实际业务问题，需要开发者自己编写函数实现自身的业务实现诉求。 应用场景非常多，面临的业务不同导致个性化实现很多，故udf...
rows between unbounded preceding and current row等，unbounded ，preceding ，following，current row含义详解
卡奥斯道的博客
09-30 3万+
unbounded：无界限 preceding：从分区第一行头开始，则为 unbounded。 N为：相对当前行向前的偏移量 following ：与preceding相反,到该分区结束，则为 unbounded。N为：相对当前行向后的偏移量 current row：顾名思义，当前行，偏移量为0 例子： partition by order by asc/desc rows ...
Hive中使用over()实现累积求和和滑动求和
Vulpes corsac
06-08 4311
1.累积求和 实现累积求和，使用sum()函数配合over()来实现，具体的实现语法如下： sum(需要求和的列) over(partition by 分组列 order by 排序列 asc/desc)
HIVE SQL 聚合函数与 rows between / range between详解
qq_37771475的博客
12-08 1万+
一、rows between 与 range between 用法 1. 相关关键词解析 unbounded 无边界 preceding 往前 following 往后 unbounded preceding 往前所有行，即初始行 n preceding 往前n行 unbounded following 往后所有行，即末尾行 n following 往后n行 current row 当前行 语法 (ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDI
hive的分析函数
枣泥馅的博客
04-28 1609
hive的分析函数 1、rollup：group by 的增强函数 实现根据部门号、工作职位求工资总和，根据部门求工资总和，查所有人的工资总和 通过group by 语句的增加来实现： select deptno,job,sum(sal) from emp group by rollup(dep...
hive之窗口函数理解与实践
热门推荐
xiepeifeng的专栏
01-13 4万+
rt_data表存放了当天每半小时的店铺销售数据 ------------------------------------------------------------ 1.统计截止到当前时间段的店铺累计销售金额 select shop_id, stat_date, ordamt, sum(ordamt) over(partition by shop_id order by stat_da
hive 窗口函数，分析函数的一些理解
kuodannie1668的博客
03-30 6932
一.先解释什么是窗口函数首先，我们要知道什么是窗口子句：需要指定一个窗口的边界，语法是这样的：ROWS betweenCURRENT ROW | UNBOUNDED PRECEDING | [num] PRECEDING AND UNBOUNDED FOLLOWING | [num] FOLLOWING| CURRENTROW或RANGE between [num] PRECEDING AN...
hive：函数使用
琦彦
09-28 6407
hive函数使用 小技巧：测试函数的用法，可以专门准备一个专门的dual表 create table dual(x string); insert into table dual values(''); 其实：直接用常量来测试函数即可 select substr("abcdefg",1,3); substr，在数据库中脚标是从1开始； hive的所有函数手册： https:...
OVER(PARTITION BY)函数用法
weixin_34273046的博客
10-26 3749
 2010年10月26日OVER(PARTITION BY)函数介绍开窗函数               Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。      开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：1：over后的写法：     ...
mysql开窗函数有哪些_mysql开窗函数
weixin_29955689的博客
01-20 2378
开窗函数：它可以理解为记录集合，开窗函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。开窗函数的本质还是聚合运算，只不过它更具灵活性，它对数据的每一行，都使用与该行相关的行进行计算并返回计算结果。开窗函数和普通聚合函数的区别聚合...
csdn hive 窗口函数
最新发布
01-13
CSND Hive窗口函数是Hive数据库中用于处理分组数据的一种强大工具。在数据仓库中，通常需要对数据进行分组计算和分析，而Hive窗口函数可以帮助我们实现这些操作。窗口函数可以在数据分组的基础上对分组数据进行排序...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值