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.

O operador oferece suporte às funções agregadas incorporadas AVG, COUNT, MAX, MIN e SUM.

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

Consulte também:: UNPIVOT

Sintaxe¶

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

Parâmetros¶

aggregate_function: A função agregada para combinar os valores agrupados de pivot_column.
pivot_column: A coluna da tabela de origem ou subconsulta que será agregada.
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: Uma lista de valores para a coluna pivô para articular em cabeçalhos nos resultados da consulta.
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): Substitua todos os NULLs no resultado do pivô com o 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 com o corpo de um procedimento armazenado ou função definida pelo usuário (UDF).
Uma consulta dinâmica que não usa pivô dinâmica pode retornar uma saída com colunas duplicadas. Recomendamos evitar saída com colunas duplicadas. Uma consulta de pivô desduplica colunas duplicadas.
Uma consulta de pivô que não usa pivô dinâmico pode falhar se tentar CAST uma coluna VARIANT para um tipo de dados diferente. Consultas dinâmicas de pivô não têm essa limitação.

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'),
    (1, 8000, '2023_Q4'),
    (1, 10000, '2023_Q4'),
    (2, 800, '2023_Q4'),
    (2, 4500, '2023_Q4');

Copy

Articule todos os valores de coluna distintos automaticamente com o pivô dinâmico¶

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;

Copy

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

Articulação em valores de coluna usando uma subconsulta com pivô dinâmico¶

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

Copy

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;

Copy

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

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;

Copy

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

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;

Copy

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

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', 
      '2023_Q2', 
      '2023_Q3', 
      '2023_Q4')
    ) AS p (empid_renamed, Q1, Q2, Q3, Q4)
  ORDER BY empid_renamed;

Copy

+---------------+-------+-------+-------+-------+
| EMPID_RENAMED |    Q1 |    Q2 |    Q3 |    Q4 |
|---------------+-------+-------+-------+-------|
|             1 | 10400 |  8000 | 11000 | 18000 |
|             2 | 39500 | 90700 | 12000 |  5300 |
+---------------+-------+-------+-------+-------+

Você também pode listar colunas específicas na listagem SELECT e alterar os nomes das colunas:

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

Se a consulta retornar nulos, você poderá substituí-los por um valor padrão. Por exemplo, a consulta a seguir retorna nulos para 2024_Q1:

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

Copy

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

Você pode substituir os nulos por um valor padrão de 0 executando a seguinte consulta:

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

Copy

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