書いてあること
データレイクのデータを汎用的な構造に加工してデータウェアハウス化する部分であったり、BIや学習系などセマンティックなマート化する目的であったりによく使うSQL句の以下3つを紹介します。
- cross join
- full outer join & coalesce
- qualify
最近物忘れが増えてきまして、あれどう書くんだっけ？になりがちな自分向けだったりもします。
※SQLはBigQueryベースです
cross join
いわゆる直積です。
idテーブル
id
------
1
2
3
nameテーブル
name
------
aaa
bbb
cross joinさせて
id | name
------+------
1 | aaa
1 | bbb
2 | aaa
2 | bbb
3 | aaa
3 | bbb
というデータセットを作れるSQL句です。
これは日別データなどマート作るときに0実績の行を作るのに重宝します。
日付 | 部署 | 販売数量
-------+----------+----------
11-01 | 営業1課 | 10
11-01 | 営業2課 | 5
11-02 | 営業1課 | 13
11-02 | 営業2課 | 9
11-06 | 営業1課 | 3
11-06 | 営業2課 | 6
このようなデータから11月の1日から6日までの日別販売数を出すとき、休日である3-5日の0実績レコードを作るような場面で使います。
WITH
calendar AS (
SELECT
date_seq
FROM
UNNEST(GENERATE_DATE_ARRAY('2023-11-01', '2023-11-06')) AS date_seq
)
SELECT
calendar.date_seq AS sale_date
, SUM(CASE WHEN calendar.date_seq = データ.日付 THEN データ.販売数量 ELSE 0 END) AS qty
FROM
calendar
CROSS JOIN
データ
GROUP BY
calendar.date_seq
といった具合で書いたりします。
sales_date | qty
--------------+------
11-01 | 15
11-02 | 22
11-03 | 0
11-04 | 0
11-05 | 0
11-06 | 9
後続の可視化や学習などへデータ提供する際、欠損を0として解釈することを委ねずにマート作成時に0と明示的に定義すべきケースにはぜひ使いましょう。
full outer join & coalesce
こちらはセットでよく使い、複数のデータソースからマージしたり、差分データのマージによる積み上げに使ったりします。
-- 決済方法その1（◯◯店の実績から引用データ）
id | name
----+------
1 | 現金
2 | クレカ
3 | 交通系
-- 決済方法その2（△△店の実績から引用データ）
id | name
----+------
1 | 現金
2 | クレカ
4 | paypay
idの一意性が担保されることを確認したうえでこのような2つのPOSデータからの引用で決済方法マスタを作るとして、
SELECT
COALESCE(決済方法その1.id, 決済方法その2.id) AS id
, COALESCE(決済方法その1.name, 決済方法その2.name) AS name
FROM
決済方法その1
FULL OUTER JOIN
決済方法その2
ON
決済方法その1.id = 決済方法その2.id
のように書いて出力します。
id | name
----+------
1 | 現金
2 | クレカ
3 | 交通系
4 | paypay
左右の属性項目に差異が存在して、ロジックを組んで名寄せするパターンなどではCASE文を用いたりもします。
qualify
window関数を使った絞り込みでサブクエリを書いて条件指定をしていたものが、簡潔な書き方を可能にするとても便利なSQL句です。
計測日時 | 商品 | 在庫数量
----------------------+-------+-----------
2023-12-01 12:01:45 | みかん | 10
2023-12-01 19:55:17 | りんご | 25
2023-12-01 23:32:09 | みかん | 18
このようなデータから供給量の意思決定向けに日別の最新在庫データを使いたい場合
WITH
stock_with_rownum AS (
SELECT
計測日時
, 商品
, 在庫数量
, ROW_NUMBER() OVER (PARTITION BY DATE(計測日時), 商品 ORDER BY 計測日時 DESC) AS rownum
FROM
在庫データ
)
SELECT
計測日時
, 商品
, 在庫数量
FROM
stock_with_rownum
WHERE
rownum = 1
と書いていたものが
SELECT
計測日時
, 商品
, 在庫数量
FROM
在庫データ
QUALIFY
ROW_NUMBER() OVER (PARTITION BY DATE(計測日時), 商品 ORDER BY 計測日時 DESC) = 1
と書けてデータが出力されます。
計測日時 | 商品 | 在庫数量
----------------------+-------+-----------
2023-12-01 19:55:17 | りんご | 25
2023-12-01 23:32:09 | みかん | 18
便利なqualify句ですが、where句無しでのview作成ではエラーとなるらしく、where trueを追記するなどして回避が必要とのことです。