版本: 最新版本-3.5

窗口函数

背景

窗口函数是一类特殊的内置函数。与聚合函数类似，它也对多个输入行执行计算以获得单个数据值。不同之处在于，窗口函数在特定窗口中处理输入数据，而不是使用“group by”方法。可以使用 over() 子句对每个窗口中的数据进行排序和分组。窗口函数为每一行计算一个单独的值，而不是为每个组计算一个值。这种灵活性允许用户向 select 子句添加额外的列，并进一步筛选结果集。窗口函数只能出现在选择列表和子句的最外层位置。它在查询结束时生效，即在执行 join、where 和 group by 操作之后。窗口函数通常用于分析趋势、计算异常值以及对大规模数据执行分桶分析。

用法

语法

function(args) OVER([partition_by_clause] [order_by_clause] [order_by_clause window_clause])
partition_by_clause ::= PARTITION BY expr [, expr ...]
order_by_clause ::= ORDER BY expr [ASC | DESC] [, expr [ASC | DESC] ...]

PARTITION BY 子句

Partition By 子句类似于 Group By。它按一个或多个指定的列对输入行进行分组。具有相同值的行被分组在一起。

ORDER BY 子句

Order By 子句与外部 Order By 基本相同。它定义了输入行的顺序。如果指定了 Partition By，则 Order By 定义每个 Partition 分组中的顺序。唯一的区别是 OVER 子句中的 Order By n（n 是正整数）等效于无操作，而外部 Order By 中的 n 表示按第 n 列排序。

示例

此示例显示向选择列表添加一个 id 列，其值分别为 1、2、3 等，并按 events 表中的 date_and_time 列排序。

SELECT row_number() OVER (ORDER BY date_and_time) AS id,
    c1, c2, c3, c4
FROM events;

Window 子句

Window 子句用于指定要操作的行范围（基于当前行的前一行和后一行）。它支持以下语法：AVG()、COUNT()、FIRST_VALUE()、LAST_VALUE() 和 SUM()。对于 MAX() 和 MIN()，窗口子句可以将起始位置指定为 UNBOUNDED PRECEDING。

语法

ROWS BETWEEN [ { m | UNBOUNDED } PRECEDING | CURRENT ROW] [ AND [CURRENT ROW | { UNBOUNDED | n } FOLLOWING] ]

窗口函数示例表

本节创建一个示例表 scores。您可以使用此表来测试下面的许多窗口函数。

CREATE TABLE `scores` (
    `id` int(11) NULL,
    `name` varchar(11) NULL,
    `subject` varchar(11) NULL,
    `score` int(11) NULL
  )
DISTRIBUTED BY HASH(`score`) BUCKETS 10;

INSERT INTO `scores` VALUES
  (1, "lily", "math", NULL),
  (1, "lily", "english", 100),
  (1, "lily", "physics", 60),
  (2, "tom", "math", 80),
  (2, "tom", "english", 98),
  (2, "tom", "physics", NULL),
  (3, "jack", "math", 95),
  (3, "jack", "english", NULL),
  (3, "jack", "physics", 99),
  (4, "amy", "math", 80),
  (4, "amy", "english", 92),
  (4, "amy", "physics", 99),
  (5, "mike", "math", 70),
  (5, "mike", "english", 85),
  (5, "mike", "physics", 85),
  (6, "amber", "math", 92),
  (6, "amber", NULL, 90),
  (6, "amber", "physics", 100);

函数示例

本节介绍 StarRocks 中支持的窗口函数。

AVG()

计算给定窗口中字段的平均值。此函数忽略 NULL 值。

语法

AVG(expr) [OVER (*analytic_clause*)]

示例

以下示例使用股票数据作为示例。

CREATE TABLE stock_ticker (
    stock_symbol  STRING,
    closing_price DECIMAL(8,2),
    closing_date  DATETIME
)
DUPLICATE KEY(stock_symbol)
COMMENT "OLAP"
DISTRIBUTED BY HASH(closing_date);

INSERT INTO stock_ticker VALUES 
    ("JDR", 12.86, "2014-10-02 00:00:00"), 
    ("JDR", 12.89, "2014-10-03 00:00:00"), 
    ("JDR", 12.94, "2014-10-04 00:00:00"), 
    ("JDR", 12.55, "2014-10-05 00:00:00"), 
    ("JDR", 14.03, "2014-10-06 00:00:00"), 
    ("JDR", 14.75, "2014-10-07 00:00:00"), 
    ("JDR", 13.98, "2014-10-08 00:00:00")
;

计算当前行及其前后每一行的平均收盘价。

select stock_symbol, closing_date, closing_price,
    avg(closing_price)
        over (partition by stock_symbol
              order by closing_date
              rows between 1 preceding and 1 following
        ) as moving_average
from stock_ticker;

输出

+--------------+---------------------+---------------+----------------+
| stock_symbol | closing_date        | closing_price | moving_average |
+--------------+---------------------+---------------+----------------+
| JDR          | 2014-10-02 00:00:00 |         12.86 |    12.87500000 |
| JDR          | 2014-10-03 00:00:00 |         12.89 |    12.89666667 |
| JDR          | 2014-10-04 00:00:00 |         12.94 |    12.79333333 |
| JDR          | 2014-10-05 00:00:00 |         12.55 |    13.17333333 |
| JDR          | 2014-10-06 00:00:00 |         14.03 |    13.77666667 |
| JDR          | 2014-10-07 00:00:00 |         14.75 |    14.25333333 |
| JDR          | 2014-10-08 00:00:00 |         13.98 |    14.36500000 |
+--------------+---------------------+---------------+----------------+

例如，第一行中的 12.87500000 是“2014-10-02” (12.86)、其前一天“2014-10-01”（空）及其后一天“2014-10-03” (12.89) 的收盘价的平均值。

COUNT()

计算给定窗口中满足指定条件的行总数。

语法

COUNT(expr) [OVER (analytic_clause)]

示例

从当前行到数学分区中的第一行，计算大于 90 的数学分数的出现次数。此示例使用示例表 scores 中的数据。

select *,
    count(score)
        over (
            partition by subject
            order by score
            rows between unbounded preceding and current row
        ) as 'score_count'
from scores where subject in ('math') and score > 90;

