오늘의 학습

solvesql

전국 카페 주소 데이터 정제하기

SELECT SUBSTRING_INDEX(address, ' ', 1) AS sido,
       SUBSTRING_INDEX(SUBSTRING_INDEX(address, ' ', 2), ' ', -1) AS sigungu,
       COUNT(*) AS cnt
FROM cafes
GROUP BY sido, sigungu
ORDER BY cnt DESC;

어려운 문제는 아니었지만 SUBSTRING_INDEX를 알면 풀고, 모르면 못 푸는 문제. 난 몰랐기 때문에 못 풀었다.

미세먼지 수치의 계절간 차이

WITH season_data AS (
     SELECT CASE WHEN measured_at BETWEEN '2022-03-01' AND '2022-05-31' THEN 'spring'
                 WHEN measured_at BETWEEN '2022-06-01' AND '2022-08-31' THEN 'summer'
                 WHEN measured_at BETWEEN '2022-09-01' AND '2022-11-30' THEN 'autumn'
                 ELSE 'winter' END AS season,
            pm10
     FROM measurements
),
     ranked AS (
     SELECT season,
            pm10,
            ROW_NUMBER() OVER (PARTITION BY season ORDER BY pm10) AS rn,
            COUNT(*) OVER (PARTITION BY season) AS cnt
     FROM season_data
)
SELECT season,
       AVG(CASE WHEN rn IN (FLOOR((cnt + 1) / 2), CEIL((cnt + 1) / 2)) THEN pm10
                END) AS pm10_median,
       ROUND(AVG(pm10), 2) AS pm10_average
FROM ranked
GROUP BY season;

중앙값 구하는게 핵심이었던 이 문제..
각 계절마다 ROW_NUMBER()로 순위를 매기고, COUNT로 갯수를 세고, 가운데에 위치한 값을 골라야한다. 갯수가 짝수인 경우 가운데 두개의 값을 더하고 평균, 홀수인 경우 가운데 값이 중앙값임을 이용해야한다.

PERCENTILE_CONT를 지원하는 버젼인 경우

SELECT CASE WHEN measured_at BETWEEN '2022-03-01' AND '2022-05-31' THEN 'spring' 
            WHEN measured_at BETWEEN '2022-06-01' AND '2022-08-31' THEN 'summer' 
            WHEN measured_at BETWEEN '2022-09-01' AND '2022-11-30' THEN 'autumn' 
            ELSE 'winter' END AS season, 
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY pm10) AS pm10_median,
       ROUND(AVG(pm10),2) AS pm10_average 
FROM measurements 
GROUP BY CASE WHEN measured_at BETWEEN '2022-03-01' AND '2022-05-31' THEN 'spring' 
              WHEN measured_at BETWEEN '2022-06-01' AND '2022-08-31' THEN 'summer' 
              WHEN measured_at BETWEEN '2022-09-01' AND '2022-11-30' THEN 'autumn' 
              ELSE 'winter' END

그냥 한줄 딸깍하면 끝인데 안타깝게도 solvesql에서는 PERCENTILE_CONT를 지원하지 않는다.

피드백 or 느낀점

오늘도 역시 하루의 시작을 코드카타 10문제로 했다. 아직은 크게 어려움없이 10문제 무난무난하게 풀고 뇌를 깨우는 중

라이브 세션 파이썬 - 파이썬은 SQLD 시험이 끝나고 빡세게 빡집중해서 복습해야될거같다. 지금은 말그대로 그냥 듣기만 하는중...

solvesql은 어렵긴한데 꾸준히 해보자

내일 학습 할 것은 무엇인지

SQLD 기출복습
solvesql 최소 1문제 꾸준맨
파이썬 복습, 코딩 문제 레츠고

#내일배움캠프 #TIL #데이터분석 #실무형데이터분석가양성과정