SELECT절 Correlated Subquery

# 1

문제

 

일 별로 pm10의 이동 평균을 계산하여라.

이동 평균은 당일, 전날, 다음날의 pm10을 이용해 계산할 것.

단, 데이터가 시작하는 2022년 1월 1일에는 당일과 다음날의 pm10 수치 정보만 사용할 것.

 

Solution

✅ 윈도우 함수 사용하는 방법

SELECT measured_at
     , pm10
     , AVG(pm10) OVER(ORDER BY measured_at ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS pm10_running_average
FROM measurements

 

Advent of SQL 2024 챌린지의 [DAY 14] 문제에서 이동평균을 SQL로 구현하는 방법을 공부했기 때문에 쉽게 해결하였다.

https://hwangbyeongho.tistory.com/29

[Day 14] solvesql | Advent of SQL 2024 | 전력 소비량 이동 평균 구하기

 

✅ SELECT 상관 서브쿼리 사용하는 방법

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_average
FROM measurements m1

 

종종 윈도우 함수를 사용하지 않고 해결을 요하는 코딩 테스트도 있다고 한다.

핵심은 SELF-JOIN 방법과 같이 동일한 테이블을 별도의 2개 테이블로 간주하여 접근하는 것이다.

 

m1

measured_at	pm10
2022-01-02	39

 

m2

measured_at	pm10
2022-01-01	31
2022-01-02	39
2022-01-03	28

 

---

# 2

문제

 

pm10의 누적합을 계산하여라.

누적합은 당일의 pm10 수치와 이전 날짜의 pm10 수치를 모두 합산하는 방식으로 계산할 것.

 

Solution

✅ 윈도우 함수 사용하는 방법

SELECT measured_at
     , pm10
     , SUM(pm10) OVER(ORDER BY measured_at) AS pm10_running_total
FROM measurements
ORDER BY measured_at

 

Advent of SQL 2024 챌린지의 [DAY 23] 문제에서 누적합을 SQL로 구현하는 방법을 공부했기 때문에 쉽게 해결하였다.

https://hwangbyeongho.tistory.com/42

[Day 23] solvesql | Advent of SQL 2024 | 유량 지표와 저량 지표

 

✅ SELECT 상관 서브쿼리 사용하는 방법

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

 

m1

measured_at	pm10
2022-01-03	28

 

m2

measured_at	pm10
2022-01-01	31
2022-01-02	39
2022-01-03	28

 

 

💡DATE_ADD/SUB() 관련 자료
https://dev.mysql.com/doc/refman/9.1/en/date-and-time-functions.html#function_date-add