Extensions de regroupement

Amazon Redshift prend en charge les extensions d’agrégation permettant d’effectuer plusieurs opérations GROUP BY dans une seule instruction.

Les exemples d’extensions d’agrégation utilisent la table orders, qui contient les données de vente d’une entreprise d’électronique. Pour créer des orders, procédez comme suit.

CREATE TABLE ORDERS (
    ID INT,
    PRODUCT CHAR(20),
    CATEGORY CHAR(20),
    PRE_OWNED CHAR(1),
    COST DECIMAL
);

INSERT INTO ORDERS VALUES
    (0, 'laptop',       'computers',    'T', 1000),
    (1, 'smartphone',   'cellphones',   'T', 800),
    (2, 'smartphone',   'cellphones',   'T', 810),
    (3, 'laptop',       'computers',    'F', 1050),
    (4, 'mouse',        'computers',    'F', 50);

GROUPING SETS

Calcule un ou plusieurs jeux de regroupement dans une seule instruction. Un jeu de regroupement est l’ensemble d’une clause GROUP BY unique, un jeu de 0 colonne ou plus avec lequel vous pouvez regrouper le jeu de résultats d’une requête. GROUP BY GROUPING SETS revient à exécuter une requête UNION ALL sur un jeu de résultats groupé par différentes colonnes. Par exemple, GROUP BY GROUPING SETS((a), (b)) est équivalent à GROUP BY a UNION ALL GROUP BY b.

L’exemple suivant renvoie le coût des produits de la table des commandes, regroupés par catégories de produits et type de produits vendus.

SELECT category, product, sum(cost) as total
FROM orders
GROUP BY GROUPING SETS(category, product);

       category       |       product        | total
----------------------+----------------------+-------
 computers            |                      |  2100
 cellphones           |                      |  1610
                      | laptop               |  2050
                      | smartphone           |  1610
                      | mouse                |    50

(5 rows)

ROLLUP

Suppose une hiérarchie dans laquelle les colonnes précédentes sont considérées comme les parents des colonnes suivantes. ROLLUP regroupe les données par colonnes fournies et renvoie des lignes de sous-totaux supplémentaires représentant les totaux à tous les niveaux de colonnes de regroupement, en plus des lignes groupées. Par exemple, vous pouvez utiliser GROUP BY ROLLUP((a), (b)) pour renvoyer un jeu de résultats regroupé d’abord par a, puis par b en supposant que b est une sous-section de a. ROLLUP renvoie également une ligne contenant le jeu des résultats sans regrouper les colonnes.

GROUP BY ROLLUP((a), (b)) équivaut à GROUP BY GROUPING SETS((a,b), (a), ()).

L’exemple suivant renvoie le coût des produits de la table des commandes, regroupés d’abord par catégorie, puis par produit, le produit étant une subdivision de la catégorie.

SELECT category, product, sum(cost) as total
FROM orders
GROUP BY ROLLUP(category, product) ORDER BY 1,2;

       category       |       product        | total
----------------------+----------------------+-------
 cellphones           | smartphone           |  1610
 cellphones           |                      |  1610
 computers            | laptop               |  2050
 computers            | mouse                |    50
 computers            |                      |  2100
                      |                      |  3710
(6 rows)

CUBE

Regroupe les données par colonnes fournies et renvoie des lignes de sous-totaux supplémentaires représentant les totaux à tous les niveaux de colonnes de regroupement, en plus des lignes groupées. CUBE renvoie les mêmes lignes que ROLLUP, mais ajoute des lignes de sous-total supplémentaires pour chaque combinaison de colonnes de regroupement non couverte par ROLLUP. Par exemple, vous pouvez utiliserGROUP BY CUBE ((a), (b)) pour renvoyer un jeu de résultats regroupé d’abord par a, puis par b en supposant que b est une sous-section de a, puis par b uniquement. CUBE renvoie également une ligne contenant le jeu des résultats sans regrouper les colonnes.

