SQL 정리(하나의 쿼리로 요구하는 모든 값들을 출력할 수 없을때 - WITH, VIEW, IF)(24.01.17)

조건이 다른 결과값을 출력해야하는 경우가 있다.

예를 들어 대구에 사는 사람 전체의 수와 그 사람들 중 특정 조건에 해당하는 사람들의 수를 동시에 확인해야하는 경우가 그것이다.

아래 예제를 한번 살펴보자

https://leetcode.com/problems/monthly-transactions-i/submissions/1148357072/

LeetCode - The World's Leading Online Programming Learning Platform

예제에서는

월별, 국가별 총 transaction의 수, transaction amount와

그 중에서도 승인된 transaction의 수, transaction amount를

각각 구하라고한다.

 

이 경우 하나의 본쿼리만으로는 요구하는 값들을 모두 출력할 수 없다고 판단했기때문에

with절을 사용해야겠다고 생각했다.

 

본 쿼리에서 서브쿼리를 사용하면 되지 않냐라고 할 수 있는데
서브 쿼리는 하나의 값만을(하나의 로우&컬럼) 출력할 수 있기때문에
GROUP별로 값을 출력해야하는 금번 예제와 같은 경우에서는 서브쿼리를 사용할 수 없다.
물론 FROM절에서는 서브쿼리를 사용해서 여러 값을 출력할 수는 있지만 이는 어디까지나 FROM절의 서브쿼리 안에 있는 값들만을 가지고 올 수 있기 때문에 조건이 다른 두 개 이상의 값을 구해야하는 상황에서는 이 역시도 방법이 될 수 없다.

 

다시 쿼리문 작성으로 돌아와서

본 쿼리에서는 월별, 국가별 총 transaction의 수, transaction amount를 구하고,

승인된 transaction의 수, transaction amount는 with절을 사용해서 구해준다.

그렇게 했을때의 쿼리문은 아래와 같다.

WITH APPROVED_TABLE AS (
    SELECT DATE_FORMAT(TRANS_DATE, '%Y-%m'),
           country,
           COUNT(*) APPROVED_COUNT,
           SUM(AMOUNT) APPROVED_TOTAL_AMOUNT,
           id
    FROM TRANSACTIONS
    WHERE STATE = 'APPROVED'
    GROUP BY 1, 2
)

SELECT DATE_FORMAT(TR.TRANS_DATE, '%Y-%m') month,
       TR.country,
       COUNT(*) trans_count,
       if(AT.APPROVED_COUNT is not null, at.approved_count, 0) approved_count,
       SUM(TR.AMOUNT) trans_total_amount,
       if(AT.APPROVED_TOTAL_AMOUNT is not null, at.approved_total_amount, 0) approved_total_amount
FROM TRANSACTIONS TR LEFT JOIN APPROVED_TABLE AT ON TR.ID = AT.ID
GROUP BY MONTH, TR.COUNTRY

 

그런데 이렇게 쿼리를 작성할 경우 아래와 같은 문제가 생긴다.

 국가 LL의 값이 0으로 출력된다.

무엇이 문제일까?

문제에서는 'ID' 컬럼은 출력하지 마라고 했는데

'ID'컬럼을 출력해보면 WITH절에서의 ID와 본 쿼리에서의 ID가 같은 경우도 있고,

다른 경우도 있었다. 다른 경우는 WITH절의 속성값들이 NULL로 출력된다.

왜 WITH절의 ID와 본 쿼리의 ID가 다른걸까?

우리는 MONTH로 그룹화를 하고 있다.

그런데 같은 年, 月일지라도 日은 다를수가 있다.

각각의 ID도 다르다.

각각의 ID를 가지고 있는 인스턴스(=RAW)들을 GROUP지을때

어떤 ID가 출력될 지는 랜덤일 것으로 추정된다.

그렇다보니 어떤 ID는 WITH절과 본 쿼리의 그것이 동일하고,

어떤 ID는 동일하지 않게 되는 것이다.

 

그래서 다른 방법을 찾아보던 중 아래와 같이 쿼리를 작성하면 문제를 해결할 수 있다는 것을 알게됐다.

SELECT  DATE_FORMAT(trans_date, '%Y-%m') AS month, country, 
        COUNT(id) AS trans_count, 
        SUM(IF(state = 'approved',1,0)) AS approved_count,
        SUM(amount) AS trans_total_amount,
        SUM(IF(state = 'approved',amount,0)) AS approved_total_amount
FROM  Transactions
GROUP BY month,country

state가 approved이면 1을, 그렇지 않으면 0을 출력하게끔해서
그 값들을 sum해서 total_count를 구하고,
state가 approved이면 amount값을, 그렇지 않으면 0을 출력하게끔해서
그 값들을 sum한 후 total_amount를 구하는 방식으로 쿼리를 작성해야한다.
이렇게 할 경우 country의 state가 뭐가 나오건 상관없이
month, country별 total_count와 total_amount를 구할 수 있다.

 

if를 활용하여 문제를 해결한 적이 거의 없는데

if 사용해서 문제를 풀어보는 연습을 많이 해봐야겠다.