+------+-------+---------+-------+-------------+
| id   | name  | subject | score | score_count |
+------+-------+---------+-------+-------------+
|    6 | amber | math    |    92 |           1 |
|    3 | jack  | math    |    95 |           2 |
+------+-------+---------+-------+-------------+

CUME_DIST()

CUME_DIST() 函数计算值在分区或窗口内的累积分布，指示其在分区中的相对位置（以百分比表示）。它通常用于计算组中最高或最低值的分布。

如果数据按升序排序，则此函数计算小于或等于当前行中的值的值的百分比。
如果数据按降序排序，则此函数计算大于或等于当前行中的值的值的百分比。

累积分布的范围为 0 到 1。它对于百分位计算和数据分布分析很有用。

该函数从 v3.2 版本开始支持。

语法

CUME_DIST() OVER (partition_by_clause order_by_clause)

partition_by_clause：可选。如果未指定此子句，则将整个结果集作为单个分区处理。
order_by_clause：此函数必须与 ORDER BY 一起使用，以将分区行排序为所需的顺序。

CUME_DIST() 包含 NULL 值，并将它们视为最低值。

示例

以下示例显示每个 subject 组中每个分数的累积分布。此示例使用示例表 scores 中的数据。

SELECT *, 
    cume_dist() 
      OVER (
        PARTITION BY subject
        ORDER BY score
      ) AS cume_dist 
FROM scores;
+------+-------+---------+-------+---------------------+
| id   | name  | subject | score | cume_dist           |
+------+-------+---------+-------+---------------------+
|    6 | amber | NULL    |    90 |                   1 |
|    3 | jack  | english |  NULL |                 0.2 |
|    5 | mike  | english |    85 |                 0.4 |
|    4 | amy   | english |    92 |                 0.6 |
|    2 | tom   | english |    98 |                 0.8 |
|    1 | lily  | english |   100 |                   1 |
|    1 | lily  | math    |  NULL | 0.16666666666666666 |
|    5 | mike  | math    |    70 |  0.3333333333333333 |
|    2 | tom   | math    |    80 |  0.6666666666666666 |
|    4 | amy   | math    |    80 |  0.6666666666666666 |
|    6 | amber | math    |    92 |  0.8333333333333334 |
|    3 | jack  | math    |    95 |                   1 |
|    2 | tom   | physics |  NULL | 0.16666666666666666 |
|    1 | lily  | physics |    60 |  0.3333333333333333 |
|    5 | mike  | physics |    85 |                 0.5 |
|    4 | amy   | physics |    99 |  0.8333333333333334 |
|    3 | jack  | physics |    99 |  0.8333333333333334 |
|    6 | amber | physics |   100 |                   1 |
+------+-------+---------+-------+---------------------+

对于第一行中的 cume_dist，NULL 组只有一行，并且只有该行本身满足“小于或等于当前行”的条件。累积分布为 1。
对于第二行中的 cume_dist，english 组有五行，并且只有该行本身 (NULL) 满足“小于或等于当前行”的条件。累积分布为 0.2。
对于第三行中的 cume_dist，english 组有五行，并且两行（85 和 NULL）满足“小于或等于当前行”的条件。累积分布为 0.4。

DENSE_RANK()

DENSE_RANK() 函数用于表示排名。与 RANK() 不同，DENSE_RANK() 没有空缺数字。例如，如果有两个 1，则 DENSE_RANK() 的第三个数字仍然是 2，而 RANK() 的第三个数字是 3。

语法

DENSE_RANK() OVER(partition_by_clause order_by_clause)

示例

以下示例显示数学分数的排名（按降序排序）。此示例使用示例表 scores 中的数据。

select *,
    dense_rank()
        over (
            partition by subject
            order by score desc
        ) as `rank`
from scores where subject in ('math');

+------+-------+---------+-------+------+
| id   | name  | subject | score | rank |
+------+-------+---------+-------+------+
|    3 | jack  | math    |    95 |    1 |
|    6 | amber | math    |    92 |    2 |
|    2 | tom   | math    |    80 |    3 |
|    4 | amy   | math    |    80 |    3 |
|    5 | mike  | math    |    70 |    4 |
|    1 | lily  | math    |  NULL |    5 |
+------+-------+---------+-------+------+

结果数据有两行，其分数为 80。它们的排名都是 3。下一个分数 70 的排名是 4。这表明 DENSE_RANK() 没有空缺数字。

FIRST_VALUE()

FIRST_VALUE() 返回窗口范围的第一个值。

语法

FIRST_VALUE(expr [IGNORE NULLS]) OVER(partition_by_clause order_by_clause [window_clause])

v2.5.0 起支持 IGNORE NULLS。它用于确定是否从计算中消除 expr 的 NULL 值。默认情况下，包含 NULL 值，这意味着如果筛选结果中的第一个值为 NULL，则返回 NULL。如果指定 IGNORE NULLS，则返回筛选结果中的第一个非空值。如果所有值均为 NULL，即使指定 IGNORE NULLS，也会返回 NULL。

示例

返回按 subject 分组的每个成员在每个组中的第一个 score 值（降序）。此示例使用示例表 scores 中的数据。

select *,
    first_value(score IGNORE NULLS)
        over (
            partition by subject
            order by score desc
        ) as first
from scores;

+------+-------+---------+-------+-------+
| id   | name  | subject | score | first |
+------+-------+---------+-------+-------+
|    1 | lily  | english |   100 |   100 |
|    2 | tom   | english |    98 |   100 |
|    4 | amy   | english |    92 |   100 |
|    5 | mike  | english |    85 |   100 |
|    3 | jack  | english |  NULL |   100 |
|    6 | amber | physics |   100 |   100 |
|    3 | jack  | physics |    99 |   100 |
|    4 | amy   | physics |    99 |   100 |
|    5 | mike  | physics |    85 |   100 |
|    1 | lily  | physics |    60 |   100 |
|    2 | tom   | physics |  NULL |   100 |
|    6 | amber | NULL    |    90 |    90 |
|    3 | jack  | math    |    95 |    95 |
|    6 | amber | math    |    92 |    95 |
|    2 | tom   | math    |    80 |    95 |
|    4 | amy   | math    |    80 |    95 |
|    5 | mike  | math    |    70 |    95 |
|    1 | lily  | math    |  NULL |    95 |
+------+-------+---------+-------+-------+

