MYSQL8.0-WINDOW函数

为什么要使用窗口函数？

1.窗口函数的作用？

窗口函数对一组查询行执行类似聚合的操作
和聚合函数不同点在于，窗口函数会为每个查询行生成一个结果
1.使用聚合函数查询

mysql> SELECT SUM(profit) AS total_profit
       FROM sales;
+--------------+
| total_profit |
+--------------+
|         7535 |
+--------------+
mysql> SELECT country, SUM(profit) AS country_profit
       FROM sales
       GROUP BY country
       ORDER BY country;
+---------+----------------+
| country | country_profit |
+---------+----------------+
| Finland |           1610 |
| India   |           1350 |
| USA     |           4575 |
+---------+----------------+

2.使用窗口函数

mysql> SELECT
         year, country, product, profit,
         SUM(profit) OVER() AS total_profit,
         SUM(profit) OVER(PARTITION BY country) AS country_profit
       FROM sales
       ORDER BY country, year, product, profit;
+------+---------+------------+--------+--------------+----------------+
| year | country | product    | profit | total_profit | country_profit |
+------+---------+------------+--------+--------------+----------------+
| 2000 | Finland | Computer   |   1500 |         7535 |           1610 |
| 2000 | Finland | Phone      |    100 |         7535 |           1610 |
| 2001 | Finland | Phone      |     10 |         7535 |           1610 |
| 2000 | India   | Calculator |     75 |         7535 |           1350 |
| 2000 | India   | Calculator |     75 |         7535 |           1350 |
| 2000 | India   | Computer   |   1200 |         7535 |           1350 |
| 2000 | USA     | Calculator |     75 |         7535 |           4575 |
| 2000 | USA     | Computer   |   1500 |         7535 |           4575 |
| 2001 | USA     | Calculator |     50 |         7535 |           4575 |
| 2001 | USA     | Computer   |   1200 |         7535 |           4575 |
| 2001 | USA     | Computer   |   1500 |         7535 |           4575 |
| 2001 | USA     | TV         |    100 |         7535 |           4575 |
| 2001 | USA     | TV         |    150 |         7535 |           4575 |
+------+---------+------------+--------+--------------+----------------+

2.窗口函数的使用场景？

为每一行生成一个聚合结果
提高查询效率

mysql> SELECT i, (SELECT SUM(i) FROM t) FROM t;
+------+------------------------+
| i    | (SELECT SUM(i) FROM t) |
+------+------------------------+
|    1 |                     10 |
|    2 |                     10 |
|    3 |                     10 |
|    4 |                     10 |
+------+------------------------+

窗口函数的使用规则？

1.可以使用的函数

1.聚合函数

函数名称	作用
AVG()	返回参数的平均值
BIT_AND()	按位返回 AND
BIT_OR()	按位或返回
BIT_XOR()	返回按位异或
COUNT()	返回返回的行数
COUNT(DISTINCT)	返回多个不同值的计数
GROUP_CONCAT()	返回一个连接的字符串
JSON_ARRAYAGG()	将结果集作为单个 JSON 数组返回
JSON_OBJECTAGG()	将结果集作为单个 JSON 对象返回
MAX()	返回最大值
MIN()	返回最小值
STD()	返回总体标准差
STDDEV()	返回总体标准差
STDDEV_POP()	返回总体标准差
STDDEV_SAMP()	返回样本标准差
SUM()	返回总和
VAR_POP()	返回总体标准方差
VAR_SAMP()	返回样本方差
VARIANCE()	返回总体标准方差

2.窗口函数专有的非聚合函数

函数名称	作用	描述
CUME_DIST()	累计分配值	作用于整个partition
DENSE_RANK()	当前行在其分区内的排名，没有间隙	作用于整个partition
FIRST_VALUE()	窗口框架第一行的参数值	用于partition中的指定行
LAG()	来自分区内滞后当前行的行的参数值	作用于整个partition
LAST_VALUE()	窗口框架最后一行的参数值	用于partition中的指定行
LEAD()	分区内行前导当前行的参数值	作用于整个partition
NTH_VALUE()	来自第 N 行窗口框架的参数值	用于partition中的指定行
NTILE()	其分区内当前行的桶数。	作用于整个partition
PERCENT_RANK()	百分比排名值	作用于整个partition
RANK()	当前行在其分区内的排名，有间隙	作用于整个partition
ROW_NUMBER()	其分区内的当前行数	作用于整个partition

