sql开窗函数详解_sql笔试题及答案

2024年 8月 2日下午9:08 • 激活谷笔记

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;

sql开窗函数详解_sql笔试题及答案

相关推荐