GROUP BY CUBE((a), (b)) équivaut à GROUP BY GROUPING SETS((a, b), (a), (b), ()).

L’exemple suivant renvoie le coût des produits de la table des commandes, regroupés d’abord par catégorie, puis par produit, le produit étant une subdivision de la catégorie. Contrairement à l’exemple précédent pour ROLLUP, l’instruction renvoie des résultats pour chaque combinaison de colonnes de regroupement.

SELECT category, product, sum(cost) as total
FROM orders
GROUP BY CUBE(category, product) ORDER BY 1,2;

       category       |       product        | total
----------------------+----------------------+-------
 cellphones           | smartphone           |  1610
 cellphones           |                      |  1610
 computers            | laptop               |  2050
 computers            | mouse                |    50
 computers            |                      |  2100
                      | laptop               |  2050
                      | mouse                |    50
                      | smartphone           |  1610
                      |                      |  3710
(9 rows)

Fonctions GROUPING/GROUPING_ID

ROLLUP et CUBE ajoutent des valeurs NULL au jeu de résultats pour indiquer le sous-total des lignes. Par exemple, GROUP BY ROLLUP((a), (b)) renvoie une ou plusieurs lignes dont la valeur est NULL dans la colonne de regroupement b pour indiquer qu’il s’agit de sous-totaux des champs de la colonne de regroupement a. Ces valeurs NULL ne servent qu’à satisfaire le format des tuples renvoyés.

Lorsque vous exécutez des opérations GROUP BY avec ROLLUP et CUBE sur des relations qui stockent elles-mêmes des valeurs NULL, cela peut produire des jeux de résultats dont les lignes semblent avoir des colonnes de regroupement identiques. Pour revenir à l’exemple précédent, si la colonne de regroupement b contient une valeur NULL stockée, GROUP BY ROLLUP((a), (b)) renvoie une ligne avec une valeur NULL dans la colonne de regroupement b qui n’est pas un sous-total.

Pour faire la distinction entre les valeurs NULL créées par ROLLUP et CUBE et les valeurs NULL stockées dans les tables elles-mêmes, vous pouvez utiliser la fonction GROUPING ou son alias GROUPING_ID. GROUPING prend un seul jeu de regroupement comme argument et, pour chaque ligne du jeu de résultats, renvoie une valeur de 0 ou 1 bit correspondant à la colonne de regroupement dans cette position, puis convertit cette valeur en nombre entier. Si la valeur de cette position est une valeur NULL créée par une extension d’agrégation, GROUPING renvoie 1. Elle renvoie 0 pour toutes les autres valeurs, y compris les valeurs NULL stockées.

Par exemple, GROUPING(category, product) peut renvoyer les valeurs suivantes pour une ligne donnée, en fonction des valeurs des colonnes de regroupement pour cette ligne. Pour les besoins de cet exemple, toutes les valeurs NULL de la table sont des valeurs NULL créées par une extension d’agrégation.

colonne de catégorie	colonne de produits	Valeur en bits de la fonction GROUPING	Valeur décimale
not NULL	not NULL	00	0
not NULL	NULL	01	1
NULL	not NULL	10	2
NULL	NULL	11	3

Les fonctions GROUPING apparaissent dans la partie liste SELECT de la requête au format suivant.

SELECT ... [GROUPING( expr )...] ...
  GROUP BY ... {CUBE | ROLLUP| GROUPING SETS} ( expr ) ...
               

L’exemple suivant est identique à l’exemple précédent pour CUBE, mais avec l’ajout de fonctions GROUPING pour ses jeux de regroupement.

SELECT category, product,
       GROUPING(category) as grouping0,
       GROUPING(product) as grouping1,
       GROUPING(category, product) as grouping2,
       sum(cost) as total
