sql常见开窗函数示例
2021/7/16 19:08:45
本文主要是介绍sql常见开窗函数示例,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
1、开窗函数基本语法:
函数名()over(partition by 分区字段 order by 排序字段)
2.1、示例数据:
2.2、示例sql:
create table kchs( ID int, product varchar(50), amount decimal(18,2) ) insert into kchs values (1,'苹果',100); insert into kchs values (2,'苹果',200); insert into kchs values (3,'苹果',300); insert into kchs values (4,'香蕉',450); insert into kchs values (5,'香蕉',550); insert into kchs values (6,'香蕉',650); insert into kchs values (7,'西瓜',750); insert into kchs values (8,'西瓜',850); insert into kchs values (9,'西瓜',950); insert into kchs values (10,'西瓜',950); insert into kchs values (11,'西瓜',1050); -- sum开窗,用于累计求和 select *,sum(amount)over(partition by product order by id) as sum_over from kchs order by id; -- count开窗,用于统计当前分区当前行及以前行的纪录数 select *,count(*)over(partition by product order by id) as count_over from kchs order by id; -- max开窗,用于统计当前分区当前行及以前行的最大值 select *,max(amount)over(partition by product order by id) as max_over from kchs order by id; -- min开窗,用于统计当前分区当前行及以前行的最小值 select *,min(amount)over(partition by product order by id) as min_over from kchs order by id; -- avg开窗,用于统计当前分区当前行及以前行的平均数 select *,avg(amount)over(partition by product order by id) as avg_over from kchs order by id; -- lag开窗,用于获取往前偏移N行的数据 select *,lag(amount,1,null)over(partition by product order by id) as lag_over from kchs order by id; -- lead开窗,用于获取往后偏移N行的数据 select *,lead(amount,1,null)over(partition by product order by id) as lead_over from kchs order by id; -- rank开窗,用于计算当前分区按排序规则的排名,会并列排名,排名不连续,比如并列第三名有两个,那么就不会有第四名 select *,rank()over(partition by product order by amount) as rk from kchs order by id; -- dense_rank开窗,用于计算当前分区按排序规则的排名,会并列排名,排名连续,比如并列第三名有两个,那么仍然会有第四名 select *,dense_rank()over(partition by product order by amount) as drk from kchs order by id; -- row_number开窗,用于计算当前分区按排序规则的排序,不会并列排序 select *,row_number()over(partition by product order by amount) as rn from kchs order by id;View Code
3.1、sum开窗:
-- sum开窗,用于累计求和 select *,sum(amount)over(partition by product order by id) as sum_over from kchs order by id;
3.2、count开窗:
-- count开窗,用于统计当前分区当前行及以前行的纪录数 select *,count(*)over(partition by product order by id) as count_over from kchs order by id;
3.3、max开窗:
-- max开窗,用于统计当前分区当前行及以前行的最大值 select *,max(amount)over(partition by product order by id) as max_over from kchs order by id;
3.4、min开窗:
-- min开窗,用于统计当前分区当前行及以前行的最小值 select *,min(amount)over(partition by product order by id) as min_over from kchs order by id;
3.5、avg开窗:
-- avg开窗,用于统计当前分区当前行及以前行的平均数 select *,avg(amount)over(partition by product order by id) as avg_over from kchs order by id;
3.6、lag开窗:
-- lag开窗,用于获取往前偏移N行的数据 select *,lag(amount,1,null)over(partition by product order by id) as lag_over from kchs order by id;
3.7、lead开窗:
-- lead开窗,用于获取往后偏移N行的数据 select *,lead(amount,1,null)over(partition by product order by id) as lead_over from kchs order by id;
3.8、rank开窗:
-- rank开窗,用于计算当前分区按排序规则的排名,会并列排名,排名不连续,比如并列第三名有两个,那么就不会有第四名 select *,rank()over(partition by product order by amount) as rk from kchs order by id;
3.9、dense_rank开窗:
-- dense_rank开窗,用于计算当前分区按排序规则的排名,会并列排名,排名连续,比如并列第三名有两个,那么仍然会有第四名 select *,dense_rank()over(partition by product order by amount) as drk from kchs order by id;
3.10、row_number开窗:
-- row_number开窗,用于计算当前分区按排序规则的排序,不会并列排序 select *,row_number()over(partition by product order by amount) as rn from kchs order by id;
这篇关于sql常见开窗函数示例的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-05-09“2024鸿蒙零基础快速实战-仿抖音App开发(ArkTS版)”实战课程已上线
- 2024-05-09聊聊如何通过arthas-tunnel-server来远程管理所有需要arthas监控的应用
- 2024-05-09log4j2这么配就对了
- 2024-05-09nginx修改Content-Type
- 2024-05-09Redis多数据源,看这篇就够了
- 2024-05-09Google Chrome驱动程序 124.0.6367.62(正式版本)去哪下载?
- 2024-05-09有没有大佬知道这种数据应该怎么抓取呀?
- 2024-05-09这种运行结果里的10.100000001,怎么能最快改成10.1?
- 2024-05-09企业src漏洞挖掘-有意思的命令执行
- 2024-05-08阿里云域名注册流程,分享给第一次购买域名的新手站长!