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

in 가이던스 on SQL

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


1. 문제 발생


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


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

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

결과적으로 ,아래 이미지와 같이 집계 결과를 출력하면 된다.
( (ALL)로 적혀있는 부분이 소계라고 보면 된다.)


ROLLUP 구문을 이용하고 싶었으나, ROLLUP은 인수 순서에 따라
소계 집계가 계층별로 이루어져 모든 조합(필드)에 대한 소계치를 볼 수 없었다.
( [null]로 적혀있는 부분이 소계라고 보면 된다.)

아래 이미지가 rollup 구문으로 출력한 결과다.



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

즉, rollup 구문으로는 아래 이미지처럼 노란 형광펜으로 칠해진 부분을 구할수 없다.


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

2. 문제 해결

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

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

-- UNNEST([필드1,'전체']) AS 칼럼 1 
	
SELECT
  필드1
  , SUM(won) AS krw
FROM sample_table
, UNNEST([필드1,'전체']) AS 필드1
WHERE 1=1
GROUP BY 필드1

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

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

-- 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.