LAST_VALUE()

LAST_VALUE() 返回窗口范围的最后一个值。它是 FIRST_VALUE() 的逆运算。

语法

LAST_VALUE(expr [IGNORE NULLS]) OVER(partition_by_clause order_by_clause [window_clause])

v2.5.0 起支持 IGNORE NULLS。它用于确定是否从计算中消除 expr 的 NULL 值。默认情况下，包含 NULL 值，这意味着如果筛选结果中的最后一个值为 NULL，则返回 NULL。如果指定 IGNORE NULLS，则返回筛选结果中的最后一个非空值。如果所有值均为 NULL，即使指定 IGNORE NULLS，也会返回 NULL。

默认情况下，LAST_VALUE() 计算 rows between unbounded preceding and current row，它将当前行与其所有前面的行进行比较。如果您只想显示每个分区的单个值，请在 ORDER BY 之后使用 rows between unbounded preceding and unbounded following。

示例

返回按 subject 分组的每个成员在组中的最后一个 score（降序）。此示例使用示例表 scores 中的数据。

select *,
    last_value(score IGNORE NULLS)
        over (
            partition by subject
            order by score desc
            rows between unbounded preceding and unbounded following
        ) as last
from scores;

+------+-------+---------+-------+------+
| id   | name  | subject | score | last |
+------+-------+---------+-------+------+
|    1 | lily  | english |   100 |   85 |
|    2 | tom   | english |    98 |   85 |
|    4 | amy   | english |    92 |   85 |
|    5 | mike  | english |    85 |   85 |
|    3 | jack  | english |  NULL |   85 |
|    6 | amber | physics |   100 |   60 |
|    3 | jack  | physics |    99 |   60 |
|    4 | amy   | physics |    99 |   60 |
|    5 | mike  | physics |    85 |   60 |
|    1 | lily  | physics |    60 |   60 |
|    2 | tom   | physics |  NULL |   60 |
|    6 | amber | NULL    |    90 |   90 |
|    3 | jack  | math    |    95 |   70 |
|    6 | amber | math    |    92 |   70 |
|    2 | tom   | math    |    80 |   70 |
|    4 | amy   | math    |    80 |   70 |
|    5 | mike  | math    |    70 |   70 |
|    1 | lily  | math    |  NULL |   70 |
+------+-------+---------+-------+------+

LAG()

返回滞后于当前行 offset 行的行的值。此函数通常用于比较行之间的值并筛选数据。

LAG() 可用于查询以下类型的数据

数值：TINYINT、SMALLINT、INT、BIGINT、LARGEINT、FLOAT、DOUBLE、DECIMAL
字符串：CHAR、VARCHAR
日期：DATE、DATETIME
StarRocks v2.5 起支持 BITMAP 和 HLL。

语法

LAG(expr [IGNORE NULLS] [, offset[, default]])
OVER([<partition_by_clause>] [<order_by_clause>])

参数

expr：您要计算的字段。
offset：偏移量。它必须是正整数。如果未指定此参数，则默认为 1。
default：如果未找到匹配的行，则返回的默认值。如果未指定此参数，则默认为 NULL。default 支持类型与 expr 兼容的任何表达式。
v3.0 起支持 IGNORE NULLS。它用于确定结果中是否包含 expr 的 NULL 值。默认情况下，在计数 offset 行时包含 NULL 值，这意味着如果目标行的值为 NULL，则返回 NULL。请参阅示例 1。如果指定 IGNORE NULLS，则在计数 offset 行时将忽略 NULL 值，并且系统会继续搜索 offset 非空值。如果找不到 offset 非空值，则返回 NULL 或 default（如果已指定）。请参阅示例 2。

示例 1：未指定 IGNORE NULLS

创建表并插入值

CREATE TABLE test_tbl (col_1 INT, col_2 INT)
DISTRIBUTED BY HASH(col_1);

INSERT INTO test_tbl VALUES 
    (1, NULL),
    (2, 4),
    (3, NULL),
    (4, 2),
    (5, NULL),
    (6, 7),
    (7, 6),
    (8, 5),
    (9, NULL),
    (10, NULL);

从此表查询数据，其中 offset 为 2，这意味着遍历前两行；default 为 0，这意味着如果未找到匹配的行，则返回 0。

输出

SELECT col_1, col_2, LAG(col_2,2,0) OVER (ORDER BY col_1) 
FROM test_tbl ORDER BY col_1;
+-------+-------+---------------------------------------------+
| col_1 | col_2 | lag(col_2, 2, 0) OVER (ORDER BY col_1 ASC ) |
+-------+-------+---------------------------------------------+
|     1 |  NULL |                                           0 |
|     2 |     4 |                                           0 |
|     3 |  NULL |                                        NULL |
|     4 |     2 |                                           4 |
|     5 |  NULL |                                        NULL |
|     6 |     7 |                                           2 |
|     7 |     6 |                                        NULL |
|     8 |     5 |                                           7 |
|     9 |  NULL |                                           6 |
|    10 |  NULL |                                           5 |
+-------+-------+---------------------------------------------+

对于前两行，不存在前两行，并且返回默认值 0。

对于第 3 行中的 NULL，向后两行的值为 NULL，并且由于允许 NULL 值，因此返回 NULL。

示例 2：已指定 IGNORE NULLS

使用前面的表和参数设置。

SELECT col_1, col_2, LAG(col_2 IGNORE NULLS,2,0) OVER (ORDER BY col_1) 
FROM test_tbl ORDER BY col_1;
+-------+-------+---------------------------------------------+
| col_1 | col_2 | lag(col_2, 2, 0) OVER (ORDER BY col_1 ASC ) |
+-------+-------+---------------------------------------------+
|     1 |  NULL |                                           0 |
|     2 |     4 |                                           0 |
|     3 |  NULL |                                           0 |
|     4 |     2 |                                           0 |
|     5 |  NULL |                                           4 |
|     6 |     7 |                                           4 |
|     7 |     6 |                                           2 |
|     8 |     5 |                                           7 |
|     9 |  NULL |                                           6 |
|    10 |  NULL |                                           6 |
+-------+-------+---------------------------------------------+

