MySQL Correlated Subquery 상관 서브쿼리 개념 정리 및 활용(이동 평균,누적 합 계산)

 
 

상관 서브쿼리 개념 정리 및 활용(이동평균과 누적 합 계산)

 
 
 

MySQL Correlated Subquery 상관 서브쿼리

 
상관 서브쿼리는 영어 이름 그대로, 쿼리들이 서로 연관성이 있다는 의미이다.
내부 서브쿼리에서 외부 쿼리(Outer Query)의 컬럼을 참조하는 등, 내부 서브쿼리와 외부 쿼리 간 연관성이 있는 경우다.
"연관성이 있다"는 "의존성이 있다"라는 말인데, 상관 서브쿼리는 내부 서브쿼리가 외부 쿼리와 무관하게 독립적으로 실행할 수 없다는 의미이다.
왜냐하면 외부 쿼리 선 실행 후,  외부 쿼리(메인 쿼리)의 행마다 상관 서브쿼리를 반복 실행하기 때문이다.
 
이는 같은 테이블처럼 보이나, 별칭(Alias)을 다르게 붙이고 서로 다른 테이블로 여기며 자신을 참조하는 셀프 조인과 SELECT, WHERE절 서브쿼리에서 보인다.
 
아래는 WHERE절 상관 서브쿼리 예시이다.
customer_info 테이블은 같은 테이블이기는 하지만, 서로 다른 테이블로 여기며 별칭을 c1, c2로 사용했다.
그리고 메인 쿼리 WHERE절에 쓰인 서브 쿼리 WHERE절 조건 상 c1.salary를 참조하는데, 쿼리 작동 방식은 다음과 같다.
 
1. 외부 쿼리(메인 쿼리)인 c1이 선 실행된다.
2. 이 테이블의 행마다 c1.salary > c2.salary를 비교하는 서브쿼리가 작동하며 COUNT(*)가 실행된다.
3. c1 상위 10개 미만인 행들에서 name 컬럼의 데이터를 반환한다.
 
즉, 아래 서브쿼리는 내부 서브쿼리가 외부 쿼리(메인 쿼리)와 무관하게 독립적으로 실행될 수 없는 상관 서브쿼리이다.

SELECT c1.name
FROM customer_info AS c1
WHERE 10 > (                               # 급여 상위 10명을 제외한 직원들의 이름 반환
            SELECT COUNT(*)
            FROM customer_info AS c2
            WHERE c1.salary > c2.salary	 # 급여 비교 시, 상대적으로 높은 급여를 받는 직원 데이터 수 카운트
);

 
 

---

 
 

상관 서브쿼리와 이동 평균(MA) 계산

 
이동 평균(Moving Average, MA)은 전체에서 부분 집합을 연속적으로 이동시키며 평균값을 계산하는 방법이다.
이는 데이터 상 이상치같은 잡음의 영향을 줄여 부드러운 값을 도출한다. 다시 말해, 단기적으로 튀는 변화를 잡아내어 전반적인 트렌드를 확인한다는 말이다.
따라서 이동 평균은 특정 기간 동안(시계열 분석) 발생하는 평균값 변화를 통해 트렌드를 확인할 때 사용한다.
 
단순 이동평균(Simple Moving Average, SMA)은 이동 평균에서 가장 기본 형태로, 모든 값들에 동일 가중치를 부여해서 평균을 계산하는 방법이다.
 
아래는 2일 단순 이동평균 계산 방법이다. Price는 당일 가격, Price_SMA2는 2일 단순 이동평균값이다.
이때 2일은 시작부터 2개의 데이터를 포함한 데이터의 산술평균이 된다.
그러나 2024/01/01은 시작점이므로, 2개의 데이터를 포함할 수 없기에 NaN이다.
따라서 2일 단순 이동평균 계산은 2024/01/02부터 가능하다.
 

Order_Date	Price	Price_SMA2
2024/01/01	5300	NaN
2024/01/02	6000	5650 ((5300+6000) / 2)
2024/01/03	4700	5350 ((6000+4700) / 2)
2024/01/04	5000	4850 ((4700+5000) / 2)
2024/01/05	5500	5250 ((5000+5500) / 2)
2024/01/06	4800	5150 ((5500+4800) / 2)
2024/01/07	5100	4950 ((4800+5100) / 2)

* 2024/01/02 Price_SMA2는 (5300+6000) / 2 = 5650이다. 즉, (전날+당일) / 2이다.

 

SELECT p1.order_date
     , p1.price
     , CASE WHEN p1.order_date = '2024/01/01' THEN 'NaN'
            ELSE (p1.price
               + (SELECT p2.price
                  FROM price_info AS p2
                  WHERE p2.order_date = DATE_ADD(p1.order_date, INTERVAL 1 DAY))
                 ) / 2
            END AS Price_SMA2
FROM price_info AS p1
ORDER BY p1.order_date ASC;

 
 

---

 
 

상관 서브쿼리와 누적 합(CS) 계산

 
누적 합(Cumulative Sum, CS)은 이름 그대로 값을 누적해서 더하는 계산 방식이며, 아래는 일별 값의 누적 합 예시이다.
 

Order_Date	Price	Price_CS
2024/01/01	5300	5300
2024/01/02	6000	11300
2024/01/03	4700	16000
2024/01/04	5000	21000
2024/01/05	5500	26500
2024/01/06	4800	31300
2024/01/07	5100	36400

* 2024/01/03 Price_CS는 (5300+6000+4700) = 16000이다. 즉, 당일까지의 모든 가격을 더한 값이다.
 

SELECT order_date
     , price
     , (
         SELECT SUM(p2.price)
         FROM price_info p2
         WHERE p2.order_date <= p1.order_date
       ) AS Price_CS
FROM price_info p1