[SolveSQL] 다음날도 서울숲의 미세먼지 농도는 나쁨, with as 문

 

### 놓쳤던 부분

데이터를 만든 후에, 해당 데이터의 테이블을 새로운 테이블로 만든 후

where 문으로 조건을 설정해서 해주는 쉬운 방법이 있었다.

 

방법은 WITH table_name AS (select ~ from ~) 이런 식으로 진행해주면 된다.

 

쿼리를 작성할 때, 서브쿼리로 끝내는 것 말고, 쿼리를 분리해내는 방법이 가능한지 생각을 못했던 것 같다.

WITH AS 구문을 통해 복잡한 부분의 쿼리를 분리하거나,(가독성 +1)

반복적으로 쓰이는 서브 쿼리를 성능의 향상으로 생각할 수 있다는 장점이 있다.

 

WITH 이름 AS (
	SELECT ...
    FROM ...
    WHERE ...
)
SELECT
	....
FROM 이름
WHERE
	....

 

출처 : https://www.geeksforgeeks.org/sql-with-clause/

 

 

 

## 틀린 코드

SELECT
  measured_at as today,
  lead(measured_at, 1) over (
    order by
      measured_at
  ) as next_day,
  pm10,
  lead(pm10, 1) over (
    order by
      measured_at
  ) as next_pm10
FROM
  measurements
WHERE
  pm10 < (
    SELECT
      lead(pm10, 1) over (
        order by
          measured_at
      )
    from
      measurements
  )

 

위의 코드는 왜 틀렸을까? 그리고 왜 이상한 값을 어떤 로직으로 출력했을까

결과값은 아래와 같았다.

틀린 결과값

 

 

### 오답노트 자세히

 

잘못된 코드를 통해서 나온 결과값은

today-pm10, 그리고 next_day-next_pm10 끼리는 매칭이 되는 것 같았다.

그리고 next_day의 값들이 다음의 today의 값이 되는 것 같았다.

그러면

의문1] next_day를 지정하는 이유는 무엇일까?

 

은근히 시간을 쏟아본 결과, 서브쿼리문의 결과는 단지 "39"로만 나오는 것 같다.

그래서 무조건 39를 기준으로 작은지, 같은지, 큰지로 나눠지는 것 같았다.

 

그래서 SQL 실행 순서를 봤을때,

1. 외부 쿼리를 실행하기 전에 서브쿼리 먼저 실행

2. 서브 쿼리는 결과 집합을 반환

3. 외부 쿼리는 반환된 결과 집합을 사용하여 나머지 연산 수행

 

첫 날짜인 2022-01-01의 다음 next_pm10은 39이고, 모든 서브 쿼리 결과 집합은 39로 도배가 되었던 것 같다.

 

의문2] 그 문제는 lead 함수의 문제였을까?

아니다. 

where 내부의 select [ ]의 값을 수정하니 31로 도배가 되었다는 것을 확인할 수 있었다.

WHERE
  pm10 == (
    SELECT
      pm10 --lead(pm10, 1) over ()
    from
      measurements
  )

 

찾아보면, 서브쿼리 사용 패턴 중,

서브쿼리는 경우에 따라 다양한 형태로 작성하는데, 

위와 같이 사용할 경우의 서브쿼리 특징은 추출 결과가 반드시 1건이어야 한다고 한다.

만약에 서브쿼리 결과로 2건 이상이 추출된다면 에러가 발생한다고 해서, 위와 같은 패턴은 보통 서브쿼리부터 먼저 수행한 후, Main SQL의 컬럼 값과 비교하는 형태로 수행되는 것이 일반적이라고 한다.

 

아 대박! 

애초에 사용 패턴에 따라서 틀렸던 것이다.

그래서 다른 사용패턴인 EXISTS나 IN 연산자 등을 사용하면, 서브 쿼리의 결과가 여러 건으로 추출이 된다고 한다.

( 참조했던 블로그 : https://12bme.tistory.com/299)

 

 

 

## 정답 코드

WITH t1 AS(SELECT
  measured_at as today,
  lead(measured_at, 1) over (
    order by
      measured_at
  ) as next_day,
  pm10,
  lead(pm10, 1) over (
    order by
      measured_at
  ) as next_pm10
FROM
  measurements
)

SELECT today, next_day, pm10, next_pm10
FROM t1
WHERE pm10 < next_pm10

 

 

 

느낀점, 

아직 데이터를 보았을 때, 전체적인 데이터를 보는 방법과 비판적 사고가 부족한 듯 하다.

아마 1-2시간 정도 쓴 것 같은데, 이번 분석을 통해 다음에는 더 빠르게 진행할 수 있겠지 기대해본다.

 

 

 

참고 많이 한 블로그

- 선생님 아주 굿 https://ganghee-lee.tistory.com/17

- https://www.kaggle.com/learn/intro-to-sql

- https://12bme.tistory.com/299

- https://schatz37.tistory.com/3