🌀
長いSQLの1人実装を生成AIで解消しようとした

kaneko
2024/12/01に公開
SQL
生成 AI
tech
この記事は株式会社ガラパゴス（有志）アドベントカレンダー2024の1日目の記事です。

今年もアドベントカレンダーの時期がやって来ました！1年が終わるのは早いですね。
私は個人的なことを神頼みするタイプではないので初詣で「うちのチームにかわいい後輩が欲しい」みたいなお願いはしないのですが、個人的に今年は良い年でした。

残り短いですが、皆さまにとって良い2024年になりますように。

それでは本題に入ります。

 背景最近様々なデータを紐づけて、多種多様な条件を付けて分析できるようにデータを整形する作業が増えています。
似たような条件も多いので都度過去の実装を参照しながら必要な部分をコピペすることで時間短縮を図っていますが、大体新しい条件を取り扱う際に仕様を読み間違えたりSQLの実装でミスしたりしてチームに迷惑をかけています。
というわけであまり時間や人手をかけずに正しいかどうか確認する方法は無いものか、と考えていた際にこれまで個人的にあまり活用できていなかった生成AIの存在を思い出しました。という訳で、今回は生成AIを用いることでSQLの実装まわりをどのように改善できそうか検証してみます。

 試したこと元となるテーブルのスキーマと欲しいテータのスキーマを与え、それぞれの集計条件等も日本語で仕様として渡します。それらのインプットを元にSQLを生成AIに書いてもらいました。
作成したSQLに誤りがあるかどうかを確認し、問題があれば指摘して再度SQLを書き直してもらいました。これを何回か繰り返しました。
またこちらでもサンプルでSQLを作成し、その内容が正しいかどうかを生成AIにレビューしてもらいました。
最初に渡したインプット以下の条件で、テーブル１とテーブル２を紐づけるSQLを生成してください：

○テーブル１
イベント名
利用規約確認か購入のいずれか

タイムスタンプ（マイクロ秒）
ユーザーID
○テーブル２
メンバーID
確認日時（秒単位）
購入日時（秒単位）
購入アイテム
利用ポイント数
○欲しいデータ
独自形式のユーザーID
メンバーID
メンバーステータス
利用規約確認、購入経験あり、ポイント利用あり

初回確認日時
初回購入日時
初回ポイント利用日時
○テーブル１とテーブル２を紐づける条件
利用規約確認イベントのタイムスタンプと確認日時が最も近い
場合によってはテーブル１にイベントが登録されない可能性がある

購入イベントのタイムスタンプと購入日時が最も近い
ただし購入アイテムに「かめ」を含むデータのみテーブル１のイベントとして登録される

利用規約確認の方が紐づく割合が高いため、購入イベントの紐づけは補助的に行う
1人のユーザーに対して2人以上のメンバーは紐づけない
1人のメンバーに対して2人以上のユーザーは紐づけない
○ステータス関連の条件
基本的に利用規約確認→購入→ポイント利用の順で遷移する
初回確認日時=テーブル１とテーブル２が紐づいた初回の確認日時とする
初回購入日時=初回確認日時以降で最も早い購入日時とする
初回ポイント利用日時=初回ポイント利用日時以降で最も早く、利用ポイント数が1以上となった購入日時とする
メンバーステータス=初回確認日時があれば利用規約確認、また初回購入日時もあれば購入経験あり、さらに初回ポイント利用日時もあればポイント利用ありとする

 実行結果今回は2種類の生成AIにSQLを作成、レビューしてもらいました。それぞれ細かい点では特色が見られたものの、最初のアウトプットで既に概ね正しいものを生成してくれました。
Claude 3.5 Sonnet での結果
初回の出力WITH 
-- 利用規約確認イベントの抽出
terms_events AS (
    SELECT 
        user_id,
        timestamp / 1000000 as event_time_sec
    FROM table1
    WHERE event_name = '利用規約確認'
),

-- 購入イベントの抽出（かめ商品のみ）
purchase_events AS (
    SELECT 
        user_id,
        timestamp / 1000000 as event_time_sec
    FROM table1
    WHERE event_name = '購入'
),

-- テーブル2の各メンバーに対する最適なユーザーIDの紐づけ
user_member_mapping AS (
    -- 利用規約確認での紐づけ
    SELECT 
        t2.member_id,
        te.user_id,
        t2.confirm_time,
        ABS(te.event_time_sec - t2.confirm_time) as time_diff,
        ROW_NUMBER() OVER (PARTITION BY te.user_id ORDER BY ABS(te.event_time_sec - t2.confirm_time)) as user_rank,
        ROW_NUMBER() OVER (PARTITION BY t2.member_id ORDER BY ABS(te.event_time_sec - t2.confirm_time)) as member_rank
    FROM table2 t2
    LEFT JOIN terms_events te
    ON ABS(te.event_time_sec - t2.confirm_time) < 86400  -- 1日以内の差異を許容

    UNION ALL

    -- 購入での紐づけ（補助的）
    SELECT 
        t2.member_id,
        pe.user_id,
        t2.confirm_time,
        ABS(pe.event_time_sec - t2.purchase_time) as time_diff,
        ROW_NUMBER() OVER (PARTITION BY pe.user_id ORDER BY ABS(pe.event_time_sec - t2.purchase_time)) as user_rank,
        ROW_NUMBER() OVER (PARTITION BY t2.member_id ORDER BY ABS(pe.event_time_sec - t2.purchase_time)) as member_rank
    FROM table2 t2
    LEFT JOIN purchase_events pe
    ON ABS(pe.event_time_sec - t2.purchase_time) < 86400  -- 1日以内の差異を許容
    WHERE t2.purchase_item LIKE '%かめ%'
),

-- 1対1の紐づけを確定
final_mapping AS (
    SELECT DISTINCT
        member_id,
        user_id
    FROM user_member_mapping
    WHERE user_rank = 1 AND member_rank = 1
),

-- メンバーの状態を分析
member_status AS (
    SELECT 
        fm.user_id,
        t2.member_id,
        MIN(t2.confirm_time) as first_confirm_time,
        MIN(CASE WHEN t2.confirm_time >= MIN(t2.confirm_time) THEN t2.purchase_time END) as first_purchase_time,
        MIN(CASE 
            WHEN t2.confirm_time >= MIN(t2.confirm_time) 
            AND t2.points_used > 0 
            THEN t2.purchase_time 
        END) as first_points_time,
        CASE
            WHEN MIN(t2.confirm_time) IS NOT NULL AND 
                 MIN(CASE WHEN t2.confirm_time >= MIN(t2.confirm_time) THEN t2.purchase_time END) IS NOT NULL AND
                 MIN(CASE WHEN t2.confirm_time >= MIN(t2.confirm_time) AND t2.points_used > 0 THEN t2.purchase_time END) IS NOT NULL
            THEN 'ポイント利用あり'
            WHEN MIN(t2.confirm_time) IS NOT NULL AND 
                 MIN(CASE WHEN t2.confirm_time >= MIN(t2.confirm_time) THEN t2.purchase_time END) IS NOT NULL
            THEN '購入経験あり'
            WHEN MIN(t2.confirm_time) IS NOT NULL
            THEN '利用規約確認'
        END as member_status
    FROM final_mapping fm
    JOIN table2 t2 ON fm.member_id = t2.member_id
    GROUP BY fm.user_id, t2.member_id
)

-- 最終的な結果を出力
SELECT 
    user_id,
    member_id,
    member_status,
    first_confirm_time,
    first_purchase_time,
    first_points_time
FROM member_status
ORDER BY user_id;
このSQLの主なポイントを説明します：

1.イベントデータの正規化
テーブル1のタイムスタンプをマイクロ秒から秒に変換
利用規約確認と購入イベントを分離して処理
2.ユーザーとメンバーの紐づけロジック
時間差の最小値を基準に紐づけ
ROW_NUMBERを使用して1対1の関係を確保
利用規約確認を優先的に使用し、購入情報は補助的に使用
3.ステータス判定ロジック
利用規約確認→購入→ポイント利用の順序を考慮
各イベントの初回日時を正確に計算
条件に基づいて3段階のステータスを付与
4.時系列の考慮
初回確認日時以降の購入のみを考慮
ポイント利用は購入と同時に発生するため購入日時を基準
このSQLで改善や調整が必要な点はありますか？例えば：
時間差の許容範囲（現在は1日）の調整
ステータスの名称変更
追加で必要な条件

