데이터리안 - SQL 데이터 분석캠프 실전반- 1주차: 서브쿼리 심화

본 내용은 데이터리안 'SQL 데이터 분석 캠프 실전반'을 수강하며 작성한 내용입니다.

문제

solvesql / 데이터셋: 서울숲 일별 평균 대기오염도

Q. measurements 테이블은 서울숲의 일별 대기오염도 정보를 담고 있습니다.
일 별로 pm10의 이동 평균을 계산해 주세요. 이동 평균은 당일, 전날, 다음날의 pm10을 이용해 계산해 주세요.
데이터가 시작하는 2022년 1월 1일에는 당일과 다음날의 pm10 수치 정보만 사용하면 됩니다.
결과는 아래와 같이 날짜순으로 정렬해 주세요.

풀이

SELECT measured_at
     , pm10
     , (SELECT AVG(m2.pm10) FROM measurements m2 WHERE m2.measured_at BETWEEN DATE_SUB(m1.measured_at, INTERVAL 1 DAY) AND DATE_ADD(m1.measured_at, INTERVAL 1 DAY)) AS pm10_running_avg
FROM measurements m1

배운점

• Correlated Subquery는 outer query에 있는 걸 inner query에서 참조를 한다.
• 날짜 데이터 하나로 앞뒤 3일치 평균은 다음과 같이 구현할 수 있다.

WHERE m2.measured_at BETWEEN DATE_SUB(m1.measured_at, INTERVAL 1 DAY) AND DATE_ADD(m1.measured_at, INTERVAL 1 DAY))

---

문제

solvesql / 데이터셋: 서울숲 일별 평균 대기오염도

Q. pm10의 누적합을 계산해 주세요. 누적합은 당일의 pm10 수치와 이전 날짜의 pm10 수치를 모두 합산하는 방식으로 계산해 주세요.
결과는 아래와 같이 날짜순으로 정렬해 주세요.

풀이

SELECT measured_at
     , pm10
     , (SELECT SUM(m2.pm10) FROM measurements m2 WHERE m2.measured_at <= m1.measured_at) AS pm10_running_avg
FROM measurements m1
ORDER BY measured_at

배운점

• Correlated Subquery는 m1에 있는 샘플데이터 하나를 들고, m2를 가지고 오려면 어떻게 해야할지 생각하고 로직짜면 된다.
• 누적합은 다음처럼 구현

WHERE m2.measured_at <= m1.measured_at)

---

문제

solvesql / 고액 영수증 찾기

풀이

SELECT *
FROM tips
WHERE (size, total_bill) IN (
                             SELECT size
                                  , MAX(total_bill)
                             FROM tips
                             GROUP BY size
                            )
ORDER BY size

배운점

• 다중컬럼 서브쿼리의 문법:

SELECT *
FROM tips
WHERE (day, total_bill) IN (('Thur', 43.11), ('Fri', 40.17))