서술형문제 1번 (누적매출 구하는 SQL)

문제

아래 좌측과 같은 월별지점매출 테이블을 읽어서 우측과 같은 형태, 즉 각 지점별로 판매월과 함께 증가하는 누적매출(running total)을 구하는 SQL을 두가지 방식으로 작성하라

 

1) 윈도우 함수를 이용한 방식

2) 윈도우 함수나 스칼라 서브쿼리를 지원하지 않는 DBMS에서 활용할 수 있는 방식으로 작성(단, 전체범위처리에 최적화된 방식으로 작성할 것)

 

 

WITH 월별지점매출 AS
(
    SELECT '10' as 지점, '1' as 판매월, '521' as 매출 FROM dual
    UNION ALL SELECT '10', '2','684' FROM dual
    UNION ALL SELECT '10', '3','590' FROM dual
    UNION ALL SELECT '20', '1','537' FROM dual
    UNION ALL SELECT '20', '2','650' FROM dual
    UNION ALL SELECT '20', '3','500' FROM dual
    UNION ALL SELECT '20', '4','919' FROM dual
    UNION ALL SELECT '20', '5','658' FROM dual
    UNION ALL SELECT '30', '1','631' FROM dual
    UNION ALL SELECT '30', '2','736' FROM dual
    UNION ALL SELECT '30', '3','513' FROM dual
    UNION ALL SELECT '30', '4','970' FROM dual
    UNION ALL SELECT '30', '5','939' FROM dual
    UNION ALL SELECT '30', '6','666' FROM dual
)
<아래에 정답을 작성하면 됩니다>

---------------------------------------------------------------------------------------------------------------------

 

 

 

정답

1번

select 지점,판매월,매출,SUM(매출) over(partition by 지점 order by 판매월) as 누적매출 from 월별지점매출

 

2번

select a.지점,a.판매월,a.매출 as 매출 ,sum(b.매출) as 누적매출
from 월별지점매출 a,월별지점매출 b
where a.지점 = b.지점
and a.판매월 >= b.판매월
group by a.지점,a.판매월,a.매출
order by a.지점,a.판매월

 

또는

 

select a.지점,a.판매월,min(a.매출) as 매출 ,sum(b.매출) as 누적매출
from 월별지점매출 a,월별지점매출 b
where a.지점 = b.지점
and a.판매월 >= b.판매월
group by a.지점,a.판매월
order by a.지점,a.판매월

풀이

 

문제 해결 Point

1) 분석함수,집계함수에 대해서 이해하고 있는지

2) 윈도우 함수를 쓰지 않고 쿼리로 같은 결과를 낼 수 있는지

가 제가 생각했을 때 문제 해결의 Key Point라고 생각합니다.

 

1번 풀이

우선 분석함수와 집계함수가 있는데요, SUM,AVG,MIN,MAX 등 기본적으로 알고 있는 함수에 OVER()라는 분석함수에 대한 이해도 필요할 내용이라고 생각합니다.

OVER()만 사용시 전체 값에 대한 함수계산을 하여 뿌려줍니다.

아래의 쿼리를 보시면 누적매출 컬럼에 OVER()만 사용 시 전체의 매출의 합을 표시해주는 것을 확인할 수 있습니다.

select 지점,판매월,매출,SUM(매출) over() as 누적매출 from 월별지점매출


지점 판 매출 누적매출   
-- - --- -----------
10 1 521        9514
10 2 684        9514
10 3 590        9514
20 1 537        9514
20 2 650        9514
20 3 500        9514
20 4 919        9514
20 5 658        9514
30 1 631        9514
30 2 736        9514
30 3 513        9514
30 4 970        9514
30 5 939        9514
30 6 666        9514

14 rows selected.

OVER(partition by 컬럼명)을 사용해서 그룹화하여 함수계산을 할수도 있는데요, 문제에서 필요한 것이 이 옵션입니다.

결과를 보면 지점별로 나눠서 계산이 되고 있기 때문에 지점별로 파티션화하여 계산을 해야합니다.

 

select 지점,판매월,매출,SUM(매출) over(partition by 지점) as 누적매출 from 월별지점매출

지점 판 매출 누적매출   
-- - --- -----------
10 3 590        1795
10 2 684        1795
10 1 521        1795
20 3 500        3264
20 2 650        3264
20 1 537        3264
20 4 919        3264
20 5 658        3264
30 1 631        4455
30 2 736        4455
30 3 513        4455
30 4 970        4455
30 5 939        4455
30 6 666        4455

하지만 결과값에서 보여주는 것과 다르게 지점별 총합을 누적매출로 표시해주고 있습니다. 정답결과는 판매월에 따라 누적되서 합계로 보여지는데 말이죠.

