类别：: 查询语法

PIVOT¶

通过将输入表达式中的一列中的唯一值转换为多列并根据需要聚合任何剩余列值的结果来旋转表。在查询中，它在表名或子查询后面的 FROM 子句中指定。

该运算符支持内置聚合函数 AVG、COUNT、MAX、MIN 和 SUM

PIVOT 可用于将窄表（例如 empid、month、sales）转换为更宽的表（例如 empid、jan_sales、feb_sales、mar_sales）。

另请参阅：: UNPIVOT

语法¶

SELECT ...
FROM ...
   PIVOT ( <aggregate_function> ( <pivot_column> )
            FOR <value_column> IN (
              <pivot_value_1> [ , <pivot_value_2> ... ]
              | ANY [ ORDER BY ... ]
              | <subquery>
            )
            [ DEFAULT ON NULL (<value>) ]
         )

[ ... ]

Copy

参数¶

aggregate_function: 用于组合来自 pivot_column 的分组值的聚合函数。
pivot_column: 源表或子查询中将被聚合的列。
value_column: 源表或子查询中的列，其中包含将用于生成列名的值。
pivot_value_N: 将数据透视列的值列表转换为查询结果中的标题。
ANY [ ORDER BY ... ]: 功能支持的区域

此功能在中华人民共和国不可用。

对透视列的所有不同值执行透视。要控制输出中透视列的顺序，请在 ANY 关键字后指定 ORDER BY 子句。如果透视列包含 NULLs，则 NULL 也被视为透视值。
subquery: 功能支持的区域

此功能在中华人民共和国不可用。

对在子查询中找到的所有值执行透视。如果子查询包含 ORDER BY 子句，则需要 DISTINCT 关键字。子查询必须是返回单个列的不相关子查询。对子查询返回的所有不同值执行透视。有关不相关子查询的信息，请参阅使用子查询。
DEFAULT ON NULL (value): 使用指定的默认值替换透视结果中的所有 NULL 值。默认值可以是任何不依赖于透视和聚合列的标量表达式。

使用说明¶

Snowflake 支持 动态透视。动态透视查询使用 ANY 关键字或 PIVOT 分子句中的子查询，而不是显式指定透视值。
在视图定义中使用动态透视时，如果底层数据发生变化，导致透视输出列发生变化，则在视图上执行的查询可能会失败。
在存储过程或用户定义的函数 (UDF) 的主体中不支持动态透视。
不使用动态透视的透视查询可以返回含有重复列的输出。我们建议避免含有重复列的输出。动态透视查询会对重复列进行去重。
如果不使用动态透视的透视查询尝试执行以下操作，则可能会失败：将 VARIANT 列 CAST 为不同的数据类型。动态透视查询没有此限制。
目前，PIVOT 语义不允许多重聚合，但您可以使用 PIVOT 及 UNION 运算符来实现类似的结果。有关示例，请参阅使用 UNION 对多重聚合执行动态透视。

示例¶

PIVOT 示例使用以下 quarterly_sales 表：

CREATE OR REPLACE TABLE quarterly_sales(
  empid INT,
  amount INT,
  quarter TEXT)
  AS SELECT * FROM VALUES
    (1, 10000, '2023_Q1'),
    (1, 400, '2023_Q1'),
    (2, 4500, '2023_Q1'),
    (2, 35000, '2023_Q1'),
    (1, 5000, '2023_Q2'),
    (1, 3000, '2023_Q2'),
    (2, 200, '2023_Q2'),
    (2, 90500, '2023_Q2'),
    (1, 6000, '2023_Q3'),
    (1, 5000, '2023_Q3'),
    (2, 2500, '2023_Q3'),
    (2, 9500, '2023_Q3'),
    (3, 2700, '2023_Q3'),
    (1, 8000, '2023_Q4'),
    (1, 10000, '2023_Q4'),
    (2, 800, '2023_Q4'),
    (2, 4500, '2023_Q4'),
    (3, 2700, '2023_Q4'),
    (3, 16000, '2023_Q4'),
    (3, 10200, '2023_Q4');

Copy

以下示例使用 PIVOT：

自动对所有不同的列值执行动态透视
使用子查询对列值执行动态透视
使用 UNION 对多重聚合执行动态透视
对联接查询执行动态透视
对透视列的指定列值列表执行透视
使用默认值替换 NULL 值的透视
涉及多列的透视示例

