データをグループ化する

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                 |
| ...       | ...                 | ...               |

Let's practice!

Oracle SQL 入門