그래서 OVER(partiton by 지점)에 하나의 옵션을 더 사용해야 합니다. 바로 order by 컬럼명 옵션입니다.

order by 옵션은 컬럼명으로 정렬을 한 후 합을 보여줍니다.

아래는 OVER(order by 판매월)의 결과입니다.

select 지점,판매월,매출,SUM(매출) over(order by 판매월) as 누적매출 from 월별지점매출

지점 판 매출 누적매출   
-- - --- -----------
20 1 537        1689
10 1 521        1689
30 1 631        1689
10 2 684        3759
20 2 650        3759
30 2 736        3759
30 3 513        5362
20 3 500        5362
10 3 590        5362
20 4 919        7251
30 4 970        7251
30 5 939        8848
20 5 658        8848
30 6 666        9514

14 rows selected.

 

따라서 두개의 옵션을 같이 사용해서 지점별로 파티션을 나누고 거기서 판매월 별로 정렬을 해서 합을 구하는 것이 1번의 정답이 됩니다.

select 지점,판매월,매출,SUM(매출) over(partition by 지점 order by 판매월) as 누적매출 from 월별지점매출

지점 판 매출 누적매출   
-- - --- -----------
10 1 521         521
10 2 684        1205
10 3 590        1795
20 1 537         537
20 2 650        1187
20 3 500        1687
20 4 919        2606
20 5 658        3264
30 1 631         631
30 2 736        1367
30 3 513        1880
30 4 970        2850
30 5 939        3789
30 6 666        4455

14 rows selected.

 

 

2번풀이

2번은 위의 함수를 사용하지 않고 똑같은 결과를 나오게 쿼리를 짜는 것입니다.

제가 생각했을 때의 핵심은 같은 테이블을 Join해서 어떤 값을 선택적으로 뽑아올것이냐, 그렇게 되려면 조건절을 어떻게 만들어야 될것이냐가 제일 중요한 Point인것 같습니다.

 

먼저 같은 테이블을 같은 지점을 조건절로 한 테이블의 결과값을 확인해보겠습니다.

 

 

select a.지점,a.판매월,a.매출,b.매출
from 월별지점매출 a,월별지점매출 b
where a.지점 = b.지점
order by a.지점,a.판매월;

지점 판 매출 매출
-- - --- ---
10 1 521 521
10 1 521 590
10 1 521 684
10 2 684 590
10 2 684 521
10 2 684 684
...생략

쿼리로 나온 값들을 살펴보면 a의 지점,판매월,매출 값을 기준으로 각 b의 매출값이 보이는 것을 확인할 수 있습니다.

그렇다면 예를들어 지점 10 판매월 1 매출액 684인 컬럼의 누적매출은 어떻게 구하는게 좋을까요?

a 매출과 b매출을 비교해서 a.매출 >= b.매출 인 값의 b.매출의 합을 구하면 됩니다.

a 매출이 684인 값과 b의 매출이 684보다 작은 값들(521 590 684)의 합을 구하면 누적매출이 나오게 됩니다.

같은 원리로 지점 10 판매월 1 매출액 521인 값의 누적매출을 구해보면 a 매출이 521인 값과 같거나 작은 값 (521)이 나오게 되고 그것의 합은 누적합계가 되게 됩니다.

 

 

select a.지점,a.판매월,a.매출,b.매출
from 월별지점매출 a,월별지점매출 b
where a.지점 = b.지점
and a.판매월 >= b.판매월
order by a.지점,a.판매월

 

지점 판 매출 매출
-- - --- ---
10 1 521 521
10 2 684 521
10 2 684 684
10 3 590 684
10 3 590 590
10 3 590 521

보면 지점,판매월,매출이 같은 값들이고 b 매출만 다른 값들로 정리가 되었습니다.

각 값들을 그룹화하여 한번만 보여준다면 원하는 결과값을 얻을 수 있습니다.

 

a매출에 min함수를 사용한 이유는 group by 에 포함되지 않게 하려면 집계함수를 사용해야 하기 때문입니다.

집계함수를 사용하지 않으려면 group by 절에 a 매출의 컬럼을 추가하면 됩니다.

select a.지점,a.판매월,min(a.매출) as 매출,sum(b.매출) as 누적매출
from 월별지점매출 a,월별지점매출 b
where a.지점 = b.지점
and a.판매월 >= b.판매월
group by a.지점,a.판매월,a.매출
order by a.지점,a.판매월;

지점 판 매출 누적매출   
-- - --- -----------
10 1 521         521
10 2 684        1205
10 3 590        1795
20 1 537         537
20 2 650        1187
20 3 500        1687
20 4 919        2606
20 5 658        3264
30 1 631         631
30 2 736        1367
30 3 513        1880
30 4 970        2850
30 5 939        3789
30 6 666        4455

14 rows selected.

끝

출처 : SQL 자격검정 실전문제