自动对所有不同的列值执行动态透视¶

给定表 quarterly_sales，使用 ANY 关键字来计算所有不同季度每位员工的总销售额之和，以对 amount 列执行透视，并通过指定 ORDER BY 使透视列按顺序排列：

SELECT *
  FROM quarterly_sales
    PIVOT(SUM(amount) FOR quarter IN (ANY ORDER BY quarter))
  ORDER BY empid;

Copy

+-------+-----------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' |
|-------+-----------+-----------+-----------+-----------|
|     1 |     10400 |      8000 |     11000 |     18000 |
|     2 |     39500 |     90700 |     12000 |      5300 |
|     3 |      NULL |      NULL |      2700 |     28900 |
+-------+-----------+-----------+-----------+-----------+

使用子查询对列值执行动态透视¶

假设除了 quarterly_sales 表之外，ad_campaign_types_by_quarter 表也在跟踪在特定季度投放的广告的类型。该表具有以下结构和数据：

CREATE OR REPLACE TABLE ad_campaign_types_by_quarter(
  quarter VARCHAR,
  television BOOLEAN,
  radio BOOLEAN,
  print BOOLEAN)
  AS SELECT * FROM VALUES
    ('2023_Q1', TRUE, FALSE, FALSE),
    ('2023_Q2', FALSE, TRUE, TRUE),
    ('2023_Q3', FALSE, TRUE, FALSE),
    ('2023_Q4', TRUE, FALSE, TRUE);

Copy

您可以在透视查询中使用子查询来确定开展了特定广告活动的季度的销售额总和。例如，以下透视查询仅返回开展了电视广告活动的季度的数据：

SELECT *
  FROM quarterly_sales
    PIVOT(SUM(amount) FOR quarter IN (
      SELECT DISTINCT quarter
        FROM ad_campaign_types_by_quarter
        WHERE television = TRUE
        ORDER BY quarter))
  ORDER BY empid;

Copy

+-------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q4' |
|-------+-----------+-----------|
|     1 |     10400 |     18000 |
|     2 |     39500 |      5300 |
|     3 |      NULL |     28900 |
+-------+-----------+-----------+

使用 UNION 对多重聚合执行动态透视¶

您可以使用 UNION 运算符在单个结果集中显示多个聚合。本例使用动态透视和 UNION 运算符显示每个季度每位员工的以下信息：

使用 AVG 函数计算单笔销售的平均金额。
使用 MAX 函数确定价值最高的销售。
使用 MIN 函数确定价值最低的销售。
使用 COUNT 函数确定销售笔数。
使用 SUM 函数计算所有销售的总额。

运行查询：

SELECT 'Average sale amount' AS aggregate, *
  FROM quarterly_sales
    PIVOT(AVG(amount) FOR quarter IN (ANY ORDER BY quarter))
UNION
SELECT 'Highest value sale' AS aggregate, *
  FROM quarterly_sales
    PIVOT(MAX(amount) FOR quarter IN (ANY ORDER BY quarter))
UNION
SELECT 'Lowest value sale' AS aggregate, *
  FROM quarterly_sales
    PIVOT(MIN(amount) FOR quarter IN (ANY ORDER BY quarter))
UNION
SELECT 'Number of sales' AS aggregate, *
  FROM quarterly_sales
    PIVOT(COUNT(amount) FOR quarter IN (ANY ORDER BY quarter))
UNION
SELECT 'Total amount' AS aggregate, *
  FROM quarterly_sales
    PIVOT(SUM(amount) FOR quarter IN (ANY ORDER BY quarter))
ORDER BY aggregate, empid;

Copy

