终极总结window functions(附上测试sql)

以下都是我自己总结归纳的，都加上了reference link。

先在电脑上安装psql，这样你可以测试下面的code
这个链接可以教你在mac上安装，或者直接google。https://medium.com/@Umesh_Kafle/ ... mac-os-87fa98a6814d
[b]

首先为什么我们要使用window functions？[/b]
[reference: https://codingsight.com/grouping-data-using-the-over-and-partition-by-functions/]

先看一个例子，假设我们有这样一个table
  name  | gender | age | total_score | id
--------+--------+-----+-------------+----
Jolly  | Female |  20 |         500 |  1
Jon    | Male   |  22 |         545 |  2
Sara   | Female |  25 |         600 |  3
Laura  | Female |  18 |         400 |  4
Alan   | Male   |  20 |         500 |  5
Kate   | Female |  22 |         500 |  6
Joseph | Male   |  18 |         643 |  7
Mice   | Male   |  23 |         543 |  8
Wise   | Male   |  21 |         499 |  9
Elis   | Female |  27 |         400 | 10


你可以自己在psql创建这个table

CREATE TABLE student

(

    name VARCHAR(50) NOT NULL,

    gender VARCHAR(50) NOT NULL,

    age INT NOT NULL,

    total_score INT NOT NULL

);

INSERT INTO student

VALUES ('Jolly', 'Female', 20, 500),

('Jon', 'Male', 22, 545),

('Sara', 'Female', 25, 600),

('Laura', 'Female', 18, 400),

('Alan', 'Male', 20, 500),

('Kate', 'Female', 22, 500),

('Joseph', 'Male', 18, 643),

('Mice', 'Male', 23, 543),

('Wise', 'Male', 21, 499),

('Elis', 'Female', 27, 400);

然后我们想得到下面这样的table

  name  | gender | total_students |     average_age     | total_score
--------+--------+----------------+---------------------+-------------
Elis   | Female |              5 | 22.4000000000000000 |        2400
Sara   | Female |              5 | 22.4000000000000000 |        2400
Laura  | Female |              5 | 22.4000000000000000 |        2400
Kate   | Female |              5 | 22.4000000000000000 |        2400
Jolly  | Female |              5 | 22.4000000000000000 |        2400
Wise   | Male   |              5 | 20.8000000000000000 |        2730
Jon    | Male   |              5 | 20.8000000000000000 |        2730
Joseph | Male   |              5 | 20.8000000000000000 |        2730
Mice   | Male   |              5 | 20.8000000000000000 |        2730
Alan   | Male   |              5 | 20.8000000000000000 |        2730


这个时候可以看到行数没有改变，但是增加了三个总结的columns all partitioned by the gender

if we do it through aggregation functions

SELECT gender, count(gender) AS Total_Students, round(AVG(age)::numeric, 2) as Average_Age, SUM(total_score) as Total_Score

FROM student

GROUP BY gender

This would only return two rows, one row for female and another and we would have to join the table back

SELECT id, name, Aggregation.gender, Aggregation.Total_students, Aggregation.Average_Age, Aggregation.Total_Score

FROM student

INNER JOIN

(SELECT gender, count(gender) AS Total_students, round(AVG(age)::numeric, 2) AS Average_Age, SUM(total_score) AS Total_Score

FROM student

GROUP BY gender) AS Aggregation

on Aggregation.gender = student.gender

ORDER BY gender, name;

An easy way to do it is through window functions

SELECT id, name, gender,

COUNT(gender) OVER (PARTITION BY gender) AS Total_students,

ROUND(AVG(age) OVER (PARTITION BY gender)::numeric, 2) AS Average_Age,

SUM(total_score) OVER (PARTITION BY gender) AS Total_Score

FROM student;

window functions 最好的好处是，不会改变行数！！！！


再看一个leetocde的SQL: Highest Salary in Departments
Classic Highest Salary in Departments

https://leetcode.com/problems/department-highest-salary/solution/

http://postgresguide.com/tips/window.html

https://medium.com/statuscode/the-many-faces-of-distinct-in-postgresql-c52490de5954

Leetcode MySQL Solution

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
.--WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
        )
