데이터 그룹화

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

Ayo berlatih!

Oracle SQL 입문