[SQL] 누적합 구하기(Window 함수, 비등가 Join)

데이터 분석을 하다보면 누적합을 구하고 누적합이 어떤 수준을 초과할 때 상태를 구하는 경우가 종종 있다. 이때 사용할 수 있는 SQL 구문을 소개한다.

• 예제(leetcode 1204. Last Person to Fit in the Bus)

  • 버스를 기다리는 승객에 대한 데이터가 아래와 같이 존재한다고 하자. 여기서 weight는 각 승객의 몸무게, turn은 탑승 순서를 의미한다. 버스의 수송 무게 한도가 1000이라고 할 때, 버스에 탑승할 수 있는 마지막 승객은 누구일까?

    person_id	person_name	weight	turn
    5	Alice	250	1
    4	Bob	175	5
    3	Alex	350	2
    6	John Cena	400	3
    1	Winston	500	6
    2	Marie	200	4

• 착안 사항: turn을 기준으로 몸무게의 누적합을 구한 후 누적합이 1000을 초과하기 전의 승객을 추출한다.

• Window 함수 이용방법

  • weight를 turn 순서대로 합쳐주는 cum_sum 변수를 생성하고 cum_sum이 1000 이하인 데이터의 가장 마지막 데이터 하나(LIMIT 1)를 추출하는 방법이다.

    WITH CUM AS
    (
        SELECT *
         , SUM(weight) OVER (ORDER BY TURN) cum_sum
        FROM queue
    )
    SELECT person_name
        FROM CUM
        WHERE cum_sum <= 1000
        ORDER BY cum_sum DESC
        LIMIT 1

• 비등가 JOIN을 이용하는 방법

  • SELF JOIN을 이용하되, 첫 번째 테이블의 순서보다 이전인 순서의 자료를 기준으로 JOIN한다. 이러면 각 순서별로 이전인 순서의 자료가 반복해서 붙게 된다. 여기서 GROUP BY로 turn 별로 weight의 합계를 구해주면 누적합한 결과가 나오게 된다.

    SELECT q1.turn, sum(q2.weight)
        FROM Queue q1 JOIN Queue q2 ON q1.turn >= q2.turn
        GROUP BY q1.turn
        HAVING SUM(q2.weight) <= 1000
        ORDER BY SUM(q2.weight) DESC
        LIMIT 1

• 실행 결과

  • 누적합을 하게 되면 아래와 같은 테이블이 생성되고

    person_id	person_name	weight	turn	cum_sum
    5	Alice	250	1	250
    3	Alex	350	2	600
    6	John Cena	400	3	1000
    2	Marie	200	4	1200
    4	Bob	175	5	1375
    1	Winston	500	6	1875

  • cum_sum이 1000 이하인 마지막 승객명은 아래와 같이 조회된다.

    person_id	person_name	weight	turn	cum_sum
    5	Alice	250	1	250

• 두 방법을 비교해 보면, 직관적으로 이해하기에는 Window 함수가 비등가 JOIN보다 유리해보인다. 반면, 비등가 JOIN은 코드가 압축적으로 짜여있어 window 함수보다 조금 더 효율적으로 프로그래밍을 하는 데 유리할 것으로 보인다.