Categorias:: Sintaxe de consulta

PIVOT¶

Gira uma tabela transformando os valores únicos de uma coluna na expressão de entrada em várias colunas e agregando resultados onde for necessário em quaisquer valores restantes da coluna. Em uma consulta, ela é especificada na cláusula FROM após o nome da tabela ou subconsulta.

PIVOT oferece suporte às seguintes funções de agregação integradas:

PIVOT pode ser usado para transformar uma tabela estreita (por exemplo, empid, month, sales) em uma tabela mais ampla (por exemplo, empid, jan_sales, feb_sales, mar_sales).

Consulte também:: UNPIVOT

Sintaxe¶

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

[ ... ]

Parâmetros¶

aggregate_function

A função agregada para combinar os valores agrupados de pivot_column.

pivot_column [ [ AS ] alias ]

A coluna da tabela de origem ou subconsulta que será agregada.

A opcional [ AS ] alias A cláusula especifica o alias a ser usado para o agregado no resultado da PIVOT operação. Um sublinhado e, em seguida, o alias são anexados a cada nome de coluna pivô. Por exemplo, se o alias é total, a operação dinâmica anexa _TOTAL aos nomes das colunas dinâmicas. A palavra-chave AS é opcional.

value_column

A coluna da tabela ou subconsulta de origem que contém os valores a partir dos quais os nomes das colunas serão gerados.

pivot_value_N [ [ AS ] alias ]

Uma lista de valores para a coluna pivô para articular em cabeçalhos nos resultados da consulta.

A opcional [ AS ] alias A cláusula especifica o alias a ser usado para o valor no resultado do PIVOT operação. O alias substitui o valor.

ANY [ ORDER BY ... ]

Articule todos os valores distintos da coluna pivô. Para controlar a ordem das colunas dinâmicas na saída, especifique uma cláusula ORDER BY após a palavra-chave ANY. Se a coluna dinâmica contiver NULLs, então NULL também é tratado como um valor de pivô.

subquery

Articule todos os valores encontrados na subconsulta. A palavra-chave DISTINCT é necessária se a subconsulta incluir uma cláusula ORDER BY. A subconsulta deve ser uma subconsulta não correlacionada que retorne uma única coluna. O pivotamento é executado em todos os valores distintos retornados pela subconsulta. Para obter informações sobre subconsultas não correlacionadas, consulte Como trabalhar com subconsultas.

DEFAULT ON NULL (value)

Substitui todos os valores de NULL no resultado dinâmico pelo valor padrão especificado. O valor padrão pode ser qualquer expressão escalar que não dependa da coluna de pivô e agregação.

Notas de uso¶

O Snowflake oferece suporte ao pivô dinâmico. Uma consulta dinâmica de pivô usa a palavra-chave ANY ou uma subconsulta na subcláusula PIVOT em vez de especificar os valores de pivô explicitamente.
Quando o pivô dinâmico é usado em uma definição de exibição, as consultas na exibição podem falhar se os dados subjacente forem alterados de forma que as colunas de saída do pivô sejam alteradas.
O pivô dinâmico não é compatível no corpo de um procedimento armazenado ou função definida pelo usuário (UDF).
Uma consulta dinâmica que não usa pivô dinâmico pode retornar saídas com colunas duplicadas. Recomendamos evitar saída com colunas duplicadas. Uma consulta de pivô desduplica colunas duplicadas.
Uma consulta dinâmica que não usa pivô dinâmico pode falhar se tentar CAST uma coluna VARIANT em um tipo de dados diferente. As consultas dinâmicas de pivô não têm essa limitação.
Atualmente, a semântica de PIVOT não permite várias agregações, mas é possível obter resultados semelhantes usando PIVOT com o operador UNION. Para obter um exemplo, consulte Pivô dinâmico com várias agregações usando UNION.

Exemplos¶

Os exemplos de PIVOT utilizam a seguinte tabela 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');

Os exemplos a seguir usam PIVOT:

Pivô dinâmico em todos os valores distintos da coluna automaticamente
Pivô dinâmico em valores de coluna usando uma subconsulta
Pivô dinâmico com várias agregações usando UNION
Pivô dinâmico com uma consulta de junção
Articulação em uma lista especificada de valores de coluna para a coluna de pivô
Como dinamizar com um valor padrão para NULL valores
Exemplos de dinamização que envolvem várias colunas

Pivô dinâmico em todos os valores distintos da coluna automaticamente¶

Dada a tabela quarterly_sales, faça a articulação na coluna amount usando a palavra-chave ANY para somar as vendas totais por funcionário para todos os trimestres distintos e especificar ORDER BY para que as colunas do pivô fiquem em ordem:

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

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

O exemplo a seguir é o mesmo que o exemplo anterior, mas anexa o alias _TOTAL para cada nome de coluna pivô:

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