2回目の入力このSQLの紐づけ部分を見ると、各イベントでは1人のユーザーに1人のメンバーを紐づけられています。しかしデータ全体で見た時にこの紐づけが異なるマッピングが発生する可能性があるので、ユーザーとメンバーが1対1の関係にならないと思われます

2回目の出力ご指摘ありがとうございます。確かにその通りですね。利用規約確認イベントと購入イベントで別々に1対1のマッピングを行っているため、全体としては1対多や多対1の関係が発生してしまう可能性があります。この問題を修正しましょう。
WITH 
-- 利用規約確認イベントの抽出
terms_events AS (
    SELECT 
        user_id,
        timestamp / 1000000 as event_time_sec
    FROM table1
    WHERE event_name = '利用規約確認'
),

-- 購入イベントの抽出（かめ商品のみ）
purchase_events AS (
    SELECT 
        user_id,
        timestamp / 1000000 as event_time_sec
    FROM table1
    WHERE event_name = '購入'
),

-- 全ての紐づけ候補を統合して優先順位付け
all_mappings AS (
    -- 利用規約確認での紐づけ候補
    SELECT 
        t2.member_id,
        te.user_id,
        ABS(te.event_time_sec - t2.confirm_time) as time_diff,
        1 as mapping_type,  -- 利用規約確認の方が優先度が高い
        t2.confirm_time
    FROM table2 t2
    LEFT JOIN terms_events te
    ON ABS(te.event_time_sec - t2.confirm_time) < 86400  -- 1日以内の差異を許容
    WHERE te.user_id IS NOT NULL

    UNION ALL

    -- 購入での紐づけ候補
    SELECT 
        t2.member_id,
        pe.user_id,
        ABS(pe.event_time_sec - t2.purchase_time) as time_diff,
        2 as mapping_type,  -- 購入の方が優先度が低い
        t2.confirm_time
    FROM table2 t2
    LEFT JOIN purchase_events pe
    ON ABS(pe.event_time_sec - t2.purchase_time) < 86400
    WHERE t2.purchase_item LIKE '%かめ%'
    AND pe.user_id IS NOT NULL
),

-- スコアリングとランク付け
scored_mappings AS (
    SELECT 
        member_id,
        user_id,
        time_diff,
        mapping_type,
        confirm_time,
        -- マッピングタイプと時間差を考慮したスコアを計算
        -- 利用規約確認の方が優先度が高いため、mapping_typeに大きな重みを付ける
        (mapping_type * 100000 + time_diff) as mapping_score,
        -- 各ユーザーに対する最適なメンバーを選定
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY (mapping_type * 100000 + time_diff)) as user_rank,
        -- 各メンバーに対する最適なユーザーを選定
        ROW_NUMBER() OVER (PARTITION BY member_id ORDER BY (mapping_type * 100000 + time_diff)) as member_rank
    FROM all_mappings
),

-- 相互に最適なマッピングのみを選択
final_mapping AS (
    SELECT 
        member_id,
        user_id
    FROM scored_mappings
    WHERE user_rank = 1 AND member_rank = 1
    -- 同一ユーザーまたはメンバーが複数回出現した場合は除外
    GROUP BY member_id, user_id
    HAVING COUNT(*) = 1
),

-- メンバーの状態を分析
member_status AS (
    SELECT 
        fm.user_id,
        t2.member_id,
        MIN(t2.confirm_time) as first_confirm_time,
        MIN(CASE WHEN t2.confirm_time >= MIN(t2.confirm_time) THEN t2.purchase_time END) as first_purchase_time,
        MIN(CASE 
            WHEN t2.confirm_time >= MIN(t2.confirm_time) 
            AND t2.points_used > 0 
            THEN t2.purchase_time 
        END) as first_points_time,
        CASE
            WHEN MIN(t2.confirm_time) IS NOT NULL AND 
                 MIN(CASE WHEN t2.confirm_time >= MIN(t2.confirm_time) THEN t2.purchase_time END) IS NOT NULL AND
                 MIN(CASE WHEN t2.confirm_time >= MIN(t2.confirm_time) AND t2.points_used > 0 THEN t2.purchase_time END) IS NOT NULL
            THEN 'ポイント利用あり'
            WHEN MIN(t2.confirm_time) IS NOT NULL AND 
                 MIN(CASE WHEN t2.confirm_time >= MIN(t2.confirm_time) THEN t2.purchase_time END) IS NOT NULL
            THEN '購入経験あり'
            WHEN MIN(t2.confirm_time) IS NOT NULL
            THEN '利用規約確認'
        END as member_status
    FROM final_mapping fm
    JOIN table2 t2 ON fm.member_id = t2.member_id
    GROUP BY fm.user_id, t2.member_id
)

-- 最終的な結果を出力
SELECT 
    user_id,
    member_id,
    member_status,
    first_confirm_time,
    first_purchase_time,
    first_points_time
FROM member_status
ORDER BY user_id;
主な変更点は以下の通りです：
1.マッピングロジックの統合
利用規約確認と購入イベントのマッピング候補を一つのCTEに統合
mapping_typeで優先順位を付与（1=利用規約確認、2=購入）
2.スコアリングシステムの導入
mapping_typeと時間差を組み合わせたスコアを計算
利用規約確認の方が優先されるよう、mapping_typeに大きな重みを付与
3.厳密な1対1マッピングの確保
user_rankとmember_rankの両方が1のものだけを選択
GROUP BYとHAVING句で重複マッピングを除外
4.優先順位の明確化
mapping_typeに大きな重み（100000）を掛けることで、利用規約確認の優先度を確実に高く設定
これにより、データ全体で見たときに：
1人のユーザーが複数のメンバーと紐づくことを防止
1人のメンバーが複数のユーザーと紐づくことを防止
利用規約確認イベントでの紐づけを優先的に採用
改善点や調整が必要な点はありますか？例えば：
スコアリングの重み付けの調整
マッピングの優先順位の変更
時間差の許容範囲の調整

3回目の入力同一ユーザーやメンバーが複数回発生した場合は除外するのではなく、時間が最も早いマッピングを採用するようにしてもらいたいです

3回目の出力はい、承知しました。最も早い時間のマッピングを採用するように修正します。
WITH 
-- 利用規約確認イベントの抽出
terms_events AS (
    SELECT 
        user_id,
        timestamp / 1000000 as event_time_sec
    FROM table1
    WHERE event_name = '利用規約確認'
),

-- 購入イベントの抽出（かめ商品のみ）
purchase_events AS (
    SELECT 
        user_id,
        timestamp / 1000000 as event_time_sec
    FROM table1
    WHERE event_name = '購入'
),

-- 全ての紐づけ候補を統合して優先順位付け
all_mappings AS (
    -- 利用規約確認での紐づけ候補
    SELECT 
        t2.member_id,
        te.user_id,
        ABS(te.event_time_sec - t2.confirm_time) as time_diff,
        1 as mapping_type,  -- 利用規約確認の方が優先度が高い
        t2.confirm_time,
        te.event_time_sec
    FROM table2 t2
    LEFT JOIN terms_events te
    ON ABS(te.event_time_sec - t2.confirm_time) < 86400  -- 1日以内の差異を許容
    WHERE te.user_id IS NOT NULL

    UNION ALL

    -- 購入での紐づけ候補
    SELECT 
        t2.member_id,
        pe.user_id,
        ABS(pe.event_time_sec - t2.purchase_time) as time_diff,
        2 as mapping_type,  -- 購入の方が優先度が低い
        t2.confirm_time,
        pe.event_time_sec
    FROM table2 t2
    LEFT JOIN purchase_events pe
    ON ABS(pe.event_time_sec - t2.purchase_time) < 86400
    WHERE t2.purchase_item LIKE '%かめ%'
    AND pe.user_id IS NOT NULL
),

-- スコアリングとランク付け
scored_mappings AS (
    SELECT 
        member_id,
        user_id,
        time_diff,
        mapping_type,
        confirm_time,
        event_time_sec,
        -- マッピングタイプと時間差を考慮したスコアを計算
        (mapping_type * 100000 + time_diff) as mapping_score,
        -- 各ユーザーに対する最適なメンバーを選定（同一ユーザーの場合は最も早い時間を優先）
        ROW_NUMBER() OVER (
            PARTITION BY user_id 
            ORDER BY (mapping_type * 100000 + time_diff), event_time_sec
        ) as user_rank,
        -- 各メンバーに対する最適なユーザーを選定（同一メンバーの場合は最も早い時間を優先）
        ROW_NUMBER() OVER (
            PARTITION BY member_id 
            ORDER BY (mapping_type * 100000 + time_diff), event_time_sec
        ) as member_rank
    FROM all_mappings
),

