2021-12-08 22:21 작성
My SQL basic 메모
Table of contents
GROUP BY 도입
GROUP BY는 columns 혹은 expressions의 값들로 일련의 rows를 요약된 rows으로 그룹화한다. GROUP BY는 각 그룹을 하나의 row로 반환한다. 다시 말해 여러 rows를 줄인 형태로 결과를 반환한다는 의미다.
GROUP BY는 SELECT 구문의 선택적 절이다.
SELECT
c1, c2,..., cn, aggregate_function(ci)
FROM
table
WHERE
where_conditions
GROUP BY c1 , c2,...,cn;
위 문법에서 GROUP BY는 FROM과 WHERE 절 다음에 위치한다. GROUP BY 키워드 다음에는 그룹 rows로 만들기 위한 columns 혹은 expressions 리스트를 콤마(,)로 구분해 위치시킨다.
MySQL에서는 GROUP BY 절을 FROM과 WHERE 절 다음에 평가하고 HAVING, SELECT, DISTINCT, ORDER BY 그리고 LIMIT 절 전에 평가한다.
실제 사용에 있어서 GROUP BY 절은 종종 SUM, AVG, MAX, MIN 그리고 COUNT와 같은 aggregate functions와 함께 사용된다. SELECT 절에서 나타나는 aggregate function은 각 그룹의 정보를 제공한다.
예시
1) 간단한 MySQL GROUP BY 예시
order의 status의 값들을 하위 groups로 그룹화하고 싶다고 가정해보자:
SELECT
status
FROM
orders
GROUP BY status;
output: status는 개별 row가 다수 존재하지만 GROUP BY로 인해 각 대표 항목으로 축약되었다. DISTINCT 연산자도 똑같은 결과를 보여준다.
| status |
|---|
| Shipped |
| Resolved |
| Cancelled |
| On Hold |
| Disputed |
| In Process |
2) Aggregate functions와 MySQL GROUP BY 사용하기
Aggregate functions는 일련의 rows를 계산해 한 개의 값을 반환하는 유용한 도구다. GROUP BY 절은 종종 aggregate function과 함께 사용되어 계산을 수행한 뒤 각각의 하위 group에 하나의 값을 반환하는데 사용된다.
예를 들어, 만약 각각의 status에서 항목별 수를 알고 싶다면 GROUP BY와 함께 COUNT function을 사용할 수 있다.
SELECT
status, COUNT(*)
FROM
orders
GROUP BY status;
ORDER BY status;
output:
| status | COUNT(*) |
|---|---|
| Cancelled | 6 |
| Disputed | 3 |
| In Process | 6 |
| On Hold | 4 |
| Resolved | 4 |
| Shipped | 303 |
다음의 orders와 orderdetails table을 보자.
status 별 합계를 얻기 위해서 orders table과 orderdetails table을 join하고 합계를 계산하기 위해 SUM function을 사용한다. 다음은 관련 쿼리다:
SELECT
status,
SUM(quantityOrdered * priceEach) AS amount
FROM
orders
INNER JOIN orderdetails
USING (orderNumber)
GROUP BY status
ORDER BY amount DESC;
output:
| status | amount |
|---|---|
| Shipped | 8865094.64 |
| Cancelled | 238854.18 |
| On Hold | 169575.61 |
| In Process | 135271.52 |
| Resolved | 134235.88 |
| Disputed | 61158.78 |
3) Expression과 함께 MySQL GROUP BY 사용하기
columns에 더해 rows를 expressions로 그룹화할 수 있다. 다음은 매 년 총 판매액을 쿼리해 얻는다.
SELECT
YEAR(orderDate) AS year,
SUM(quantityOrdered * priceEach) AS total
FROM
orders
INNER JOIN orderdetails
USING (orderNumber)
WHERE
status = 'Shipped'
GROUP BY
YEAR(orderDate);
output:
| year | total |
|---|---|
| 2003 | 3223095.80 |
| 2004 | 4300602.99 |
| 2005 | 1341395.85 |
이 예시에서 orderDate로부터 year 데이터를 추출하는데 YEAR function이 사용됐다. 그리고 총 판매액에서 shipped status만 해당되도록 조건이 정해졌다. SELECT 절에서 나타나는 expression은 GROUP BY 절에서의 하나와 같아야 한다.
4) HAVING 절과 함께 GROUP BY 사용하기
GROUP BY 절에 의해 반환되는 groups를 필터하는데 있어 HAVING이 사용된다. 다음의 쿼리는 HAVING 절을 2003년 이후의 총 판매액을 선택하는데 사용한다.
SELECT
YEAR(orderDate) AS year,
SUM(quantityOrdered * priceEach) AS total
FROM
orders
INNER JOIN orderdetails
USING (orderNumber)
WHERE
status = 'Shipped'
GROUP BY
year
HAVING
year > 2003;
output:
| year | total |
|---|---|
| 2004 | 4300602.99 |
| 2005 | 1341395.85 |
GROUP BY: MySQL vs. SQL standard
SQL standard는 GROUP BY 절에서 alias를 사용하는 것을 허용하지 않는다. 반면에 MySQL은 alias를 허용한다.
예를 들어 다음의 쿼리는 orderDate에서 year을 추출하는 쿼리다. YEAR(orderDate) expression의 alias인 year는 GROUP BY에서 다시 사용된다.
SELECT
YEAR(orderDate) AS year,
COUNT(orderNumber)
FROM
orders
GROUP BY
year;
또한, MySQL은 오름차순 혹은 내림차순으로 groups를 정렬할 수 있도록 허용한다. 기본값은 오름차순이다. 예를 들어, 만약 orders의 수를 status 단위로 얻고자 하며 이것을 내림차순으로 정렬하길 원한다면 GROUP BY에 DESC를 사용하면 된다.
SELECT
status,
COUNT(*)
FROM
orders
GROUP BY
status DESC;
GROUP BY vs. DISTINCT
만약 GROUP BY를 aggregate functions 없이 SELECT 구문에서 사용한다면 GROUP BY는 DISTINCT와 비슷하게 작동한다.
SELECT
state
FROM
customers
GROUP BY state;
-- DISTINCT
SELECT DISTINCT
state
FROM
customers;
일반적으로 DISTINCT는 GROUP BY의 특별한 사례 중 하나다. DISTINCT와 GROUP BY의 차이는 GROUP BY은 결과를 정렬할 수 있지만 DISTINCT는 그럴 수 없다는 점에 있다.
주의: MySQL 8.0 이후 버전에서는
GROUP BY에서 정렬할 수 없게 되었다.