对于第 1 到 4 行，系统无法在前面的行中找到每个值的两个非 NULL 值，并且返回默认值 0。

对于第 7 行中的值 6，向后两行的值为 NULL，并且由于指定了 IGNORE NULLS，因此将忽略 NULL。系统继续搜索非空值，并返回第 4 行中的 2。

LEAD()

返回领先于当前行 offset 行的行的值。此函数通常用于比较行之间的值并筛选数据。

可由 LEAD() 查询的数据类型与LAG()支持的数据类型相同。

语法

LEAD(expr [IGNORE NULLS] [, offset[, default]])
OVER([<partition_by_clause>] [<order_by_clause>])

参数

expr：您要计算的字段。
offset：偏移量。它必须是正整数。如果未指定此参数，则默认为 1。
default：如果未找到匹配的行，则返回的默认值。如果未指定此参数，则默认为 NULL。default 支持类型与 expr 兼容的任何表达式。
v3.0 起支持 IGNORE NULLS。它用于确定结果中是否包含 expr 的 NULL 值。默认情况下，在计数 offset 行时包含 NULL 值，这意味着如果目标行的值为 NULL，则返回 NULL。请参阅示例 1。如果指定 IGNORE NULLS，则在计数 offset 行时将忽略 NULL 值，并且系统会继续搜索 offset 非空值。如果找不到 offset 非空值，则返回 NULL 或 default（如果已指定）。请参阅示例 2。

示例 1：未指定 IGNORE NULLS

创建表并插入值

CREATE TABLE test_tbl (col_1 INT, col_2 INT)
DISTRIBUTED BY HASH(col_1);

INSERT INTO test_tbl VALUES 
    (1, NULL),
    (2, 4),
    (3, NULL),
    (4, 2),
    (5, NULL),
    (6, 7),
    (7, 6),
    (8, 5),
    (9, NULL),
    (10, NULL);

从此表查询数据，其中 offset 为 2，这意味着遍历后续两行；default 为 0，这意味着如果未找到匹配的行，则返回 0。

输出

SELECT col_1, col_2, LEAD(col_2,2,0) OVER (ORDER BY col_1) 
FROM test_tbl ORDER BY col_1;
+-------+-------+----------------------------------------------+
| col_1 | col_2 | lead(col_2, 2, 0) OVER (ORDER BY col_1 ASC ) |
+-------+-------+----------------------------------------------+
|     1 |  NULL |                                         NULL |
|     2 |     4 |                                            2 |
|     3 |  NULL |                                         NULL |
|     4 |     2 |                                            7 |
|     5 |  NULL |                                            6 |
|     6 |     7 |                                            5 |
|     7 |     6 |                                         NULL |
|     8 |     5 |                                         NULL |
|     9 |  NULL |                                            0 |
|    10 |  NULL |                                            0 |
+-------+-------+----------------------------------------------+

对于第一行，向前两行的值为 NULL，并且由于允许 NULL 值，因此返回 NULL。

对于最后两行，不存在后续两行，并且返回默认值 0。

示例 2：已指定 IGNORE NULLS

使用前面的表和参数设置。

SELECT col_1, col_2, LEAD(col_2 IGNORE NULLS,2,0) OVER (ORDER BY col_1) 
FROM test_tbl ORDER BY col_1;
+-------+-------+----------------------------------------------+
| col_1 | col_2 | lead(col_2, 2, 0) OVER (ORDER BY col_1 ASC ) |
+-------+-------+----------------------------------------------+
|     1 |  NULL |                                            2 |
|     2 |     4 |                                            7 |
|     3 |  NULL |                                            7 |
|     4 |     2 |                                            6 |
|     5 |  NULL |                                            6 |
|     6 |     7 |                                            5 |
|     7 |     6 |                                            0 |
|     8 |     5 |                                            0 |
|     9 |  NULL |                                            0 |
|    10 |  NULL |                                            0 |
+-------+-------+----------------------------------------------+

对于第 7 到 10 行，系统无法在后续行中找到两个非 NULL 值，并且返回默认值 0。

对于第一行，向前两行的值为 NULL，并且由于指定了 IGNORE NULLS，因此将忽略 NULL。系统继续搜索第二个非空值，并返回第 4 行中的 2。

MAX()

返回当前窗口中指定行的最大值。

语法

MAX(expr) [OVER (analytic_clause)]

示例

计算从第一行到当前行之后行的最大值。此示例使用示例表 scores 中的数据。

select *,
    max(scores)
        over (
            partition by subject
            order by score
            rows between unbounded preceding and 1 following
        ) as max
from scores
where subject in ('math');

+------+-------+---------+-------+------+
| id   | name  | subject | score | max  |
+------+-------+---------+-------+------+
|    1 | lily  | math    |  NULL |   70 |
|    5 | mike  | math    |    70 |   80 |
|    2 | tom   | math    |    80 |   80 |
|    4 | amy   | math    |    80 |   92 |
|    6 | amber | math    |    92 |   95 |
|    3 | jack  | math    |    95 |   95 |
+------+-------+---------+-------+------+

以下示例计算 math 科目的所有行中的最高分数。

select *,
    max(score)
        over (
            partition by subject
            order by score
            rows between unbounded preceding and unbounded following
        ) as max
from scores
where subject in ('math');

从 StarRocks 2.4 开始，您可以将行范围指定为 rows between n preceding and n following，这意味着您可以捕获当前行之前的 n 行和当前行之后的 n 行。

示例语句

select *,
    max(score)
        over (
            partition by subject
            order by score
            rows between 3 preceding and 2 following) as max
from scores
where subject in ('math');

MIN()

返回当前窗口中指定行的最小值。

语法

MIN(expr) [OVER (analytic_clause)]

示例

计算数学科目的所有行中的最低分数。此示例使用示例表 scores 中的数据。

select *, 
    min(score)
        over (
            partition by subject
            order by score
            rows between unbounded preceding and unbounded following)
            as min
from scores
where subject in ('math');