-- 相互に最適なマッピングのみを選択
final_mapping AS (
    SELECT 
        member_id,
        user_id,
        confirm_time
    FROM scored_mappings
    WHERE user_rank = 1 AND member_rank = 1
),

-- メンバーの状態を分析
member_status AS (
    SELECT 
        fm.user_id,
        t2.member_id,
        MIN(t2.confirm_time) as first_confirm_time,
        MIN(CASE WHEN t2.confirm_time >= MIN(t2.confirm_time) THEN t2.purchase_time END) as first_purchase_time,
        MIN(CASE 
            WHEN t2.confirm_time >= MIN(t2.confirm_time) 
            AND t2.points_used > 0 
            THEN t2.purchase_time 
        END) as first_points_time,
        CASE
            WHEN MIN(t2.confirm_time) IS NOT NULL AND 
                 MIN(CASE WHEN t2.confirm_time >= MIN(t2.confirm_time) THEN t2.purchase_time END) IS NOT NULL AND
                 MIN(CASE WHEN t2.confirm_time >= MIN(t2.confirm_time) AND t2.points_used > 0 THEN t2.purchase_time END) IS NOT NULL
            THEN 'ポイント利用あり'
            WHEN MIN(t2.confirm_time) IS NOT NULL AND 
                 MIN(CASE WHEN t2.confirm_time >= MIN(t2.confirm_time) THEN t2.purchase_time END) IS NOT NULL
            THEN '購入経験あり'
            WHEN MIN(t2.confirm_time) IS NOT NULL
            THEN '利用規約確認'
        END as member_status
    FROM final_mapping fm
    JOIN table2 t2 ON fm.member_id = t2.member_id
    GROUP BY fm.user_id, t2.member_id
)

-- 最終的な結果を出力
SELECT 
    user_id,
    member_id,
    member_status,
    first_confirm_time,
    first_purchase_time,
    first_points_time
FROM member_status
ORDER BY user_id;
主な変更点は以下の通りです：
1.イベント時間の追加
all_mappingsにevent_time_secを追加
これにより、最も早い時間のマッピングを特定可能に
2.ROW_NUMBER()の順序条件の変更
スコアが同じ場合は、event_time_secで並び替えるように変更
ORDER BY (mapping_type * 100000 + time_diff), event_time_sec
3.HAVING句の削除
重複を除外する代わりに、最も早い時間のマッピングを採用
この変更により：
同一ユーザーが複数のマッピング候補を持つ場合、最も早い時間のものが採用される
同一メンバーが複数のマッピング候補を持つ場合、最も早い時間のものが採用される
依然として利用規約確認イベントが優先される（mapping_typeによる重み付けは維持）
調整が必要な点はありますか？例えば：
時間の優先順位の重み付け調整
別の時間軸の追加
スコアリングロジックの変更

4回目の入力ROW_NUMBER()でrankを計算するのはBigQueryではあまり計算効率が良くないと聞いたことがあります。ROW_NUBER()の代わりにARRAY_AGGでOFFSET(0)を取る方法で実装するとどうなりますか？

4回目の出力はい、その通りです。BigQueryではARRAY_AGGとOFFSET(0)を使用する方が効率的な場合が多いです。その方法で実装し直してみましょう。
WITH 
-- 利用規約確認イベントの抽出
terms_events AS (
    SELECT 
        user_id,
        timestamp / 1000000 as event_time_sec
    FROM table1
    WHERE event_name = '利用規約確認'
),

-- 購入イベントの抽出（かめ商品のみ）
purchase_events AS (
    SELECT 
        user_id,
        timestamp / 1000000 as event_time_sec
    FROM table1
    WHERE event_name = '購入'
),

-- 全ての紐づけ候補を統合
all_mappings AS (
    -- 利用規約確認での紐づけ候補
    SELECT 
        t2.member_id,
        te.user_id,
        ABS(te.event_time_sec - t2.confirm_time) as time_diff,
        1 as mapping_type,  -- 利用規約確認の方が優先度が高い
        t2.confirm_time,
        te.event_time_sec
    FROM table2 t2
    LEFT JOIN terms_events te
    ON ABS(te.event_time_sec - t2.confirm_time) < 86400  -- 1日以内の差異を許容
    WHERE te.user_id IS NOT NULL

    UNION ALL

    -- 購入での紐づけ候補
    SELECT 
        t2.member_id,
        pe.user_id,
        ABS(pe.event_time_sec - t2.purchase_time) as time_diff,
        2 as mapping_type,  -- 購入の方が優先度が低い
        t2.confirm_time,
        pe.event_time_sec
    FROM table2 t2
    LEFT JOIN purchase_events pe
    ON ABS(pe.event_time_sec - t2.purchase_time) < 86400
    WHERE t2.purchase_item LIKE '%かめ%'
    AND pe.user_id IS NOT NULL
),

-- ユーザーごとの最適なマッピング候補を選択
user_best_matches AS (
    SELECT 
        user_id,
        ARRAY_AGG(
            STRUCT(member_id, time_diff, mapping_type, confirm_time, event_time_sec)
            ORDER BY (mapping_type * 100000 + time_diff), event_time_sec
        )[OFFSET(0)] as best_match
    FROM all_mappings
    GROUP BY user_id
),

-- メンバーごとの最適なマッピング候補を選択
member_best_matches AS (
    SELECT 
        member_id,
        ARRAY_AGG(
            STRUCT(user_id, time_diff, mapping_type, confirm_time, event_time_sec)
            ORDER BY (mapping_type * 100000 + time_diff), event_time_sec
        )[OFFSET(0)] as best_match
    FROM all_mappings
    GROUP BY member_id
),

-- 相互に最適なマッピングのみを選択
final_mapping AS (
    SELECT 
        ubm.user_id,
        ubm.best_match.member_id as member_id,
        ubm.best_match.confirm_time as confirm_time
    FROM user_best_matches ubm
    INNER JOIN member_best_matches mbm
    ON ubm.user_id = mbm.best_match.user_id
    AND ubm.best_match.member_id = mbm.member_id
),

-- メンバーの状態を分析
member_status AS (
    SELECT 
        fm.user_id,
        t2.member_id,
        MIN(t2.confirm_time) as first_confirm_time,
        MIN(CASE WHEN t2.confirm_time >= MIN(t2.confirm_time) THEN t2.purchase_time END) as first_purchase_time,
        MIN(CASE 
            WHEN t2.confirm_time >= MIN(t2.confirm_time) 
            AND t2.points_used > 0 
            THEN t2.purchase_time 
        END) as first_points_time,
        CASE
            WHEN MIN(t2.confirm_time) IS NOT NULL AND 
                 MIN(CASE WHEN t2.confirm_time >= MIN(t2.confirm_time) THEN t2.purchase_time END) IS NOT NULL AND
                 MIN(CASE WHEN t2.confirm_time >= MIN(t2.confirm_time) AND t2.points_used > 0 THEN t2.purchase_time END) IS NOT NULL
            THEN 'ポイント利用あり'
            WHEN MIN(t2.confirm_time) IS NOT NULL AND 
                 MIN(CASE WHEN t2.confirm_time >= MIN(t2.confirm_time) THEN t2.purchase_time END) IS NOT NULL
            THEN '購入経験あり'
            WHEN MIN(t2.confirm_time) IS NOT NULL
            THEN '利用規約確認'
        END as member_status
    FROM final_mapping fm
    JOIN table2 t2 ON fm.member_id = t2.member_id
    GROUP BY fm.user_id, t2.member_id
)

-- 最終的な結果を出力
SELECT 
    user_id,
    member_id,
    member_status,
    first_confirm_time,
    first_purchase_time,
    first_points_time
FROM member_status
ORDER BY user_id;
主な変更点は以下の通りです：
1.ROW_NUMBER()の置き換え
user_best_matchesとmember_best_matchesでARRAY_AGGとOFFSET(0)を使用
STRUCTを使って必要な情報をまとめて保持
2.マッピングロジックの変更
各ユーザー/メンバーの最適なマッピングを別々のCTEで計算
INNER JOINで相互に最適なマッピングを特定
この実装の利点：
BigQueryでより効率的な実行が期待できる
メモリ使用量が削減される可能性がある
コードの意図がより明確になった
調整が必要な点はありますか？例えば：
ARRAY_AGGの中のSTRUCTに含める項目の調整
ソート条件の変更
結合条件の追加/変更

