SQL中分析函数first_value(),last_value,sum() over(partition by...)详解

SQL 专栏收录该内容
6 篇文章 6 订阅

SQL中分析函数first_value(),last_value,sum() over(partition by…)详解

知乎主页地址

首先,生成有一张原始表scores_id表示学生id,c_id表示课程id,s_core表示最后课程的成绩,表结构数据如下所示:
Score表

首先,按照课程c_id进行分组,按照成绩s_core进行排序

select * ,row_number() over (partition by c_id order by s_score)rank_showfrom score

运行结果:

在这里插入图片描述
也就是说按照 c_id分组,s_score从小到大进行排序的话,s_id为06号的排第一,04号的排第二,以此类推。

1:FIRST_VALUE()

接着查看FIRST_VALUE()和LAST_VALUE(),它们的字面意思很直观了,就是取首尾记录值。同样sum()也应该是取对应分组后的值,写一段SQL测试一下:

在上面代码的基础上,我们查看分组排序后的结果中,第一个,s_id值是多少

select * ,first_value(s_id) over (partition by c_id order by s_score)first_show from score;

结果显示:

在这里插入图片描述

也就是说,得到的结果是对应c_id的首个s_id的值。

2:LAST_VALUE()

接下来再看last_value(),这里的运行结果很可能与刚开始设想的结果不同。

select * ,last_value(s_id) over (partition by c_id order by s_score)last_show from score;

运行结果:

在这里插入图片描述
正常情况下,last_value()值,学生s_id为06的last_show,不应该是03吗?

其实可以这样去理解:last_value()默认统计范围是 rows between unbounded preceding and current row,也就是取当前行数据与当前行之前的数据的比较。

那么,如何像**first_value()**那样直接在每行数据中显示最后的那个数据呢?

在order by 条件的后面加上语句:rows between unbounded preceding and unbounded following

select * ,last_value(s_id) over (partition by c_id order by s_score rows between unbounded preceding and unbounded following
)last_show from score;

运行结果:
在这里插入图片描述

这样就在每一行结果中直接显示最后的结果了。

3:sum() over()

sum是对所要求的条件进行求和的意思,还是在上面的基础上,进行测试。

输入代码:

select * ,sum(s_id) over (partition by c_id order by s_score)sum_show from score;

运行结果:
在这里插入图片描述

这里好像跟我们预先的又不一样,发现跟上面的**last_value()**方式相同,也就是根据当前行与当前行之前的历史数据进行求和。

如何像**first_value()**那样直接在每行数据中显示最后的那个数据呢?还是上面那样的方法:在order by 条件的后面加上语句:rows between unbounded preceding and unbounded following

输入代码:

select * ,sum(s_id) over (partition by c_id order by s_score rows between unbounded preceding and unbounded following)sum_show from score;

运行结果:

在这里插入图片描述

结果与我们设想的一样!

4:总结

1:first_value()的结果容易理解,直接在结果的所有行记录中输出同一个满足条件的首个记录;

2:last_value()默认统计范围是 rows between unbounded preceding and current row,也就是取当前行数据与当前行之前的数据的比较,如果需要在结果的所有行记录中输出同一个满足条件的最后一个记录,在order by 条件的后面加上语句:rows between unbounded preceding and unbounded following

3:sum( ) over(…):默认统计范围是 rows between unbounded preceding and current row,也就是取当前行数据与当前行之前的数据的求和,如果需要在结果的所有行记录中输出同一个满足条件的所有记录之和,在order by 条件的后面加上语句:rows between unbounded preceding and unbounded following

知乎主页地址

评论 4 您还未登录,请先 登录 后发表或查看评论
©️2022 CSDN 皮肤主题:技术黑板 设计师:CSDN官方博客 返回首页

打赏作者

奥卡姆的剃刀

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值