FROM orders
GROUP BY CUBE(category, product) ORDER BY 3,1,2;

       category       |       product        | grouping0 | grouping1 | grouping2 | total
----------------------+----------------------+-----------+-----------+-----------+-------
 cellphones           | smartphone           |         0 |         0 |         0 |  1610
 cellphones           |                      |         0 |         1 |         1 |  1610
 computers            | laptop               |         0 |         0 |         0 |  2050
 computers            | mouse                |         0 |         0 |         0 |    50
 computers            |                      |         0 |         1 |         1 |  2100
                      | laptop               |         1 |         0 |         2 |  2050
                      | mouse                |         1 |         0 |         2 |    50
                      | smartphone           |         1 |         0 |         2 |  1610
                      |                      |         1 |         1 |         3 |  3710
(9 rows)

ROLLUP et CUBE partiels

Vous pouvez exécuter les opérations ROLLUP et CUBE avec une partie seulement des sous-totaux.

La syntaxe des opérations ROLLUP et CUBE partielles est la suivante.

GROUP BY expr1, { ROLLUP | CUBE }(expr2, [, ...])

Ici, la clause GROUP BY crée uniquement des lignes de sous-total au niveau de expr2 et au-delà.

Les exemples suivants illustrent des opérations ROLLUP et CUBE partielles sur la table des commandes, en les regroupant d’abord par produit d’occasion ou non, puis en exécutant ROLLUP et CUBE dans les colonnes de catégorie et de produit.

SELECT pre_owned, category, product,
       GROUPING(category, product, pre_owned) as group_id,
       sum(cost) as total
FROM orders
GROUP BY pre_owned, ROLLUP(category, product) ORDER BY 4,1,2,3;

 pre_owned |       category       |       product        | group_id | total
-----------+----------------------+----------------------+----------+-------
 F         | computers            | laptop               |        0 |  1050
 F         | computers            | mouse                |        0 |    50
 T         | cellphones           | smartphone           |        0 |  1610
 T         | computers            | laptop               |        0 |  1000
 F         | computers            |                      |        2 |  1100
 T         | cellphones           |                      |        2 |  1610
 T         | computers            |                      |        2 |  1000
 F         |                      |                      |        6 |  1100
 T         |                      |                      |        6 |  2610
(9 rows)

SELECT pre_owned, category, product,
       GROUPING(category, product, pre_owned) as group_id,
       sum(cost) as total
FROM orders
GROUP BY pre_owned, CUBE(category, product) ORDER BY 4,1,2,3;

 pre_owned |       category       |       product        | group_id | total
-----------+----------------------+----------------------+----------+-------
 F         | computers            | laptop               |        0 |  1050
 F         | computers            | mouse                |        0 |    50
 T         | cellphones           | smartphone           |        0 |  1610
 T         | computers            | laptop               |        0 |  1000
 F         | computers            |                      |        2 |  1100
 T         | cellphones           |                      |        2 |  1610
 T         | computers            |                      |        2 |  1000
 F         |                      | laptop               |        4 |  1050
 F         |                      | mouse                |        4 |    50
 T         |                      | laptop               |        4 |  1000
 T         |                      | smartphone           |        4 |  1610
 F         |                      |                      |        6 |  1100
 T         |                      |                      |        6 |  2610
(13 rows)

Comme la colonne d’occasion n’est pas incluse dans les opérations ROLLUP et CUBE, aucune ligne du total général n’inclut toutes les autres lignes.

Concatenated grouping

Vous pouvez concaténer plusieurs clauses GROUPING SETS/ROLLUP/CUBE pour calculer différents niveaux de sous-totaux. Les regroupements concaténés renvoient le produit cartésien des jeux de regroupement fournis.

La syntaxe de concaténation des clauses GROUPING SETS/ROLLUP/CUBE est la suivante.

GROUP BY {ROLLUP|CUBE|GROUPING SETS}(expr1[, ...]),
         {ROLLUP|CUBE|GROUPING SETS}(expr1[, ...])[, ...]

