DROP TABLE IF EXISTS demo ;
CREATE TABLE IF NOT EXISTS demo
(
id INT COMMENT 'id'
,name STRING COMMENT '名字'
,group_id INT COMMENT '组id'
,group_name STRING COMMENT '组名'
,age INT COMMENT '年龄'
)
;
INSERT OVERWRITE TABLE HZYC_BI_dev.demo
SELECT '1'
,'张三'
,'1'
,'A'
,'10'
UNION ALL
SELECT '2'
,'李四'
,'1'
,'A'
,'18'
UNION ALL
SELECT '3'
,'王五'
,'2'
,'B'
,'20'
UNION ALL
SELECT '4'
,'TOM'
,'2'
,'B'
,'30'
UNION ALL
SELECT '5'
,'小李子'
,'2'
,'B'
,'15'
UNION ALL
SELECT '6'
,'狗剩'
,'2'
,'B'
,'20'
;
--组内排名
SELECT *
,RANK() OVER(PARTITION BY group_id,group_name ORDER BY age) AS rank
FROM HZYC_BI_dev.demo
;
--组内排名
SELECT *
,ROW_NUMBER () OVER(PARTITION BY group_id,group_name ORDER BY age) AS ROW_NUMBER
FROM HZYC_BI_dev.demo
;
--组内排名(并列)
SELECT *
,DENSE_RANK() OVER(PARTITION BY group_id,group_name ORDER BY age) AS DENSE_RANK
FROM HZYC_BI_dev.demo
;
--组内取中位数
SELECT *
,median(age) OVER(PARTITION BY group_id,group_name) AS median
FROM HZYC_BI_dev.demo
;
--组内排序后取组内前一个值
SELECT *
,lag(age) OVER(PARTITION BY group_id,group_name ORDER BY age) AS lag
FROM HZYC_BI_dev.demo
;
--组内排序后取组内后一个值
SELECT *
,lead(age) OVER(PARTITION BY group_id,group_name ORDER BY age) AS lead
FROM HZYC_BI_dev.demo
;
--组内排序后取第一个值
SELECT *
,first_value(age) OVER(PARTITION BY group_id,group_name ORDER BY age) AS first_value
FROM HZYC_BI_dev.demo
;
--组内排序后取最后一个值
SELECT *
,last_value(age) OVER(PARTITION BY group_id,group_name ORDER BY age) AS last_value
FROM HZYC_BI_dev.demo
;
hive常用窗口函数
最新推荐文章于 2023-12-15 18:44:59 发布