+------+-------+---------+-------+------+
| id   | name  | subject | score | min  |
+------+-------+---------+-------+------+
|    1 | lily  | math    |  NULL |   70 |
|    5 | mike  | math    |    70 |   70 |
|    2 | tom   | math    |    80 |   70 |
|    4 | amy   | math    |    80 |   70 |
|    6 | amber | math    |    92 |   70 |
|    3 | jack  | math    |    95 |   70 |
+------+-------+---------+-------+------+

从 StarRocks 2.4 开始，您可以将行范围指定为 rows between n preceding and n following，这意味着您可以捕获当前行之前的 n 行和当前行之后的 n 行。

示例语句

select *,
    min(score)
        over (
            partition by subject
            order by score
            rows between 3 preceding and 2 following) as max
from scores
where subject in ('math');

NTILE()

NTILE() 函数按指定的 num_buckets 将分区中排序的行尽可能均匀地划分，将划分的行存储在各自的存储桶中，从 1 [1, 2, ..., num_buckets] 开始，并返回每个行所在的存储桶编号。

关于存储桶的大小

如果行计数可以被指定的 num_buckets 数完全整除，则所有存储桶的大小将相同。
如果行计数不能被指定的 num_buckets 数完全整除，则会有两个不同大小的存储桶。大小之间的差异为 1。具有更多行的存储桶将列在具有较少行的存储桶之前。

语法

NTILE (num_buckets) OVER (partition_by_clause order_by_clause)

num_buckets：要创建的存储桶的数量。该值必须是最大值为 2^63 - 1 的常量正整数。

NTILE() 函数不允许使用 Window 子句。

NTILE() 函数返回 BIGINT 类型的数据。

示例

以下示例将分区中的所有行划分为两个存储桶。此示例使用示例表 scores 中的数据。

select *,
    ntile(2)
        over (
            partition by subject
            order by score
        ) as bucket_id
from scores;

输出

+------+-------+---------+-------+-----------+
| id   | name  | subject | score | bucket_id |
+------+-------+---------+-------+-----------+
|    6 | amber | NULL    |    90 |         1 |
|    1 | lily  | math    |  NULL |         1 |
|    5 | mike  | math    |    70 |         1 |
|    2 | tom   | math    |    80 |         1 |
|    4 | amy   | math    |    80 |         2 |
|    6 | amber | math    |    92 |         2 |
|    3 | jack  | math    |    95 |         2 |
|    3 | jack  | english |  NULL |         1 |
|    5 | mike  | english |    85 |         1 |
|    4 | amy   | english |    92 |         1 |
|    2 | tom   | english |    98 |         2 |
|    1 | lily  | english |   100 |         2 |
|    2 | tom   | physics |  NULL |         1 |
|    1 | lily  | physics |    60 |         1 |
|    5 | mike  | physics |    85 |         1 |
|    3 | jack  | physics |    99 |         2 |
|    4 | amy   | physics |    99 |         2 |
|    6 | amber | physics |   100 |         2 |
+------+-------+---------+-------+-----------+

如上例所示，当 num_buckets 为 2 时

对于第一行，此分区只有此记录，并且仅分配给一个存储桶。
对于第 2 到 7 行，该分区有 6 个记录，前 3 个记录分配给存储桶 1，其他 3 个记录分配给存储桶 2。

PERCENT_RANK()

计算行在结果集中的相对排名（以百分比表示）。

PERCENT_RANK() 使用以下公式计算，其中 Rank 表示当前行在分区中的排名。

(Rank - 1)/(Rows in partition - 1)

返回值范围为 0 到 1。此函数对于百分位计算和分析数据分布很有用。自 v3.2 起受支持。

语法

PERCENT_RANK() OVER (partition_by_clause order_by_clause)

此函数必须与 ORDER BY 一起使用，以将分区行排序为所需的顺序。

示例

以下示例显示 math 组中每个 score 的相对排名。此示例使用示例表 scores 中的数据。

SELECT *,
    PERCENT_RANK()
        OVER (
            PARTITION BY subject
            ORDER BY score
        ) AS `percent_rank`
FROM scores where subject in ('math');

+------+-------+---------+-------+--------------+
| id   | name  | subject | score | percent_rank |
+------+-------+---------+-------+--------------+
|    1 | lily  | math    |  NULL |            0 |
|    5 | mike  | math    |    70 |          0.2 |
|    2 | tom   | math    |    80 |          0.4 |
|    4 | amy   | math    |    80 |          0.4 |
|    6 | amber | math    |    92 |          0.8 |
|    3 | jack  | math    |    95 |            1 |
+------+-------+---------+-------+--------------+

RANK()

RANK() 函数用于表示排名。与 DENSE_RANK() 不同，RANK() 将显示为空缺数字。例如，如果出现两个并列的 1，则 RANK() 的第三个数字将为 3 而不是 2。

语法

RANK() OVER(partition_by_clause order_by_clause)

示例

对组中的数学分数进行排名。此示例使用示例表 scores 中的数据。

select *, 
    rank() over(
        partition by subject
        order by score desc
        ) as `rank`
from scores where subject in ('math');

+------+-------+---------+-------+------+
| id   | name  | subject | score | rank |
+------+-------+---------+-------+------+
|    3 | jack  | math    |    95 |    1 |
|    6 | amber | math    |    92 |    2 |
|    4 | amy   | math    |    80 |    3 |
|    2 | tom   | math    |    80 |    3 |
|    5 | mike  | math    |    70 |    5 |
|    1 | lily  | math    |  NULL |    6 |
+------+-------+---------+-------+------+

结果数据有两行，其分数为 80。它们的排名都是 3。下一个分数 70 的排名是 5。

ROW_NUMBER()

为 Partition 的每一行返回从 1 开始的连续递增整数。与 RANK() 和 DENSE_RANK() 不同，ROW_NUMBER() 返回的值不重复或有间隙，并且是连续递增的。

语法

ROW_NUMBER() OVER(partition_by_clause order_by_clause)

示例

对组中的数学分数进行排名。此示例使用示例表 scores 中的数据。

select *, row_number() over(
    partition by subject
    order by score desc) as `rank`
from scores where subject in ('math');

+------+-------+---------+-------+------+
| id   | name  | subject | score | rank |
+------+-------+---------+-------+------+
|    3 | jack  | math    |    95 |    1 |
|    6 | amber | math    |    92 |    2 |
|    2 | tom   | math    |    80 |    3 |
|    4 | amy   | math    |    80 |    4 |
|    5 | mike  | math    |    70 |    5 |
|    1 | lily  | math    |  NULL |    6 |
+------+-------+---------+-------+------+

