一文搞懂窗口函数

MySQL从8.0开始支持窗口函数。所谓窗口函数，包括两个内容，一个是窗口，要明白窗口的概念，另一个是函数，特指应用在窗口上的函数。

#概念

我们有时候不对整个源数据进行处理，而是要一部分一部分依次处理，就需要一个窗口限定一下，这里的窗口其实真正指的是划定范围，连续作用下（就像窗口的滑动）实现了数据的动态分区。

而应用在窗口的函数则实现对窗口限定的分区的各类计算。

所谓窗口函数，窗口实现数据选择，函数实现数据处理。

窗口函数的基本用法：

1

函数名(字段名) over(子句)

其中，函数参数部分填需要被加工的字段名称，over是关键字，用来指定函数执行的窗口范围，包含三个分析子句：

分组子句：partiton by <要分列的组>
排序子句：order by <要排序的列>
窗口子句：rows between <数据范围>

1

函数名(字段名) over(partiton by <要分列的组> order by <要排序的列> rows between <数据范围>)

窗口函数相当于在整个select语句执行中过程中给行打上标签，并依据分组、排序、开窗等方法在内部生成新的结果，然后用标签匹配回原结果，一起组装后产出。

窗口函数

#数据选择

partition by它与普通函数的分组语句group by相比，它们其实作用没区别，都是分组，但处理结果有区别。group by分组后做的是整组聚合运算，各组里如果有多个行处理后最终只会有一行返回，而partition by它分组后做的是逐行按窗口计算，不管用什么函数处理，一行记录得到一行记录，返回的行还是那么多。

order by好理解，就是按字段进行排序，与普通函数的排序语句一样。

而最有意思的其实是窗口子句rows between。具体的窗口大小其实通过它来限定。

之前说了，窗口函数里对各分区中的数据的处理做的不是聚合运算，而是逐行按窗口计算，也就是基于每一行给定的窗口里的数据进行处理。这里rows between就限定了该行所对应的窗口范围。

rows between 2 preceding and current row #取当前行和前面行
rows between unbounded preceding and current row #包括本行和之前所有的行
rows between current row and unbounded following # 包括本行和之后所有的行
rows between 3 preceding and current row # 包括本行和前面三行
rows between 3 preceding and 1 following #从前面三行和下面一行,总共五行

当order by后面缺少窗口从句条件，窗口规范默认是rows between unbounded preceding and current row，也就是当前行以及之前的所有行。

当order by和窗口从句都缺失，窗口规范默认是rows between unbounded preceding and unbounded following，也就是全部的行。

【注：以上rows也可以写成range】

以上就是数据选择部分，其中分组子句实现数据的分组归类，排序子句对分区中的数据进行排序，二者均为数据提供了某种秩序，为最终的目的——窗口的选取——作了铺垫，如果数据杂乱无章，窗口的选取将难以进行。partition by提供的秩序是——分隔而互不干扰，即对于数据选取的目的来说，partition by后面接的字段如果值不同，其数据行就100%不会在后续处于同一窗口而发生相互关系。order by后面接的字段提供的秩序是有序性，后面窗口的选取需要这些字段排好序。有了partition by和order by提供的秩序，rows between就得以按照简单的规则进行数据选取了，甚至很多情况只用默认规则就达到了目的。

概念图如下：

#数据处理

对选取的数据，我们用前面的函数进行处理。一般可以将它们分成两类。

