详解窗户函数(分析工具)的运用与操作
最编程
2024-07-23 07:33:15
...
开窗函数
简介
- 开窗函数:在开窗函数出现之前存在着很多用 SQL 语句很难解决的问题,很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题,在 2003 年 ISO SQL 标准加入了开窗函数,开窗函数的使用使得这些经典的难题可以被轻松的解决。目前在 MSSQLServer、Oracle、DB2 等主流数据库中都提供了对开窗函数的支持,MySQL8.0支持。 5.7 --> 8.0
- 开窗函数简介:与聚合函数一样,开窗函数也是对行集组进行聚合计算,但是它不像普通聚合函数那样每组只返回一个值,开窗函数可以为每组返回多个值,因为开窗函数所执行聚合计算的行集组是窗口。在 ISO SQL 规定了这样的函数为开窗函数,在 Oracle 中则被称为分析函数。
语法
开窗函数格式: 函数名(列) OVER(选项)
OVER 关键字表示把函数当成开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做开窗函数,使用 OVER 关键字来区分这两种用法。
如果 OVER 关键字后的括号中的选项为空,则开窗函数会对结果集中的所有行进行聚合运算。
PARTITION BY 子句:
开窗函数的 OVER 关键字后括号中的可以使用 PARTITION BY 子句来定义行的分区来供进行聚合计算。与 GROUP BY 子句不同,PARTITION BY 子句创建的分区是独立于结果集的,创建的分区只是供进行聚合计算的,而且不同的开窗函数所创建的分区也不互相影响
ORDER BY子句:
开窗函数中可以在OVER关键字后的选项中使用ORDER BY子句来指定排序规则,而且有的开窗函数还要求必须指定排序规则。使用ORDER BY子句可以对结果集按照指定的排序规则进行排序,并且在一个指定的范围内进行聚合运算
常用开窗函数
row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
count() over(partition by ... order by ...)
max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
sum() over(partition by ... order by ...)
avg() over(partition by ... order by ...)
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
lag() over(partition by ... order by ...)
lead() over(partition by ... order by ...)
HQL中(目前只知道hql中可替换)
partition by … order by …
等价于
distribute by … sort by …
建表
排序开窗函数
row_number() over() 、Rank() over() 和 DENSE_RANK () over()
- 第一种:不写任何条件,只作排序使用
select mid,cid,date,score,
rank() over() a,
dense_rank() over() b,
row_number() over() c
from test1
运行结果:
- 第二种:分组排序(默认升序)
select mid,cid,date,score,
rank() over(partition by mid,cid order by score desc) a,
dense_rank() over(partition by mid,cid order by score desc) b,
row_number() over(partition by mid,cid order by score desc) c
from test1
运行结果:
区别图中明显可以看出,自己总结
聚合开窗函数
sum() over()
- 不加任何条件 求和该字段所有值
select mid,cid,date,score,sum(score)over()
from test1
- 添加group by
select mid,cid,date,score,sum(score)over(partition by mid)
from test1
- 添加order by排序
select mid,cid,date,score,sum(score)over(partition by mid order by date)
from test1
每次求和分组内前几个的值 常用于求单个用户对单个商品点击量的日累计点击量等。
max()over()
count() over()
max() over()
min() over()
avg() over()
这几个用法类似
常用的开窗函数还有
first_value() over(partition by … order by …)
last_value() over(partition by … order by …)
lag() over(partition by … order by …)
有兴趣自己去测试一下吧!
写在最后: 吴老狗是真的dog.
上一篇: 深入理解Hive的窗口函数高级应用
下一篇: 窗户函数介绍
推荐阅读
-
透彻理解SAS:玩转数据操作、分析升级与实战商业运用——第三部分:操控数据集中的观察记录
-
【2022新手指南】Java编程进阶之路 - 六、技术架构篇 ### MySQL索引底层解析与优化实战 - 你会讲解MySQL索引的数据结构吗?性能调优技巧知多少? - Redis深度揭秘:你知道多少?从基础到哨兵、主从复制全梳理 - Redis持久化及哨兵模式详解,还有集群搭建和Leader选举黑箱打开 - Zookeeper是个啥?特性和应用场景大公开 - ZooKeeper集群搭建攻略及 Leader选举、读写一致性、共享锁实现细节 - 探究ZooKeeper中的Leader选举机制及其在分布式环境中的作用 - Zab协议深入剖析:原理、功能与在Zookeeper中的核心地位 - RabbitMQ全方位解读:工作模式、消费限流、可靠投递与配置策略 - 设计者视角:RabbitMQ过期时间、死信队列与延时队列实践指南 - RocketMQ特性和应用场景揭示:理解其精髓与差异化优势 - Kafka详细介绍:特性及广泛应用于实时数据处理的场景解析 - ElasticSearch实力揭秘:特性概述与作为搜索引擎的广泛应用 - MongoDB认知升级:非关系型数据库的优势阐述,安装与使用实战教学 - BIO/NIO/AIO网络模型对比:掌握它们的区别与在网络编程中的实际应用 - Netty带你飞:理解其超快速度背后的秘密,包括线程模型分析 - 网络通信黑科技:Netty编解码原理与常用编解码器的应用,Protostuff实战演示 - 解密Netty粘包与拆包现象,怎样有效应对这一常见问题 - 自定义Netty心跳检测机制,轻松调整检测间隔时间的艺术 - Dubbo轻骑兵介绍:核心特性概览,服务降级实战与其实现益处 - Dubbo三大神器解读:本地存根与本地伪装的实战运用与优势呈现 ----------------------- 七、结语与回顾
-
C++教程:STL中的queue基础讲解 - 包含队列操作详解(push与pop)、其他成员函数介绍及实战运用示例2 - queue的具体实践应用
-
如何快速上手与操作SQLE数据库审核工具的部署与运用指南
-
如何在Oracle中运用分析函数RANK、ROW_NUMBER和LAG的实际操作指南
-
SQL中的滑动窗口函数:Lag()与Lead()操作详解
-
窗户函数的三种实用操作方法,清晰图解与文字说明
-
窗户函数应用详解:结合分组(partition by)与排序(order by)的实践操作
-
详解窗户函数(分析工具)的运用与操作
-
详解窗户函数:ROWS模式与RANGE模式的图形化呈现及其应用步骤