レビュー時の入力私もこのテーマでSQLを実装しました。どのような問題があるか教えて下さい
WITH term_mapping AS (
  SELECT
    user_id,
    timestamp,
    member_id,
    ABS(confirm_time - timestamp / 1000000) AS time_diff,
    confirm_time,
  FROM table1
  INNER JOIN table2
  ON ABS(confirm_time - timestamp / 1000000) < 60
  WHERE event_name = '利用規約確認'
),
-- 時間差が最小の1件のみを取得
term_mapping_one AS (
  SELECT t.*
  FROM (
    SELECT ARRAY_AGG(tm ORDER BY time_diff LIMIT 1)[OFFSET(0)] t
    FROM (
      SELECT ARRAY_AGG(tma ORDER BY time_diff) tm
      FROM term_mapping tma
      GROUP BY user_id, timestamp
    )
    GROUP BY member_id
  )
),
-- ユーザーごとに最初の1件のみに絞る
term_mapping_one_user AS (
  SELECT t.*
  FROM (
    SELECT ARRAY_AGG(tm ORDER BY timestamp LIMIT 1)[OFFSET(0)] t
    FROM term_mapping_one tm
    GROUP BY user_id
  )
),
purchase_mapping AS (
  SELECT
    user_id,
    timestamp,
    member_id,
    ABS(purchase_time - timestamp / 1000000) AS time_diff,
    confirm_time
  FROM table1
  INNER JOIN table2
  ON ABS(confirm_time - timestamp / 1000000) < 60
  WHERE event_name = '購入'
),
-- 時間差が最小の1件のみを取得
purchase_mapping_one AS (
  SELECT p.*
  FROM (
    SELECT ARRAY_AGG(pm ORDER BY time_diff LIMIT 1)[OFFSET(0)] p
    FROM (
      SELECT ARRAY_AGG(pma ORDER BY time_diff) pm
      FROM purchase_mapping pma
      GROUP BY user_id, timestamp
    )
    GROUP BY member_id
  )
),
-- ユーザーごとに最初の1件のみに絞る
purchase_mapping_one_user AS (
  SELECT p.*
  FROM (
    SELECT ARRAY_AGG(tm ORDER BY timestamp LIMIT 1)[OFFSET(0)] p
    FROM purchase_mapping_one pm
    GROUP BY user_id
  )
),
-- 2つの条件を結合
merge_mappings AS (
  SELECT
    user_id,
    COALESCE(t.timestamp, p.timestamp) AS timestamp,
    COALESCE(t.member_id, p.member_id) AS member_id,
    COALESCE(t.confirm_time, p.confirm_time) AS confirm_time
  FROM term_mapping_one_user t
  FULL OUTER JOIN purchase_mapping_one_user p
  USING(user_id)
),
-- メンバーごとに最初の1件のみに絞る
merge_mappings_one AS (
  SELECT m.*
  FROM (
    SELECT ARRAY_AGG(mm ORDER BY confirm_time LIMIT 1)[OFFSET(0)] m
    FROM merge_mappings mm
    GROUP BY member_id
  )
),
-- テーブル2と紐づけ
table2_member AS (
  SELECT
    m.member_id,
    m.user_id,
    m.timestamp,
    m.confirm_time,
    t2.purchase_time,
    t2.points_used
  FROM merge_mappings_one m
  LEFT JOIN table2 t2
  USING(member_id)
  WHERE m.confirm_time <= t2.purchase_time OR t2.purchase_time IS NULL
),
purchase_agg_table AS (
  SELECT
    member_id,
    user_id,
    timestamp,
    confirm_time,
    MIN(purchase_time) AS first_purchase_time
  FROM table2_member
  GROUP BY member_id, user_id, timestamp, confirm_time
)
points_agg_table AS (
  SELECT
    purchase_agg_table.*,
    first_points_time
  FROM purchase_agg_table
  LEFT JOIN (
    SELECT
      member_id,
      user_id,
      timestamp,
      confirm_time,
      MIN(purchase_time) AS first_points_time
    FROM table2_member
    WHERE max_points_used > 0
    GROUP BY member_id, user_id, timestamp, confirm_time
  )
)
SELECT
  member_id,
  user_id,
  timestamp,
  confirm_time,
  first_purchase_time,
  first_points_time,
  CASE WHEN first_points_time IS NOT NULL THEN "ポイント利用あり"
    WHEN first_purchase_time IS NOT NULL THEN "購入経験あり"
    ELSE "利用規約確認"
    END AS member_status
FROM points_agg_table

レビュー時の出力1.データの整合性に関する問題:
INNER JOIN table2
ON ABS(confirm_time - timestamp / 1000000) < 60
時間差の許容範囲が60秒と短すぎる可能性があります
許容範囲を広げることで、より多くの有効なマッピングを見つけられる可能性があります
2.購入イベントのフィルタリング:
WHERE event_name = '購入'
「かめ」を含む購入アイテムの条件が抜けています
WHERE event_name = '購入' AND t2.purchase_item LIKE '%かめ%' とする必要があります
3.ポイント利用の判定:
WHERE max_points_used > 0
max_points_used というカラムが定義されていません
おそらく points_used の誤りと思われます
4.イベントの順序性:
WHERE m.confirm_time <= t2.purchase_time OR t2.purchase_time IS NULL
これは良い実装ですが、ポイント利用に関しても同様の順序性チェックが必要かもしれません
5.冗長なGROUP BY:
GROUP BY member_id, user_id, timestamp, confirm_time
多くのカラムでグループ化していますが、member_idだけで十分な可能性があります
改善案を含めた修正版を提案させていただきます：
WITH term_mapping AS (
    SELECT
        user_id,
        timestamp,
        member_id,
        ABS(confirm_time - timestamp / 1000000) AS time_diff,
        confirm_time,
    FROM table1
    INNER JOIN table2
    ON ABS(confirm_time - timestamp / 1000000) < 86400  -- 1日の許容範囲
    WHERE event_name = '利用規約確認'
),

-- 時間差が最小の1件のみを取得
term_mapping_one AS (
    SELECT t.*
    FROM (
        SELECT ARRAY_AGG(tm ORDER BY time_diff LIMIT 1)[OFFSET(0)] t
        FROM (
            SELECT ARRAY_AGG(tma ORDER BY time_diff) tm
            FROM term_mapping tma
            GROUP BY user_id, timestamp
        )
        GROUP BY member_id
    )
),

-- ユーザーごとに最初の1件のみに絞る
term_mapping_one_user AS (
    SELECT t.*
    FROM (
        SELECT ARRAY_AGG(tm ORDER BY timestamp LIMIT 1)[OFFSET(0)] t
        FROM term_mapping_one tm
        GROUP BY user_id
    )
),

purchase_mapping AS (
    SELECT
        user_id,
        timestamp,
        member_id,
        ABS(purchase_time - timestamp / 1000000) AS time_diff,
        confirm_time
    FROM table1
    INNER JOIN table2
    ON ABS(purchase_time - timestamp / 1000000) < 86400  -- 1日の許容範囲
    WHERE event_name = '購入'
    AND purchase_item LIKE '%かめ%'  -- かめ商品の条件を追加
),

-- 以下のCTEは同様の構造で続く...

merge_mappings AS (
    SELECT
        COALESCE(t.user_id, p.user_id) as user_id,  -- user_idのCOALESCEを追加
        COALESCE(t.timestamp, p.timestamp) AS timestamp,
        COALESCE(t.member_id, p.member_id) AS member_id,
        COALESCE(t.confirm_time, p.confirm_time) AS confirm_time,
        -- マッピングの種類を記録
        CASE 
            WHEN t.user_id IS NOT NULL THEN 1  -- 利用規約確認
            ELSE 2  -- 購入
        END as mapping_type
    FROM term_mapping_one_user t
    FULL OUTER JOIN purchase_mapping_one_user p
    USING(user_id)
),

-- メンバーごとに最初の1件のみに絞る（マッピング種類を考慮）
merge_mappings_one AS (
    SELECT m.*
    FROM (
        SELECT ARRAY_AGG(mm ORDER BY mapping_type, confirm_time LIMIT 1)[OFFSET(0)] m
        FROM merge_mappings mm
        GROUP BY member_id
    )
),

table2_member AS (
    SELECT
        m.member_id,
        m.user_id,
        m.timestamp,
        t2.confirm_time,
        t2.purchase_time,
        t2.points_used
    FROM merge_mappings_one m
    LEFT JOIN table2 t2
    ON m.member_id = t2.member_id
    WHERE t2.confirm_time <= t2.purchase_time  -- 順序性のチェック
    OR t2.purchase_time IS NULL
),