2.over子句的使用

2.1 over子句的作用

对窗口函数的补充
决定了使用哪些查询行来计算函数结果以及它们是如何分区和排序的：

2.2 over子句的两种形式

1. 通过over(window_spec)这种形式来实现
1. 当多个字段的window子句相同时，可以定义单独的window，在over函数中进行引用
  使用规则

OVER (w ORDER BY country)
... WINDOW w AS (PARTITION BY country)

举例

SELECT
  val,
  ROW_NUMBER() OVER (ORDER BY val) AS 'row_number',
  RANK()       OVER (ORDER BY val) AS 'rank',
  DENSE_RANK() OVER (ORDER BY val) AS 'dense_rank'
FROM numbers;

SELECT
  DISTINCT year, country,
  FIRST_VALUE(year) OVER (w ORDER BY year ASC) AS first,
  FIRST_VALUE(year) OVER (w ORDER BY year DESC) AS last
FROM sales
WINDOW w AS (PARTITION BY country);

注意：
- 定义多个window的时候，某一个window，可以被前面的或者后面的window引用，但是不能形成循环链
- over()函数中已经包含的子句，不能和引入自定义window子句重复

2.3 over子句中的window子句

over(window_spec)
# window子句包含以下内容：
window_spec:
    [window_name] [partition_clause] [order_clause] [frame_clause]

参数说明

window_name：定义的window函数名称，2.2中第二种形式定义的window函数
partition_clause：根据那个字段进行查询分组。注意：如果引入自定义window函数中存在分组语句，则不能再次使用，否则会报错
order_clause：对每个分区中的行进行排序
frame_clause：用于定义子集。下面单独记录

3.over子句中的frame_clause

frame_clause的作用：对当前partition分组，定义子集。
提出了一个frame的概念，定义的是可以在partition中移动
语法规则：

frame_clause:
    frame_units frame_extent

3.1指令说明

frame_units

ROWS：框架由开始和结束行位置定义。偏移量是行号与当前行号的差异。
RANGE：框架由值范围内的行定义。偏移量是行值与当前行值的差异。

frame_extent：表示frame的起止位置

有两种范围：

frame_start
BETWEEN frame_start AND frame_end：区间

frame_start和 frame_end值具有以下含义：

CURRENT ROW：对于ROWS，边界是当前行。对于RANGE，边界是当前行的对等点。
UNBOUNDED PRECEDING：边界是partition的第一个行。
UNBOUNDED FOLLOWING：边界是partition的最后一个行

案例

mysql> SELECT
         time, subject, val,
         FIRST_VALUE(val)  OVER w AS 'first',
         LAST_VALUE(val)   OVER w AS 'last',
         NTH_VALUE(val, 2) OVER w AS 'second',
         NTH_VALUE(val, 4) OVER w AS 'fourth'
       FROM observations
       WINDOW w AS (PARTITION BY subject ORDER BY time
                    ROWS UNBOUNDED PRECEDING);
+----------+---------+------+-------+------+--------+--------+
| time     | subject | val  | first | last | second | fourth |
+----------+---------+------+-------+------+--------+--------+
| 07:00:00 | st113   |   10 |    10 |   10 |   NULL |   NULL |
| 07:15:00 | st113   |    9 |    10 |    9 |      9 |   NULL |
| 07:30:00 | st113   |   25 |    10 |   25 |      9 |   NULL |
| 07:45:00 | st113   |   20 |    10 |   20 |      9 |     20 |
| 07:00:00 | xh458   |    0 |     0 |    0 |   NULL |   NULL |
| 07:15:00 | xh458   |   10 |     0 |   10 |     10 |   NULL |
| 07:30:00 | xh458   |    5 |     0 |    5 |     10 |   NULL |
| 07:45:00 | xh458   |   30 |     0 |   30 |     10 |     30 |
| 08:00:00 | xh458   |   25 |     0 |   25 |     10 |     30 |
+----------+---------+------+-------+------+--------+--------+