mysql开窗函数

开窗函数

注：开窗函数只有MySQL8.0版本之后才有

1. 开窗函数

• 官网定义：A window function performs an aggregate-like operation on a set of query rows. However, whereas an aggregate operation groups query rows into a single result row, a window function produces a result for each query row。

• 开窗函数和像聚合函数一样，查询多行数据，然而，聚合操作将查询行分组到单个结果行中，而窗口函数则为每个查询行生成一个结果。

  • 对其进行函数求值的行称为当前行。
  • 与进行函数计算的当前行相关的查询行构成当前行的窗口。

• 开窗函数的调用格式为：
  函数名(列名) OVER(partition by 列名 order by列名) 。

MySQL提供的开窗函数

• DENSE_RNAK() ：其分区中当前行的排名，无间隙
• FIRST_VALUE()：窗口中第一行中的参数值
• LAG()：分区中滞后当前行的行的参数值
• LAST_VALUE()：窗口中最后一行中的参数值
• LEAD()：分区中领先当前行的参数值
• NTILE()：其分区中当前行的存储桶编号。
• RANK()：当前行在其分区内的排名，带有间隙
• ROW_NUMBER()：当前行在其分区内的排名，带有间隙

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 |
+----------+---------+------+-------+------+--------+--------+

mysql> SELECT
         t, val,
         LAG(val)        OVER w AS 'lag',
         LEAD(val)       OVER w AS 'lead',
         val - LAG(val)  OVER w AS 'lag diff',
         val - LEAD(val) OVER w AS 'lead diff'
       FROM series
       WINDOW w AS (ORDER BY t);
+----------+------+------+------+----------+-----------+
| t        | val  | lag  | lead | lag diff | lead diff |
+----------+------+------+------+----------+-----------+
| 12:00:00 |  100 | NULL |  125 |     NULL |       -25 |
| 13:00:00 |  125 |  100 |  132 |       25 |        -7 |
| 14:00:00 |  132 |  125 |  145 |        7 |       -13 |
| 15:00:00 |  145 |  132 |  140 |       13 |         5 |
| 16:00:00 |  140 |  145 |  150 |       -5 |       -10 |
| 17:00:00 |  150 |  140 |  200 |       10 |       -50 |
| 18:00:00 |  200 |  150 | NULL |       50 |      NULL |
+----------+------+------+------+----------+-----------+

mysql> SELECT
         val,
         ROW_NUMBER() OVER w AS 'row_number',
         NTILE(2)     OVER w AS 'ntile2',
         NTILE(4)     OVER w AS 'ntile4'
       FROM numbers
       WINDOW w AS (ORDER BY val);
+------+------------+--------+--------+
| val  | row_number | ntile2 | ntile4 |
+------+------------+--------+--------+
|    1 |          1 |      1 |      1 |
|    1 |          2 |      1 |      1 |
|    2 |          3 |      1 |      1 |
|    3 |          4 |      1 |      2 |
|    3 |          5 |      1 |      2 |
|    3 |          6 |      2 |      3 |
|    4 |          7 |      2 |      3 |
|    4 |          8 |      2 |      4 |
|    5 |          9 |      2 |      4 |
+------+------------+--------+--------+

1.1 开窗函数和聚合函数的区别

• 数据
  
• 使用聚合函数的效果

SELECT 
	country, 
	SUM(profit) AS country_profit
FROM sales
GROUP BY country
ORDER BY country;

• 使用开窗函数的效果

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;

第一个 OVER 子句为空，它将整个查询行集视为单个分区。因此，窗口函数生成全局和，但对每行都这样做。

第二个 OVER 子句按country对行进行分区，从而生成country分区的总和。该函数为每个分区行生成此总和。

1.2 要使用开窗函数（或将聚合函数视为开窗函数），请在函数调用后包含一个 OVER 子句。OVER 子句有两种形式：

• over_clause:
  

  这两种形式都定义了窗口函数应如何处理查询行。它们的不同之处在于，窗口是直接在 OVER 子句中定义，还是由对查询中其他位置定义的命名窗口的引用提供：

  • 在第一种情况下，窗口规范直接出现在 OVER 子句中，位于括号之间。
  • 在第二种情况下，window_name是由查询中其他位置的 WINDOW 子句定义的窗口规范的名称。

-- 第一种情况
SELECT
         year, country, product, profit,
         ROW_NUMBER() OVER(PARTITION BY country) AS row_num1,
         ROW_NUMBER() OVER(PARTITION BY country ORDER BY year, product) AS row_num2
FROM sales;

-- 第二种情况
SELECT
         val,
         ROW_NUMBER()   OVER w AS 'row_number',
         CUME_DIST()    OVER w AS 'cume_dist',
         PERCENT_RANK() OVER w AS 'percent_rank'
FROM numbers
WINDOW w AS (ORDER BY val);

• OVER (window_spec)语法
  

  • window_name：窗口函数名称

    • 窗口函数命名规则
      

  • partition_clause：子句指示如何将查询行划分为多个组。给定行的窗口函数结果基于包含该行的分区的行。如果省略了partition BY，则存在一个由所有查询行组成的分区。

  • order_clause:如果省略 ORDER BY，则分区行是无序的，不隐含处理顺序，并且所有分区行都是相同的。

1.3 开窗函数的执行顺序

开窗函数只允许在select和 ORDER BY 子句中使用。查询结果行 由 FROM 子句、在 WHERE、GROUP BY 和 HAVING 处理之后确定，并且窗口执行发生在 ORDER BY、LIMIT 和 SELECT DISTINCT 之前。

总结就是: 开窗函数在WHERE,GROUP BY 和 HAVING之后执行,在在 ORDER BY、LIMIT 和 SELECT DISTINCT 之前执行.

2. 聚合函数

聚合函数经常和开窗函数一起使用。

参考链接

• https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html

• https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

• https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html