+---------------------+-------+--------------+--------------+--------------+--------------+
| AGGREGATE           | EMPID |    '2023_Q1' |    '2023_Q2' |    '2023_Q3' |    '2023_Q4' |
|---------------------+-------+--------------+--------------+--------------+--------------|
| Average sale amount |     1 |  5200.000000 |  4000.000000 |  5500.000000 |  9000.000000 |
| Average sale amount |     2 | 19750.000000 | 45350.000000 |  6000.000000 |  2650.000000 |
| Average sale amount |     3 |         NULL |         NULL |  2700.000000 |  9633.333333 |
| Highest value sale  |     1 | 10000.000000 |  5000.000000 |  6000.000000 | 10000.000000 |
| Highest value sale  |     2 | 35000.000000 | 90500.000000 |  9500.000000 |  4500.000000 |
| Highest value sale  |     3 |         NULL |         NULL |  2700.000000 | 16000.000000 |
| Lowest value sale   |     1 |   400.000000 |  3000.000000 |  5000.000000 |  8000.000000 |
| Lowest value sale   |     2 |  4500.000000 |   200.000000 |  2500.000000 |   800.000000 |
| Lowest value sale   |     3 |         NULL |         NULL |  2700.000000 |  2700.000000 |
| Number of sales     |     1 |     2.000000 |     2.000000 |     2.000000 |     2.000000 |
| Number of sales     |     2 |     2.000000 |     2.000000 |     2.000000 |     2.000000 |
| Number of sales     |     3 |     0.000000 |     0.000000 |     1.000000 |     3.000000 |
| Total amount        |     1 | 10400.000000 |  8000.000000 | 11000.000000 | 18000.000000 |
| Total amount        |     2 | 39500.000000 | 90700.000000 | 12000.000000 |  5300.000000 |
| Total amount        |     3 |         NULL |         NULL |  2700.000000 | 28900.000000 |
+---------------------+-------+--------------+--------------+--------------+--------------+

对联接查询执行动态透视¶

要在带联接的查询中执行透视，可以使用通用表表达式 (CTE) 进行透视查询。

例如，假设有一个简单的表将员工映射到经理：

CREATE OR REPLACE TABLE emp_manager(
    empid INT,
    managerid INT)
  AS SELECT * FROM VALUES
    (1, 7),
    (2, 8),
    (3, 9);

SELECT * from emp_manager;

Copy

+-------+-----------+
| EMPID | MANAGERID |
|-------+-----------|
|     1 |         7 |
|     2 |         8 |
|     3 |         9 |
+-------+-----------+

运行一个查询，联接 emp_manager 表和 quarterly_sales 表，并对 quarterly_sales 表中的 amount 列执行透视：

WITH
  src AS
  (
    SELECT *
      FROM quarterly_sales
        PIVOT(SUM(amount) FOR quarter IN (ANY ORDER BY quarter))
  )
SELECT em.managerid, src.*
  FROM emp_manager em
  JOIN src ON em.empid = src.empid
  ORDER BY empid;

Copy

+-----------+-------+-----------+-----------+-----------+-----------+
| MANAGERID | EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' |
|-----------+-------+-----------+-----------+-----------+-----------|
|         7 |     1 |     10400 |      8000 |     11000 |     18000 |
|         8 |     2 |     39500 |     90700 |     12000 |      5300 |
|         9 |     3 |      NULL |      NULL |      2700 |     28900 |
+-----------+-------+-----------+-----------+-----------+-----------+

对透视列的指定列值列表执行透视¶

给定表 quarterly_sales，对 amount 列执行透视，以计算指定季度每个员工的总销售额之和：

SELECT *
  FROM quarterly_sales
    PIVOT(SUM(amount) FOR quarter IN (
      '2023_Q1',
      '2023_Q2',
      '2023_Q3'))
  ORDER BY empid;

Copy

+-------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' |
|-------+-----------+-----------+-----------|
|     1 |     10400 |      8000 |     11000 |
|     2 |     39500 |     90700 |     12000 |
|     3 |      NULL |      NULL |      2700 |
+-------+-----------+-----------+-----------+

通过运行以下查询，可以对 amount 列中的所有季度执行透视：

SELECT *
  FROM quarterly_sales
    PIVOT(SUM(amount) FOR quarter IN (
      '2023_Q1',
      '2023_Q2',
      '2023_Q3',
      '2023_Q4'))
  ORDER BY empid;

Copy

+-------+-----------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' |
|-------+-----------+-----------+-----------+-----------|
|     1 |     10400 |      8000 |     11000 |     18000 |
|     2 |     39500 |     90700 |     12000 |      5300 |
|     3 |      NULL |      NULL |      2700 |     28900 |
+-------+-----------+-----------+-----------+-----------+

您可以使用 AS 子句修改输出中的列名。例如，要缩短列名并显示不带引号的列名，请运行以下查询：

SELECT *
  FROM quarterly_sales
    PIVOT(SUM(amount) FOR quarter IN (
      '2023_Q1',
      '2023_Q2',
      '2023_Q3',
      '2023_Q4')) AS p (employee, q1, q2, q3, q4)
  ORDER BY employee;

