[TIL] SUM함수, CASE WHEN ~ THEN 구문 활용 #SQL #SQLite #solvesql

solvesql - 3년간 들어온 소장품 집계하기
👉 문제 바로가기(solvesql)

-- 각 분류에 대해 연도별 추가된 소장품 수를 집계하는 쿼리를 작성
-- 각 행은 분류(classification) 컬럼 기준으로 오름차순 정렬
-- 집계하는 3년간 추가된 특정 분류의 소장품이 없더라도 해당 분류와 집계 내역을 결과 테이블에서 누락시키지 말고 포함

# BEST SOLUTION

SELECT
  classification,
  sum(CASE WHEN acquisition_date LIKE '2014-%' THEN 1 ELSE 0 END) AS '2014',
  sum(CASE WHEN acquisition_date LIKE '2015-%' THEN 1 ELSE 0 END) AS '2015',
  sum(CASE WHEN acquisition_date LIKE '2016-%' THEN 1 ELSE 0 END) AS '2016'
FROM artworks
GROUP BY 1
ORDER BY 1;


# 내 SOLUTION
WITH whole_t as
(
SELECT classification
FROM artworks
GROUP BY classification
),

t_2014 as
(
SELECT classification, COUNT(*) as 'a'
FROM artworks
WHERE acquisition_date LIKE '2014-%'
GROUP BY classification
ORDER BY 1
)

, t_2015 as
(
SELECT classification, COUNT(*) as 'b'
FROM artworks
WHERE acquisition_date LIKE '2015-%'
GROUP BY classification
ORDER BY 1
)

, t_2016 as
(
SELECT classification, COUNT(*) as 'c'
FROM artworks
WHERE acquisition_date LIKE '2016-%'
GROUP BY classification
ORDER BY 1
)

SELECT t1.classification, IFNULL(t2.a,0) as '2014', IFNULL(t3.b,0) as '2015', IFNULL(t4.c, 0) as '2016'
FROM whole_t t1
LEFT OUTER JOIN t_2014 t2 ON t1.classification = t2.classification
LEFT OUTER JOIN t_2015 t3 ON t1.classification = t3.classification
LEFT OUTER JOIN t_2016 t4 ON t1.classification = t4.classification

---

[ 논리 과정 ]

1. 2014, 2015, 2016 컬럼을 생성한다.
2. classification으로 그룹핑한다.
3. 각 연도 컬럼값으로 연도에 해당하는 데이터 값을 카운팅하여 조회한다.

[ 문제 상황 ]

• WITH AS 구문을 활용하여 4개의 새로운 테이블을 생성하고 LEFT OUTER JOIN으로 원하는 데이터를 조회하였으나, 비용이 너무 많이 든다.

[ 해결 ]

• 각 연도에 해당하는 acquisition_date 컬럼값을 1로 바꾸고 해당하지 않는 컬럼값은 0으로 바꾸어 SUM을 구한다.
• 새로운 테이블을 생성하지 않고 CASE WHEN ~ THEN 구문만으로 해결이 가능하다.

[ 회고 ]

• WITH ~ AS 구문은 간편하나 비용이 많이든다. (연산 + 코딩의 복잡도)
• WITH ~ AS 구문을 사용하지 않고 다른 구문에 집계함수를 융합 활용하면 비용을 절감할 수 있다.