(SQL) 모든 필드의 소계 구하기 - UNNEST 이용하여 CUBE 계산

in 가이던스 on SQL

(SQL) 모든 필드의 소계 구하기 - UNNEST 이용하여 CUBE 계산


1. 문제 발생


유저 id , 마켓 , 국가 , 고객 유형, 매출이 각각 필드로 저장되어 있는 데이터가 있다.


유저들의 매출 총액을 마켓 , 국가 , 고객 타입별로 구하려고 하며 소계와 총계도 같이 보려고 한다.

즉 , 각 필드를 기준으로 CUBE 계산을 하려고 한다.
( CUBE 계산 : 결합 가능한 모든 값에 대하여 다차원 집계를 생성하는 것을 말함)

결과적으로 ,아래 이미지와 같이 집계 결과를 출력하면 된다.
( (ALL)로 적혀있는 부분이 해당 필드의 전체 경우 라고 보면 된다.)



ROLLUP 구문을 이용하고 싶었으나, ROLLUP은 인수 순서에 따라
소계 집계가 계층별로 이루어져 모든 조합(필드)에 대한 소계치를 볼 수 없었다.

아래 이미지가 rollup 구문으로 출력한 결과다.
( [null]로 적혀있는 부분이 해당 필드의 전체 경우라고 보면 된다.) 



-- rollup 구문 
SELECT
 market
 , country
 , customer_type
 , SUM(won) AS krw
FROM sample_table
WHERE 1=1
GROUP BY ROLLUP (market, country , customer_type)

즉, 위의 rollup 구문은 인수인 market,country,customer 순으로 CUBE 계산을 하기 때문에
아래 이미지처럼 노란 형광펜으로 칠해진 부분을 구할수 없다.


어떻게 쿼리 구문을 짜야 모든 필드를 기준으로 소계를 구할수 있을까?

2. 문제 해결

UNNEST를 이용하여 배열을 만들고 ,그 배열을 소계를 구하고자 하는 필드에 덮어씌워
GROUP BY 해줌으로써 이를 구현할 수 있다.

말이 복잡해보일 수 있는데 , 정리하자면 아래 구문을 이용한다고 보면 된다.

/* UNNEST([필드1,'전체']) AS 필드 1
   GROUP BY 필드 1 
*/
	
SELECT
  필드1
  , SUM(won) AS krw
FROM sample_table
, UNNEST([필드1,'전체']) AS 필드1 -- 기존 필드와 UNNEST문에서 배열을 풀면서 지정한 필드명은 동일해야함.
WHERE 1=1
GROUP BY 필드1

소계 계산할 칼럼을 UNNEST로 묶어준다.
‘전체’를 값으로 넣은 배열을 만들어주고 UNNEST 해줘서 풀어버린후 집계해준다.
( 전체 대신 “ALL”로 전체치를 표시하고 싶으면 ‘전체’ 대신 “ALL” 을 넣으면 된다)

이때 주의할점은 UNNEST문에서 배열을 풀어줄때 별칭으로 지정하는 필드명과
SELECT문에서 사용할 필드명이 동일해야 된다는 것이다.

지정하는 필드명이 동일해야 GROUP BY로 집계하면서 필드가 덮어씌어지는 효과가 난다.
만약 UNNEST문의 필드명을 SELECT문의 필드명과 다르게 지정한다면 해당 이름으로 된 필드가 하나 더 생기게 된다.
이렇게 될 경우 헷갈릴 가능성이 크므로 위의 예시처럼 필드명을 동일하게 지정하는 것이 좋다.

문제 상황에 적용해보면 아래 구문이 될것이다.

-- unnest로 cube하기 
# (Google BIGQUERY)

SELECT
 market
 , country
 , customer_type
 , SUM(won) AS user_cnt
FROM  blog.post1_unnest_cube 
,UNNEST([market,'(ALL)']) AS market
,UNNEST([country,'(ALL)']) AS country
,UNNEST([customer_type,'(ALL)']) AS customer_type 
GROUP BY market,country,customer_type
;

-- unnest로 cube하기 
# (PostgreSQL)

SELECT
 market2 AS market
 , country2 AS country
 , customer_type2 AS customer_type
 , SUM(won) AS krw
FROM blog.post1_unnest_cube 
, UNNEST(ARRAY[market,'(ALL)'] ) AS market2
, UNNEST(ARRAY[country,'(ALL)']) AS country2
, UNNEST(ARRAY[customer_type,'(ALL)']) AS customer_type2
GROUP BY market2,country2,customer_type2
;

집계 결과가 원하는대로 나온 것을 알 수 있다.



© 2024. All rights reserved.