MySQL CASE 뭐시기랑 작성 시간대별로 그룹핑해 조회하기!

ORM과 단순한 쿼리만 접하다보니 부족한 부분이 많았네요.

흥미로운 SQL문제가 있어 정리합니다.

요구사항

하루를 6개의 시간대로 나눠 각 시간대별 구매내역의 개수를 반환하세요.

Transaction Table

name	Type
customer_id	int
created_at	datetime

output

Duration	Count
00:00 ~ 03:59	1
04:00 ~ 07:59	1
08:00 ~ 11:59	5
12:00 ~ 15:59	3
16:00 ~ 19:59	1
20:00 ~ 23:59	2

---

코드

SELECT
	CASE
    	WHEN HOUR(created_at) BETWEEN 0 AND 3 THEN '00:00 ~ 03:59'
        WHEN HOUR(created_at) BETWEEN 4 AND 7 THEN '04:00 ~ 07:59'
        WHEN HOUR(created_at) BETWEEN 8 AND 11 THEN '08:00 ~ 11:59'
        WHEN HOUR(created_at) BETWEEN 12 AND 15 THEN '12:00 ~ 15:59'
        WHEN HOUR(created_at) BETWEEN 16 AND 19 THEN '16:00 ~ 19:59'
        WHEN HOUR(created_at) BETWEEN 20 AND 23 THEN '20:00 ~ 23:59'
    END AS Duration,
    count(*) AS Count
FROM Transaction
GROUP BY Duration
ORDER BY Duration;

해석

• CASE WHEN THEN을 사용했습니다.
  시간대를 나눠 비교할 때 직관적이여서 좋습니다. END로 마무리 짓고 그 뒤 AS 별칭을 둘 수 있다는걸 잊으면 안되겠습니다.

• 실행흐름

1. FROM Transaction
   쿼리는 먼저 Transaction 테이블에서 데이터를 가져옵니다.
   이 테이블에서 각 행의 created_at 열을 참고하여 각 레코드가 언제 생성되었는지를 추출합니다.

2. SELECT CASE...END AS Duration
   각 행에서 created_at 열의 시간 값을 추출합니다.
   HOUR(created_at)을 통해 created_at의 시간(Hour) 부분을 추출하여 각 레코드의 시간을 0부터 23까지의 값으로 표현합니다.

   • CASE 문을 사용해 다음과 같은 시간대에 따라 분류합니다:
   • 00:00 ~ 03:59
   • 04:00 ~ 07:59
   • 08:00 ~ 11:59
   • 12:00 ~ 15:59
   • 16:00 ~ 19:59
   • 20:00 ~ 23:59
   해당 시간대에 맞는 레이블(Duration)을 생성합니다.
   예를 들어, HOUR(created_at) 값이 5라면, ‘04:00 ~ 07:59’ 라벨이 부여됩니다.

id	created_at	Duration
1	2024-10-01 02:30:00	00:00 ~ 03:59
2	2024-10-01 05:15:00	04:00 ~ 07:59
3	2024-10-01 10:45:00	08:00 ~ 11:59
4	2024-10-01 13:20:00	12:00 ~ 15:59
5	2024-10-01 17:05:00	16:00 ~ 19:59

3. GROUP BY Duration
   Duration별로 데이터를 그룹화하여 시간대별로 집계합니다.
   동일한 시간대 레이블을 가진 레코드들이 하나의 그룹으로 묶입니다.
   예를 들어, ‘08:00 ~ 11:59’에 속하는 모든 레코드가 한 그룹이 됩니다.

4. COUNT(*) AS Count
   각 시간대(Duration)에 속하는 레코드의 개수를 셉니다.
   모든 Transaction 테이블의 레코드가 해당하는 시간대별로 카운트됩니다.

5. ORDER BY Duration
   결과를 Duration 순서대로 정렬합니다.
   여기서 중요한 점은 CASE 문의 출력이 문자열이므로, 시간대 순서대로 자연스럽게 정렬되지는 않습니다. 만약 특정 순서를 보장하려면 ORDER BY를 수정해야 할 수 있습니다. (현재는 문자열 알파벳 순서로 정렬됩니다.)

6. 최종 결과
   결과는 각 시간대(Duration)와 해당 시간대에 작성된 레코드의 개수(Count)가 표시됩니다.

열심히 공부해야겠다! DB야!!!!!!!