hive 常用开窗函数整理

最新推荐文章于 2025-11-29 20:33:13 发布

原创最新推荐文章于 2025-11-29 20:33:13 发布 · 887 阅读

1 ·

CC 4.0 BY-SA版权

Hive知识点专栏收录该内容

2 篇文章

订阅专栏

本文深入讲解SQL中的窗口函数，包括累加求和(sum)，行号(row_number)，排名(rank和dense_rank)，以及前后值(lag和lead)的使用方法。通过具体示例，展示了如何在数据查询中利用这些函数进行复杂的数据分析。

1、sum()over(partition by ...) (累加求和)

select cookieid,create_time,pv,
sum(pv) over(partition by cookieid order by createtime) as pv1, -- 默认为从起点到当前行
sum(pv) over(partition by cookieid order by create_time rows between unbounded preceding and current row) as pv2, --从起点到当前行，结果同pv1
sum(pv) over(partition by cookieid order by create_time rows between 3 preceding and current row) as pv3, --当前行+往前3行
sum(pv) over(partition by cookieid order by create_time rows between 3 preceding and 1 following) as pv4, --当前行+往前3行+往后1行
sum(pv) over(partition by cookieid order by create_time rows between current row and unbounded following) as pv5 ---当前行+往后所有行
from table_test;

注释rows between 含义,也叫做window子句：
preceding：往前
following：往后
current row：当前行
unbounded：无边界，unbounded preceding 表示从最前面的起点开始， unbounded following：表示到最后面的终点
–其他avg，min，max，和sum用法一样

2、row_number()over(partition by ...) 按顺序排序 (排序eg:1,2,3,4,5,6)

select cookieid,row_number()over(partition by cookieid order by create_time desc ) as rn from table_test;

3、rank()over(partition by ..,) (排序eg:1,2,2,4)
dense_rank()over(partition by ..,) (排序eg:1,2,2,3,3,4,5)

select cookieid,createtime,pv,
rank() over(partition by cookieid order by pv desc) as rank1,
dense_rank() over(partition by cookieid order by pv desc) as d_rank2,
row_number() over(partition by cookieid order by pv desc) as rn3
from table_test;

4、lag 和 lead 函数
lag(col,n,default) 用于统计窗口内往上第n行值
第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为null时候，取默认值，如不指定，则为null）

select cookieid,create_time,pv,
row_number() over(partition by cookieid order by createtime) as rn,
lag(create_time,1,'1970-01-01') over(partition by cookieid order by createtime) as lag1, --根据cookieid往上一条记录的create_time
lag(create_time,2) over(partition by cookieid order by createtime) as lag2 --根据cookieid往上弟二条记录的create_time
from table_test;

lead 函数则与 lag 相反：
lead(col,n,default) 用于统计窗口内往下第n行值
第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为null时候，取默认值，如不指定，则为null）

5、cume_dist
percent_rank
first_value 和 last_value