データマート作成における左結合でCASE文を活用してみた

はじめに

こんにちは。（心の内では）健康を目指して日々精進しているshimojです。
普段はデータ分析基盤周りを主に担当しており、最近はデータマートの仕様に関する調整もさせてもらえるようになりました。
今回は、データマート作成時の結合時に条件によって結合キーを変更するcase文を利用しましたので内容を整理したいと思います。
なお、Amazon Redshiftで実装確認を進めます。

データマートの全体像

作成するデータマートは、「売上のトランザクション」をFROM区に「商品のマスタ」データを左結合したシンプルなデータマートを作成します。以下に処理のイメージ図を記載します。

なお2つのテーブルのDDLは以下のようになっております。

• 売上のトランザクション（tmp_sales_table）

No	カラム名	型
1	tran_id	文字列
2	product_id	文字列
3	sales_date	日付
4	flg	文字列

• 商品テーブル（tmp_product_table）

No	カラム名	型
1	product_id	文字列
2	product_name	文字列
3	product_price	数値
4	product_id_flg	文字列

結合条件の分岐は「売上のトランザクション」のflgカラム値によってカラムを変更します

• 0: product_id
• 0以外: product_id_flg

検証用テーブルの作成

検証前に利用する「売上トランザクション」と「商品マスタ」テーブルを一時的に作成するコードを記載します。

-- 売上トランザクションテーブル
create table tmp_sales_table  as (
select '1' as tran_id, '100' as product_id, '2024-01-01'::date as sales_date, '0' as flg union all 
select '2' as tran_id, '100' as product_id, '2024-02-01'::date as sales_date, '1' as flg union all 
select '3' as tran_id, '200' as product_id, '2024-01-01'::date as sales_date, '0' as flg union all 
select '4' as tran_id, '200' as product_id, '2024-02-01'::date as sales_date, '1' as flg union all 
select '5' as tran_id, '200' as product_id, '2024-02-01'::date as sales_date, '1' as flg
);

-- 商品マスタテーブル
create table tmp_product_table  as (
select '100' as product_id, '商品A' as product_name, 200 as product_price, 'Null' as product_id_flg union all
select '110' as product_id, '商品A' as product_name, 150 as product_price, '100' as product_id_flg union all
select '200' as product_id, '商品B' as product_name, 1000 as product_price, 'Null' as product_id_flg union all
select '210' as product_id, '商品B' as product_name, 500 as product_price, '200' as product_id_flg
);

動作確認と結果

データマートを作成するSQLと結果の表を記載します。

データマート作成のSQL

売上トランザクションのflgカラム値によって、商品マスタの結合キーを変更するシンプルなデータマートのSQLです。

select *
from tmp_sales_table s
left join tmp_product_table p
  on s.product_id = case when s.flg = 0
                         then p.product_id
                         else p.product_id_flg 
                         end
order by s.tran_id;

実行結果

それではSQLの実行結果を表に記載します。
売上トランザクションの同一product_idでも、flgカラムの値によって紐づく金額が異なることが確認できました。

tran_id	product_id	sales_date	flg	product_id	product_name	product_price	product_id_flg
1	100	2024-01-01	0	100	商品A	200	Null
2	100	2024-02-01	1	110	商品A	150	100
3	200	2024-01-01	0	200	商品B	1000	Null
4	200	2024-02-01	1	210	商品B	500	200
5	200	2024-02-01	1	210	商品B	500	200

まとめ

データマートを作成する際に、flgカラムの値によって結合するキー項目を変更する必要があったので実装した際の内容をまとめました。この記事がどなたかの助けになれば幸いです。