;

| Department | Employee | Salary |
|------------|----------|--------|
| Sales      | Henry    | 80000  |
| IT         | Max      | 90000  |

Postgresql with Window Functions

SELECT *
FROM (
    SELECT
           last_name,
           salary,
           department,
           rank() OVER (
             PARTITION BY department
             ORDER BY salary
             DESC
            ) as rank
    FROM employees) sub_query
WHERE rank = 1;

last_name    salary   department    rank
Jones        45000    Accounting    1
Smith        55000    Sales         1
Johnson      40000    Marketing     1


如果有人看我再继续写下面的 ：P 这个文本编辑器有点点难用。。。


什么时候可以不用Partitioned By 和 Order By，分别的影响是？


ROW_NUMBER, RANK, DENSE_RANK？



LEAD, LAG？



What is Rolling SUM, Rolling Average?

Example on Monthy Over Month Retention, Growth

什么时候可以不用Partitioned By 和 Order By，分别的影响是？


没有 partitioned by


整个table都会被当作同一个window，还是用上面的那个student table

select *, avg(age) over() from student;

  name  | gender | age | total_score | id |         avg
--------+--------+-----+-------------+----+---------------------
Jolly  | Female |  20 |         500 |  1 | 21.6000000000000000
Jon    | Male   |  22 |         545 |  2 | 21.6000000000000000
Sara   | Female |  25 |         600 |  3 | 21.6000000000000000
Laura  | Female |  18 |         400 |  4 | 21.6000000000000000
Alan   | Male   |  20 |         500 |  5 | 21.6000000000000000
Kate   | Female |  22 |         500 |  6 | 21.6000000000000000
Joseph | Male   |  18 |         643 |  7 | 21.6000000000000000
Mice   | Male   |  23 |         543 |  8 | 21.6000000000000000
Wise   | Male   |  21 |         499 |  9 | 21.6000000000000000
Elis   | Female |  27 |         400 | 10 | 21.6000000000000000

可以看到我们加了一个新的column，这个average就是整个table 的 average

没有 Order By

在我们做 sum， avg， max， min 的时候一般都不加order by 除非是为了做rolling sum，rolling avg, rolling max, rolling min。之后在rolling的部分补充。

order by需要的是对应排序的比如 row_number, rank, dense_rank, lag, lead 如果他们没有order by会怎么样呢？
这个时候会有保持原有排序对table进行处理。rank, dense_rank 只会出现1。

看看例子
select *, row_number() over() from student;
.   name  | gender | age | total_score | id | row_number
--------+--------+-----+-------------+----+------------
Jolly  | Female |  20 |         500 |  1 |          1
Jon    | Male   |  22 |         545 |  2 |          2
Sara   | Female |  25 |         600 |  3 |          3
Laura  | Female |  18 |         400 |  4 |          4
Alan   | Male   |  20 |         500 |  5 |          5
Kate   | Female |  22 |         500 |  6 |          6
Joseph | Male   |  18 |         643 |  7 |          7
Mice   | Male   |  23 |         543 |  8 |          8
Wise   | Male   |  21 |         499 |  9 |          9
Elis   | Female |  27 |         400 | 10 |         10

select *, rank() over() from student;
  name  | gender | age | total_score | id | rank
--------+--------+-----+-------------+----+------
Jolly  | Female |  20 |         500 |  1 |    1
Jon    | Male   |  22 |         545 |  2 |    1
Sara   | Female |  25 |         600 |  3 |    1
Laura  | Female |  18 |         400 |  4 |    1
Alan   | Male   |  20 |         500 |  5 |    1
Kate   | Female |  22 |         500 |  6 |    1
Joseph | Male   |  18 |         643 |  7 |    1
Mice   | Male   |  23 |         543 |  8 |    1
Wise   | Male   |  21 |         499 |  9 |    1
Elis   | Female |  27 |         400 | 10 |    1

select *, dense_rank() over() from student;
  name  | gender | age | total_score | id | dense_rank
