Uso e exemplos das palavras-chave PIVOT e UNPIVOT - MaxCompute: Central de Documentação do Alibaba Cloud

O PIVOT transforma uma tabela estreita (por exemplo, product, season, sales_amount) em uma tabela larga (por exemplo, product, Q1, Q2, Q3, Q4) ao agregar linhas em colunas. O UNPIVOT realiza o processo inverso: converte colunas largas em linhas, o que facilita a filtragem e a análise uniforme dos dados.

Ambas as palavras-chave são usadas na cláusula FROM e podem ser combinadas com JOIN e outras cláusulas.

Nota: O PIVOT está em versão canary e pode não estar disponível para todos os usuários.

PIVOT

Sintaxe

SELECT ...
FROM ...
PIVOT (
    <aggregate function> [AS <alias>] [, <aggregate function> [AS <alias>]] ...
    FOR (<column> [, <column>] ...)
    IN (
        (<value> [, <value>] ...) AS <new column>
        [, (<value> [, <value>] ...) AS <new column>]
        ...
       )
    )
[...]

Parâmetros

Parâmetro	Obrigatório	Descrição
`aggregate function`	Sim	Função de agregação usada na conversão de linhas para colunas. Para ver as funções compatíveis, consulte Funções de agregação.
`alias`	Não	Alias da função de agregação. Incluído no nome da coluna gerada.
`column`	Sim	Coluna de origem cujos valores serão agregados.
`value`	Sim	Valor da linha usado como base para o pivot, atuando como seletor de coluna.
`new column`	Não	Nome da coluna de saída após a conversão.

Colunas de saída e regras de nomenclatura

A tabela de saída possui dois tipos de colunas:

Colunas de agrupamento: colunas de origem não referenciadas nas funções de agregação ou na cláusula FOR. Elas são mantidas sem alterações.
Colunas com pivot: uma coluna para cada valor na lista IN, nomeada conforme as regras de alias descritas abaixo.

Os aliases definidos no PIVOT determinam os nomes das colunas de saída com pivot:

Sem alias — PIVOT (agg1 for axis1 in ('1', '2', '3')): os nomes das colunas correspondem aos próprios valores — '1', '2', '3'.
Um alias de agregação — PIVOT (agg1 AS a for axis1 in ('1', '2', '3')): as colunas recebem o nome value_aggregateAlias — '1'_a, '2'_a, '3'_a.
Múltiplos aliases de agregação — PIVOT (agg1 AS a, agg2 AS b for axis1 in ('1', '2', '3')): cada agregação gera seu próprio conjunto de colunas — '1'_a, '2'_a, '1'_b, '2'_b.
Valores de expressão (parciais ou totais) — O MaxCompute gera aliases automaticamente para qualquer expressão sem alias explícito e para funções de agregação sem alias. A consulta é reescrita internamente com os aliases gerados, e as colunas recebem o nome expressionAlias_aggregateAlias.

Limitações

Não aninhe uma função de agregação dentro de outra função.
Funções de agregação podem incluir funções escalares e colunas de agrupamento, mas não podem conter funções de janela ou outras funções de agregação.
As colunas dentro de uma função de agregação devem pertencer à tabela de origem do pivot.
Um alias só pode aparecer como parte do nome da coluna gerada; não pode ser uma expressão.
Uma expressão de value pode referenciar apenas colunas da tabela de origem e conter funções escalares, mas não funções de agregação ou de janela.

Funcionamento do PIVOT

O PIVOT equivale a um GROUP BY com funções de agregação e cláusulas FILTER. As instruções a seguir produzem o mesmo resultado:

-- Using PIVOT
SELECT ...
FROM ...
PIVOT (
  agg1 AS a, agg2 AS b, ...
  FOR (axis1, ..., axisN)
  IN (
      (v11, ..., v1N) AS label1,
      (v21, ..., v2N) AS label2,
      ...)
)

-- Equivalent without PIVOT
SELECT
  k1, ..., kN,
  agg1 AS label1_a FILTER (WHERE axis1 = v11 AND ... AND axisN = v1N),
  agg2 AS label1_b FILTER (WHERE axis1 = v21 AND ... AND axisN = v2N),
  ...,
  agg1 AS label2_a FILTER (WHERE axis1 = v11 AND ... AND axisN = v1N),
  agg2 AS label2_b FILTER (WHERE axis1 = v21 AND ... AND axisN = v2N),
  ...
FROM source_table
GROUP BY k1, ..., kN

k1, ..., kN representam as colunas de origem não listadas nas funções de agregação ou na cláusula FOR.

Exemplos

