DAY 4 / [solvesql] Advent of SQL 2024 3문제 (최대값을 가진 행 찾기, 서울숲 요일별 대기오염도 계산하기, 3년간 들어온 소장품 집계하기)

최대값을 가진 행 찾기

SELECT id
FROM points
WHERE x = (SELECT MAX(x) FROM points)
   OR y = (SELECT MAX(y) FROM points)
ORDER BY id

 

해결 방법

1. 조건절에 최대값을 작성할 때, 서브쿼리를 이용한다.

 

 

 

서울숲 요일별 대기오염도 계산하기

SELECT CASE weekday_num
         WHEN 0 THEN "월요일"
         WHEN 1 THEN "화요일"
         WHEN 2 THEN "수요일"
         WHEN 3 THEN "목요일"
         WHEN 4 THEN "금요일"
         WHEN 5 THEN "토요일"
         WHEN 6 THEN "일요일"
       END AS "weekday"
     , no2, o3, co, so2, pm10, pm2_5
FROM (
  SELECT WEEKDAY(measured_at) AS weekday_num
      , ROUND(AVG(no2) , 4) AS no2
      , ROUND(AVG(o3) , 4) AS o3
      , ROUND(AVG(co) , 4) AS co
      , ROUND(AVG(so2) , 4) AS so2
      , ROUND(AVG(pm10) , 4) AS pm10
      , ROUND(AVG(pm2_5) , 4) AS pm2_5
  FROM measurements
  GROUP BY WEEKDAY(measured_at)
  ORDER BY WEEKDAY(measured_at)
  ) AS tmp

 

쿼리 작성 전, 먼저 생각해보기

1. 측정 일시를 요일 넘버로 변환하여 그룹핑 (MySQL 두가지가 있음 WEEKDAY, DAYOFWEEK)
2. 그룹핑한 결과에서 요일 넘버를 한글명으로 변환

 

 

 

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

SELECT classification
     , COUNT(DISTINCT IF(YEAR(acquisition_date) = 2014, artwork_id, NULL)) AS "2014"
     , COUNT(DISTINCT IF(YEAR(acquisition_date) = 2015, artwork_id, NULL)) AS "2015"
     , COUNT(DISTINCT IF(YEAR(acquisition_date) = 2016, artwork_id, NULL)) AS "2016"
FROM artworks
GROUP BY classification
ORDER BY classification

 

쿼리 작성 전, 먼저 생각해보기

1. classification 별로 그룹핑하여 소장품 수 집계하는 테이블을 생성하는데,
2. IF/CASE문 활용하여 연도별로 컬럼 나눠서 카운트해야 함.