建立資料分組

Oracle SQL 入門

Hadrien Lacroix

Content Developer

資料分組

彙總資料

群組資訊

| Composer        | Milliseconds |
|-----------------|--------------|
| Antonio Vivaldi | 199,086      |
| Pearl Jam       | 122,801      |
| Pearl Jam       | 65,593       |
| Jimmy Page      | 401,920      |
| Jimmy Page      | 386,063      |
| Jimmy Page      | 132,702      |
| Jimmy Page      | 189,675      |
| Jimmy Page      | 126,641      |
| Carlos Santana  | 318,432      |
| Carlos Santana  | 296,437      |
| Carlos Santana  | 882,834      |
| ...             | ...          |

每位作曲家的歌曲，平均曲目長度是多少？

GROUP BY
- 將資料表的列分成群組
- 對每個群組使用聚合函式取得摘要

GROUP BY

SELECT Composer, AVG(Milliseconds)
FROM Track
GROUP BY Composer

| Composer        | AVG(Milliseconds) |
|-----------------|-------------------|
| Antonio Vivaldi | 199,086.0         |
| Pearl Jam       | 94,197.0          |
| Jimmy Page      | 474,888.3         |
| Carlos Santana  | 499,234.3         |
| ...             | ...               |

GROUP BY 與 WHERE

SELECT Composer, AVG(Milliseconds)
FROM Track
WHERE Genre = 1
GROUP BY Composer

| Composer        | AVG(Milliseconds) |
|-----------------|-------------------|
| Antonio Vivaldi | 199,086.0         |
| Pearl Jam       | 94,197.0          |
| Jimmy Page      | 474,888.3         |
| Carlos Santana  | 499,234.3         |
| ...             | ...               |

GROUP BY 與 ORDER BY

SELECT Composer, AVG(Milliseconds) AS Average
FROM Track
GROUP BY Composer
ORDER BY AVG(Milliseconds)

| Composer        | AVG(Milliseconds) |
|-----------------|-------------------|
| Pearl Jam       | 94,197.0          |
| Antonio Vivaldi | 199,086.0         |
| Jimmy Page      | 474,888.3         |
| Carlos Santana  | 499,234.3         |
| ...             | ...               |

GROUP BY 與 ORDER BY

SELECT Composer, AVG(Milliseconds) AS Average
FROM Track
GROUP BY Composer
ORDER BY 2

| Composer        | AVG(Milliseconds) |
|-----------------|-------------------|
| Pearl Jam       | 94,197.0          |
| Antonio Vivaldi | 199,086.0         |
| Jimmy Page      | 474,888.3         |
| Carlos Santana  | 499,234.3         |
| ...             | ...               |

GROUP BY 與 ORDER BY

SELECT Composer, AVG(Milliseconds) AS Average
FROM Track
GROUP BY Composer
ORDER BY Average

| Composer        | Average           |
|-----------------|-------------------|
| Pearl Jam       | 94,197.0          |
| Antonio Vivaldi | 199,086.0         |
| Jimmy Page      | 474,888.3         |
| Carlos Santana  | 499,234.3         |
| ...             | ...               |

指引

在 SELECT 陳述式中，凡不是聚合函式的欄位或運算式，都必須出現在 GROUP BY 子句中。

SELECT Composer, AVG(Milliseconds), UnitPrice
FROM Track
GROUP BY Composer

column "track.unitprice" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2: SELECT Composer, AVG(Milliseconds), UnitPrice

指引

SELECT 清單中凡不是聚合函式的欄位或運算式，都必須出現在 GROUP BY 子句中。

SELECT Composer, AVG(Milliseconds), MAX(UnitPrice)
FROM Track
GROUP BY Composer

| Composer        | AVG(Milliseconds) | MAX(UnitPrice) |
|-----------------|-------------------|----------------|
| Antonio Vivaldi | 199,086.0         | 0.99           |
| Pearl Jam       | 94,197.0          | 0.99           |
| Jimmy Page      | 474,888.3         | 0.99           |
| Carlos Santana  | 499,234.3         | 0.99           |
| ...             | ...               | ...            |

指引

指定在 GROUP BY 的運算式，不一定要包含在 SELECT 陳述式中。

SELECT AVG(Milliseconds)
FROM Track
GROUP BY Composer

| AVG(Milliseconds) |
|-------------------|
| 199,086.0         |
| 94,197.0          |
| 474,888.3         |
| 499,234.3         |
| ...               |

多欄位

SELECT Country, City, COUNT(CustomerId)
FROM Customer
GROUP BY Country, City

| Country   | City                | COUNT(CustomerId) |
|-----------|---------------------|-------------------|
| Argentina | Buenos Aires        | 1                 |
| Australia | Sidney              | 1                 |
| Austria   | Vienne              | 1                 |
| Belgium   | Brussels            | 1                 |
| Brazil    | Brasilia            | 1                 |
| Brazil    | São José dos Campos | 1                 |
| Brazil    | São Paulo           | 2                 |
| ...       | ...                 | ...               |

一起來練習吧！

Oracle SQL 入門