member_status_summary AS (
    SELECT
        member_id,
        user_id,
        MIN(confirm_time) as first_confirm_time,
        MIN(CASE WHEN confirm_time <= purchase_time THEN purchase_time END) as first_purchase_time,
        MIN(CASE 
            WHEN confirm_time <= purchase_time 
            AND points_used > 0 
            THEN purchase_time 
        END) as first_points_time
    FROM table2_member
    GROUP BY member_id, user_id
)

SELECT
    member_id,
    user_id,
    first_confirm_time,
    first_purchase_time,
    first_points_time,
    CASE 
        WHEN first_points_time IS NOT NULL THEN 'ポイント利用あり'
        WHEN first_purchase_time IS NOT NULL THEN '購入経験あり'
        ELSE '利用規約確認'
    END AS member_status
FROM member_status_summary
ORDER BY user_id, member_id
主な改善点：
時間の許容範囲を1日（86400秒）に拡大
購入イベントに「かめ」商品の条件を追加
マッピングタイプを追加して利用規約確認を優先
集計ロジックを簡略化
イベントの順序性チェックを強化
不要なカラムをGROUP BYから除外
これらの改善により：
より多くの有効なマッピングを見つけられる
データの整合性が向上
パフォーマンスが改善
コードの可読性が向上
いかがでしょうか？

ChatGPT 4o での結果
初回の出力以下は、指定された条件に基づき、テーブル1（イベントデータ）とテーブル2（メンバーデータ）を紐づけ、必要なデータを抽出するSQLクエリです。
WITH Closest_Confirmation AS (
    -- 利用規約確認イベントと確認日時を最も近いものに紐づける
    SELECT 
        t1.user_id AS user_id,
        t2.member_id AS member_id,
        t2.confirmation_time AS confirmation_time,
        ABS(UNIX_TIMESTAMP(t1.timestamp) - t2.confirmation_time) AS time_diff
    FROM 
        table1 t1
    INNER JOIN 
        table2 t2
    ON 
        t1.event_name = '利用規約確認'
    ORDER BY 
        user_id, member_id, time_diff
),
Ranked_Confirmation AS (
    -- 各ユーザーごとに最も近い確認日時を選ぶ
    SELECT 
        user_id,
        member_id,
        confirmation_time,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY time_diff ASC) AS rank
    FROM 
        Closest_Confirmation
),
Selected_Confirmation AS (
    -- 上位1件のみを抽出
    SELECT 
        user_id,
        member_id,
        confirmation_time
    FROM 
        Ranked_Confirmation
    WHERE 
        rank = 1
),
Closest_Purchase AS (
    -- 購入イベントと購入日時を紐づける
    SELECT 
        t1.user_id AS user_id,
        t2.member_id AS member_id,
        t2.purchase_time AS purchase_time,
        ABS(UNIX_TIMESTAMP(t1.timestamp) - t2.purchase_time) AS time_diff
    FROM 
        table1 t1
    INNER JOIN 
        table2 t2
    ON 
        t1.event_name = '購入'
        AND t1.event_detail LIKE '%かめ%' -- 購入アイテムに「かめ」を含む条件
    ORDER BY 
        user_id, member_id, time_diff
),
Ranked_Purchase AS (
    -- 各ユーザーごとに最も近い購入日時を選ぶ
    SELECT 
        user_id,
        member_id,
        purchase_time,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY time_diff ASC) AS rank
    FROM 
        Closest_Purchase
),
Selected_Purchase AS (
    -- 上位1件のみを抽出
    SELECT 
        user_id,
        member_id,
        purchase_time
    FROM 
        Ranked_Purchase
    WHERE 
        rank = 1
),
Member_Status AS (
    -- 各メンバーの初回確認日時、初回購入日時、初回ポイント利用日時を計算
    SELECT 
        t2.member_id,
        MIN(sc.confirmation_time) AS first_confirmation_time,
        MIN(CASE WHEN sp.purchase_time > sc.confirmation_time THEN sp.purchase_time END) AS first_purchase_time,
        MIN(CASE 
            WHEN sp.purchase_time > sc.confirmation_time AND t2.points_used > 0 
            THEN sp.purchase_time 
        END) AS first_points_use_time,
        CASE 
            WHEN MIN(sc.confirmation_time) IS NOT NULL THEN '利用規約確認'
            || CASE 
                WHEN MIN(CASE WHEN sp.purchase_time > sc.confirmation_time THEN sp.purchase_time END) IS NOT NULL 
                THEN '、購入経験あり' 
            END
            || CASE 
                WHEN MIN(CASE 
                    WHEN sp.purchase_time > sc.confirmation_time AND t2.points_used > 0 
                    THEN sp.purchase_time 
                END) IS NOT NULL 
                THEN '、ポイント利用あり' 
            END
            ELSE 'なし'
        END AS member_status
    FROM 
        table2 t2
    LEFT JOIN 
        Selected_Confirmation sc
    ON 
        t2.member_id = sc.member_id
    LEFT JOIN 
        Selected_Purchase sp
    ON 
        t2.member_id = sp.member_id
    GROUP BY 
        t2.member_id
)
-- 必要なデータを取得
SELECT 
    sc.user_id AS user_unique_id,
    t2.member_id,
    ms.member_status,
    ms.first_confirmation_time,
    ms.first_purchase_time,
    ms.first_points_use_time
FROM 
    Member_Status ms
LEFT JOIN 
    Selected_Confirmation sc
ON 
    ms.member_id = sc.member_id
LEFT JOIN 
    table2 t2
ON 
    ms.member_id = t2.member_id;
クエリの説明

1.Closest_Confirmation: 利用規約確認イベントと確認日時が最も近いペアを計算。

2.Ranked_Confirmation: ROW_NUMBERを使ってユーザーごとに最も近い確認日時を順位付け。

3.Selected_Confirmation: 上位1件の確認日時を抽出。

4.Closest_Purchase: 購入イベントのうち、「かめ」を含む購入アイテムと購入日時が最も近いペアを計算。

5.Ranked_Purchase: 購入イベントを順位付け。

6.Selected_Purchase: 上位1件の購入イベントを抽出。

7.Member_Status: 初回確認日時、初回購入日時、初回ポイント利用日時を計算し、ステータスを判定。

8.最終SELECT: 必要なデータを取得して出力。

条件が複雑なため、テストと検証を行いながら調整してください！

2回目の入力このSQLの紐づけ部分を見ると、各イベントでは1人のユーザーに1人のメンバーを紐づけられています。しかしメンバーでLEFT JOINする際にこの紐づけが異なるマッピングが発生する可能性があるので、ユーザーとメンバーが1対1の関係にならないと思われます

2回目の出力ご指摘の通り、このクエリでは、利用規約確認と購入でそれぞれ独立して最も近いイベントを紐づけるため、1人のユーザーに1人のメンバーを厳密に制約できていません。この結果、同じユーザーが異なるイベントで異なるメンバーに紐づくケースが発生し、1対1の関係が崩れる可能性があります。
この問題を解決するには、以下の方法を採用し、ユーザーとメンバー間の一貫した1対1のマッピングを確保する必要があります。
修正版アプローチ

1.全イベントで1人のユーザーに1人のメンバーを紐づける:
利用規約確認イベントを主軸に最も近いメンバーを決定。
そのメンバーとのみ紐づけて以降のイベントを評価。

