Zenn
📘

GROUP BY と GROUP BY CUBE

2023/01/19に公開
SQL
tech

GROUP BY CUBE

Snowflakeにおいてgroup byには3つの派生(拡張機能)があります。前回は、group by rollupについて説明を行いました。今回はgroup by cubeについて紹介したいと思います。これが終わると最後のGROUP BYは, GROUP BY GROUPING SETSとなります。

サンプルデータ

早速本題に入っていきたいと思います。
Snowflakeが示してくれている例を基に挙動を確認したいと思います。

前回と同様にサンプルデータを作成していきます。
以下のサンプルクエリを実行します。

サンプルデータのクエリ①
create table tr_daisuke_harato.snowflake_demo.products (product_id integer, wholesale_price real);

insert into tr_daisuke_harato.snowflake_demo.products (product_id, wholesale_price) values 
    (1, 1.00),
    (2, 2.00);
;
サンプルデータのクエリ②
create table test_sample.sales (product_id integer, retail_price real, 
    quantity integer, city varchar, state varchar);
insert into test_sample.sales (product_id, retail_price, quantity, city, state) values 
    (1, 2.00,  1, 'SF', 'CA'),
    (1, 2.00,  2, 'SJ', 'CA'),
    (2, 5.00,  4, 'SF', 'CA'),
    (2, 5.00,  8, 'SJ', 'CA'),
    (2, 5.00, 16, 'Miami', 'FL'),
    (2, 5.00, 32, 'Orlando', 'FL'),
    (2, 5.00, 64, 'SJ', 'PR');

各データの確認をササっとしてみます

サンプルデータの確認①
PRODUCT_ID WHOLESALE_PRICE
1 1
2 2
サンプルデータの確認②
PRODUCT_ID RETAIL_PRICE QUANTITY CITY STATE
1 2 1 SF CA
1 2 2 SJ CA
2 5 4 SF CA
2 5 8 SJ CA
2 5 16 Miami FL
2 5 32 Orlando FL
2 5 64 SJ PR

GROUP BY

いったんよく使用されるgroup byを実行してみます。テーブルは出力結果になります。

select
    state,
    city,
    sum((s.retail_price - p.wholesale_price) * s.quantity) as profit 
from
    snowflake_demo.products as p
left join
    snowflake_demo.sales as s
where
    s.product_id = p.product_id
group by
    state, city
order by
    state, city nulls last
 ;
クエリの出力結果
STATE CITY PROFIT
CA SF 13
CA SJ 26
FL Miami 48
FL Orlando 96
PR SJ 192

GROUP BY CUBE

さて、今回の本題であるgroup by cubeを実行してみます。

select
    state,
    city,
    sum((s.retail_price - p.wholesale_price) * s.quantity) as profit 
from
    snowflake_demo.products as p
left join
    snowflake_demo.sales as s
where
    s.product_id = p.product_id
group by
    cube (state, city)
order by
    state, city nulls last
;

group byとは少し仕様が異なり、group by cubeは対象のカラムを()の中に指定をしてあげる必要があります。テーブルは出力結果になります。

group by cube のサンプル
SELECT ...
FROM ...
[ ... ]
GROUP BY CUBE ( groupCube [ , groupCube [ , ... ] ] )
[ ... ]
クエリの出力結果
STATE CITY PROFIT
CA SF 13
CA SJ 26
CA null 39
FL Miami 48
FL Orlando 96
FL null 144
PR SJ 192
PR null 192
null Miami 48
null Orlando 96
null SF 13
null SJ 218
null null 375

group by cubeの簡単なロジックの説明を書きたいと思います(下の図がほとんどになりますが....)。増えたレコードは前回説明したgroup by rollupと比べて増えたレコードに印を入れています。各行・列に対するクロス集計が可能になっています。

STAET CITY PROFIT 増えたレコード
CA SF A
CA SJ B
CA null A+B
PR SJ C
PR null C
null SF A 〇
null SJ B+C 〇
null null A+B+C

感想

またしても使う機会があるかはわかりませんが、使うときはシュシュッと思い出せるように頭の片隅に置いておきます。

参考記事

Discussion