Os exemplos abaixo utilizam uma tabela de vendas de frutas com dados trimestrais de transações:

-- Create and populate the table
CREATE TABLE mf_cop_sales (
  tran_id   BIGINT,
  productID STRING,
  tran_amt  DECIMAL,
  season    STRING
);

INSERT INTO TABLE mf_cop_sales VALUES
  (1,  'apple',  100, 'Q1'),
  (2,  'orange', 200, 'Q1'),
  (3,  'banana', 300, 'Q1'),
  (4,  'apple',  400, 'Q2'),
  (5,  'orange', 500, 'Q2'),
  (6,  'banana', 600, 'Q2'),
  (7,  'apple',  700, 'Q3'),
  (8,  'orange', 800, 'Q3'),
  (9,  'banana', 700, 'Q3'),
  (10, 'apple',  500, 'Q4'),
  (11, 'orange', 400, 'Q4'),
  (12, 'banana', 200, 'Q4');

Vendas totais por trimestre

Transforme a coluna season em quatro colunas nomeadas (uma por trimestre) e some todas as transações em cada uma delas:

SELECT *
FROM (SELECT season, tran_amt FROM mf_cop_sales)
PIVOT (
  SUM(tran_amt)
  FOR season IN ('Q1' AS spring, 'Q2' AS summer, 'Q3' AS autumn, 'Q4' AS winter)
);

Resultado:

+--------+--------+--------+--------+
| spring | summer | autumn | winter |
+--------+--------+--------+--------+
| 600    | 1500   | 2200   | 1100   |
+--------+--------+--------+--------+

Vendas totais por produto com alias de agregação

Adicionar um alias à função de agregação (AS sumbypro) anexa esse alias ao nome de cada coluna de saída, resultando em 'value'_sumbypro:

SELECT *
FROM (SELECT productid, tran_amt FROM mf_cop_sales)
PIVOT (
  SUM(tran_amt) AS sumbypro
  FOR productid IN ('apple', 'orange', 'banana')
);

Resultado:

+------------------+-------------------+-------------------+
| 'apple'_sumbypro | 'orange'_sumbypro | 'banana'_sumbypro |
+------------------+-------------------+-------------------+
| 1700             | 1900              | 1800              |
+------------------+-------------------+-------------------+

Venda máxima no Q4

Use MAX para identificar o maior valor de transação única no Q4:

SELECT *
FROM (SELECT season, tran_amt FROM mf_cop_sales)
PIVOT (MAX(tran_amt) FOR season IN ('Q4'));

Resultado:

+------+
| 'Q4' |
+------+
| 500  |
+------+

UNPIVOT

Sintaxe

SELECT ...
FROM ...
UNPIVOT (
  <new column of value> [, <new column of value>] ...
  FOR (<new column of name> [, <new column of name>] ...)
  IN (
      (<column> [, <column>] ...) [AS (<column value> [, <column value>] ...)]
      [, (<column> [, <column>] ...) [AS (<column value> [, <column value>] ...)]]
      ...
    )
)
[...]

Parâmetros

Parâmetro	Obrigatório	Descrição
`new column of value`	Sim	Nome da nova coluna que receberá os valores das colunas de origem submetidas ao unpivot.
`new column of name`	Sim	Nome da nova coluna que receberá os nomes originais das colunas (ou seus aliases).
`column`	Sim	Coluna de origem para aplicar o unpivot. Seu nome preenche a `new column of name`; seus valores preenchem a `new column of value`.
`column value`	Não	Alias da coluna de origem. Substitui o nome da coluna na saída da `new column of name`.

Limitações

A quantidade de parâmetros new column of value deve ser igual ao número de grupos de colunas em IN (...).
A quantidade de parâmetros new column of name deve corresponder ao número de grupos de aliases em IN (...).
Os campos new column of value e new column of name devem ter nomes exclusivos e não podem conter expressões.
O column value pode ser uma constante ou uma expressão, mas a expressão não pode referenciar nomes de colunas — ela deve resultar em uma constante.
Cada grupo de colunas em IN (...) pode conter até 100 colunas. Exceder esse limite causa inchaço de dados.
Adicione EXCLUDE NULLS após a palavra-chave UNPIVOT para descartar linhas em que qualquer coluna do unpivot seja NULL.

Regras de alias automático

Se você omitir a cláusula AS (...) para um grupo de colunas, o MaxCompute gera aliases automaticamente:

