Exemplos de PIVOT e UNPIVOT

PIVOT e UNPIVOT são parâmetros na cláusula FROM que trocam a saída da consulta de linhas para colunas e colunas para linhas, respectivamente. Eles representam resultados de consultas tabulares em um formato fácil de ler. Os exemplos a seguir usam consultas e dados de teste para mostrar como usá-los.

Para obter mais informações sobre esses parâmetros, consulte FROM clause.

Exemplos de PIVOT

Configure a tabela e os dados de exemplo e use-os para executar as consultas de exemplo subsequentes.


CREATE TABLE part (
    partname varchar,
    manufacturer varchar,
    quality int,
    price decimal(12, 2)
);

INSERT INTO part VALUES ('prop', 'local parts co', 2, 10.00);
INSERT INTO part VALUES ('prop', 'big parts co', NULL, 9.00);
INSERT INTO part VALUES ('prop', 'small parts co', 1, 12.00);

INSERT INTO part VALUES ('rudder', 'local parts co', 1, 2.50);
INSERT INTO part VALUES ('rudder', 'big parts co', 2, 3.75);
INSERT INTO part VALUES ('rudder', 'small parts co', NULL, 1.90);

INSERT INTO part VALUES ('wing', 'local parts co', NULL, 7.50);
INSERT INTO part VALUES ('wing', 'big parts co', 1, 15.20);
INSERT INTO part VALUES ('wing', 'small parts co', NULL, 11.80);

PIVOT em partname com um agregação de AVG em price.


SELECT *
FROM (SELECT partname, price FROM part) PIVOT (
    AVG(price) FOR partname IN ('prop', 'rudder', 'wing')
);

A consulta resulta na saída a seguir.


  prop   |  rudder  |  wing
---------+----------+---------
 10.33   | 2.71     | 11.50

No exemplo anterior, os resultados são transformados em colunas. O exemplo a seguir mostra uma consulta GROUP BY que retorna os preços médios em linhas, em vez de em colunas.


SELECT partname, avg(price)
FROM (SELECT partname, price FROM part)
WHERE partname IN ('prop', 'rudder', 'wing')
GROUP BY partname;

A consulta resulta na saída a seguir.


 partname |  avg
----------+-------
 prop     | 10.33
 rudder   |  2.71
 wing     | 11.50

Um exemplo de PIVOT com manufacturer como uma coluna implícita.


SELECT *
FROM (SELECT quality, manufacturer FROM part) PIVOT (
    count(*) FOR quality IN (1, 2, NULL)
);

A consulta resulta na saída a seguir.


 manufacturer      | 1  | 2  | null
-------------------+----+----+------
 local parts co    | 1  | 1  |  1
 big parts co      | 1  | 1  |  1
 small parts co    | 1  | 0  |  2

Colunas da tabela de entrada que não são referenciadas na definição PIVOT são adicionadas implicitamente à tabela de resultados. Este é o caso da coluna manufacturer no exemplo anterior. O exemplo também mostra que NULL é um valor válido para o operador IN.

PIVOT no exemplo acima retorna informações semelhantes à consulta a seguir, que inclui GROUP BY. A diferença é que PIVOT retorna o valor 0 para a coluna 2 e o fabricante small parts co. A consulta GROUP BY não contém uma linha correspondente. Na maioria dos casos, PIVOT insere NULL se uma linha não tem dados de entrada para determinada coluna. Porém, o agregado de contagem não retorna NULL e 0 é o valor padrão.


SELECT manufacturer, quality, count(*)
FROM (SELECT quality, manufacturer FROM part)
WHERE quality IN (1, 2) OR quality IS NULL
GROUP BY manufacturer, quality
ORDER BY manufacturer;

A consulta resulta na saída a seguir.


 manufacturer        | quality | count
---------------------+---------+-------
 big parts co        |         |     1
 big parts co        |       2 |     1
 big parts co        |       1 |     1
 local parts co      |       2 |     1
 local parts co      |       1 |     1
 local parts co      |         |     1
 small parts co      |       1 |     1
 small parts co      |         |     2

