[BigQuery] CROSS JOIN을 이용한 Table 열 압축, 정리

출처 : 데이터 분석을 위한 SQL 레시피

참고 : https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax?hl=ko#cross\_join

CROSS JOIN

cross join은 두 개의 table의 각 행들을 연결하는 쿼리이다.

M개의 행을 가진 table과 N개의 행을 가진 table을 cross join 할 경우 M * N개의 행을 가진 table이 생성된다.

 

'quarterly_sale' table 생성

drop table if exists ch3.quarterly_sales;
create table ch3.quarterly_sales(
  year integer,
  q1 integer,
  q2 integer,
  q3 integer,
  q4 integer
);

insert into ch3.quarterly_sales
values
(2015, 82000, 83000, 78000, 83000),
(2016, 85000, 85000, 80000, 81000),
(2017, 92000, 81000, NULL, NULL);

실행 결과

year	q1	q2	q3	q4
2015	82000	83000	78000	83000
2016	85000	85000	80000	81000
2017	92000	81000	NULL	NULL

'axis' table 생성

drop table if exists ch3.axis;
create table ch3.axis(
axis integer
);
insert into ch3.axis
values
(1),
(2),
(3),
(4);

실행 결과

axis
1
2
3
4

'quarterly_sales' table과 'p' table cross join

select
q.year,
-- q1에서 q4까지의 레이블 이름 출력
case
when p.idx = 1 then 'q1' 
when p.idx = 2 then 'q2'
when p.idx = 3 then 'q3'
when p.idx = 4 then 'q4'
end as quarter,
-- q1에서 q4까지의 매출 출력하기
case
when p.idx = 1 then q.q1
when p.idx = 2 then q.q2
when p.idx = 3 then q.q3
when p.idx = 4 then q.q4
end as sales
from ch3.quarterly_sales as q
cross join 
-- 행으로 전개하고 싶은 열의 수만큼 순번 테이블 만들기
(
  select 1 as idx
  union all select 2 as idx
  union all select 3 as idx
  union all select 4 as idx
) as p
order by year, quarter
;

year	quarter	sales
2015	q1	82000
2015	q2	83000
2015	q3	78000
2015	q4	83000
2016	q1	85000
2016	q2	85000
2016	q3	80000
2016	q4	81000
2017	q1	92000
2017	q2	81000
2017	q3	NULL
2017	q4	NULL

cross join을 통한 여러 중,소분류 열에서 하나의 대분류 열로 통합

 

만약 열을 조작하지 않고 cross join만 사용했다면 12 * 6의 table이 생성되었을 것이다. 하지만 cross join 쿼리를 이용하여 열을 행의 데이터로 정리하고 case 쿼리를 통해서 'quarterly_sale' table에서 q1, q2, q3, q4 열을 통해 표현되었던 데이터들을 quarter, sales 열로 정리하여 12 * 3의 테이블로 변환할 수 있다.

 

q1, q2, q3, q4 열과 같이 열이 구분하고 있는 것이 중분류, 소분류일 경우 quarter 열과 같이 하나의 대분류의 열로 통합하고 싶을 때 위와 같은 방법으로 통합하고자 하는 열의 개수와 같은 행 길이를 가진 축 테이블과 cross join 시켜 열을 압축할 수 있다.