[오늘의 코드 148] [SOLVESQL] 3년간 들어온 소장품 집계하기

 

[문제]

https://solvesql.com/problems/summary-of-artworks-in-3-years/

 

https://solvesql.com/problems/summary-of-artworks-in-3-years/

소장품 분류에 대해 연도별 소장품 수를 집계하는 문제이다.

분류로 오름차순 정렬하고, 3년간 하나도 추가되지 않은 경우에도 추출한다.

핵심은 3년간 추가되지 않아서 집계되지 않은 분류를 어떻게 출력할 것인가가 될 수 있겠다.

[💡 정답]

WITH
  years AS (
    SELECT
      '2014' AS year
    UNION ALL
    SELECT
      '2015'
    UNION ALL
    SELECT
      '2016'
  ),
  classifications AS (
    SELECT DISTINCT
      classification
    FROM
      artworks
  ),
  classification_years AS (
    SELECT
      c.classification,
      y.year
    FROM
      classifications c
      CROSS JOIN years y
  ),
  artwork_counts AS (
    SELECT
      classification,
      strftime ('%Y', acquisition_date) AS year,
      COUNT(*) AS count
    FROM
      artworks
    WHERE
      strftime ('%Y', acquisition_date) BETWEEN '2014' AND '2016'
    GROUP BY
      classification,
      year
  )
SELECT
  cy.classification,
  cy.year,
  COALESCE(ac.count, 0) AS count
FROM
  classification_years cy
  LEFT JOIN artwork_counts ac ON cy.classification = ac.classification
  AND cy.year = ac.year
ORDER BY
  cy.classification,
  cy.year;

[💡 풀이 과정]

1) 분류별, 연도별 소장품 수를 집계한다.

SELECT
  classification,
  strftime('%Y', acquisition_date) AS year,
  COUNT(*) AS count
FROM artworks
WHERE strftime('%Y', acquisition_date) BETWEEN '2014' AND '2016'
GROUP BY classification, year;

 

2) 모든 분류와, 연도 조합을 포함하여 누락된 경우에도 출력해주기 위해 분류, 연도 조합을 생성한다.

SELECT 
    c.classification,    
    y.year              
  FROM 
    (SELECT DISTINCT classification FROM artworks) c
  CROSS JOIN 
    (VALUES ('2014'), ('2015'), ('2016')) y(year)

그러면 print 2016/ print 2017 / painting 2017/ painting  2018 이런식으로 출력이 된다.

 

3) 모든 분류, 연도 조합에 대해 1)에서 집계한 소장품 수 데이터를 결합한다.

SELECT 
  cy.classification,                 
  cy.year,                           
  COALESCE(ac.count, 0) AS count     #개수 카운트, 없으면 0
FROM 
  classification_years cy            #모든 조합에 대해
LEFT JOIN 
  artwork_counts ac                 #집계한 소장품 수 결합
ON 
  cy.classification = ac.classification 
  AND cy.year = ac.year              #분류, 연도 키
ORDER BY 
  cy.classification, cy.year;