LoginSignup
0
0

More than 1 year has passed since last update.

【データ抽出/BigQuery】CROSS JOINを使って不足しているデータを補足した

Posted at

今回の課題

下記の2種類のテーブルを使って、
titleテーブルのtitle_nameに対応するseries_nameを抽出できるようにしたい。
(業務で行った処理を分かりやすいテーブルに書き換えて記載していく。)

【titleテーブル】

title_id title_name
1 トイ・ストーリー
2 トイ・ストーリー2
3 トイ・ストーリー3
4 バイオハザード
5 バイオハザード2
6 バイオハザード3
7 バイオハザード4
8 バイオハザード5
9 バイオハザード6

【seriesテーブル】

series_id title_name series_name
1 トイ・ストーリー トイ・ストーリー
1 トイ・ストーリー2 トイ・ストーリー
1 トイ・ストーリー3 トイ・ストーリー
2 バイオハザード バイオハザード
2 バイオハザード2 バイオハザード

【欲しいデータ】

title_name series_name
トイ・ストーリー トイ・ストーリー
トイ・ストーリー2 トイ・ストーリー
トイ・ストーリー3 トイ・ストーリー
バイオハザード バイオハザード
バイオハザード2 バイオハザード
バイオハザード3 バイオハザード
バイオハザード4 バイオハザード
バイオハザード5 バイオハザード
バイオハザード6 バイオハザード

失敗例

最初2つのテーブルを左外部結合や完全外部結合で結合していた。

select
    t.title_name
    , s.series_name
from
    `title` as t
    left join `series` as s
        on t.title_name = s.title_name

出力結果

title_name series_name
トイ・ストーリー トイ・ストーリー
トイ・ストーリー2 トイ・ストーリー
トイ・ストーリー3 トイ・ストーリー
バイオハザード バイオハザード
バイオハザード2 バイオハザード
バイオハザード3 null
バイオハザード4 null
バイオハザード5 null
バイオハザード6 null

このように、完全外部結合や左外部結合を行うとnullが複数発生してしまうので使えない。

成功例

/* seriesをユニークな値に絞り込んでおく */
with series_mst as (
    select
        distinct series_name
    from
        `series`
)
/* series_mstとtitleテーブルをcross joinする&余計な組み合わせをWHERE句で除去する */
select
    t.title_name
    , s.series_name
from
    `title` as t
    cross join series_mst as s
where
    t.title_name like "%"||s.series_name||"%" -- title_nameの中にseries_nameの文字列が入っているものの組み合わせに絞り込む(cross joinによってtitle×seriesのすべての組み合わせが生成されているから)

出力結果

title_name series_name
トイ・ストーリー トイ・ストーリー
トイ・ストーリー2 トイ・ストーリー
トイ・ストーリー3 トイ・ストーリー
バイオハザード バイオハザード
バイオハザード2 バイオハザード
バイオハザード3 バイオハザード
バイオハザード4 バイオハザード
バイオハザード5 バイオハザード
バイオハザード6 バイオハザード

cross joinを使えば、外部結合の時のNULLを取り除いてデータを抽出できた。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0