2.一貫したマッピングの保持:
利用規約確認イベントと紐づいたメンバーだけを利用して購入やポイント利用を評価。
以下は修正版のSQLです。
WITH Closest_Confirmation AS (
    -- 利用規約確認イベントと確認日時を最も近いものに紐づける
    SELECT 
        t1.user_id AS user_id,
        t2.member_id AS member_id,
        t2.confirmation_time AS confirmation_time,
        ABS(UNIX_TIMESTAMP(t1.timestamp) - t2.confirmation_time) AS time_diff
    FROM 
        table1 t1
    INNER JOIN 
        table2 t2
    ON 
        t1.event_name = '利用規約確認'
    ORDER BY 
        user_id, member_id, time_diff
),
Ranked_Confirmation AS (
    -- 各ユーザーごとに最も近い確認日時を選ぶ
    SELECT 
        user_id,
        member_id,
        confirmation_time,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY time_diff ASC) AS rank
    FROM 
        Closest_Confirmation
),
Selected_Confirmation AS (
    -- 上位1件のみを抽出
    SELECT 
        user_id,
        member_id,
        confirmation_time
    FROM 
        Ranked_Confirmation
    WHERE 
        rank = 1
),
Purchases_With_Mapping AS (
    -- 利用規約確認で紐づけたメンバーのみを使い、購入イベントを評価
    SELECT 
        sc.user_id AS user_id,
        sc.member_id AS member_id,
        t2.purchase_time AS purchase_time,
        ABS(UNIX_TIMESTAMP(t1.timestamp) - t2.purchase_time) AS time_diff
    FROM 
        Selected_Confirmation sc
    INNER JOIN 
        table1 t1
    ON 
        sc.user_id = t1.user_id AND t1.event_name = '購入' AND t1.event_detail LIKE '%かめ%'
    INNER JOIN 
        table2 t2
    ON 
        sc.member_id = t2.member_id
    ORDER BY 
        user_id, purchase_time, time_diff
),
Ranked_Purchase AS (
    -- 各ユーザーごとに最も近い購入日時を選ぶ
    SELECT 
        user_id,
        member_id,
        purchase_time,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY time_diff ASC) AS rank
    FROM 
        Purchases_With_Mapping
),
Selected_Purchase AS (
    -- 上位1件のみを抽出
    SELECT 
        user_id,
        member_id,
        purchase_time
    FROM 
        Ranked_Purchase
    WHERE 
        rank = 1
),
Member_Status AS (
    -- 各メンバーの初回確認日時、初回購入日時、初回ポイント利用日時を計算
    SELECT 
        sc.member_id,
        sc.confirmation_time AS first_confirmation_time,
        sp.purchase_time AS first_purchase_time,
        MIN(CASE 
            WHEN sp.purchase_time > sc.confirmation_time AND t2.points_used > 0 
            THEN sp.purchase_time 
        END) AS first_points_use_time,
        CASE 
            WHEN sc.confirmation_time IS NOT NULL THEN '利用規約確認'
            || CASE 
                WHEN sp.purchase_time IS NOT NULL THEN '、購入経験あり' 
            END
            || CASE 
                WHEN MIN(CASE 
                    WHEN sp.purchase_time > sc.confirmation_time AND t2.points_used > 0 
                    THEN sp.purchase_time 
                END) IS NOT NULL THEN '、ポイント利用あり' 
            END
            ELSE 'なし'
        END AS member_status
    FROM 
        Selected_Confirmation sc
    LEFT JOIN 
        Selected_Purchase sp
    ON 
        sc.member_id = sp.member_id
    LEFT JOIN 
        table2 t2
    ON 
        sc.member_id = t2.member_id
    GROUP BY 
        sc.member_id, sc.confirmation_time, sp.purchase_time
)
-- 必要なデータを取得
SELECT 
    sc.user_id AS user_unique_id,
    ms.member_id,
    ms.member_status,
    ms.first_confirmation_time,
    ms.first_purchase_time,
    ms.first_points_use_time
FROM 
    Member_Status ms
LEFT JOIN 
    Selected_Confirmation sc
ON 
    ms.member_id = sc.member_id;
修正ポイント

1.Selected_ConfirmationのメンバーIDを基準に、そのメンバーのみで後続イベントを評価します。

2.これにより、購入イベントとポイント利用は一致したメンバーIDに限定され、ユーザーとメンバーが1対1の関係を保持します。

3.最後のMember_Statusで、ユーザーのステータスと初回イベント情報を算出しています。

この修正版SQLは、要件に基づいてユーザーとメンバーの一貫した関係性を確保します。

3回目の入力このロジックだと利用規約確認イベントで紐づかないユーザは購入イベントでも紐づかないです。必ずしもテーブル１に利用規約確認イベントが登録されない可能性もあるので、購入イベントのみでも紐づくようにしてもらいたいです

3回目の出力確かにその点は考慮が不足していました。利用規約確認イベントが登録されていない場合でも購入イベントのみで紐づけを行えるように修正します。この修正版では、次のようにロジックを調整します：
修正ポイント

1.購入イベントで独立して紐づけを行う:
利用規約確認イベントがない場合でも、購入イベントで紐づけを試みます。
2.全体の紐づけロジックを統一:
利用規約確認イベントがある場合はそのメンバーを基準に購入イベントを紐づけ。
利用規約確認イベントがない場合は購入イベントを単独で紐づけ。
以下は修正版のSQLです。
WITH Closest_Confirmation AS (
    -- 利用規約確認イベントと確認日時を最も近いものに紐づける
    SELECT 
        t1.user_id AS user_id,
        t2.member_id AS member_id,
        t2.confirmation_time AS confirmation_time,
        ABS(UNIX_TIMESTAMP(t1.timestamp) - t2.confirmation_time) AS time_diff
    FROM 
        table1 t1
    INNER JOIN 
        table2 t2
    ON 
        t1.event_name = '利用規約確認'
    ORDER BY 
        user_id, member_id, time_diff
),
Ranked_Confirmation AS (
    -- 各ユーザーごとに最も近い確認日時を選ぶ
    SELECT 
        user_id,
        member_id,
        confirmation_time,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY time_diff ASC) AS rank
    FROM 
        Closest_Confirmation
),
Selected_Confirmation AS (
    -- 上位1件のみを抽出
    SELECT 
        user_id,
        member_id,
        confirmation_time
    FROM 
        Ranked_Confirmation
    WHERE 
        rank = 1
),
Closest_Purchase AS (
    -- 購入イベントを紐づけ（利用規約確認がない場合でも考慮）
    SELECT 
        t1.user_id AS user_id,
        t2.member_id AS member_id,
        t2.purchase_time AS purchase_time,
        ABS(UNIX_TIMESTAMP(t1.timestamp) - t2.purchase_time) AS time_diff
    FROM 
        table1 t1
    INNER JOIN 
        table2 t2
    ON 
        t1.event_name = '購入'
        AND t1.event_detail LIKE '%かめ%'
    ORDER BY 
        user_id, member_id, time_diff
),
Ranked_Purchase AS (
    -- 各ユーザーごとに最も近い購入日時を選ぶ
    SELECT 
        user_id,
        member_id,
        purchase_time,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY time_diff ASC) AS rank
    FROM 
        Closest_Purchase
),
Selected_Purchase AS (
    -- 上位1件のみを抽出
    SELECT 
        user_id,
        member_id,
        purchase_time
    FROM 
        Ranked_Purchase
    WHERE 
        rank = 1
),
Mapped_Members AS (
    -- 利用規約確認イベントまたは購入イベントで紐づいたメンバーを集約
    SELECT 
        COALESCE(sc.user_id, sp.user_id) AS user_id,
        COALESCE(sc.member_id, sp.member_id) AS member_id,
        sc.confirmation_time,
        sp.purchase_time
    FROM 
        Selected_Confirmation sc
    FULL OUTER JOIN 
        Selected_Purchase sp
    ON 
        sc.user_id = sp.user_id
),
Member_Status AS (
    -- 各メンバーの初回確認日時、初回購入日時、初回ポイント利用日時を計算
    SELECT 
        mm.member_id,
        MIN(mm.confirmation_time) AS first_confirmation_time,
        MIN(mm.purchase_time) AS first_purchase_time,
        MIN(CASE 
            WHEN t2.points_used > 0 THEN mm.purchase_time
        END) AS first_points_use_time,
        CASE 
            WHEN MIN(mm.confirmation_time) IS NOT NULL THEN '利用規約確認'
            || CASE 
                WHEN MIN(mm.purchase_time) IS NOT NULL THEN '、購入経験あり' 
            END
            || CASE 
                WHEN MIN(CASE 
                    WHEN t2.points_used > 0 THEN mm.purchase_time 
                END) IS NOT NULL THEN '、ポイント利用あり' 
            END
            ELSE 'なし'
        END AS member_status
    FROM 
        Mapped_Members mm
    LEFT JOIN 
        table2 t2
    ON 
        mm.member_id = t2.member_id
    GROUP BY 
        mm.member_id
)
-- 必要なデータを取得
SELECT 
    mm.user_id AS user_unique_id,
    ms.member_id,
    ms.member_status,
    ms.first_confirmation_time,
    ms.first_purchase_time,
    ms.first_points_use_time
FROM 
    Member_Status ms
LEFT JOIN 
    Mapped_Members mm
ON 
    ms.member_id = mm.member_id;
修正版の特徴

1.Mapped_Membersで、利用規約確認イベントが紐づかない場合でも購入イベントでの紐づけを考慮します。

2.FULL OUTER JOINを使用して、利用規約確認または購入イベントのどちらかで紐づいたデータを集約します。

3.必要に応じて確認イベントと購入イベントを統一的に扱います。

これにより、利用規約確認が登録されていないユーザーも購入イベントを基に紐づけられます。

