SQL高级特性 - 窗口函数
本文参考:
定义
窗口函数在和当前行相关的一组表行上执行计算。 这相当于一个可以由聚合函数完成的计算类型。但不同于常规的聚合函数, 使用的窗口函数不会导致行被分组到一个单一的输出行;行保留其独立的身份。 在后台,窗口函数能够访问的不止查询结果的当前行。
简单来说,窗口函数用于跨行计算。
语法
-- 在窗口内,按照key2分类,按照value倒序排列,返回在窗口内的序好
SELECT key1, key2, value,
rank() OVER (PARTITION BY key2
ORDER BY value DESC) AS rnk
FROM orders
ORDER BY key1,rnk
-- PARTITION BY 以... ...划分
-- rank()是一个聚合函数。
窗口函数中使用的特殊聚合函数
- rank函数按照由ORDER BY子句定义的顺序, 在当前行的分区里为每个不同的ORDER BY值产生了一个数值排名。 rank 不需要明确的参数,因为它的行为完全取决于OVER子句。
- 窗口函数的调用总是包含一个OVER子句,后面直接跟着窗口函数的名称和参数。 这是它在语法上区别于普通函数或聚合功能的地方。 OVER子句决定如何将查询的行进行拆分以便给窗口函数处理。 OVER子句内的PARTITION BY列表指定将行划分成组或分区, 组或分区共享相同的PARTITION BY表达式的值。窗口函数OVER内的ORDER BY来控制行的顺序。
- 我们已经看到了,如果行排序并不重要,ORDER BY可以省略。 在只有一个包含了所有行的分区情况下,也可以省略PARTITION BY。
- 对于每一行,有在其分区范围内的行集, 又称为它的window frame。许多(但不是全部)窗口函数,只作用于window frame中的行上, 而不是整个分区。
- OVER中的ORDER BY限制了从分区开始到当前行的所有行,以及等于当前行的所有行;如果省略ORDER BY则默认包含分区中的所有行。
例子
-- 在整个公司的人员中,获取每个人的薪水在部门内排名
SELECT department, persionId, sallary,
rank() OVER(PARTITION BY department
ORDER BY sallary DESC) as sallary_rank
FROM sallaries
ORDER NY department,sallary_rank
-- 在整个公司的人员中,获取每个人的薪水在部门内的占比
SELECT department, persionId,
sallary *1.0 / sum(sallary) OVER(PARTITION BY department) AS sallary_percentage
FROM sallaries
-- 按天统计,获取每天UV相对前一天的增长情况
SELECT day, uv,
uv *1.0 /(lag(uv,1,0) OVER() ) AS diff_percentage
FROM (
SELECT approx_distinct(ip) AS uv, date_trunc('day',__time__) AS day
FROM log
GROUP BY day
ORDER BY day ASC)
说点有的没的:
最近心态其实不太好,一方面参加提前批至今没有得到一个面试机会让我非常失落,一方面会想起自己高中、大学乃至研一浪费的时间,顿时感到人生每个阶段都在为上个阶段埋单。也有鼓励自己继续的时候,也有突然落泪想要逃避的时候。虽然常常说自己是个不爱后悔的人,但我确实不能不对十年前的自己说句对不起,那时候的自己好像已成为了个遥不可及的梦,和她一同留在旧时光里的是我的信心和骄傲。
最近在听艾热。
别老想着睡,有多人比你还疲惫。你的痛都不算痛,你的累就不算累。
——艾热《都是小事儿》
要努力啦,要对十年前的自己说,“你看,我也很棒呢。”