O operador PIVOT aceita aliases opcionais na expressão agregada e em cada valor para o operador IN. Use aliases para personalizar os nomes das colunas. Se não houver um alias agregado, somente os aliases da lista IN serão usados. Caso contrário, o alias agregado será anexado ao nome da coluna com um sublinhado para separar os nomes.


SELECT *
FROM (SELECT quality, manufacturer FROM part) PIVOT (
    count(*) AS count FOR quality IN (1 AS high, 2 AS low, NULL AS na)
);

A consulta resulta na saída a seguir.


 manufacturer      | high_count  | low_count | na_count
-------------------+-------------+-----------+----------
 local parts co    |           1 |         1 |        1
 big parts co      |           1 |         1 |        1
 small parts co    |           1 |         0 |        2

Configure a tabela e os dados de exemplo a seguir e use-os para executar as consultas de exemplo subsequentes. Os dados representam datas de reserva para um grupo de hotéis.


CREATE TABLE bookings (
    booking_id int,
    hotel_code char(8),
    booking_date date,
    price decimal(12, 2)
);

INSERT INTO bookings VALUES (1, 'FOREST_L', '02/01/2023', 75.12);
INSERT INTO bookings VALUES (2, 'FOREST_L', '02/02/2023', 75.00);
INSERT INTO bookings VALUES (3, 'FOREST_L', '02/04/2023', 85.54);

INSERT INTO bookings VALUES (4, 'FOREST_L', '02/08/2023', 75.00);
INSERT INTO bookings VALUES (5, 'FOREST_L', '02/11/2023', 75.00);
INSERT INTO bookings VALUES (6, 'FOREST_L', '02/14/2023', 90.00);

INSERT INTO bookings VALUES (7, 'FOREST_L', '02/21/2023', 60.00);
INSERT INTO bookings VALUES (8, 'FOREST_L', '02/22/2023', 85.00);
INSERT INTO bookings VALUES (9, 'FOREST_L', '02/27/2023', 90.00);

INSERT INTO bookings VALUES (10, 'DESERT_S', '02/01/2023', 98.00);
INSERT INTO bookings VALUES (11, 'DESERT_S', '02/02/2023', 75.00);
INSERT INTO bookings VALUES (12, 'DESERT_S', '02/04/2023', 85.00);

INSERT INTO bookings VALUES (13, 'DESERT_S', '02/05/2023', 75.00);
INSERT INTO bookings VALUES (14, 'DESERT_S', '02/06/2023', 34.00);
INSERT INTO bookings VALUES (15, 'DESERT_S', '02/09/2023', 85.00);

INSERT INTO bookings VALUES (16, 'DESERT_S', '02/12/2023', 23.00);
INSERT INTO bookings VALUES (17, 'DESERT_S', '02/13/2023', 76.00);
INSERT INTO bookings VALUES (18, 'DESERT_S', '02/14/2023', 85.00);

INSERT INTO bookings VALUES (19, 'OCEAN_WV', '02/01/2023', 98.00);
INSERT INTO bookings VALUES (20, 'OCEAN_WV', '02/02/2023', 75.00);
INSERT INTO bookings VALUES (21, 'OCEAN_WV', '02/04/2023', 85.00);

INSERT INTO bookings VALUES (22, 'OCEAN_WV', '02/06/2023', 75.00);
INSERT INTO bookings VALUES (23, 'OCEAN_WV', '02/09/2023', 34.00);
INSERT INTO bookings VALUES (24, 'OCEAN_WV', '02/12/2023', 85.00);

INSERT INTO bookings VALUES (25, 'OCEAN_WV', '02/13/2023', 23.00);
INSERT INTO bookings VALUES (26, 'OCEAN_WV', '02/14/2023', 76.00);
INSERT INTO bookings VALUES (27, 'OCEAN_WV', '02/16/2023', 85.00);

INSERT INTO bookings VALUES (28, 'CITY_BLD', '02/01/2023', 98.00);
INSERT INTO bookings VALUES (29, 'CITY_BLD', '02/02/2023', 75.00);
INSERT INTO bookings VALUES (30, 'CITY_BLD', '02/04/2023', 85.00);