QUALIFY()

QUALIFY 子句筛选窗口函数的结果。在 SELECT 语句中，您可以使用 QUALIFY 子句将条件应用于列以筛选结果。QUALIFY 类似于聚合函数中的 HAVING 子句。v2.5 起支持此函数。

QUALIFY 简化了 SELECT 语句的编写。

在使用 QUALIFY 之前，SELECT 语句可能如下所示

SELECT *
FROM (SELECT DATE,
             PROVINCE_CODE,
             TOTAL_SCORE,
             ROW_NUMBER() OVER(PARTITION BY PROVINCE_CODE ORDER BY TOTAL_SCORE) AS SCORE_ROWNUMBER
      FROM example_table) T1
WHERE T1.SCORE_ROWNUMBER = 1;

使用 QUALIFY 后，语句缩短为

SELECT DATE, PROVINCE_CODE, TOTAL_SCORE
FROM example_table 
QUALIFY ROW_NUMBER() OVER(PARTITION BY PROVINCE_CODE ORDER BY TOTAL_SCORE) = 1;

QUALIFY 仅支持以下三个窗口函数：ROW_NUMBER()、RANK() 和 DENSE_RANK()。

语法

SELECT <column_list>
FROM <data_source>
[GROUP BY ...]
[HAVING ...]
QUALIFY <window_function>
[ ... ]

参数

<column_list>：要从中获取数据的列。

<data_source>：数据源通常是一个表。

<window_function>：QUALIFY 子句只能后跟一个窗口函数，包括 ROW_NUMBER()、RANK() 和 DENSE_RANK()。

示例

-- Create a table.
CREATE TABLE sales_record (
   city_id INT,
   item STRING,
   sales INT
) DISTRIBUTED BY HASH(`city_id`);

-- Insert data into the table.
insert into sales_record values
(1,'fruit',95),
(2,'drinks',70),
(3,'fruit',87),
(4,'drinks',98);

-- Query data from the table.
select * from sales_record order by city_id;
+---------+--------+-------+
| city_id | item   | sales |
+---------+--------+-------+
|       1 | fruit  |    95 |
|       2 | drinks |    70 |
|       3 | fruit  |    87 |
|       4 | drinks |    98 |
+---------+--------+-------+

示例 1：从表中获取行号大于 1 的记录。

SELECT city_id, item, sales
FROM sales_record
QUALIFY row_number() OVER (ORDER BY city_id) > 1;
+---------+--------+-------+
| city_id | item   | sales |
+---------+--------+-------+
|       2 | drinks |    70 |
|       3 | fruit  |    87 |
|       4 | drinks |    98 |
+---------+--------+-------+

示例 2：从表的每个分区中获取行号为 1 的记录。该表按 item 分为两个分区，并返回每个分区中的第一行。

SELECT city_id, item, sales
FROM sales_record 
QUALIFY ROW_NUMBER() OVER (PARTITION BY item ORDER BY city_id) = 1
ORDER BY city_id;
+---------+--------+-------+
| city_id | item   | sales |
+---------+--------+-------+
|       1 | fruit  |    95 |
|       2 | drinks |    70 |
+---------+--------+-------+
2 rows in set (0.01 sec)

示例 3：从表的每个分区中获取销售排名第一的记录。该表按 item 分为两个分区，并返回每个分区中销售额最高的行。

SELECT city_id, item, sales
FROM sales_record
QUALIFY RANK() OVER (PARTITION BY item ORDER BY sales DESC) = 1
ORDER BY city_id;
+---------+--------+-------+
| city_id | item   | sales |
+---------+--------+-------+
|       1 | fruit  |    95 |
|       4 | drinks |    98 |
+---------+--------+-------+

使用说明

QUALIFY 仅支持以下三个窗口函数：ROW_NUMBER()、RANK() 和 DENSE_RANK()。
带有 QUALIFY 的查询中子句的执行顺序按以下顺序评估

FROM
WHERE
GROUP BY
HAVING
Window
QUALIFY
DISTINCT
ORDER BY
LIMIT

SUM()

计算指定行的总和。

语法

SUM(expr) [OVER (analytic_clause)]

示例

按 subject 对数据进行分组，并计算组中所有行的分数之和。此示例使用示例表 scores 中的数据。

select *,
    sum(score)
        over (
            partition by subject
            order by score
            rows between unbounded preceding and unbounded following
        ) as 'sum'
from scores;

+------+-------+---------+-------+------+
| id   | name  | subject | score | sum  |
+------+-------+---------+-------+------+
|    6 | amber | NULL    |    90 |   90 |
|    1 | lily  | math    |  NULL |  417 |
|    5 | mike  | math    |    70 |  417 |
|    2 | tom   | math    |    80 |  417 |
|    4 | amy   | math    |    80 |  417 |
|    6 | amber | math    |    92 |  417 |
|    3 | jack  | math    |    95 |  417 |
|    3 | jack  | english |  NULL |  375 |
|    5 | mike  | english |    85 |  375 |
|    4 | amy   | english |    92 |  375 |
|    2 | tom   | english |    98 |  375 |
|    1 | lily  | english |   100 |  375 |
|    2 | tom   | physics |  NULL |  443 |
|    1 | lily  | physics |    60 |  443 |
|    5 | mike  | physics |    85 |  443 |
|    3 | jack  | physics |    99 |  443 |
|    4 | amy   | physics |    99 |  443 |
|    6 | amber | physics |   100 |  443 |
+------+-------+---------+-------+------+

VARIANCE, VAR_POP, VARIANCE_POP

返回表达式的总体方差。VAR_POP 和 VARIANCE_POP 是 VARIANCE 的别名。自 v2.5.10 起，这些函数可以用作窗口函数。

语法

VARIANCE(expr) OVER([partition_by_clause] [order_by_clause] [order_by_clause window_clause])

提示

从 2.5.13、3.0.7、3.1.4 开始，此窗口函数支持 ORDER BY 和 Window 子句。

参数

