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;
2024最新激活全家桶教程，稳定运行到2099年，请移步至置顶文章：https://sigusoft.com/99576.html
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。 文章由激活谷谷主-小谷整理,转载请注明出处：https://sigusoft.com/91201.html