专有函数（即窗口环境下专有的函数，主要有排序函数三巨头，偏移函数，切分函数ntile）
1. rank()：为跳跃排序，结果相同的两个数据并列，例如并列第一，则为1134
2. dense_rank()：为有重复的连续排序，结果相同的两个数据并列，例如并列第一，则为1123
3. row_number()：为不重复的连续排序，例如并列第一，照样为1234
4. ntile(n)：用于将分组数据平均切分成n块，如果切分的每组数量不均等，则第一组分得的数据更多
5. 偏移函数，用于取出同一字段的前N行数据或后N行数据，作为单独的列，分别是lead(str, n, default)和lag(str, n, default)，n表示前/后n行数据,默认值为1，default表示如果取值范围已经超过整个表的返回值，可以不填，不填默认返回N/A
6. 位置函数，用于取出第一行的first_value()函数和最后一行的last_value()函数，以及取出第n个的nth_value(str,n)函数
7. 分布函数，包括PERCENT_RANK()每行按照公式(rank-1) / (rows-1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数。CUME_DIST()则按rank/rows进行计算，即当前行的行号/总行数。
聚合类函数（即普通场景下与group by一起使用的那类函数，作用相同）
1. sum()
2. count()
3. avg()
4. max()
5. min()

掌握这些函数，具体还是需要在具体例子中完成。

#实战

#准备

建表

（考试成绩和生活消费记录）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


create table exam_score(
    sname varchar(20),
    age int,
    subject varchar(20),
    score varchar(20)
)charset=utf8;

# ----------------------- #

create table cost_fee(
    sname varchar(20),
    buydate varchar(20),
    buycost int
)charset=utf8;

插值

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27


insert into exam_score values
('张三' , 18, '语文' , 90),
('张三' , 18, '数学' , 80),
('张三' , 18, '英语' , 70),
('李四' , 21, '语文' , 88),
('李四' , 21, '数学' , 78),
('李四' , 21, '英语' , 71),
('王五' , 18, '语文' , 95),
('王五' , 18, '数学' , 83),
('王五' , 18, '英语' , 71),
('赵六' , 19, '语文' , 98),
('赵六' , 19, '数学' , 90),
('赵六' , 19, '英语' , 80);
# ----------------------- #
insert into cost_fee values
('张三','2019-01-01',10),
('张三','2019-03-03',23),
('张三','2019-02-05',46),
('李四','2019-02-02',15),
('李四','2019-01-07',50),
('李四','2019-03-04',29),
('王五','2019-03-08',62),
('王五','2019-02-09',68),
('王五','2019-01-11',75),
('赵六','2019-02-08',55),
('赵六','2019-03-10',12),
('赵六','2019-01-12',80);

检查

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


mysql> select * from exam_score;
+--------+------+---------+-------+
| sname  | age  | subject | score |
+--------+------+---------+-------+
| 张三   |   18 | 语文    | 90    |
| 张三   |   18 | 数学    | 80    |
| 张三   |   18 | 英语    | 70    |
| 李四   |   21 | 语文    | 88    |
| 李四   |   21 | 数学    | 78    |
| 李四   |   21 | 英语    | 71    |
| 王五   |   18 | 语文    | 95    |
| 王五   |   18 | 数学    | 83    |
| 王五   |   18 | 英语    | 71    |
| 赵六   |   19 | 语文    | 98    |
| 赵六   |   19 | 数学    | 90    |
| 赵六   |   19 | 英语    | 80    |
+--------+------+---------+-------+

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


mysql> select * from cost_fee;
+--------+------------+---------+
| sname  | buydate    | buycost |
+--------+------------+---------+
| 张三   | 2019-01-01 |      10 |
| 张三   | 2019-03-03 |      23 |
| 张三   | 2019-02-05 |      46 |
| 李四   | 2019-02-02 |      15 |
| 李四   | 2019-01-07 |      50 |
| 李四   | 2019-03-04 |      29 |
| 王五   | 2019-03-08 |      62 |
| 王五   | 2019-02-09 |      68 |
| 王五   | 2019-01-11 |      75 |
| 赵六   | 2019-02-08 |      55 |
| 赵六   | 2019-03-10 |      12 |
| 赵六   | 2019-01-12 |      80 |
+--------+------------+---------+

#聚合函数

1.计算每位同学的得分与平均值的情况

分析：

这里的结果需要原来的得分，也需要平均值，结果还是需要有原来的行数，所以要用窗口函数
不同人的分需要隔离开，立即想到partition by的字段是人名
平均值不需要各分区的值排序，所以可以无order by
全区求平均，无需划定滑动小窗口，自始至终都是静态的大窗口——整个分区全部的行，也就是rows between需要用rows between unbounded preceding and unbounded following
但由于省略order by与rows between时，rows between默认就是unbounded preceding and unbounded following，同样满足条件，因此可简写

代码：

1
2
3
4
5


select sname
,subject
,score
,avg(score) over(partition by sname rows between unbounded preceding and unbounded following) as avg_score
from exam_score;

结果如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


+--------+---------+-------+-------------------+
| sname  | subject | score | avg_score         |
+--------+---------+-------+-------------------+
| 张三   | 语文    | 90    |                80 |
| 张三   | 数学    | 80    |                80 |
| 张三   | 英语    | 70    |                80 |
| 李四   | 语文    | 88    |                79 |
| 李四   | 数学    | 78    |                79 |
| 李四   | 英语    | 71    |                79 |
| 王五   | 语文    | 95    |                83 |
| 王五   | 数学    | 83    |                83 |
| 王五   | 英语    | 71    |                83 |
| 赵六   | 语文    | 98    | 89.33333333333333 |
| 赵六   | 数学    | 90    | 89.33333333333333 |
| 赵六   | 英语    | 80    | 89.33333333333333 |
+--------+---------+-------+-------------------+

简写代码：

1
2
3
4
5


select sname
,subject
,score
,avg(score) over(partition by sname) as avg_score
from exam_score;

结果相同：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


+--------+---------+-------+-------------------+
| sname  | subject | score | avg_score         |
+--------+---------+-------+-------------------+
| 张三   | 语文    | 90    |                80 |
| 张三   | 数学    | 80    |                80 |
| 张三   | 英语    | 70    |                80 |
| 李四   | 语文    | 88    |                79 |
| 李四   | 数学    | 78    |                79 |
| 李四   | 英语    | 71    |                79 |
| 王五   | 语文    | 95    |                83 |
| 王五   | 数学    | 83    |                83 |
| 王五   | 英语    | 71    |                83 |
| 赵六   | 语文    | 98    | 89.33333333333333 |
| 赵六   | 数学    | 90    | 89.33333333333333 |
| 赵六   | 英语    | 80    | 89.33333333333333 |
+--------+---------+-------+-------------------+

2.计算每位同学的消费情况和消费总额

分析：

既要消费情况又要消费总额，则窗口函数请求一战
【每位同学】，立马有partition by 同学名
消费总额，sum函数跃跃欲试，且是整个区的静态大窗口，无需rows between，且无关order by，简写方式与第一题同理

代码：

1
2
3
4
5


select sname
,buydate
,buycost
,sum(buycost) over(partition by sname) as sum_cost
from cost_fee;

结果：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


+--------+------------+---------+----------+
| sname  | buydate    | buycost | sum_cost |
+--------+------------+---------+----------+
| 张三   | 2019-01-01 |      10 |       79 |
| 张三   | 2019-03-03 |      23 |       79 |
| 张三   | 2019-02-05 |      46 |       79 |
| 李四   | 2019-02-02 |      15 |       94 |
| 李四   | 2019-01-07 |      50 |       94 |
| 李四   | 2019-03-04 |      29 |       94 |
| 王五   | 2019-03-08 |      62 |      205 |
| 王五   | 2019-02-09 |      68 |      205 |
| 王五   | 2019-01-11 |      75 |      205 |
| 赵六   | 2019-02-08 |      55 |      147 |
| 赵六   | 2019-03-10 |      12 |      147 |
| 赵六   | 2019-01-12 |      80 |      147 |
+--------+------------+---------+----------+

3.计算每位同学的消费情况和累计消费总额

分析：

这里与第二题基本一样，但这里是累积消费总额，是随时间累积的，与时间的顺序有关，因此order by后要接时间字段
窗口在每行都需要有所变化，累积即本行之前行至本行的总额，因此rows between是unbounded preceding and current row，窗口随着【本行】的变化是滑动的
但由于有partition by和order by但无rows between则rows between的默认结果是unbounded preceding and current row，因此可省略rows between

代码：

1
2
3
4
5


select sname
,buydate
,buycost
,sum(buycost) over(partition by sname order by buydate rows between unbounded preceding and current row) as sum_cost
from cost_fee;

结果：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


+--------+------------+---------+----------+
| sname  | buydate    | buycost | sum_cost |
+--------+------------+---------+----------+
| 张三   | 2019-01-01 |      10 |       10 |
| 张三   | 2019-02-05 |      46 |       56 |
| 张三   | 2019-03-03 |      23 |       79 |
| 李四   | 2019-01-07 |      50 |       50 |
| 李四   | 2019-02-02 |      15 |       65 |
| 李四   | 2019-03-04 |      29 |       94 |
| 王五   | 2019-01-11 |      75 |       75 |
| 王五   | 2019-02-09 |      68 |      143 |
| 王五   | 2019-03-08 |      62 |      205 |
| 赵六   | 2019-01-12 |      80 |       80 |
| 赵六   | 2019-02-08 |      55 |      135 |
| 赵六   | 2019-03-10 |      12 |      147 |
+--------+------------+---------+----------+

简化代码：

1
2
3
4
5


select sname
,buydate
,buycost
,sum(buycost) over(partition by sname order by buydate) as sum_cost
from cost_fee;

结果一样：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


+--------+------------+---------+----------+
| sname  | buydate    | buycost | sum_cost |
+--------+------------+---------+----------+
| 张三   | 2019-01-01 |      10 |       10 |
| 张三   | 2019-02-05 |      46 |       56 |
| 张三   | 2019-03-03 |      23 |       79 |
| 李四   | 2019-01-07 |      50 |       50 |
| 李四   | 2019-02-02 |      15 |       65 |
| 李四   | 2019-03-04 |      29 |       94 |
| 王五   | 2019-01-11 |      75 |       75 |
| 王五   | 2019-02-09 |      68 |      143 |
| 王五   | 2019-03-08 |      62 |      205 |
| 赵六   | 2019-01-12 |      80 |       80 |
| 赵六   | 2019-02-08 |      55 |      135 |
| 赵六   | 2019-03-10 |      12 |      147 |
+--------+------------+---------+----------+

#排序函数

1.计算每个科目的排名，相同的分数排名不同，顺序依次增加

分析：

每个科目的排名，要用窗口排序函数，且【每个科目】锁定partition by 科目
排名一定要用到order by，字段显然是分数
排名是基于本行及之前行进行的，因此rows between使用默认的unbounded preceding and current row
相同的分数排名不同，要用row_number()函数

1
2
3
4
5
6


select sname
,subject
,score
,row_number() over(partition by subject order by score) as rank1
from
exam_score;

结果如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


+--------+---------+-------+-------+
| sname  | subject | score | rank1 |
+--------+---------+-------+-------+
| 李四   | 数学    | 78    |     1 |
| 张三   | 数学    | 80    |     2 |
| 王五   | 数学    | 83    |     3 |
| 赵六   | 数学    | 90    |     4 |
| 张三   | 英语    | 70    |     1 |
| 李四   | 英语    | 71    |     2 |
| 王五   | 英语    | 71    |     3 |
| 赵六   | 英语    | 80    |     4 |
| 李四   | 语文    | 88    |     1 |
| 张三   | 语文    | 90    |     2 |
| 王五   | 语文    | 95    |     3 |
| 赵六   | 语文    | 98    |     4 |
+--------+---------+-------+-------+

可以看到，英语成绩中李四与王五并列第二，但李四名字排名更前，因此排第二，王五含泪第三。

2.计算每个科目的排名，相同的分数排名相同，余下排名跳跃增加

分析：

与第一题同理，但函数换成rank()。

代码：

1
2
3
4
5
6


select sname
,subject
,score
,rank() over(partition by subject order by score) as rank1
from
exam_score;

结果：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


+--------+---------+-------+-------+
| sname  | subject | score | rank1 |
+--------+---------+-------+-------+
| 李四   | 数学    | 78    |     1 |
| 张三   | 数学    | 80    |     2 |
| 王五   | 数学    | 83    |     3 |
| 赵六   | 数学    | 90    |     4 |
| 张三   | 英语    | 70    |     1 |
| 李四   | 英语    | 71    |     2 |
| 王五   | 英语    | 71    |     2 |
| 赵六   | 英语    | 80    |     4 |
| 李四   | 语文    | 88    |     1 |
| 张三   | 语文    | 90    |     2 |
| 王五   | 语文    | 95    |     3 |
| 赵六   | 语文    | 98    |     4 |
+--------+---------+-------+-------+

这回英语成绩李四与王五并列第二，但赵六还是第四。

3.计算每个科目的排名，相同的分数排名相同，余下排名顺序增加

分析：

与第一题同理，这回排序函数换成dense_rank()

代码：

1
2
3
4
5
6


select sname
,subject
,score
,dense_rank() over(partition by subject order by score) as rank1
from
exam_score;

结果：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


+--------+---------+-------+-------+
| sname  | subject | score | rank1 |
+--------+---------+-------+-------+
| 李四   | 数学    | 78    |     1 |
| 张三   | 数学    | 80    |     2 |
| 王五   | 数学    | 83    |     3 |
| 赵六   | 数学    | 90    |     4 |
| 张三   | 英语    | 70    |     1 |
| 李四   | 英语    | 71    |     2 |
| 王五   | 英语    | 71    |     2 |
| 赵六   | 英语    | 80    |     3 |
| 李四   | 语文    | 88    |     1 |
| 张三   | 语文    | 90    |     2 |
| 王五   | 语文    | 95    |     3 |
| 赵六   | 语文    | 98    |     4 |
+--------+---------+-------+-------+

这回结果英语成绩李四与王五还是并列第二，但赵六为第三。

#切分函数

这个函数一般是用来划分数据桶用的。

1.将exam_score表分成四份

代码：

1
2
3
4
5
6


select sname
,subject
,score
,ntile(4) over() rank1
from
exam_score;

结果：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


+--------+---------+-------+-------+
| sname  | subject | score | rank1 |
+--------+---------+-------+-------+
| 张三   | 语文    | 90    |     1 |
| 张三   | 数学    | 80    |     1 |
| 张三   | 英语    | 70    |     1 |
| 李四   | 语文    | 88    |     2 |
| 李四   | 数学    | 78    |     2 |
| 李四   | 英语    | 71    |     2 |
| 王五   | 语文    | 95    |     3 |
| 王五   | 数学    | 83    |     3 |
| 王五   | 英语    | 71    |     3 |
| 赵六   | 语文    | 98    |     4 |
| 赵六   | 数学    | 90    |     4 |
| 赵六   | 英语    | 80    |     4 |
+--------+---------+-------+-------+

由于没分组，因此对整个表进行切分，有12份，每份3个。

2.将exam_score表分成五份

代码：

1
2
3
4
5
6


select sname
,subject
,score
,ntile(5) over() rank1
from
exam_score;

结果：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


+--------+---------+-------+-------+
| sname  | subject | score | rank1 |
+--------+---------+-------+-------+
| 张三   | 语文    | 90    |     1 |
| 张三   | 数学    | 80    |     1 |
| 张三   | 英语    | 70    |     1 |
| 李四   | 语文    | 88    |     2 |
| 李四   | 数学    | 78    |     2 |
| 李四   | 英语    | 71    |     2 |
| 王五   | 语文    | 95    |     3 |
| 王五   | 数学    | 83    |     3 |
| 王五   | 英语    | 71    |     4 |
| 赵六   | 语文    | 98    |     4 |
| 赵六   | 数学    | 90    |     5 |
| 赵六   | 英语    | 80    |     5 |
+--------+---------+-------+-------+

这里它要保证前面的份量更大，因此第一二份有3个，第三四五份有2个。

3.对exam_score表，对score排序后，按照subject分组切成4份

分析：

这里有4个人，分组并排序后切分成4份的结果就相当于row_number()了

1
2
3
4
5
6


select sname
,subject
,score
,ntile(4) over(partition by subject order by score) rank1
from
exam_score;

结果：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


+--------+---------+-------+-------+
| sname  | subject | score | rank1 |
+--------+---------+-------+-------+
| 李四   | 数学    | 78    |     1 |
| 张三   | 数学    | 80    |     2 |
| 王五   | 数学    | 83    |     3 |
| 赵六   | 数学    | 90    |     4 |
| 张三   | 英语    | 70    |     1 |
| 李四   | 英语    | 71    |     2 |
| 王五   | 英语    | 71    |     3 |
| 赵六   | 英语    | 80    |     4 |
| 李四   | 语文    | 88    |     1 |
| 张三   | 语文    | 90    |     2 |
| 王五   | 语文    | 95    |     3 |
| 赵六   | 语文    | 98    |     4 |
+--------+---------+-------+-------+

#位置函数

1.截止到当前日期，每位同学的“最后一次购买时间”、“首次购买时间”和“第二次购买金额”

注意【截止到当前日期】，注意窗口是前面的行至当前行。

代码：

1
2
3
4
5
6
7


select sname
,buydate
,buycost
,last_value(buydate) over(partition by sname order by buydate ) as last_buydate
,first_value(buydate) over(partition by sname order by buydate) as first_buydate
,nth_value(buycost,2) over(partition by sname order by buydate) as 2nd_buycost
from cost_fee;

结果：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


+--------+------------+---------+--------------+---------------+-------------+
| sname  | buydate    | buycost | last_buydate | first_buydate | 2nd_buycost |
+--------+------------+---------+--------------+---------------+-------------+
| 张三   | 2019-01-01 |      10 | 2019-01-01   | 2019-01-01    |        NULL |
| 张三   | 2019-02-05 |      46 | 2019-02-05   | 2019-01-01    |          46 |
| 张三   | 2019-03-03 |      23 | 2019-03-03   | 2019-01-01    |          46 |
| 李四   | 2019-01-07 |      50 | 2019-01-07   | 2019-01-07    |        NULL |
| 李四   | 2019-02-02 |      15 | 2019-02-02   | 2019-01-07    |          15 |
| 李四   | 2019-03-04 |      29 | 2019-03-04   | 2019-01-07    |          15 |
| 王五   | 2019-01-11 |      75 | 2019-01-11   | 2019-01-11    |        NULL |
| 王五   | 2019-02-09 |      68 | 2019-02-09   | 2019-01-11    |          68 |
| 王五   | 2019-03-08 |      62 | 2019-03-08   | 2019-01-11    |          68 |
| 赵六   | 2019-01-12 |      80 | 2019-01-12   | 2019-01-12    |        NULL |
| 赵六   | 2019-02-08 |      55 | 2019-02-08   | 2019-01-12    |          55 |
| 赵六   | 2019-03-10 |      12 | 2019-03-10   | 2019-01-12    |          55 |
+--------+------------+---------+--------------+---------------+-------------+

注意，这里体现了first_value/last_value依前述这里有partition by和order by，窗口按默认应该是rows between unbounded preceding and current row，同样nth_value与前述一致，当计算分区第一行时并不知道第二个值，因此填NULL，计算到第二行时知道第二个值，返回，第三行也一样。

2.每位同学的“最后一次购买时间”、“首次购买时间”和“第二次购买金额”

这里窗口就是整个分区了

代码：

1
2
3
4
5
6
7


select sname
,buydate
,buycost
,last_value(buydate) over(partition by sname order by buydate rows between unbounded preceding and unbounded following) as last_buydate
,first_value(buydate) over(partition by sname order by buydate rows between unbounded preceding and unbounded following) as first_buydate
,nth_value(buycost,2) over(partition by sname order by buydate rows between unbounded preceding and unbounded following) as 2nd_buycost
from cost_fee;

结果：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


+--------+------------+---------+--------------+---------------+-------------+
| sname  | buydate    | buycost | last_buydate | first_buydate | 2nd_buycost |
+--------+------------+---------+--------------+---------------+-------------+
| 张三   | 2019-01-01 |      10 | 2019-03-03   | 2019-01-01    |          46 |
| 张三   | 2019-02-05 |      46 | 2019-03-03   | 2019-01-01    |          46 |
| 张三   | 2019-03-03 |      23 | 2019-03-03   | 2019-01-01    |          46 |
| 李四   | 2019-01-07 |      50 | 2019-03-04   | 2019-01-07    |          15 |
| 李四   | 2019-02-02 |      15 | 2019-03-04   | 2019-01-07    |          15 |
| 李四   | 2019-03-04 |      29 | 2019-03-04   | 2019-01-07    |          15 |
| 王五   | 2019-01-11 |      75 | 2019-03-08   | 2019-01-11    |          68 |
| 王五   | 2019-02-09 |      68 | 2019-03-08   | 2019-01-11    |          68 |
| 王五   | 2019-03-08 |      62 | 2019-03-08   | 2019-01-11    |          68 |
| 赵六   | 2019-01-12 |      80 | 2019-03-10   | 2019-01-12    |          55 |
| 赵六   | 2019-02-08 |      55 | 2019-03-10   | 2019-01-12    |          55 |
| 赵六   | 2019-03-10 |      12 | 2019-03-10   | 2019-01-12    |          55 |
+--------+------------+---------+--------------+---------------+-------------+

由于窗口是整个分区，在第一行就知道了第二次购买金额以及整个分区的最后一次购买时间。

#偏移函数

1.展示各位同学的“上次购买时间”和“下次购买时间”。对于第一天，显示 “first buy”；对于最后一天，显示 “last buy”

这里上次购买就相当于把排好序的日期向下偏移一行，首行空缺则填默认值first buy。下次购买则相当于把排好序的日期向上偏移一行，尾行空缺则填默认值last buy。

代码：

1
2
3
4
5


select sname
,buydate
,lag(buydate,1,'first buy') over(partition by sname order by buydate) as prior_buy
,lead(buydate,1,'last buy') over(partition by sname order by buydate) as next_buy
from cost_fee;

结果：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


+--------+------------+------------+------------+
| sname  | buydate    | prior_buy  | next_buy   |
+--------+------------+------------+------------+
| 张三   | 2019-01-01 | first buy  | 2019-02-05 |
| 张三   | 2019-02-05 | 2019-01-01 | 2019-03-03 |
| 张三   | 2019-03-03 | 2019-02-05 | last buy   |
| 李四   | 2019-01-07 | first buy  | 2019-02-02 |
| 李四   | 2019-02-02 | 2019-01-07 | 2019-03-04 |
| 李四   | 2019-03-04 | 2019-02-02 | last buy   |
| 王五   | 2019-01-11 | first buy  | 2019-02-09 |
| 王五   | 2019-02-09 | 2019-01-11 | 2019-03-08 |
| 王五   | 2019-03-08 | 2019-02-09 | last buy   |
| 赵六   | 2019-01-12 | first buy  | 2019-02-08 |
| 赵六   | 2019-02-08 | 2019-01-12 | 2019-03-10 |
| 赵六   | 2019-03-10 | 2019-02-08 | last buy   |
+--------+------------+------------+------------+

#分布函数

举例说明一下分布函数的用法

好的。

代码：

1
2
3
4
5
6


select sname
,buydate
,buycost
,percent_rank() over(partition by sname order by buydate) as percentrank
,cume_dist() over(partition by sname order by buydate) as cumedist
from cost_fee;

结果：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


+--------+------------+---------+-------------+--------------------+
| sname  | buydate    | buycost | percentrank | cumedist           |
+--------+------------+---------+-------------+--------------------+
| 张三   | 2019-01-01 |      10 |           0 | 0.3333333333333333 |
| 张三   | 2019-02-05 |      46 |         0.5 | 0.6666666666666666 |
| 张三   | 2019-03-03 |      23 |           1 |                  1 |
| 李四   | 2019-01-07 |      50 |           0 | 0.3333333333333333 |
| 李四   | 2019-02-02 |      15 |         0.5 | 0.6666666666666666 |
| 李四   | 2019-03-04 |      29 |           1 |                  1 |
| 王五   | 2019-01-11 |      75 |           0 | 0.3333333333333333 |
| 王五   | 2019-02-09 |      68 |         0.5 | 0.6666666666666666 |
| 王五   | 2019-03-08 |      62 |           1 |                  1 |
| 赵六   | 2019-01-12 |      80 |           0 | 0.3333333333333333 |
| 赵六   | 2019-02-08 |      55 |         0.5 | 0.6666666666666666 |
| 赵六   | 2019-03-10 |      12 |           1 |                  1 |
+--------+------------+---------+-------------+--------------------+

这里，每个分区的行数是3，我们看下法外狂徒张三的消费情况，这里percent_rank第一行是(rank-1)/(rows-1)，rows已经固定了是3，那么第一行就是(1-1)/(3-1)=0了，第二行就是(2-1)/(3-1)=0.5了，第三行就是(3-1)/(3-1)就是1了。

而just-dist第一行是1/3，第二行是2/3，第三行是3/3=1。

只是计算方式不一样而已。