INSERT INTO bookings VALUES (31, 'CITY_BLD', '02/12/2023', 75.00);
INSERT INTO bookings VALUES (32, 'CITY_BLD', '02/13/2023', 34.00);
INSERT INTO bookings VALUES (33, 'CITY_BLD', '02/17/2023', 85.00);

INSERT INTO bookings VALUES (34, 'CITY_BLD', '02/22/2023', 23.00);
INSERT INTO bookings VALUES (35, 'CITY_BLD', '02/23/2023', 76.00);
INSERT INTO bookings VALUES (36, 'CITY_BLD', '02/24/2023', 85.00);

Nesse exemplo de consulta, os registros de reservas são calculados para fornecer um total de cada semana. A data de término de cada semana se torna um nome de coluna.


SELECT * FROM
    (SELECT
       booking_id,
       (date_trunc('week', booking_date::date) + '5 days'::interval)::date as enddate,
       hotel_code AS "hotel code"
FROM bookings
) PIVOT (
    count(booking_id) FOR enddate IN ('2023-02-04','2023-02-11','2023-02-18') 
);

A consulta resulta na saída a seguir.


 hotel code | 2023-02-04  | 2023-02-11 | 2023-02-18
------------+-------------+------------+----------
 FOREST_L   |           3 |          2 |        1
 DESERT_S   |           4 |          3 |        2
 OCEAN_WV   |           3 |          3 |        3
 CITY_BLD   |           3 |          1 |        2

O Amazon Redshift não é compatível com CROSSTAB para girar em várias colunas. No entanto, é possível alterar dados de linha em colunas, de forma semelhante a uma agregação com PIVOT, com uma consulta como a seguinte. Isso usa os mesmos dados de exemplo de reserva como no exemplo anterior.


SELECT 
  booking_date,
  MAX(CASE WHEN hotel_code = 'FOREST_L' THEN 'forest is booked' ELSE '' END) AS FOREST_L,
  MAX(CASE WHEN hotel_code = 'DESERT_S' THEN 'desert is booked' ELSE '' END) AS DESERT_S,
  MAX(CASE WHEN hotel_code = 'OCEAN_WV' THEN 'ocean is booked' ELSE '' END)  AS OCEAN_WV
FROM bookings
GROUP BY booking_date
ORDER BY booking_date asc;

O exemplo de consulta resulta em datas de reserva listadas ao lado de frases curtas que indicam quais hotéis estão reservados.


 booking_date  | forest_l         | desert_s         | ocean_wv
---------------+------------------+------------------+--------------------
 2023-02-01    | forest is booked | desert is booked |  ocean is booked
 2023-02-02    | forest is booked | desert is booked |  ocean is booked
 2023-02-04    | forest is booked | desert is booked |  ocean is booked
 2023-02-05    |                  | desert is booked |        
 2023-02-06    |                  | desert is booked |

Veja a seguir as observações de uso do PIVOT:

PIVOT pode ser aplicado a tabelas, subconsultas e expressões de tabela comuns (CTEs). PIVOT não pode ser aplicado a expressões JOIN, CTEs recursivos, PIVOT ou expressões UNPIVOT. Também não são compatíveis expressões SUPER não aninhadas e tabelas aninhadas do Redshift Spectrum.
PIVOT é compatível com funções agregadas COUNT, SUM, MIN, MAX e AVG.
A expressão agregada PIVOT deve ser uma chamada de uma função agregada compatível. Expressões complexas na parte superior do agregado não são compatíveis. Os argumentos agregados não podem conter referências a tabelas diferentes da tabela de entrada do PIVOT. Referências correlacionadas a uma consulta principal também não são compatíveis. O argumento agregado pode conter subconsultas. Elas podem ser correlacionadas internamente ou na tabela de entrada PIVOT.
Os valores da lista PIVOT IN não podem ser referências de coluna ou subconsultas. Cada valor deve ser compatível com a referência de coluna FOR.
Se os valores de lista IN não tiverem aliases, PIVOT gerará nomes de coluna padrão. Por valores IN constantes, como 'abc' ou 5, o nome da coluna padrão é a constante em si. Para qualquer expressão complexa, o nome da coluna é um nome padrão do Amazon Redshift, como ?column?.

