[Day 12] solvesql | Advent of SQL 2024 | 3년간 들어온 소장품 집계하기💡

3년간 들어온 소장품 집계하기

 

문제 조건

1. 각 분류에 대해 2014년부터 2016년까지 추가된 소장품 수 집계

2. 분류 컬럼 기준 오름차순 정렬

3. 집계하는 3년간 추가된 특정 분류의 소장품이 없더라도 해당 분류와 집계 내역을 결과 테이블에서 누락시키지 말 것 ⭐

 

Solution

 

✅SQLite

WITH T AS (
  SELECT classification,
         substr(acquisition_date, 1, 4) as acquisition_year
  FROM artworks
  -- WHERE acquisition_date BETWEEN '2014-01-01' AND '2016-12-31'
  )
  SELECT classification,
         sum(case when acquisition_year = '2014' then 1 else 0 end) as '2014',
         sum(case when acquisition_year = '2015' then 1 else 0 end) as '2015',
         sum(case when acquisition_year = '2016' then 1 else 0 end) as '2016'
  FROM T
  GROUP BY classification
  ORDER BY classification

 

✔️WITH 절에서 acquisition_date의 연도 부분만 추출한다.

✔️메인 쿼리에서는 SUM과 CASE문을 사용해 해당 연도에 대해 조건에 맞는 데이터를 집계한다.

 

 

✅MySQL 

SELECT classification
     , COUNT(CASE WHEN acquisition_date LIKE '2014%' THEN artwork_id END) AS '2014'
     , COUNT(CASE WHEN acquisition_date LIKE '2015%' THEN artwork_id END) AS '2015'
     , COUNT(CASE WHEN acquisition_date LIKE '2016%' THEN artwork_id END) AS '2016'
FROM artworks
GROUP BY classification
ORDER BY classification

/*
SELECT classification
     , COUNT(CASE WHEN YEAR(acquisition_date) = 2014 THEN artwork_id END) AS '2014'
     , COUNT(CASE WHEN YEAR(acquisition_date) = 2015 THEN artwork_id END) AS '2015'
     , COUNT(CASE WHEN YEAR(acquisition_date) = 2016 THEN artwork_id END) AS '2016'
FROM artworks
GROUP BY classification

SELECT classification
     , SUM(CASE WHEN acquisition_date BETWEEN '2014-01-01' AND '2014-12-31' THEN 1 ELSE 0 END) AS '2014' -- 👉숫자를 컬럼으로 할 때는 ''
     , SUM(CASE WHEN acquisition_date BETWEEN '2015-01-01' AND '2015-12-31' THEN 1 ELSE 0 END) AS '2015'
     , SUM(CASE WHEN acquisition_date BETWEEN '2016-01-01' AND '2016-12-31' THEN 1 ELSE 0 END) AS '2016'
FROM artworks
GROUP BY classification
*/

 

피드백  

이 문제는 3번째 조건을 놓쳐서는 안 된다. 

3년간 추가된 특정 분류의 소장품이 없더라도 포함시켜야 하기 때문에, WITH 절에서 2014~2016년의 데이터를 필터링하는 조건을 제외한다.

 

✔️ 0210 0820 복습