UNPIVOT (measure1 FOR axis IN (c1, c2, c3)) é reescrito internamente como UNPIVOT (measure1 FOR axis IN (c1 AS c1, c2 AS c2, c3 AS c3)).
Nos demais casos, o MaxCompute gera aliases do tipo STRING para as colunas não especificadas.
Caso especifique aliases apenas para algumas colunas, os aliases explícitos devem ser do tipo STRING para garantir compatibilidade com os gerados automaticamente. Se algum alias não for STRING, defina aliases para todas as colunas.

Funcionamento do UNPIVOT

O UNPIVOT equivale a um CROSS JOIN com uma expressão CASE WHEN:

-- Using UNPIVOT
SELECT ...
FROM ...
UNPIVOT (
    (measure1, ..., measureM)
    FOR (axis1, ..., axisN)
    IN ((c11, ..., c1M) AS (value11, ..., value1N),
        (c21, ..., c2M) AS (value21, ..., value2N), ...))
[...]

-- Equivalent without UNPIVOT
SELECT
    k1, ..., kN,
    CASE
      WHEN axis1 = value11 AND ... AND axisN = value1N THEN c11
      WHEN axis1 = value21 AND ... AND axisN = value2N THEN c21
      ...
      ELSE NULL
    END AS measure1,
    ...,
    CASE
      WHEN axis1 = value11 AND ... AND axisN = value1N THEN c1M
      WHEN axis1 = value21 AND ... AND axisN = value2N THEN c2M
      ELSE NULL
    END AS measureM,
    axis1, ..., axisN
FROM source_table
JOIN (VALUES (value11, ..., value1N), (value21, ..., value2N), ...)
  AS generated_table_name(axis1, ..., axisN)

Exemplos

Os exemplos abaixo utilizam uma tabela de vendas com dados por produto e por ano em quatro lojas diferentes:

-- Create and populate the table
CREATE TABLE mf_shops (
  item_id BIGINT,
  year    STRING,
  shop1   DECIMAL,
  shop2   DECIMAL,
  shop3   DECIMAL,
  shop4   DECIMAL
);

WITH shops_table AS (
  SELECT * FROM VALUES
    (1, 2020, 100, 200, 300, 400),
    (1, 2021, 100, 200, 200, 100),
    (2, 2020, 300, 400, 300, 200),
    (2, 2021, 400, 300, 100, 100)
    shops(item_id, year, shop1, shop2, shop3, shop4)
)
INSERT OVERWRITE TABLE mf_shops SELECT * FROM shops_table;

Consolidar colunas de lojas em linhas

Converta as quatro colunas de lojas em linhas, criando uma coluna sales para o valor e uma coluna shop para o nome da loja. O MaxCompute gera automaticamente os aliases dos nomes das lojas a partir dos nomes das colunas de origem:

SELECT * FROM mf_shops
UNPIVOT (sales FOR shop IN (shop1, shop2, shop3, shop4));

Resultado:

+------------+------------+------------+-------+
| item_id    | year       | sales      | shop  |
+------------+------------+------------+-------+
| 1          | 2020       | 100        | shop1 |
| 1          | 2020       | 200        | shop2 |
| 1          | 2020       | 300        | shop3 |
| 1          | 2020       | 400        | shop4 |
| 1          | 2021       | 100        | shop1 |
| 1          | 2021       | 200        | shop2 |
| 1          | 2021       | 200        | shop3 |
| 1          | 2021       | 100        | shop4 |
| 2          | 2020       | 300        | shop1 |
| 2          | 2020       | 400        | shop2 |
| 2          | 2020       | 300        | shop3 |
| 2          | 2020       | 200        | shop4 |
| 2          | 2021       | 400        | shop1 |
| 2          | 2021       | 300        | shop2 |
| 2          | 2021       | 100        | shop3 |
| 2          | 2021       | 100        | shop4 |
+------------+------------+------------+-------+

Usar aliases personalizados para nomes de lojas

Substitua os nomes das colunas de origem na saída por rótulos de string descritivos:

SELECT * FROM mf_shops
UNPIVOT (sales FOR shop IN (
  shop1 AS 'shop_name_1',
  shop2 AS 'shop_name_2',
  shop3 AS 'shop_name_3',
  shop4 AS 'shop_name_4'
));

Resultado:

+------------+------------+------------+-------------+
| item_id    | year       | sales      | shop        |
+------------+------------+------------+-------------+
| 1          | 2020       | 100        | shop_name_1 |
| 1          | 2020       | 200        | shop_name_2 |
| 1          | 2020       | 300        | shop_name_3 |
| 1          | 2020       | 400        | shop_name_4 |
| 1          | 2021       | 100        | shop_name_1 |
| 1          | 2021       | 200        | shop_name_2 |
| 1          | 2021       | 200        | shop_name_3 |
| 1          | 2021       | 100        | shop_name_4 |
| 2          | 2020       | 300        | shop_name_1 |
| 2          | 2020       | 400        | shop_name_2 |
| 2          | 2020       | 300        | shop_name_3 |
| 2          | 2020       | 200        | shop_name_4 |
| 2          | 2021       | 400        | shop_name_1 |
| 2          | 2021       | 300        | shop_name_2 |
| 2          | 2021       | 100        | shop_name_3 |
| 2          | 2021       | 100        | shop_name_4 |
+------------+------------+------------+-------------+

