sql窗口函数

最新推荐文章于 2024-08-03 23:10:08 发布

肆泰梓

最新推荐文章于 2024-08-03 23:10:08 发布

阅读量355

点赞数

分类专栏： Hive 文章标签： hive

本文链接：https://blog.csdn.net/jq_123321/article/details/109226454

版权

本文介绍了SQL中的窗口函数，重点讲解了SUM, AVG, COUNT函数在配合ROWS BETWEEN子句时的应用，并探讨了RANK(), ROW_NUMBER(), DENSE_RANK()三种排名函数的区别和使用场景。" 126141654,9271668,Python2.7配置RobotFramework自动化测试环境指南,"['测试环境', 'RobotFramebook', '自动化测试']

摘要由CSDN通过智能技术生成

一、SUM,AVG,COUNT函数

关键是理解 ROWS BETWEEN 含义,也叫做window子句：
PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：无边界，
UNBOUNDED PRECEDING 表示从最前面的起点开始，
UNBOUNDED FOLLOWING：表示到最后面的终点-
1、叠加求sum必须加order by
不加orde by 结果是整个分区的sum

SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行，结果同pv1 
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv3,   --当前行+往前3行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv4,    --当前行+往前3行+往后1行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv5   ---