SQL 기타 - 주의할 점

SELECT *
FROM tips
WHERE (day, total_bill) in (SELECT day
                                 , MAX(total_bill)
                            FROM tips
                            GROUP BY day)

1. 날짜 포멧에 BETWEEN을 활용할 경우

• 포멧이 시, 분, 초까지 표현하는 데이터라면 년,월,일로만 작성할 경우에 마지막 날짜 정보가 포함되지 않고 출력된다.
• 따라서, 시, 분, 초까지로 범위를 모두 써주어야 한다.

 

• 예시

-- 틀린 예시 (모든 범위가 포함되지 않음)
WHERE order_delivered_carrier_date BETWEEN '2017-01-01' AND '2017-01-31'

-- 올바른 예시
WHERE order_delivered_carrier_date BETWEEN '2017-01-01 00:00:00' AND '2017-01-31 23:59:59'

 

2. 다중컬럼 subquery

 

where절에서 다중 컬럼을 가진 subquery를 ()를 활용하여 사용할 수 있다.

SELECT *
FROM tips
WHERE (day, total_bill) in (SELECT day
                                 , MAX(total_bill)
                            FROM tips
                            GROUP BY day)

 

3. 요일, 시간별 데이터에서 GROUP BY를 통해 평균을 구할 경우

 

평균을 구하는 방법에는 AVG와 직접 공식(sum() / n) 써서 구하는 방법이 있는데, 상황에 따라 선택하여 써야한다.

예를 들어, 일주일 간 평균을 구할 때 월, 수, 금에만 매출이 난 경우 AVG를 쓴다면, 분모가 7이 아닌 3이 되어버린다.

따라서 이럴 경우에는 직접 공식을 통해서 구하여야 한다.

 

4. correlated subquery

Window 함수를 활용하기 어려운 경우에 SELECT 절에 subquery를 활용하여 누적합과 같은 문제를 해결할 수 있는 기술이다.

종종 버젼이 낮은 환경에서 업무를 하거나 Window함수를 활용하지 않고 문제를 풀어야하는 경우가 있으니 알고 있어야 한다.

마치 self_join과 같이 자기 자신을 SELECT에서도 활용하게 되는데, 각각의 테이블간 관계를 생각하면서 설계를 한 후 별칭을 두어 코드를 쓰면 된다.

 

-- 누적합
-- m1보다 이전에 미세먼지를 측정한 m2의 pm10의 합을 각 행에 나타낸다.

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

-- m1 당일의 전날부터 다음날까지의 m2의 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))
FROM measurements m1