Esempi PIVOT e UNPIVOT - Amazon Redshift
Esempi PIVOTEsempi di UNPIVOT

Le traduzioni sono generate tramite traduzione automatica. In caso di conflitto tra il contenuto di una traduzione e la versione originale in Inglese, quest'ultima prevarrà.

Esempi PIVOT e UNPIVOT

PIVOT e UNPIVOT sono parametri della clausola FROM che ruotano rispettivamente l'output della query da righe a colonne e da colonne a righe. Rappresentano i risultati delle query tabulari in un formato facile da leggere. Gli esempi seguenti utilizzano dati e query di prova per mostrare come utilizzarli.

Per ulteriori informazioni su questi e altri parametri, consulta Clausola FROM.

Esempi PIVOT

Impostare la tabella di esempio e i dati e utilizzarli per eseguire le query di esempio successive.

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 su partname con un'aggregazione AVG suprice.

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

La query restituisce i seguenti risultati:

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

Nell'esempio precedente, i risultati vengono trasformati in colonne. L'esempio seguente mostra una query GROUP BY che restituisce i prezzi medi in righe anziché in colonne.

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

La query restituisce i seguenti risultati:

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

Un esempio PIVOT con manufacturer come colonna implicita.

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

La query restituisce i seguenti risultati:

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

Le colonne della tabella di input che non sono referenziate nella definizione PIVOT sono aggiunte implicitamente alla tabella dei risultati. Questo è il caso della colonna manufacturer dell'esempio precedente. L'esempio dimostra anche che NULL è un valore valido per l'operatore IN.

PIVOT nell'esempio precedente restituisce informazioni simili a quelle della seguente query, che include GROUP BY. La differenza è che PIVOT restituisce il valore 0 per colonna 2 e il produttore small parts co. La query GROUP BY non contiene una riga corrispondente. Nella maggior parte dei casi PIVOT inserisce NULL se una riga non contiene dati di input per una determinata colonna. Tuttavia, l'aggregato di conteggio non restituisce NULL e 0 viene utilizzato come valore predefinito.

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;

La query restituisce i seguenti risultati:

 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

L'operatore PIVOT accetta alias opzionali sull'espressione aggregata e su ciascun valore per l'operatore IN. Usa gli alias per personalizzare i nomi delle colonne. Se non esiste un alias aggregato, solo gli alias dell'elenco IN. In caso contrario, l'alias aggregato viene aggiunto al nome della colonna con un trattino di sottolineatura per separare i nomi. 

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

La query restituisce i seguenti risultati:

 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

Imposta la tabella e i dati di esempio seguenti e utilizzali per eseguire le query di esempio successive. I dati rappresentano le date di prenotazione di una raccolta di hotel.

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

In questa query di esempio, i record delle prenotazioni vengono conteggiati per fornire il totale ogni settimana. La data di fine di ogni settimana diventa un nome di colonna.

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

La query restituisce i seguenti risultati:

 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

Amazon Redshift non supporta l'uso di CROSSTAB per il passaggio in più colonne. È tuttavia possibile modificare i dati delle righe nelle colonne, in modo simile a un'aggregazione con PIVOT, utilizzando una query simile alla seguente. Usa gli stessi dati delle prenotazioni dell'esempio precedente.

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;

La query di esempio restituisce le date di prenotazione elencate accanto a una breve frase che indica quali hotel sono prenotati.

 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 |

Di seguito sono riportate le note di utilizzo per PIVOT:

  • PIVOT può essere applicato a tabelle, sottoquery ed espressioni di tabella comuni (CTE). PIVOT non può essere applicato ad alcuna espressione JOIN, CTE ricorrenti, PIVOT, oppure espressioni UNPIVOT. Inoltre non sono supportate le espressioni non annidate SUPER e le tabelle annidate di Redshift Spectrum.

  • PIVOT supporta COUNT, SUM, MIN, MAX e le funzioni aggregate AVG.

  • L'espressione aggregata PIVOT deve essere una chiamata di una funzione di aggregazione supportata. Le espressioni complesse sopra l'aggregato non sono supportate. Gli argomenti aggregati non possono contenere riferimenti a tabelle diverse dalla tabella di input PIVOT. Anche i riferimenti correlati a una query principale non sono supportati. L'argomento aggregato può contenere sottoquery. Questi possono essere correlati internamente o sulla tabella di input PIVOT.

  • I valori della lista PIVOT IN non possono essere riferimenti di colonna o sottoquery. Ogni valore deve essere compatibile con la colonna di riferimento FOR.

  • Se i valori della lista IN non hanno alias, PIVOT genera nomi di colonne predefiniti. Per i valori IN costanti come 'abc' o 5 il nome di colonna predefinito è la costante stessa. Per qualsiasi espressione complessa, il nome della colonna è un nome predefinito Amazon Redshift standard, ad esempio ?column?.

Esempi di UNPIVOT

Impostare i dati di esempio e utilizzarli per eseguire le query di esempio successive.

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 sulle colonne di input rosso, verde e blu.

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

La query restituisce i seguenti risultati:

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

Per impostazione predefinita,i valori NULL nella colonna di input vengono ignorati e non producono una riga di risultato.

L'esempio seguente mostra UNPIVOT con INCLUDE NULLS.

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

Di seguito è riportato l'output risultante.

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

Se il parametro INCLUDING NULLS è impostato,i valori di input NULL generano righe dei risultati.

The following query shows UNPIVOT con quality come colonna implicita.

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

La query restituisce i seguenti risultati:

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

Le colonne della tabella di input a cui non si fa riferimento nella definizione UNPIVOT vengono aggiunte implicitamente alla tabella dei risultati. Nell'esempio, questo è il caso della colonna quality.

L'esempio seguente mostra UNPIVOT con alias per i valori nell'elenco IN.

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

La query precedente restituisce i seguenti risultati:

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

L'operatore UNPIVOT accetta alias opzionali su ciascuno valore di elenco IN. Ogni alias fornisce la personalizzazione dei dati in ciascuna colonna value.

Di seguito sono riportate le note di utilizzo per UNPIVOT.

  • UNPIVOT può essere applicato a tabelle, sottoquery ed espressioni di tabella comuni (CTE). UNPIVOT non può essere applicato ad alcuna espressione JOIN, CTE ricorrenti, PIVOT, oppure espressioni UNPIVOT. Inoltre non sono supportate le espressioni non annidate SUPER e le tabelle annidate di Redshift Spectrum.

  • L'elenco UNPIVOT IN deve contenere solo i riferimenti alle colonne della tabella di input. Le colonne dell'elenco IN devono avere un tipo comune con cui sono tutte compatibili. La colonna valore UNPIVOT ha questo tipo comune. La colonna del nome UNPIVOT è di tipo VARCHAR.

  • Se un valore di elenco IN non ha un alias, UNPIVOT utilizza il nome della colonna come valore predefinito.