--------+--------+-----+-------------+----+------------
Jolly  | Female |  20 |         500 |  1 |          1
Jon    | Male   |  22 |         545 |  2 |          1
Sara   | Female |  25 |         600 |  3 |          1
Laura  | Female |  18 |         400 |  4 |          1
Alan   | Male   |  20 |         500 |  5 |          1
Kate   | Female |  22 |         500 |  6 |          1
Joseph | Male   |  18 |         643 |  7 |          1
Mice   | Male   |  23 |         543 |  8 |          1
Wise   | Male   |  21 |         499 |  9 |          1
Elis   | Female |  27 |         400 | 10 |          1

select *, lag(age) over() from student;
  name  | gender | age | total_score | id | lag
--------+--------+-----+-------------+----+-----
Jolly  | Female |  20 |         500 |  1 |
Jon    | Male   |  22 |         545 |  2 |  20
Sara   | Female |  25 |         600 |  3 |  22
Laura  | Female |  18 |         400 |  4 |  25
Alan   | Male   |  20 |         500 |  5 |  18
Kate   | Female |  22 |         500 |  6 |  20
Joseph | Male   |  18 |         643 |  7 |  22
Mice   | Male   |  23 |         543 |  8 |  18
Wise   | Male   |  21 |         499 |  9 |  23
Elis   | Female |  27 |         400 | 10 |  21

select *, lead(age) over() from student;
  name  | gender | age | total_score | id | lead
--------+--------+-----+-------------+----+------
Jolly  | Female |  20 |         500 |  1 |   22
Jon    | Male   |  22 |         545 |  2 |   25
Sara   | Female |  25 |         600 |  3 |   18
Laura  | Female |  18 |         400 |  4 |   20
Alan   | Male   |  20 |         500 |  5 |   22
Kate   | Female |  22 |         500 |  6 |   18
. ---- Joseph | Male   |  18 |         643 |  7 |   23
Mice   | Male   |  23 |         543 |  8 |   21
Wise   | Male   |  21 |         499 |  9 |   27
Elis   | Female |  27 |         400 | 10 |

补充内容 (2019-3-11 02:35):
partition by 上面打错了，有谁可以告诉我怎么编辑原帖。

LC里MS SQL Server 支持window functions。 附上刚run成功的 Department Highest Salary那题：

select d.name as department, sub.name as employee, sub.salary as salary
from
(select name, salary, departmentid,
rank() over (partition by departmentid order by salary desc) as  'rank'
from employee) sub
inner join department d on sub.departmentid = d.id
where sub.rank = 1;

呃，不知道要怎么样才能编辑原帖。。。

什么时候可以不用Partitioned By 和 Order By，分别的影响是？

但是Leetcode SQL 版本一直没更新，刷题的时候用不上就很烦

shirleykeat 发表于 2019-3-11 03:17
但是Leetcode SQL 版本一直没更新，刷题的时候用不上就很烦

就是呀，leetcode sql还是不太够没有cover window function

lc里用sql server刷就可以用window function的 我就一直那么干的。。

Cosett 发表于 2019-3-11 09:34
lc里用sql server刷就可以用window function的 我就一直那么干的。。

赞👍我之前都不知道。。。

后面的专题都在回帖里面了。

ROW_NUMBER, RANK, DENSE_RANK？

• The ROW_NUMBER() function assigns a running serial number to rows in each partition.
• The RANK() function assigns ranking within an ordered partition.  If the values of the two rows are the same, the  RANK() function assigns the same rank, with the next ranking(s) skipped.
• Similar to the RANK() function, the DENSE_RANK() function assigns the ranking within an ordered partition, but the ranks are consecutive. In other words, the same ranks are assigned to multiple rows and no ranks are skipped.
  

[size=15.3333px]总之，就是
[size=15.3333px]

• ROW_NUMBER() 就像数数，1，2，3，4，5每个数次都出现一次是一个从小到大一个数字都不会少的数数，就是数的行数
• RANK() 如果排列一样就会跳过！！！喜欢跳过数字！会出现 11335558 这样有的数字就跳过了
• DENSE_RANK() 因为是dense的所以不会跳过就算出现11223455，12345都是会出现的