如何使用窗口函数为每个分组返回多个值_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

传统的Group By函数会按照分组后的查询结果进行聚合计算，且每个分组只输出一条数据。但与传统的Group By函数不同，窗口函数（也称OLAP函数）可以为每个分组返回多个值，且不会影响记录的数量。本文介绍如何使用窗口函数。

使用限制

窗口函数仅支持用于SELECT语句中。
窗口函数禁止与单独的聚合函数混合使用。
例如，在如下语句中，SUM为聚合函数，且未与OVER关键字组合，因此您无法使用如下语句进行查询：
```
SELECT SUM(NAME),COUNT() OVER(...) FROM <table_name>
```
若需实现如上查询，您可以使用如下语句代替：
```
SELECT SUM(NAME),WIN1 FROM (SELECT NAME,COUNT() OVER(...) AS WIN1 FROM <table_name>) alias
```

语法

FUNCTION OVER ([[PARTITION BY column_some1] [ORDER BY column_some2] [RANGE|ROWS BETWEEN start AND end]])

参数	说明
`FUNCTION`	该部分指定了窗口函数中支持的函数，取值范围如下：可以在窗口函数中结合OVER关键字使用如下聚合函数： `SUM()` `COUNT()` `AVG()` `MAX()` `MIN()` 专用窗口函数如下： `ROW_NUMBER()` `RANK()` `DENSE_RANK()` `PERCENT_RANK()` `CUME_DIST()` `FIRST_VALUE()` `LAST_VALUE()` `LAG()` `LEAD()` `NTH_VALUE()` 说明当使用专用窗口函数`RANK()`或`DENSE_RANK()`时，窗口函数中的`order by`部分不可省略。更多专用窗口函数的介绍，请参见Window Function Descriptions。支持如下专用窗口函数： `PERCENT_RANK()` `CUME_DIST()` `FIRST_VALUE()` `LAST_VALUE()` `LAG()` `LEAD()` `NTH_VALUE()`
`[PARTITION BY column_some1]`	该部分指定了窗口函数的分区规范，用于将输入行分散到不同的分区中，过程和GROUP BY子句的分散过程相似。说明 `partition by`部分不支持引用复杂表达式，如您可以引用`column_some1`，但不可以引用`column_some1 + 1`。
`[ORDER BY column_some2]`	该部分指定了窗口函数的排序规范，用于确定输入数据行在窗口函数中执行的顺序。说明 `order by`部分不支持引用复杂表达式，如您可以引用`column_some2`，但不可以引用`column_some2 + 1`。
`[RANGE\|ROWS BETWEEN start AND end]`	该部分指定了窗口函数的窗口区间，支持按照计算列值的范围（即RANGE）或计算列的行数（即ROWS）等两种模式来定义区间。您可以使用`BETWEEN start AND end`指定边界的可取值，其中： `start`取值范围如下： `CURRENT ROW`：当前行 `N PRECEDING`：前N行 `UNBOUNDED PRECEDING`：直到第1行 `end`取值范围如下： `CURRENT ROW`：当前行 `N FOLLOWING`：后N行 `UNBOUNDED FOLLOWING`：直到最后1行

使用示例

假设已有如下示例表及数据：

CREATE TABLE test_window (
    year INT NOT NULL,
    country VARCHAR(50) NOT NULL,
    product VARCHAR(100) NOT NULL,
    profit INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO test_window (year, country, product, profit) VALUES
(2001, 'Finland', 'Phone', 10),
(2000, 'Finland', 'Computer', 1500),
(2001, 'USA', 'Calculator', 50),
(2001, 'USA', 'Computer', 1500),
(2000, 'Singapore', 'Calculator', 75),
(2000, 'Singapore', 'Calculator', 75),
(2001, 'Singapore', 'Calculator', 79);

您可以使用如下聚合函数来统计每个国家的总利润：

SELECT
    country,
    SUM(profit) OVER (PARTITION BY country) sum_profit
FROM test_window;

返回结果如下：

+-----------+------------+
| country   | sum_profit |
+-----------+------------+
| Finland   |       1510 |
| Finland   |       1510 |
| USA       |       1550 |
| USA       |       1550 |
| Singapore |        229 |
| Singapore |        229 |
| Singapore |        229 |
+-----------+------------+

您可以使用如下专用窗口函数将数据按照国家分组，并将国家内的产品按利润由小到大排名：

SELECT
    year,
    country,
    product,
    profit,
    RANK() OVER (PARTITION BY country ORDER BY profit) AS rank
FROM test_window;

返回结果如下：

+------+-----------+------------+--------+------+
| year | country   | product    | profit | rank |
+------+-----------+------------+--------+------+
| 2001 | Finland   | Phone      |     10 |    1 |
| 2000 | Finland   | Computer   |   1500 |    2 |
| 2000 | Singapore | Calculator |     75 |    1 |
| 2000 | Singapore | Calculator |     75 |    1 |
| 2001 | Singapore | Calculator |     79 |    3 |
| 2001 | USA       | Calculator |     50 |    1 |
| 2001 | USA       | Computer   |   1500 |    2 |
+------+-----------+------------+--------+------+

您可以使用如下带有ROWS命令的语句，查询根据当前窗口的每行数据计算利润部分的总和：

SELECT 
    year,
    country,
    profit,
    SUM(profit) OVER (PARTITION BY country ORDER BY 'year' ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_win 
FROM test_window;

返回结果如下：

+------+-----------+--------+---------+
| year | country   | profit | sum_win |
+------+-----------+--------+---------+
| 2001 | Finland   |     10 |      10 |
| 2000 | Finland   |   1500 |    1510 |
| 2000 | Singapore |     75 |      75 |
| 2000 | Singapore |     75 |     150 |
| 2001 | Singapore |     79 |     229 |
| 2001 | USA       |     50 |      50 |
| 2001 | USA       |   1500 |    1550 |
+------+-----------+--------+---------+