如果 expr 是一个表列，它必须评估为 TINYINT、SMALLINT、INT、BIGINT、LARGEINT、FLOAT、DOUBLE 或 DECIMAL。

示例

此示例使用示例表 scores 中的数据。

select *,
    variance(score)
        over (
            partition by subject
            order by score
        ) as 'variance'
from scores where subject in ('math');
+------+-------+---------+-------+--------------------+
| id   | name  | subject | score | variance           |
+------+-------+---------+-------+--------------------+
|    1 | lily  | math    |  NULL |               NULL |
|    5 | mike  | math    |    70 |                  0 |
|    2 | tom   | math    |    80 | 22.222222222222225 |
|    4 | amy   | math    |    80 | 22.222222222222225 |
|    6 | amber | math    |    92 |  60.74999999999997 |
|    3 | jack  | math    |    95 |  82.23999999999998 |
+------+-------+---------+-------+--------------------+

VAR_SAMP, VARIANCE_SAMP

返回表达式的样本方差。自 v2.5.10 起，这些函数可以用作窗口函数。

语法

VAR_SAMP(expr) OVER([partition_by_clause] [order_by_clause] [order_by_clause window_clause])

提示

从 2.5.13、3.0.7、3.1.4 开始，此窗口函数支持 ORDER BY 和 Window 子句。

参数

如果 expr 是一个表列，它必须评估为 TINYINT、SMALLINT、INT、BIGINT、LARGEINT、FLOAT、DOUBLE 或 DECIMAL。

示例

此示例使用示例表 scores 中的数据。

select *,
    VAR_SAMP(score)
       over (partition by subject
            order by score) as VAR_SAMP
from scores where subject in ('math');
+------+-------+---------+-------+--------------------+
| id   | name  | subject | score | VAR_SAMP           |
+------+-------+---------+-------+--------------------+
|    1 | lily  | math    |  NULL |               NULL |
|    5 | mike  | math    |    70 |                  0 |
|    2 | tom   | math    |    80 | 33.333333333333336 |
|    4 | amy   | math    |    80 | 33.333333333333336 |
|    6 | amber | math    |    92 |  80.99999999999996 |
|    3 | jack  | math    |    95 | 102.79999999999997 |
+------+-------+---------+-------+--------------------+

STD, STDDEV, STDDEV_POP

返回表达式的标准差。自 v2.5.10 起，这些函数可以用作窗口函数。

语法

STD(expr) OVER([partition_by_clause] [order_by_clause] [order_by_clause window_clause])

提示

从 2.5.13、3.0.7、3.1.4 开始，此窗口函数支持 ORDER BY 和 Window 子句。

参数

如果 expr 是一个表列，它必须评估为 TINYINT、SMALLINT、INT、BIGINT、LARGEINT、FLOAT、DOUBLE 或 DECIMAL。

示例

此示例使用示例表 scores 中的数据。

select *, STD(score)
    over (
        partition by subject
        order by score) as std
from scores where subject in ('math');
+------+-------+---------+-------+-------------------+
| id   | name  | subject | score | std               |
+------+-------+---------+-------+-------------------+
|    1 | lily  | math    |  NULL |              NULL |
|    5 | mike  | math    |    70 |                 0 |
|    4 | amy   | math    |    80 | 4.714045207910317 |
|    2 | tom   | math    |    80 | 4.714045207910317 |
|    6 | amber | math    |    92 | 7.794228634059946 |
|    3 | jack  | math    |    95 | 9.068627239003707 |
+------+-------+---------+-------+-------------------+

STDDEV_SAMP

返回表达式的样本标准差。自 v2.5.10 起，此函数可以用作窗口函数。

语法

STDDEV_SAMP(expr) OVER([partition_by_clause] [order_by_clause] [order_by_clause window_clause])

提示

从 2.5.13、3.0.7、3.1.4 开始，此窗口函数支持 ORDER BY 和 Window 子句。

参数

如果 expr 是一个表列，它必须评估为 TINYINT、SMALLINT、INT、BIGINT、LARGEINT、FLOAT、DOUBLE 或 DECIMAL。

示例

此示例使用示例表 scores 中的数据。

select *, STDDEV_SAMP(score)
    over (
        partition by subject
        order by score
        ) as STDDEV_SAMP
from scores where subject in ('math');
+------+-------+---------+-------+--------------------+
| id   | name  | subject | score | STDDEV_SAMP        |
+------+-------+---------+-------+--------------------+
|    1 | lily  | math    |  NULL |               NULL |
|    5 | mike  | math    |    70 |                  0 |
|    2 | tom   | math    |    80 |  5.773502691896258 |
|    4 | amy   | math    |    80 |  5.773502691896258 |
|    6 | amber | math    |    92 |  8.999999999999998 |
|    3 | jack  | math    |    95 | 10.139033484509259 |
+------+-------+---------+-------+--------------------+

select *, STDDEV_SAMP(score)
    over (
        partition by subject
        order by score
        rows between unbounded preceding and 1 following) as STDDEV_SAMP
from scores where subject in ('math');
+------+-------+---------+-------+--------------------+
| id   | name  | subject | score | STDDEV_SAMP        |
+------+-------+---------+-------+--------------------+
|    1 | lily  | math    |  NULL |                  0 |
|    5 | mike  | math    |    70 | 7.0710678118654755 |
|    2 | tom   | math    |    80 |  5.773502691896258 |
|    4 | amy   | math    |    80 |  8.999999999999998 |
|    6 | amber | math    |    92 | 10.139033484509259 |
|    3 | jack  | math    |    95 | 10.139033484509259 |
+------+-------+---------+-------+--------------------+

COVAR_SAMP

返回两个表达式的样本协方差。自 v2.5.10 起支持此函数。它也是一个聚合函数。

语法

COVAR_SAMP(expr1,expr2) OVER([partition_by_clause] [order_by_clause] [order_by_clause window_clause])

提示

从 2.5.13、3.0.7、3.1.4 开始，此窗口函数支持 ORDER BY 和 Window 子句。

参数

如果 expr 是一个表列，它必须评估为 TINYINT、SMALLINT、INT、BIGINT、LARGEINT、FLOAT、DOUBLE 或 DECIMAL。

示例

此示例使用示例表 scores 中的数据。

select *, COVAR_SAMP(id, score) 
    over (
        partition by subject
        order by score) as covar_samp