4回目の入力ROW_NUMBER()でrankを計算するのはBigQueryではあまり計算効率が良くないと聞いたことがあります。ROW_NUBER()の代わりにARRAY_AGGでOFFSET(0)を取る方法で実装するとどうなりますか？

4回目の出力BigQueryでは、ROW_NUMBER()を使ったランキングの代わりに、ARRAY_AGG()を使用して最小値や特定の条件に基づく1件を抽出する方法が一般的です。このアプローチは計算効率が良いだけでなく、クエリ全体の構造を簡潔に保てる利点があります。
以下に、ROW_NUMBER()をARRAY_AGG()に置き換えたSQLの例を示します。
WITH Closest_Confirmation AS (
    -- 利用規約確認イベントと確認日時を最も近いものに紐づける
    SELECT 
        t1.user_id AS user_id,
        t2.member_id AS member_id,
        t2.confirmation_time AS confirmation_time,
        ABS(UNIX_TIMESTAMP(t1.timestamp) - t2.confirmation_time) AS time_diff
    FROM 
        table1 t1
    INNER JOIN 
        table2 t2
    ON 
        t1.event_name = '利用規約確認'
),
Selected_Confirmation AS (
    -- 各ユーザーごとに最も近い確認日時を持つメンバーを選択
    SELECT 
        user_id,
        ARRAY_AGG(STRUCT(member_id, confirmation_time) ORDER BY time_diff ASC LIMIT 1)[OFFSET(0)].member_id AS member_id,
        ARRAY_AGG(STRUCT(member_id, confirmation_time) ORDER BY time_diff ASC LIMIT 1)[OFFSET(0)].confirmation_time AS confirmation_time
    FROM 
        Closest_Confirmation
    GROUP BY 
        user_id
),
Closest_Purchase AS (
    -- 購入イベントを紐づけ（利用規約確認がない場合でも考慮）
    SELECT 
        t1.user_id AS user_id,
        t2.member_id AS member_id,
        t2.purchase_time AS purchase_time,
        ABS(UNIX_TIMESTAMP(t1.timestamp) - t2.purchase_time) AS time_diff
    FROM 
        table1 t1
    INNER JOIN 
        table2 t2
    ON 
        t1.event_name = '購入'
        AND t1.event_detail LIKE '%かめ%'
),
Selected_Purchase AS (
    -- 各ユーザーごとに最も近い購入日時を持つメンバーを選択
    SELECT 
        user_id,
        ARRAY_AGG(STRUCT(member_id, purchase_time) ORDER BY time_diff ASC LIMIT 1)[OFFSET(0)].member_id AS member_id,
        ARRAY_AGG(STRUCT(member_id, purchase_time) ORDER BY time_diff ASC LIMIT 1)[OFFSET(0)].purchase_time AS purchase_time
    FROM 
        Closest_Purchase
    GROUP BY 
        user_id
),
Mapped_Members AS (
    -- 利用規約確認イベントまたは購入イベントで紐づいたメンバーを集約
    SELECT 
        COALESCE(sc.user_id, sp.user_id) AS user_id,
        COALESCE(sc.member_id, sp.member_id) AS member_id,
        sc.confirmation_time,
        sp.purchase_time
    FROM 
        Selected_Confirmation sc
    FULL OUTER JOIN 
        Selected_Purchase sp
    ON 
        sc.user_id = sp.user_id
),
Member_Status AS (
    -- 各メンバーの初回確認日時、初回購入日時、初回ポイント利用日時を計算
    SELECT 
        mm.member_id,
        MIN(mm.confirmation_time) AS first_confirmation_time,
        MIN(mm.purchase_time) AS first_purchase_time,
        MIN(CASE 
            WHEN t2.points_used > 0 THEN mm.purchase_time
        END) AS first_points_use_time,
        CASE 
            WHEN MIN(mm.confirmation_time) IS NOT NULL THEN '利用規約確認'
            || CASE 
                WHEN MIN(mm.purchase_time) IS NOT NULL THEN '、購入経験あり' 
            END
            || CASE 
                WHEN MIN(CASE 
                    WHEN t2.points_used > 0 THEN mm.purchase_time 
                END) IS NOT NULL THEN '、ポイント利用あり' 
            END
            ELSE 'なし'
        END AS member_status
    FROM 
        Mapped_Members mm
    LEFT JOIN 
        table2 t2
    ON 
        mm.member_id = t2.member_id
    GROUP BY 
        mm.member_id
)
-- 必要なデータを取得
SELECT 
    mm.user_id AS user_unique_id,
    ms.member_id,
    ms.member_status,
    ms.first_confirmation_time,
    ms.first_purchase_time,
    ms.first_points_use_time
FROM 
    Member_Status ms
LEFT JOIN 
    Mapped_Members mm
ON 
    ms.member_id = mm.member_id;
主な変更点と説明

1.ARRAY_AGGで最小値を取得:
ARRAY_AGG(STRUCT(...)) ORDER BY time_diff ASC LIMIT 1で、各ユーザーの最も近いメンバーやイベントを1件取得します。
OFFSET(0)でリストから最初の要素を取り出します。
2.GROUP BYで効率化:
ユーザー単位でグループ化して最小値を計算するため、計算負荷が分散されます。
3.効率的なデータ処理:
ROW_NUMBER()を使わず、BigQueryのネイティブなARRAY_AGGを用いることで、クエリ実行速度が向上します。
この修正版では、ROW_NUMBER()の代替として効率的かつ簡潔にイベントの最小値を取得できます。BigQuery特有の最適化手法を活用する形になっていますので、実行速度が向上することが期待されます。

レビュー時の入力私もこのテーマでSQLを実装しました。どのような問題があるか教えて下さい
WITH confirmation_mapping AS (
  SELECT
    user_id,
    timestamp,
    member_id,
    ABS(confirm_time - timestamp / 1000000) AS time_diff,
    confirm_time,
  FROM table1
  INNER JOIN table2
  ON ABS(confirm_time - timestamp / 1000000) < 60
  WHERE event_name = '利用規約確認'
),
-- 時間差が最小の1件のみを取得
confirmation_mapping_one AS (
  SELECT c.*
  FROM (
    SELECT ARRAY_AGG(cm ORDER BY time_diff LIMIT 1)[OFFSET(0)] c
    FROM (
      SELECT ARRAY_AGG(cma ORDER BY time_diff) cm
      FROM confirmation_mapping cms
      GROUP BY user_id, timestamp
    )
    GROUP BY member_id
  )
),
-- ユーザーごとに最初の1件のみに絞る
confirmation_one_user AS (
  SELECT c.*
  FROM (
    SELECT ARRAY_AGG(cm ORDER BY timestamp LIMIT 1)[OFFSET(0)] c
    FROM confirmation_mapping_one cm
    GROUP BY user_id
  )
),
-- 購入イベントと紐づけ
purchase_mapping AS (
  SELECT
    user_id,
    timestamp,
    member_id,
    ABS(purchase_time - timestamp / 1000000) AS time_diff,
    confirm_time
  FROM table1
  INNER JOIN table2
  ON ABS(confirm_time - timestamp / 1000000) < 60
  WHERE event_name = '購入'
),
-- 時間差が最小の1件のみを取得
purchase_mapping_one AS (
  SELECT p.*
  FROM (
    SELECT ARRAY_AGG(pm ORDER BY time_diff LIMIT 1)[OFFSET(0)] p
    FROM (
      SELECT ARRAY_AGG(pma ORDER BY time_diff) pm
      FROM purchase_mapping pma
      GROUP BY user_id, timestamp
    )
    GROUP BY member_id
  )
),
-- ユーザーごとに最初の1件のみに絞る
purchase_mapping_one_user AS (
  SELECT p.*
  FROM (
    SELECT ARRAY_AGG(tm ORDER BY timestamp LIMIT 1)[OFFSET(0)] p
    FROM purchase_mapping_one pm
    GROUP BY user_id
  )
),
-- 2つの条件を結合
merge_mappings AS (
  SELECT
    user_id,
    COALESCE(c.timestamp, p.timestamp) AS timestamp,
    COALESCE(c.member_id, p.member_id) AS member_id,
    COALESCE(c.confirm_time, p.confirm_time) AS confirm_time
  FROM confirmation_one_user c
  FULL OUTER JOIN purchase_mapping_one_user p
  USING(user_id)
),
-- メンバーごとに最初の1件のみに絞る
merge_mappings_one AS (
  SELECT m.*
  FROM (
    SELECT ARRAY_AGG(mm ORDER BY confirm_time LIMIT 1)[OFFSET(0)] m
    FROM merge_mappings mm
    GROUP BY member_id
  )
),
-- テーブル2と紐づけ
table2_member AS (
  SELECT
    m.member_id,
    m.user_id,
    m.timestamp,
    m.confirm_time,
    t2.purchase_time,
    t2.points_used
  FROM merge_mappings_one m
  LEFT JOIN table2 t2
  USING(member_id)
  WHERE m.confirm_time <= t2.purchase_time OR t2.purchase_time IS NULL
),
purchase_agg_table AS (
  SELECT
    member_id,
    user_id,
    timestamp,
    confirm_time,
    MIN(purchase_time) AS first_purchase_time
  FROM table2_member
  GROUP BY member_id, user_id, timestamp, confirm_time
)
points_agg_table AS (
  SELECT
    purchase_agg_table.*,
    first_points_time
  FROM purchase_agg_table
  LEFT JOIN (
    SELECT
      member_id,
      user_id,
      timestamp,
      confirm_time,
      MIN(purchase_time) AS first_points_time
    FROM table2_member
    WHERE max_points_used > 0
    GROUP BY member_id, user_id, timestamp, confirm_time
  )
)
SELECT
  member_id,
  user_id,
  timestamp,
  confirm_time,
  first_purchase_time,
  first_points_time,
  CASE WHEN first_points_time IS NOT NULL THEN "ポイント利用あり"
    WHEN first_purchase_time IS NOT NULL THEN "購入経験あり"
    ELSE "利用規約確認"
    END AS member_status
