怎么在group by分组后获取其他列的数据（窗口函数）

一.窗口函数有什么用

聚合函数对结果集进行计算并且通常只返回一行，只会返回聚合的结果和聚合的那列。如果需要返回聚合列之外的列的结果则可以使用窗口函数。窗口函数也是基于结果集计算，与聚合函数不同的是窗口函数会把计算后的结果合并到原来的结果集上，返回多行并保持与结果集相同的行数。

可以使用窗口函数的业务场景：

排名问题：比如每个部门按业绩来排名
topN问题：比如找出每个部门排名前N的员工进行奖励

二.窗口函数语法

除了pg内置的窗口函数rank()，dense_rank()， row_numbe()，lag()等，聚合函数或自定函数接OVER也可作为窗口函数。

窗口函数需要放在SELECT子句中，SQL中语法如下：

<窗口函数> OVER(PARTITION BY <分组列名> ORDER BY <排序列名>)

SQLAlchemy 下可以这样使用：

三.窗口函数怎么用

1.排名问题
如下学生成绩表（stu_score）：

stu_id	class	score
0001	1	90
0002	1	65
0003	2	70
0004	1	65
0005	2	80
0006	3	75
0007	3	95

使用窗口函数取每个班级内按成绩的排名：

SELECT 
	*, 
  RANK() OVER(PARTITION BY class ORDER BY score DESC) AS ranking 
 FROM stu_score;

结果为：

stu_id	class	score	ranking
0001	1	90	1
0002	1	65	2
0004	1	65	2
0005	2	80	1
0003	2	70	2
0007	3	95	1
0006	3	75	2

2.topN问题
取学生成绩表每个班成绩排名第一的学生：

SELECT * 
FROM
	(SELECT *, ROW_NUMBER() OVER (
    PARTITION BY class ORDER BY score DESC
  ) AS ranking FROM stu_score) t1 
WHERE ranking = 1;

结果：

stu_id	class	score	ranking
0001	1	90	1
0005	2	80	1
0007	3	95	1

3.举一个实际中遇到的问题是怎么用窗口函数解决的
场景是，现在有一个表有user_id（用户id）、log_date（记录日期）和其他信息，需要把一批用户最新的记录日期的那条记录取出来并且能获取其他信息。
有内味了，这不就是topN问题。具体思路就是按user_id分组，log_date排倒叙取第一条。解决如下：

SELECT * 
FROM
	(SELECT *, ROW_NUMBER() OVER (
    PARTITION BY user_id ORDER BY log_date DESC
  ) AS log_date_rank FROM TABLE) t1 
WHERE log_date_rank = 1;

3.rank、dense_rank、row_number区别

rank 是正常排序，当并列时行号重复，并且产生行号间隙。
dense_rank 是紧密排序，当并列时行号重复，但是不产生间隙，依然按自然数顺延。
row_number 是行数排序，不考虑并列的情况，按照行数依次排序。

四.总结

1）窗口函数同时具有分组（partition by）和排序（order by）的功能。
2）不会改变结果集的行数，可以取到所有列的结果。