Catégories :: Syntaxe de requête

GROUP BY¶

Regroupe les lignes avec les mêmes expressions group-by-item et calcule les fonctions d’agrégation pour le groupe résultant. Une expression GROUP BY peut être un nom de colonne, un nombre référençant une position dans la liste SELECT ou une expression générale.

Extensions :: GROUP BY CUBE, GROUP BY GROUPING SETS, GROUP BY ROLLUP

Syntaxe¶

SELECT ...
FROM ...
[ ... ]
GROUP BY groupItem [ , groupItem> [ , ... ] ]
[ ... ]

Où :

groupItem ::= { <column_alias> | <position> | <expr> }

column_alias: Alias de colonne figurant dans la liste SELECT du bloc de requête.
position: Position d’une expression dans la liste SELECT.
expr: Toute expression sur les tables du périmètre actuel.

Notes sur l’utilisation¶

Une clause GROUP BY peut faire référence à des expressions dans la clause de projection par nom ou par position. Si la clause GROUP BY fait référence à un nom, alors chaque référence est résolue comme suit :
- Si la requête contient un objet de base de données (par exemple, une table ou une vue) avec un nom de colonne correspondant, la référence est résolue au nom de la colonne.
- Sinon, si la clause de projection de SELECT contient un alias d’expression avec un nom correspondant, alors la référence est résolue vers l’alias.
Pour un exemple, voir Démontrer la priorité lorsqu’un nom de colonne et un alias correspondent.

Exemples¶

Grouper par une colonne¶

Cet exemple montre le revenu brut par produit, groupé par product_id :

-- Create the sales table and insert some rows.
CREATE TABLE sales (product_ID INTEGER, retail_price REAL,
                    quantity INTEGER, city VARCHAR, state VARCHAR);
INSERT INTO sales (product_id, retail_price, quantity, city, state) VALUES
     (1, 2.00,  1, 'SF', 'CA'),
     (1, 2.00,  2, 'SJ', 'CA'),
     (2, 5.00,  4, 'SF', 'CA'),
     (2, 5.00,  8, 'SJ', 'CA'),
     (2, 5.00, 16, 'Miami', 'FL'),
     (2, 5.00, 32, 'Orlando', 'FL'),
     (2, 5.00, 64, 'SJ', 'PR');

-- Show the gross revenue, grouped by product_id.
-- In other words, show the total amount of money received for
-- selling each product.
SELECT product_ID, SUM(retail_price * quantity) AS gross_revenue
  FROM sales
  GROUP BY product_ID;

Sortie :

+------------+---------------+
| PRODUCT_ID | GROSS_REVENUE |
+============+===============+
|          1 |          6    |
+------------+---------------+
|          2 |        620    |
+------------+---------------+

Cet exemple s’appuie sur l’exemple précédent et présente le bénéfice net par produit, regroupé par product_id :

-- Create the products table and insert some rows.
CREATE TABLE products (product_ID INTEGER, wholesale_price REAL);
INSERT INTO products (product_ID, wholesale_price) VALUES (1, 1.00);
INSERT INTO products (product_ID, wholesale_price) VALUES (2, 2.00);

-- Show the net profit on each product.
SELECT p.product_ID, SUM((s.retail_price - p.wholesale_price) * s.quantity) AS profit
  FROM products AS p, sales AS s
  WHERE s.product_ID = p.product_ID
  GROUP BY p.product_ID;

Sortie :

+------------+--------+
| PRODUCT_ID | PROFIT |
+============+========+
|          1 |      3 |
+------------+--------+
|          2 |    372 |
+------------+--------+

Vous pouvez grouper par plusieurs colonnes :¶

Vous pouvez grouper par plusieurs colonnes :

SELECT State, City, SUM(retail_price * quantity) AS gross_revenue
 FROM sales
 GROUP BY State, City;

Sortie :

+-------+---------+---------------+
| STATE |   CITY  | GROSS REVENUE |
+=======+=========+===============+
|   CA  | SF      |            22 |
+-------+---------+---------------+
|   CA  | SJ      |            44 |
+-------+---------+---------------+
|   FL  | Miami   |            80 |
+-------+---------+---------------+
|   FL  | Orlando |           160 |
+-------+---------+---------------+
|   PR  | SJ      |           320 |
+-------+---------+---------------+

Démontrer la priorité lorsqu’un nom de colonne et un alias correspondent¶

Il est possible (mais c’est généralement une mauvaise idée) de créer une requête qui contient un alias correspondant à un nom de colonne :

select x, some_expression as x
    from ...

Si une clause GROUP BY contient un nom qui correspond à la fois à un nom de colonne et à un alias, alors la clause GROUP BY utilise le nom de la colonne. Voici une démonstration dans l’exemple ci-dessous.

Créer une table et insérer des lignes :

Create table employees (salary float, state varchar, employment_state varchar);
insert into employees (salary, state, employment_state) values
    (60000, 'California', 'Active'),
    (70000, 'California', 'On leave'),
    (80000, 'Oregon', 'Active');

La requête ci-dessous renvoie la somme des salaires des employés qui sont actifs et la somme des salaires des employés qui sont en congé.

select sum(salary), ANY_VALUE(employment_state)
    from employees
    group by employment_state;
+-------------+-----------------------------+
| SUM(SALARY) | ANY_VALUE(EMPLOYMENT_STATE) |
|-------------+-----------------------------|
|      140000 | Active                      |
|       70000 | On leave                    |
+-------------+-----------------------------+

La requête ci-dessous utilise l’alias state, qui correspond au nom d’une colonne d’une table dans la requête. Lorsque state est utilisé dans GROUP BY, Snowflake l’interprète comme une référence au nom de la colonne, et non à l’alias. Cette requête renvoie donc la somme des salaires des employés de l’État de Californie et la somme des salaires des employés de l’État de l’Oregon, tout en affichant les informations employment_state (par ex. « Active ») plutôt que des noms d’États ou de provinces.

select sum(salary), ANY_VALUE(employment_state) as state
    from employees
    group by state;
+-------------+--------+
| SUM(SALARY) | STATE  |
|-------------+--------|
|      130000 | Active |
|       80000 | Active |
+-------------+--------+