- Catégories :
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 :
Syntaxe¶
SELECT ...
FROM ...
[ ... ]
GROUP BY groupItem [ , groupItem> [ , ... ] ]
[ ... ]
Où :
groupItem ::= { <column_alias> | <position> | <expr> }
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 | +-------------+--------+