Examinez l’exemple suivant pour voir comment un petit regroupement concaténé peut produire un jeu de résultats final volumineux.

SELECT pre_owned, category, product,
       GROUPING(category, product, pre_owned) as group_id,
       sum(cost) as total
FROM orders
GROUP BY CUBE(category, product), GROUPING SETS(pre_owned, ())
ORDER BY 4,1,2,3;

 pre_owned |       category       |       product        | group_id | total
-----------+----------------------+----------------------+----------+-------
 F         | computers            | laptop               |        0 |  1050
 F         | computers            | mouse                |        0 |    50
 T         | cellphones           | smartphone           |        0 |  1610
 T         | computers            | laptop               |        0 |  1000
           | cellphones           | smartphone           |        1 |  1610
           | computers            | laptop               |        1 |  2050
           | computers            | mouse                |        1 |    50
 F         | computers            |                      |        2 |  1100
 T         | cellphones           |                      |        2 |  1610
 T         | computers            |                      |        2 |  1000
           | cellphones           |                      |        3 |  1610
           | computers            |                      |        3 |  2100
 F         |                      | laptop               |        4 |  1050
 F         |                      | mouse                |        4 |    50
 T         |                      | laptop               |        4 |  1000
 T         |                      | smartphone           |        4 |  1610
           |                      | laptop               |        5 |  2050
           |                      | mouse                |        5 |    50
           |                      | smartphone           |        5 |  1610
 F         |                      |                      |        6 |  1100
 T         |                      |                      |        6 |  2610
           |                      |                      |        7 |  3710
(22 rows)

Nested grouping

Vous pouvez utiliser les opérations GROUPING SETS/ROLLUP/CUBE comme expr pour former un regroupement imbriqué. Le sous-regroupement au sein des GROUPING SETS imbriqués est aplati.

La syntaxe pour les regroupements imbriqués est la suivante.

GROUP BY GROUPING SETS({ROLLUP|CUBE|GROUPING SETS}(expr[, ...])[, ...])

Prenez l’exemple de code suivant.

SELECT category, product, pre_owned,
       GROUPING(category, product, pre_owned) as group_id,
       sum(cost) as total
FROM orders
GROUP BY GROUPING SETS(ROLLUP(category), CUBE(product, pre_owned))
ORDER BY 4,1,2,3;

       category       |       product        | pre_owned | group_id | total
----------------------+----------------------+-----------+----------+-------
 cellphones           |                      |           |        3 |  1610
 computers            |                      |           |        3 |  2100
                      | laptop               | F         |        4 |  1050
                      | laptop               | T         |        4 |  1000
                      | mouse                | F         |        4 |    50
                      | smartphone           | T         |        4 |  1610
                      | laptop               |           |        5 |  2050
                      | mouse                |           |        5 |    50
                      | smartphone           |           |        5 |  1610
                      |                      | F         |        6 |  1100
                      |                      | T         |        6 |  2610
                      |                      |           |        7 |  3710
                      |                      |           |        7 |  3710
(13 rows)

Notez que, comme ROLLUP(category) et CUBE(product, pre_owned) contiennent tous deux le jeu de regroupement (), la ligne représentant le total général est dupliquée.

Notes d’utilisation

• La clause GROUP BY prend en charge jusqu’à 64 jeux de regroupement. Dans le cas de ROLLUP et CUBE, ou d’une combinaison de GROUPING SETS, ROLLUP et CUBE, cette limitation s’applique au nombre implicite de jeux de regroupement. Par exemple, GROUP BY CUBE((a), (b)) compte comme 4 jeux de regroupement, et non 2.

• Vous ne pouvez pas utiliser de constantes pour regrouper des colonnes lorsque vous utilisez des extensions d’agrégation.

• Vous ne pouvez pas créer un ensemble de regroupement qui contient des colonnes en double.