SQL高级特性 - 窗口函数

本文参考：

• PostgreSQL 9.4.4 中文手册
• 窗口函数_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)

---

说点有的没的：

最近心态其实不太好，一方面参加提前批至今没有得到一个面试机会让我非常失落，一方面会想起自己高中、大学乃至研一浪费的时间，顿时感到人生每个阶段都在为上个阶段埋单。也有鼓励自己继续的时候，也有突然落泪想要逃避的时候。虽然常常说自己是个不爱后悔的人，但我确实不能不对十年前的自己说句对不起，那时候的自己好像已成为了个遥不可及的梦，和她一同留在旧时光里的是我的信心和骄傲。

最近在听艾热。

别老想着睡，有多人比你还疲惫。你的痛都不算痛，你的累就不算累。

——艾热《都是小事儿》

要努力啦，要对十年前的自己说，“你看，我也很棒呢。”