[SQL문제풀기] 미세먼지 수치의 계절간 차이

문제

solvesql.com

https://solvesql.com/problems/finedust-seasonal-summary/

SQLite

내가 작성한 정답 1 - row_number()

with a as ( select case
	            when strftime ('%m', measured_at) in ('03', '04', '05') then 'spring'
	            when strftime ('%m', measured_at) in ('06', '07', '08') then 'summer'
	            when strftime ('%m', measured_at) in ('09', '10', '11') then 'autumn'
	            else 'winter' end     season,  pm10
					  from measurements ),
				   
     b as ( select season, count(*) cnt, round(avg(pm10),2) pm10_average
			      from a
			      group by season
			      order by pm10 )
			      
select b.season, c.pm10 pm10_median, b.pm10_average
from b b
join (select row_number() over(partition by a.season order by a.pm10) ranking,a.season,a.pm10 
      from b b join a a on b.season = a.season) c
on b.season = c.season and (cnt+1)/2 = ranking

ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY column_name)

// 아래의 쿼리는 각 부서(department_id) 내에서 직원(employee_id)에 대해 순번을 매긴다.
// 각 부서의 첫 번째 직원은 row_num이 1이 되고, 두 번째 직원은 2가 된다.
SELECT 
    employee_id,
    department_id,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_id) AS row_num
FROM 
    employees;

내가 작성한 정답 2 - PERCENT_RANK()

with a as ( select case
	            when strftime ('%m', measured_at) in ('03', '04', '05') then 'spring'
	            when strftime ('%m', measured_at) in ('06', '07', '08') then 'summer'
	            when strftime ('%m', measured_at) in ('09', '10', '11') then 'autumn'
	            else 'winter' end     season,  pm10
					  from measurements ),
				   
     b as ( select season, round(avg(pm10),2) pm10_average
			      from a
			      group by season
			      order by pm10 )
			      
select b.season, max(c.pm10) pm10_median, b.pm10_average
from b b
join (select percent_rank() over(partition by a.season order by a.pm10) ranking,a.season,a.pm10 
      from b b join a a on b.season = a.season) c
on b.season = c.season
where (ceiling(c.ranking*100))*0.01 = 0.5 
      or (ceiling(c.ranking*10))*0.1 = 0.5
group by b.season

PERCENT_RANK() OVER (PARTITION BY partition_expression ORDER BY order_expression)

다른 사람들이 작성한 정답

SELECT
  CASE
    WHEN substr(measured_at, 6, 2) IN ('03', '04', '05') THEN 'spring'
    WHEN substr(measured_at, 6, 2) IN ('06', '07', '08') THEN 'summer'
    WHEN substr(measured_at, 6, 2) IN ('09', '10', '11') THEN 'autumn'
    ELSE 'winter'
  END season,
  median(pm10) pm10_median,
  round(avg(pm10), 2) pm10_average
FROM measurements
GROUP BY 1;

Oracle

SELECT 
    season,
    ROUND(AVG(pm10), 2) AS pm10_average,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY pm10) AS pm10_median
FROM (
    SELECT 
        CASE 
            WHEN TO_CHAR(TO_DATE(date, 'YYYY-MM-DD'), 'MM') BETWEEN '03' AND '05' THEN 'spring'
            WHEN TO_CHAR(TO_DATE(date, 'YYYY-MM-DD'), 'MM') BETWEEN '06' AND '08' THEN 'summer'
            WHEN TO_CHAR(TO_DATE(date, 'YYYY-MM-DD'), 'MM') BETWEEN '09' AND '11' THEN 'autumn'
            ELSE 'winter'
        END AS season,
        pm10
    FROM measurements
)
GROUP BY season;