Copy

+----------+-------+-------+-------+-------+
| EMPLOYEE |    Q1 |    Q2 |    Q3 |    Q4 |
|----------+-------+-------+-------+-------|
|        1 | 10400 |  8000 | 11000 | 18000 |
|        2 | 39500 | 90700 | 12000 |  5300 |
|        3 |  NULL |  NULL |  2700 | 28900 |
+----------+-------+-------+-------+-------+

您还可以在 SELECT 列表中列出特定透视列，并更改列名：

SELECT empid,
       "'2023_Q1'" AS q1,
       "'2023_Q2'" AS q2,
       "'2023_Q3'" AS q3,
       "'2023_Q4'" AS q4
  FROM quarterly_sales
    PIVOT(sum(amount) FOR quarter IN (
      '2023_Q1',
      '2023_Q2',
      '2023_Q3',
      '2023_Q4'))
  ORDER BY empid;

Copy

+-------+-------+-------+-------+-------+
| EMPID |    Q1 |    Q2 |    Q3 |    Q4 |
|-------+-------+-------+-------+-------|
|     1 | 10400 |  8000 | 11000 | 18000 |
|     2 | 39500 | 90700 | 12000 |  5300 |
|     3 |  NULL |  NULL |  2700 | 28900 |
+-------+-------+-------+-------+-------+

使用默认值替换 NULL 值的透视¶

如果查询返回 NULL 值，您可以通过 DEFAULT ON NULL 来用默认值替换这些值。例如，您可以使用动态透视，并通过运行以下查询来用默认值 0 替换 NULL 值：

SELECT *
  FROM quarterly_sales
    PIVOT(SUM(amount) FOR quarter IN (ANY ORDER BY quarter)
      DEFAULT ON NULL (0))
  ORDER BY empid;

Copy

+-------+-----------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' |
|-------+-----------+-----------+-----------+-----------|
|     1 |     10400 |      8000 |     11000 |     18000 |
|     2 |     39500 |     90700 |     12000 |      5300 |
|     3 |         0 |         0 |      2700 |     28900 |
+-------+-----------+-----------+-----------+-----------+

您还可以使用 DEFAULT ON NULL 和指定的列列表：

SELECT *
  FROM quarterly_sales
    PIVOT(SUM(amount)
      FOR quarter IN (
        '2023_Q1',
        '2023_Q2')
      DEFAULT ON NULL (0))
  ORDER BY empid;

Copy

+-------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' |
|-------+-----------+-----------|
|     1 |     10400 |      8000 |
|     2 |     39500 |     90700 |
|     3 |         0 |         0 |
+-------+-----------+-----------+

涉及多列的透视示例¶

透视查询可以涉及多列。在运行这些示例之前，请在 quarterly_sales 表中添加一列，并在该列中填入随机值。

首先，在 quarterly_sales 表中添加一列，跟踪每笔销售应用的折扣：

ALTER TABLE quarterly_sales ADD COLUMN discount_percent INT DEFAULT 0;

Copy

用 0 和 5 之间的随机值填充新列，用于指定每笔销售的折扣百分比：

UPDATE quarterly_sales SET discount_percent = UNIFORM(0, 5, RANDOM());

Copy

查询表以显示已添加随机值的新列：

SELECT * FROM quarterly_sales;

Copy

+-------+--------+---------+------------------+
| EMPID | AMOUNT | QUARTER | DISCOUNT_PERCENT |
|-------+--------+---------+------------------|
|     1 |  10000 | 2023_Q1 |                0 |
|     1 |    400 | 2023_Q1 |                1 |
|     2 |   4500 | 2023_Q1 |                4 |
|     2 |  35000 | 2023_Q1 |                2 |
|     1 |   5000 | 2023_Q2 |                2 |
|     1 |   3000 | 2023_Q2 |                1 |
|     2 |    200 | 2023_Q2 |                2 |
|     2 |  90500 | 2023_Q2 |                1 |
|     1 |   6000 | 2023_Q3 |                1 |
|     1 |   5000 | 2023_Q3 |                3 |
|     2 |   2500 | 2023_Q3 |                1 |
|     2 |   9500 | 2023_Q3 |                3 |
|     3 |   2700 | 2023_Q3 |                1 |
|     1 |   8000 | 2023_Q4 |                1 |
|     1 |  10000 | 2023_Q4 |                4 |
|     2 |    800 | 2023_Q4 |                3 |
|     2 |   4500 | 2023_Q4 |                5 |
|     3 |   2700 | 2023_Q4 |                3 |
|     3 |  16000 | 2023_Q4 |                0 |
|     3 |  10200 | 2023_Q4 |                1 |
+-------+--------+---------+------------------+