from scores where subject in ('math');
+------+-------+---------+-------+----------------------+
| id   | name  | subject | score | covar_samp           |
+------+-------+---------+-------+----------------------+
|    1 | lily  | math    |  NULL |                 NULL |
|    5 | mike  | math    |    70 |                    0 |
|    2 | tom   | math    |    80 |   -6.666666666666668 |
|    4 | amy   | math    |    80 |   -6.666666666666668 |
|    6 | amber | math    |    92 |                  4.5 |
|    3 | jack  | math    |    95 | -0.24999999999999822 |
+------+-------+---------+-------+----------------------+

select *, COVAR_SAMP(id,score)
    over (
        partition by subject
        order by score
        rows between unbounded preceding and 1 following) as COVAR_SAMP
from scores where subject in ('math');
+------+-------+---------+-------+----------------------+
| id   | name  | subject | score | COVAR_SAMP           |
+------+-------+---------+-------+----------------------+
|    1 | lily  | math    |  NULL |                    0 |
|    5 | mike  | math    |    70 |                   -5 |
|    4 | amy   | math    |    80 |   -6.666666666666661 |
|    2 | tom   | math    |    80 |    4.500000000000004 |
|    6 | amber | math    |    92 | -0.24999999999999467 |
|    3 | jack  | math    |    95 | -0.24999999999999467 |

COVAR_POP

返回两个表达式的总体协方差。自 v2.5.10 起支持此函数。它也是一个聚合函数。

语法

COVAR_POP(expr1, expr2) OVER([partition_by_clause] [order_by_clause] [order_by_clause window_clause])

提示

从 2.5.13、3.0.7、3.1.4 开始，此窗口函数支持 ORDER BY 和 Window 子句。

参数

如果 expr 是一个表列，它必须评估为 TINYINT、SMALLINT、INT、BIGINT、LARGEINT、FLOAT、DOUBLE 或 DECIMAL。

示例

此示例使用示例表 scores 中的数据。

select *, COVAR_POP(id, score)
    over (
        partition by subject
        order by score) as covar_pop
from scores where subject in ('math');
+------+-------+---------+-------+----------------------+
| id   | name  | subject | score | covar_pop            |
+------+-------+---------+-------+----------------------+
|    1 | lily  | math    |  NULL |                 NULL |
|    5 | mike  | math    |    70 |                    0 |
|    2 | tom   | math    |    80 |  -4.4444444444444455 |
|    4 | amy   | math    |    80 |  -4.4444444444444455 |
|    6 | amber | math    |    92 |                3.375 |
|    3 | jack  | math    |    95 | -0.19999999999999857 |
+------+-------+---------+-------+----------------------+

CORR

返回两个表达式之间的 Pearson 相关系数。自 v2.5.10 起支持此函数。它也是一个聚合函数。

语法

CORR(expr1, expr2) OVER([partition_by_clause] [order_by_clause] [order_by_clause window_clause])

提示

从 2.5.13、3.0.7、3.1.4 开始，此窗口函数支持 ORDER BY 和 Window 子句。

参数

如果 expr 是一个表列，它必须评估为 TINYINT、SMALLINT、INT、BIGINT、LARGEINT、FLOAT、DOUBLE 或 DECIMAL。

示例

此示例使用示例表 scores 中的数据。

select *, CORR(id, score)
    over (
        partition by subject
        order by score) as corr
from scores where subject in ('math');
+------+-------+---------+-------+-----------------------+
| id   | name  | subject | score | corr                  |
+------+-------+---------+-------+-----------------------+
|    5 | mike  | math    |    70 | -0.015594571538795355 |
|    1 | lily  | math    |  NULL | -0.015594571538795355 |
|    2 | tom   | math    |    80 | -0.015594571538795355 |
|    4 | amy   | math    |    80 | -0.015594571538795355 |
|    3 | jack  | math    |    95 | -0.015594571538795355 |
|    6 | amber | math    |    92 | -0.015594571538795355 |
+------+-------+---------+-------+-----------------------+

select *, CORR(id,score)
    over (
        partition by subject
        order by score
        rows between unbounded preceding and 1 following) as corr 
from scores where subject in ('math');
+------+-------+---------+-------+-------------------------+
| id   | name  | subject | score | corr                    |
+------+-------+---------+-------+-------------------------+
|    1 | lily  | math    |  NULL | 1.7976931348623157e+308 |
|    5 | mike  | math    |    70 |                      -1 |
|    2 | tom   | math    |    80 |     -0.7559289460184546 |
|    4 | amy   | math    |    80 |     0.29277002188455997 |
|    6 | amber | math    |    92 |   -0.015594571538795024 |
|    3 | jack  | math    |    95 |   -0.015594571538795024 |
+------+-------+---------+-------+-------------------------+

背景​

用法​

语法​

PARTITION BY 子句​

ORDER BY 子句​

Window 子句​

窗口函数示例表​

函数示例​

AVG()​

COUNT()​

CUME_DIST()​

DENSE_RANK()​

FIRST_VALUE()​

LAST_VALUE()​

LAG()​

LEAD()​

MAX()​

MIN()​

NTILE()​

PERCENT_RANK()​

RANK()​

ROW_NUMBER()​

QUALIFY()​

SUM()​

VARIANCE, VAR_POP, VARIANCE_POP​

VAR_SAMP, VARIANCE_SAMP​

STD, STDDEV, STDDEV_POP​

STDDEV_SAMP​

COVAR_SAMP​

COVAR_POP​

CORR​

您觉得这篇文档怎么样？

背景

用法

语法

PARTITION BY 子句

ORDER BY 子句

Window 子句

窗口函数示例表

函数示例

AVG()

COUNT()

CUME_DIST()

DENSE_RANK()

FIRST_VALUE()

LAST_VALUE()

LAG()

LEAD()

MAX()

MIN()

NTILE()

PERCENT_RANK()

RANK()

ROW_NUMBER()

QUALIFY()

SUM()

VARIANCE, VAR_POP, VARIANCE_POP

VAR_SAMP, VARIANCE_SAMP

STD, STDDEV, STDDEV_POP

STDDEV_SAMP

COVAR_SAMP

COVAR_POP

CORR