전체 매장의 수수료 타입을 뽑아 보자.

MySQL 그룹 함수

안녕하세요. Calvin입니다. 업무 처리 중에 SQL의 그룹핑과 관련 내용을 배운 것이 있어서 공유드리려 합니다.

---

카카오헤어샵 MySQL DB의 shop_platform_fee는 매장별로 수수료 타입을 저장하는 테이블입니다.

칼럼은 apply_date (적용일) approved_at (승인 일시) type (수수료 타입) shop_id (매장 id)이고,

다음 달부터 수수료 타입 변경을 예약할 수 있기 때문에 apply_date은 미래 날짜 일수도 있습니다.

매장별로 현재 수수료 타입을 결정하는 쿼리는 아래와 같습니다.

SELECT type 
FROM shop_platform_fee 
WHERE shop_id = ?
AND apply_date <= now()
ORDER BY apply_date DESC
LIMIT 1;

그런데 전체 매장의 수수료 타입을 뽑으려면 어떻게 해야 할까요?

방법 1. Self Join을 이용하자.

SELECT
a.shop_id, b.type
FROM
shop_platform_fee b
INNER JOIN (
   SELECT
   shop_id, max(apply_date) as max_apply_date 
   FROM shop_platform_fee
   WHERE apply_date <= now()
   GROUP BY shop_id) a 
ON b.shop_id = a.shop_id 
AND b.apply_date = a.max_apply_date

수행 결과

주의할 점은 ORDER BY 조건이 많아지면 위 SQL로는 충분하지 않다는 점입니다.

(ORDER BY apply_date DESC, approved_at DESC 이면 JOIN을 2번 해야 합니다.)

방법 2. GROUP_CONCAT 함수를 이용하자.

MySQL에는 그룹에서 NULL 이 아닌 값들을 연결하여 결과 문자열을 VARCHAR 타입으로 반환하는  GROUP_CONCAT 함수가 있습니다. 

SELECT shop_id, GROUP_CONCAT(type)
FROM shop_platform_fee
WHERE apply_date <= now()
GROUP BY shop_id;

수행 결과

GROUP_CONCAT 함수에 ORDER BY를 넣어 정렬합니다. (아쉽게도 LIMIT는 지원 안 함)

SELECT shop_id, GROUP_CONCAT(type ORDER BY apply_date DESC)
FROM shop_platform_fee
WHERE apply_date <= now()
GROUP BY shop_id;

그다음은 문자열 중에서 첫 번째 수수료 타입만 뽑아 볼게요.

SUBSTRING_INDEX 함수가 그 일을 해 줍니다.

SELECT shop_id,
SUBSTRING_INDEX(GROUP_CONCAT(type ORDER BY apply_date DESC), ',', 1)
FROM shop_platform_fee
WHERE apply_date <= now()
GROUP BY shop_id;

수행 결과