Kategorien:: Abfragesyntax

GROUP BY¶

Gruppiert Zeilen mit denselben Ausdrücken zum Gruppieren nach Elementen und berechnet Aggregatfunktionen für die resultierende Gruppe. Ein GROUP BY-Ausdruck kann ein Spaltenname sein, eine Nummer, die auf eine Position in der Liste SELECT verweist, oder ein allgemeiner Ausdruck.

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

Syntax¶

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

Wobei:

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

column_alias: Spaltenalias, der in der Liste SELECT des Abfrageblocks angezeigt wird.
position: Position eines Ausdrucks in der Liste SELECT.
expr: Jeder Ausdruck in Tabellen im aktuellen Bereich.

Nutzungshinweise¶

Eine GROUP BY-Klausel kann auf Ausdrücke in der Projektionsklausel nach „name“ oder nach „position“ verweisen. Wenn die GROUP BY-Klausel nach „name“ referenziert, wird jede Referenz wie folgt aufgelöst:
- Wenn die Abfrage ein Datenbankobjekt (z. B. Tabelle oder View) mit einem übereinstimmenden Spaltennamen enthält, wird die Referenz in den Spaltennamen aufgelöst.
- Wenn aber die Projektionsklausel von SELECT einen Alias-Ausdruck mit einem übereinstimmenden Namen enthält, wird die Referenz in den Alias aufgelöst.
Ein Beispiel dazu finden Sie unter Rangfolge bei Übereinstimmung von Spaltenname und Alias.

Beispiele¶

Gruppieren nach einer Spalte¶

In diesem Beispiel wird der Bruttoumsatz pro Produkt angezeigt, gruppiert nach 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;

Ausgabe:

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

Dieses Beispiel baut auf dem vorherigen Beispiel auf und zeigt den Nettogewinn pro Produkt an, gruppiert nach 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;

Ausgabe:

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

Gruppieren nach mehreren Spalten¶

Sie können anhand mehrerer Spalten gruppieren:

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

Ausgabe:

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

Rangfolge bei Übereinstimmung von Spaltenname und Alias¶

Es ist möglich (aber normalerweise keine gute Idee), eine Abfrage zu erstellen, die einen Alias enthält, der mit einem Spaltennamen übereinstimmt:

select x, some_expression as x
    from ...

Wenn eine GROUP BY-Klausel einen Namen enthält, der sowohl mit einem Spaltennamen als auch mit einem Alias übereinstimmt, dann verwendet die GROUP BY-Klausel den Spaltennamen. Dies wird im Beispiel unten veranschaulicht.

Erstellen Sie eine Tabelle, und fügen Sie Zeilen ein:

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

Die folgende Abfrage gibt die Summe der Gehälter der aktiven Mitarbeiter zurück sowie die Summe der Gehälter der Mitarbeiter, die im Urlaub sind.

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

Die nachstehende Abfrage verwendet den Alias state, der mit dem Namen einer Spalte in einer Tabelle der Abfrage übereinstimmt. Wenn state in GROUP BY verwendet wird, interpretiert Snowflake dies als Verweis auf den Spaltennamen, nicht auf den Alias. Diese Abfrage gibt also die Summe der Gehälter der Beschäftigten im Bundesstaat Kalifornien und die Summe der Gehälter der Beschäftigten im Bundesstaat Oregon zurück, zeigt aber dennoch Informationen zum Beschäftigungsstatus (employment_state) an (z. B. „Active“) und nicht die Namen von Bundesstaaten oder Provinzen.

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