在新列已添加并填充完毕后，运行以下示例：

使用 CTE 从透视查询中排除列
运行多维透视查询

使用 CTE 从透视查询中排除列¶

可以使用通用表表达式 (CTE) 从数据透视查询中排除列。

下面的示例使用 CTE 将 discount_percent 列排除在数据透视查询之外：

WITH
  sales_without_discount AS
    (SELECT * EXCLUDE(discount_percent) FROM quarterly_sales)
SELECT *
  FROM sales_without_discount
    PIVOT(SUM(amount) FOR quarter IN (ANY ORDER BY quarter))
  ORDER BY empid;

Copy

+-------+-----------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' |
|-------+-----------+-----------+-----------+-----------|
|     1 |     10400 |      8000 |     11000 |     18000 |
|     2 |     39500 |     90700 |     12000 |      5300 |
|     3 |      NULL |      NULL |      2700 |     28900 |
+-------+-----------+-----------+-----------+-----------+

您可以使用 CTE 来排除 amount 列，并显示每位员工在每个季度提供的平均折扣：

WITH
  sales_without_amount AS
    (SELECT * EXCLUDE(amount) FROM quarterly_sales)
SELECT *
  FROM sales_without_amount
    PIVOT(AVG(discount_percent) FOR quarter IN (ANY ORDER BY quarter))
  ORDER BY empid;

Copy

+-------+-----------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' |
|-------+-----------+-----------+-----------+-----------|
|     1 |  0.500000 |  1.500000 |  2.000000 |  2.500000 |
|     2 |  3.000000 |  1.500000 |  2.000000 |  4.000000 |
|     3 |      NULL |      NULL |  1.000000 |  1.333333 |
+-------+-----------+-----------+-----------+-----------+

运行多维透视查询¶

多维透视查询对多个列执行透视。本示例对 amount 列和 discount_percentage 列进行透视。该查询会返回每个季度所有员工的所有销售额总和，以及每个季度所有销售的最大折扣百分比。

在查询中，SELECT 列表使用 $col_position 参数依次对返回的列运行 SUM 和 MAX 函数，并为返回的列命名。FROM 子查询为透视操作提供数据。由于输出结果显示的是所有员工的销售结果，因此子查询不包括 empid 列。

SELECT SUM($1) AS q1_sales_total,
       SUM($2) AS q2_sales_total,
       SUM($3) AS q3_sales_total,
       SUM($4) AS q4_sales_total,
       MAX($5) AS q1_maximum_discount,
       MAX($6) AS q2_maximum_discount,
       MAX($7) AS q3_maximum_discount,
       MAX($8) AS q4_maximum_discount
  FROM
    (SELECT amount,
            quarter AS quarter_amount,
            quarter AS quarter_discount,
            discount_percent
      FROM quarterly_sales)
  PIVOT (
    SUM(amount)
    FOR quarter_amount IN (
      '2023_Q1',
      '2023_Q2',
      '2023_Q3',
      '2023_Q4'))
  PIVOT (
    MAX(discount_percent)
    FOR quarter_discount IN (
      '2023_Q1',
      '2023_Q2',
      '2023_Q3',
      '2023_Q4'));

Copy

+----------------+----------------+----------------+----------------+---------------------+---------------------+---------------------+---------------------+
| Q1_SALES_TOTAL | Q2_SALES_TOTAL | Q3_SALES_TOTAL | Q4_SALES_TOTAL | Q1_MAXIMUM_DISCOUNT | Q2_MAXIMUM_DISCOUNT | Q3_MAXIMUM_DISCOUNT | Q4_MAXIMUM_DISCOUNT |
|----------------+----------------+----------------+----------------+---------------------+---------------------+---------------------+---------------------|
|          49900 |          98700 |          25700 |          52200 |                   4 |                   2 |                   3 |                   5 |
+----------------+----------------+----------------+----------------+---------------------+---------------------+---------------------+---------------------+