Agrupar colunas antes do unpivot

Agrupe as lojas em conjuntos leste e oeste, convertendo cada par em duas colunas de valor (sales1, sales2):

SELECT * FROM mf_shops
UNPIVOT (
  (sales1, sales2) FOR shop IN (
    (shop1, shop2) AS 'east_shop',
    (shop3, shop4) AS 'west_shop'
  )
);

Resultado:

+------------+------------+------------+------------+-----------+
| item_id    | year       | sales1     | sales2     | shop      |
+------------+------------+------------+------------+-----------+
| 1          | 2020       | 100        | 200        | east_shop |
| 1          | 2020       | 300        | 400        | west_shop |
| 1          | 2021       | 100        | 200        | east_shop |
| 1          | 2021       | 200        | 100        | west_shop |
| 2          | 2020       | 300        | 400        | east_shop |
| 2          | 2020       | 300        | 200        | west_shop |
| 2          | 2021       | 400        | 300        | east_shop |
| 2          | 2021       | 100        | 100        | west_shop |
+------------+------------+------------+------------+-----------+

Usar múltiplas colunas de nome

Adicione uma segunda coluna FOR para capturar uma dimensão adicional de rótulo. Neste exemplo, cada grupo de lojas recebe tanto um nome quanto uma localização geográfica:

SELECT * FROM mf_shops
UNPIVOT (
  (sales1, sales2) FOR (shop_name, location) IN (
    (shop1, shop2) AS ('east_shop', 'east'),
    (shop3, shop4) AS ('west_shop', 'west')
  )
);

Resultado:

+------------+------------+------------+------------+-----------+----------+
| item_id    | year       | sales1     | sales2     | shop_name | location |
+------------+------------+------------+------------+-----------+----------+
| 1          | 2020       | 100        | 200        | east_shop | east     |
| 1          | 2020       | 300        | 400        | west_shop | west     |
| 1          | 2021       | 100        | 200        | east_shop | east     |
| 1          | 2021       | 200        | 100        | west_shop | west     |
| 2          | 2020       | 300        | 400        | east_shop | east     |
| 2          | 2020       | 300        | 200        | west_shop | west     |
| 2          | 2021       | 400        | 300        | east_shop | east     |
| 2          | 2021       | 100        | 100        | west_shop | west     |
+------------+------------+------------+------------+-----------+----------+

Excluir linhas nulas com EXCLUDE NULLS

Quando uma linha de origem contém NULL em qualquer coluna do unpivot, use EXCLUDE NULLS para removê-la da saída. No exemplo abaixo, item_id=3 possui todos os valores de loja nulos e é excluído:

WITH shops AS (
  SELECT * FROM VALUES
    (1, 2020, 100, 200, 300, 400),
    (1, 2021, 100, 200, 200, 100),
    (2, 2020, 300, 400, 300, 200),
    (2, 2021, 400, 300, 100, 100),
    (3, 2020, NULL, NULL, NULL, NULL)
    shops(item_id, year, shop1, shop2, shop3, shop4)
)
SELECT * FROM shops
UNPIVOT EXCLUDE NULLS (
  (sales1, sales2) FOR (shop_name, location) IN (
    (shop1, shop2) AS ('east_shop', 'east'),
    (shop3, shop4) AS ('west_shop', 'west')
  )
);

Resultado (as linhas com item_id=3 estão ausentes):

+------------+------------+------------+------------+-----------+----------+
| item_id    | year       | sales1     | sales2     | shop_name | location |
+------------+------------+------------+------------+-----------+----------+
| 1          | 2020       | 100        | 200        | east_shop | east     |
| 1          | 2020       | 300        | 400        | west_shop | west     |
| 1          | 2021       | 100        | 200        | east_shop | east     |
| 1          | 2021       | 200        | 100        | west_shop | west     |
| 2          | 2020       | 300        | 400        | east_shop | east     |
| 2          | 2020       | 300        | 200        | west_shop | west     |
| 2          | 2021       | 400        | 300        | east_shop | east     |
| 2          | 2021       | 100        | 100        | west_shop | west     |
+------------+------------+------------+------------+-----------+----------+