+-------+-----------------+-----------------+-----------------+-----------------+
| EMPID | '2023_Q1_TOTAL' | '2023_Q2_TOTAL' | '2023_Q3_TOTAL' | '2023_Q4_TOTAL' |
|-------+-----------------+-----------------+-----------------+-----------------|
|     1 |           10400 |            8000 |           11000 |           18000 |
|     2 |           39500 |           90700 |           12000 |            5300 |
|     3 |            NULL |            NULL |            2700 |           28900 |
+-------+-----------------+-----------------+-----------------+-----------------+

Pivô dinâmico em valores de coluna usando uma subconsulta¶

Suponha que, além da tabela quarterly_sales, uma tabela ad_campaign_types_by_quarter rastreie os tipos de anúncios veiculados durante trimestres específicos. Esta tabela tem a seguinte estrutura e dados:

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);

Você pode usar uma subconsulta em uma consulta dinâmica para determinar a soma das vendas nos trimestres que tiveram campanhas publicitárias específicas. Por exemplo, a seguinte consulta de pivô retorna dados somente para trimestres com campanhas publicitárias de televisão:

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;

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

Pivô dinâmico com várias agregações usando UNION¶

É possível usar o operador UNION para mostrar várias agregações em um único conjunto de resultados. Este exemplo usa o pivô dinâmico e o operador UNION para mostrar as seguintes informações de cada funcionário em cada trimestre:

O valor médio de uma venda, usando a função AVG.
A venda com o valor mais alto, usando a função MAX.
A venda com o menor valor, usando a função MIN.
O número de vendas, usando a função COUNT.
O valor total de todas as vendas, usando a função SUM.

Executar a consulta:

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;

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

Pivô dinâmico com uma consulta de junção¶

Para dinamizar em uma consulta com uma junção, é possível usar uma expressão de tabela comum (CTE) para a consulta dinâmica.

Por exemplo, suponha que uma tabela simples mapeie funcionários para gerentes:

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

SELECT * from emp_manager;

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

Execute uma consulta que una a tabela emp_manager e a tabela quarterly_sales e dinamize a coluna amount na tabela quarterly_sales:

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;

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

Articulação em uma lista especificada de valores de coluna para a coluna de pivô¶

Dada a tabela quarterly_sales, articule a coluna amount para somar as vendas totais por funcionário nos trimestres especificados:

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

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

Você pode girar articular todos os trimestres na coluna amount executando a seguinte consulta:

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

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

Você pode modificar os nomes das colunas na saída com a cláusula AS. Por exemplo, para encurtar os nomes das colunas e mostrá-los sem aspas, execute a seguinte consulta:

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

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

Como dinamizar com um valor padrão para NULL valores¶

Se a consulta retornar valores NULL, será possível substituí-los por um valor padrão usando DEFAULT ON NULL. Por exemplo, é possível usar o pivô dinâmico e substituir os valores NULL por um valor padrão de 0 executando a seguinte consulta:

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

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

Você também pode usar DEFAULT ON NULL com uma lista de colunas especificada:

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

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

Exemplos de dinamização que envolvem várias colunas¶

As consultas dinâmicas podem trabalhar com várias colunas. Antes de executar esses exemplos, adicione uma coluna à tabela quarterly_sales e preencha a coluna com valores aleatórios.

Primeiro, adicione uma coluna que rastreie o desconto aplicado a cada venda na tabela quarterly_sales:

ALTER TABLE quarterly_sales ADD COLUMN discount_percent INT DEFAULT 0;

Preencha a nova coluna com valores aleatórios entre 0 e 5, que especificam a porcentagem de desconto para cada venda:

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

Consulte a tabela para mostrar a nova coluna com os valores aleatórios adicionados:

SELECT * FROM quarterly_sales;

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

Agora que a nova coluna foi adicionada e preenchida, execute os exemplos a seguir:

Exclusão de colunas de uma consulta dinâmica com uma CTE
Execução de uma consulta dinâmica multidimensional

Exclusão de colunas de uma consulta dinâmica com uma CTE¶

É possível usar uma expressão de tabela comum (CTE) para excluir colunas de uma consulta dinâmica.

O exemplo a seguir usa a CTE para excluir a coluna discount_percent de uma consulta dinâmica:

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;

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

É possível usar uma CTE para excluir a coluna amount e mostrar o desconto médio que cada funcionário fez em cada trimestre:

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;

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

Execução de uma consulta dinâmica multidimensional¶

Uma consulta dinâmica multidimensional dinamiza mais de uma coluna. Este exemplo se baseia nas colunas amount e discount_percentage. A consulta retorna a soma de todas as vendas de todos os funcionários em cada trimestre e a porcentagem máxima de desconto para todas as vendas em cada trimestre.

Na consulta, a lista SELECT usa os parâmetros $col_position para executar as funções SUM e MAX nas colunas retornadas, em ordem, e para nomear as colunas retornadas. Uma subconsulta na cláusula FROM fornece os dados para as operações dinâmicas. Como a saída mostra os resultados de vendas de todos os funcionários, a subconsulta não inclui a coluna 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'));

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