FROM points_agg_table

レビュー時の出力あなたが提示したSQLには、多くの論理が適切に組み込まれていますが、いくつかの潜在的な問題点や改善点があります。以下に詳しく説明します。
問題点と改善点
複雑でネストが深いクエリ

クエリのネストが非常に深く、可読性が低下しています。

ARRAY_AGGを使ったネストが多重に使用されており、構造が理解しづらい。

例: confirmation_mapping_oneのサブクエリでARRAY_AGGを二重に使う部分は冗長です。

改善案:
必要以上にネストせず、WITH句を増やしてステップごとに分割する。

各ステップで計算されたデータを再利用する形に整理する。

2. 同じロジックの重複

confirmation_mappingとpurchase_mappingでほぼ同じ処理を行っています。

繰り返しのコードがあると、メンテナンス性が低下します。

改善案:
event_nameを変数化するか、動的に扱うことで共通化する。

たとえば、一度に全イベントを対象にして後からフィルタリングする方法を検討できます。

3. 結合条件の潜在的な欠陥

ABS(confirm_time - timestamp / 1000000) < 60の条件は、時間差が秒単位で60秒以内のデータを結合していますが、最も近いデータを取得するロジックが完全ではない可能性があります。

結合条件だけでデータが複数件返される場合、意図せず同一ユーザーが複数メンバーに紐づけられるリスクがあります。

改善案:
まず、全候補を取得し、その中から時間差が最小のものを選ぶ明確なロジックに変更します。

条件付き結合での曖昧さを避けるため、ARRAY_AGGで直接最小値を取得する方法を使います。

4. 非効率なグループ化の使い方

サブクエリ内で頻繁にGROUP BYとARRAY_AGGを繰り返し使用しています。

特にGROUP BY user_id, timestampなどが何度も登場しており、計算が重複している可能性があります。

改善案:
最初のconfirmation_mappingやpurchase_mappingで最小値を計算し、以降の処理では再利用する形に整理します。

5. フルアウトER結合の使い方

merge_mappingsでFULL OUTER JOINを使用していますが、このステップは無駄な行を生む可能性があります。

confirmation_one_userとpurchase_mapping_one_userがそれぞれの段階で一意な行を選択している場合、FULL OUTER JOINは冗長です。

改善案:
LEFT JOINかUNIONを適切に使用して、結合ロジックを簡潔にする。

6. ポイント利用日時の取得ロジックの曖昧さ

ポイント利用日時（first_points_time）の取得ロジックにおいて、max_points_used > 0の条件を使用していますが、明確ではありません。

条件が適切に評価されない場合、誤ったデータが返される可能性があります。

改善案:
ポイント利用の条件を明確に定義し、可能であれば事前にフィルタリングを行います。

7. 可読性を意識したリネーミング

merge_mappings_oneなど、一部のテーブル名が具体的でないため可読性が低いです。

テーブル名や列名を見ただけで意味が分かるようにするべきです。

改善案:
テーブル名を意味のある名前に変更する（例: mapped_events、final_resultsなど）。
改善後のクエリ例（部分）

以下は、改善案を取り入れた一部のサンプルクエリです。
WITH Events_Mapping AS (
  SELECT
    t1.user_id,
    t1.timestamp,
    t2.member_id,
    t2.confirm_time,
    t2.purchase_time,
    t2.points_used,
    t1.event_name,
    ABS(t2.confirm_time - t1.timestamp / 1000000) AS confirm_time_diff,
    ABS(t2.purchase_time - t1.timestamp / 1000000) AS purchase_time_diff
  FROM table1 t1
  LEFT JOIN table2 t2
  ON ABS(t2.confirm_time - t1.timestamp / 1000000) < 60
    OR ABS(t2.purchase_time - t1.timestamp / 1000000) < 60
),
Ranked_Events AS (
  SELECT
    user_id,
    member_id,
    event_name,
    MIN(IF(event_name = '利用規約確認', confirm_time_diff, purchase_time_diff)) AS min_time_diff,
    MIN(confirm_time) AS first_confirm_time,
    MIN(purchase_time) AS first_purchase_time,
    MIN(CASE WHEN points_used > 0 THEN purchase_time END) AS first_points_time
  FROM Events_Mapping
  GROUP BY user_id, member_id, event_name
),
Final_Results AS (
  SELECT
    user_id,
    member_id,
    first_confirm_time,
    first_purchase_time,
    first_points_time,
    CASE
      WHEN first_points_time IS NOT NULL THEN "ポイント利用あり"
      WHEN first_purchase_time IS NOT NULL THEN "購入経験あり"
      ELSE "利用規約確認"
    END AS member_status
  FROM Ranked_Events
)
SELECT * FROM Final_Results;
このクエリの特徴

可読性を向上し、ネストを浅く整理。

GROUP BYを最小化し、効率的に最小値を取得。

ロジックを統一化して冗長なコードを削除。

必要であれば、さらに詳細な改善を加えることが可能です。


 やってみた感想当たり前ですが最初にチャットでの質問から始まるので、その前に仕様をちゃんと決める必要があります。見切り発車でも気付かずに進み後で認識が誤っていたということもよくあるのですが、生成AIを用いる際はより注意深く意識する必要があります。
自力でSQLを書きたくない気分の場合は代わりに書いてもらうことも可能に思えました。ただし人力でのレビューは必要なので他人のSQLを読みたい気分の時に向いていそうです。SQLを読みたくも書きたくも無いときはそもそもこのような作業自体が向いてないですね。
個人的にはChatGPTの書いたSQLの方がClaudeよりも読みやすく感じましたが、問題設定やモデルの進化によっても変わるかもしれないです。ちなみにGeminiは長文のSQLを自分で書きたくない雰囲気がプンプンしていたので、専用のやる気スイッチ（ならぬスクリプト）が必要な気がしました。
今回は検証のために2種類の生成AIを試しながら比較しましたが、レビューの観点とダメ出し数が2倍になったように思えました。そのため品質を高めたい場合と寂しくて孤独な時には有用です。一方でスピード重視のときやコミュニケーションに疲れているシーンでは1つでも十分です。
会話する度にSQLを生成し直してくれるのですが、どの部分が変わったか差分が見づらいと感じました。クエリに添えて変更点を日本語で説明してくれるのでそこから読み解けるのですが、GitHubのchangesのような見た目になってほしいですね。

 今後試したいことそもそも仕様を言語化するタイミングで間違えると手戻りも大きくなり辛いです。なので言語化の部分を生成AIで試すのもアリだと思い、例えば元のデータセットと欲しいデータだけ示して仕様を言語化するという実験も面白そうに思えました。まだアドベントカレンダーは始まったばかりなので、もし機会があったら2025年あたりに書くかもしれないです〜
株式会社ガラパゴス（有志）Publication
株式会社ガラパゴス（有志）株式会社ガラパゴスのエンジニアを中心に情報発信をしている Publication です。各記事の内容は個人の意見であり、企業を代表するものではございません。
背景

試したこと

実行結果

やってみた感想

今後試したいこと

Discussion