Exemplos de UNPIVOT

Configure os dados de exemplo e use-os para executar os exemplos subsequentes.


CREATE TABLE count_by_color (quality varchar, red int, green int, blue int);

INSERT INTO count_by_color VALUES ('high', 15, 20, 7);
INSERT INTO count_by_color VALUES ('normal', 35, NULL, 40);
INSERT INTO count_by_color VALUES ('low', 10, 23, NULL);

UNPIVOT nas colunas de entrada vermelho, verde e azul.


SELECT *
FROM (SELECT red, green, blue FROM count_by_color) UNPIVOT (
    cnt FOR color IN (red, green, blue)
);

A consulta resulta na saída a seguir.


 color | cnt
-------+-----
 red   |  15
 red   |  35
 red   |  10
 green |  20
 green |  23
 blue  |   7
 blue  |  40

Por padrão, os valores NULL na coluna de entrada são ignorados e não produzem uma linha de resultado.

O exemplo a seguir mostra UNPIVOT com INCLUDE NULLS.


SELECT *
FROM (
    SELECT red, green, blue
    FROM count_by_color
) UNPIVOT INCLUDE NULLS (
    cnt FOR color IN (red, green, blue)
);

A seguir está a saída resultante.


 color | cnt
-------+-----
 red   |  15
 red   |  35
 red   |  10
 green |  20
 green |
 green |  23
 blue  |   7
 blue  |  40
 blue  |

Se o parâmetro INCLUDING NULLS estiver definido, os valores de entrada NULL geram linhas de resultados.

The following query shows UNPIVOT com quality como uma coluna implícita.


SELECT *
FROM count_by_color UNPIVOT (
    cnt FOR color IN (red, green, blue)
);

A consulta resulta na saída a seguir.


 quality | color | cnt
---------+-------+-----
 high    | red   |  15
 normal  | red   |  35
 low     | red   |  10
 high    | green |  20
 low     | green |  23
 high    | blue  |   7
 normal  | blue  |  40

Colunas da tabela de entrada que não são referenciadas na definição UNPIVOT são adicionadas implicitamente à tabela de resultados. No exemplo, este é o caso da coluna quality.

O exemplo a seguir mostra UNPIVOT com aliases para valores na lista IN.


SELECT *
FROM count_by_color UNPIVOT (
    cnt FOR color IN (red AS r, green AS g, blue AS b)
);

A consulta anterior resulta na saída a seguir.


 quality | color | cnt
---------+-------+-----
 high    | r     |  15
 normal  | r     |  35
 low     | r     |  10
 high    | g     |  20
 low     | g     |  23
 high    | b     |   7
 normal  | b     |  40

O operador UNPIVOT aceita aliases opcionais em cada valor de lista IN. Cada alias fornece personalização dos dados em cada coluna value.

Veja a seguir as observações de uso do UNPIVOT.

UNPIVOT pode ser aplicado a tabelas, subconsultas e expressões de tabela comuns (CTEs). UNPIVOT não pode ser aplicado a expressões JOIN, CTEs recursivos, PIVOT ou expressões UNPIVOT. Também não são compatíveis expressões SUPER não aninhadas e tabelas aninhadas do Redshift Spectrum.
A lista UNPIVOT IN deve conter apenas referências de coluna da tabela de entrada. As colunas da lista IN devem ter um tipo comum com o qual todas sejam compatíveis. A coluna de valor UNPIVOT tem esse tipo comum. A coluna de nome UNPIVOT é do tipo VARCHAR.
Se um valor de lista IN não tiver um alias, UNPIVOT usará o nome da coluna como valor padrão.

Atenção O Javascript está desativado ou não está disponível no seu navegador.

Para usar a documentação da AWS, o Javascript deve estar ativado. Consulte as páginas de Ajuda do navegador para obter instruções.

Convenções do documento

Cláusula FROM

Exemplos de JOIN