Exemples PIVOT et UNPIVOT - Amazon Redshift
Exemples PIVOTExemples UNPIVOT

Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.

Exemples PIVOT et UNPIVOT

PIVOT et UNPIVOT sont des paramètres de la clause FROM qui font pivoter la sortie de la requête des lignes vers les colonnes et des colonnes vers les lignes, respectivement. Ils représentent des résultats de requêtes tabulaires dans un format facile à lire. Les exemples suivants utilisent des données et des requêtes de test pour montrer comment les utiliser.

Pour plus d’informations sur ces paramètres et d’autres, consultez Clause FROM.

Exemples PIVOT

Configurez l’exemple de table et de données, puis utilisez-les pour exécuter les requêtes d’exemple suivantes.

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 sur partname avec une agrégation AVG sur price.

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

La requête génèrera la sortie suivante.

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

Dans l’exemple précédent, les résultats sont transformés en colonnes. L’exemple suivant montre une requête GROUP BY qui renvoie les prix moyens sous forme de lignes plutôt que de colonnes.

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

La requête génèrera la sortie suivante.

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

Un exemple PIVOT avec manufacturer en tant que colonne implicite.

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

La requête génèrera la sortie suivante.

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

Les colonnes de table d’entrée qui ne sont pas référencées dans la définition de PIVOT sont ajoutées implicitement à la table de résultats. C’est le cas pour la colonne manufacturer de l’exemple précédent. L’exemple montre également que NULL est une valeur valide pour l’opérateur IN.

PIVOT dans l’exemple ci-dessus renvoie des informations similaires à celles de la requête suivante, qui inclut GROUP BY. La différence est que PIVOT renvoie la valeur 0 pour la colonne 2 et le fabricant small parts co. La requête GROUP BY ne contient pas de ligne correspondante. Dans la plupart des cas, PIVOT insère NULL si une ligne ne contient pas de données d’entrée pour une colonne donnée. Toutefois, l’agrégat de nombre ne renvoie pas NULL et 0 est la valeur par défaut.

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 requête génèrera la sortie suivante.

 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’opérateur PIVOT accepte les alias facultatifs sur l’expression agrégée et sur chaque valeur pour l’opérateur IN. Utilisez des alias pour personnaliser les noms des colonnes. S’il n’y a pas d’alias agrégé, seuls les alias de liste IN sont utilisés. Sinon, l’alias agrégé est ajouté au nom de la colonne avec un trait de soulignement pour séparer les noms. 

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

La requête génèrera la sortie suivante.

 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

Configurez les exemples de table et de données suivants, puis utilisez-les pour exécuter les requêtes d’exemple suivantes. Les données représentent les dates de réservation pour un ensemble d’hôtels.

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

Dans cet exemple de requête, les enregistrements de réservations sont comptabilisés pour donner un total pour chaque semaine. La date de fin de chaque semaine devient un nom de colonne.

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 requête génèrera la sortie suivante.

 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 ne prend pas en charge CROSSTAB pour pivoter sur plusieurs colonnes. Mais vous pouvez transformer les données de ligne en colonnes, comme pour un regroupement avec PIVOT, à l’aide d’une requête telle que la suivante. L’exemple précédent utilise les mêmes données de réservation que celles de l’exemple précédent.

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;

L’exemple de requête donne lieu à des dates de réservation répertoriées à côté de phrases courtes qui indiquent quels hôtels sont réservés.

 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 |

Voici des notes d’utilisation pour PIVOT :

  • PIVOT peut être appliqué à des tables, des sous-requêtes et des expressions de table communes (CTE).PIVOT ne peut être appliqué à aucune expression JOIN, CTE récursives, expressions PIVOT ou UNPIVOT. De plus, les expressions non imbriquées SUPER et les tables imbriquées Redshift Spectrum ne sont pas prises en charge.

  • PIVOT prend en charge les fonctions agrégées COUNT, SUM, MIN, MAX et AVG.

  • L’expression agrégée PIVOT doit être un appel d’une fonction agrégée prise en charge. Les expressions complexes en plus de l’agrégat ne sont pas prises en charge. Les arguments agrégés ne peuvent pas contenir de références à d’autres tables que la table d’entrée PIVOT. Les références corrélées à une requête parente ne sont pas prises en charge. L’argument agrégé peut contenir des sous-requêtes. Elles peuvent être corrélées en interne ou sur la table d’entrée PIVOT.

  • Les valeurs de liste PIVOT IN ne peuvent pas être des références de colonnes ou des sous-requêtes. Chaque valeur doit être de type compatible avec la référence de colonne FOR.

  • Si les valeurs de liste IN n’ont pas d’alias, PIVOT génère des noms de colonnes par défaut. Pour des valeurs constantes IN telles que « abc » ou 5, le nom de colonne par défaut est la constante elle-même. Pour toute expression complexe, le nom de la colonne est un nom par défaut Amazon Redshift standard tel que ?column?.

Exemples UNPIVOT

Configurez les exemples de données et utilisez-les pour exécuter les exemples suivants.

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 sur les colonnes d’entrée rouges, vertes et bleues.

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

La requête génèrera la sortie suivante.

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

Par défaut, les valeurs NULL de la colonne d’entrée sont ignorées et ne produisent pas de ligne de résultats.

L’exemple suivant montre UNPIVOT avec INCLUDE NULLS.

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

En voici le résultat obtenu.

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

Si le paramètre INCLUDING NULLS est défini, les valeurs d’entrée NULL génèrent des lignes de résultats.

The following query shows UNPIVOT avec quality en tant que colonne implicite.

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

La requête génèrera la sortie suivante.

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

Les colonnes de la table d’entrée qui ne sont pas référencées dans la définition de UNPIVOT sont ajoutées implicitement à la table de résultats. Dans cet exemple, c’est le cas pour la colonne quality.

L’exemple suivant en est une illustration de UNPIVOT avec des alias pour les valeurs dans la liste IN.

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

La requête précédente génère le résultat suivant.

 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’opérateur UNPIVOT accepte les alias facultatifs sur chaque valeur de liste IN. Chaque alias permet de personnaliser les données de chaque colonne value.

Voici des notes d’utilisation pour UNPIVOT.

  • UNPIVOT peut être appliqué à des tables, des sous-requêtes et des expressions de table communes (CTE).UNPIVOT ne peut être appliqué à aucune expression JOIN, CTE récursives, expressions PIVOT ou UNPIVOT. De plus, les expressions non imbriquées SUPER et les tables imbriquées Redshift Spectrum ne sont pas prises en charge.

  • La liste UNPIVOT IN doit contenir uniquement des références de colonnes de table d’entrée. Les colonnes de la liste IN doivent avoir un type commun avec lequel elles sont toutes compatibles. La colonne de valeurs UNPIVOT a ce type commun. La colonne de noms UNPIVOT est de type VARCHAR.

  • Si une valeur de liste IN ne possède pas d’alias, UNPIVOT utilise le nom de la colonne comme valeur par défaut.