課題設定

market_dataテーブルには、取引日（trade_date）と株価の騰落フラグ（is_fall: 1=下落, 0=上昇）が記録されています。このデータから、各日付における連続下落日数を算出します。

入力テーブル: market_data

期待する出力結果

アプローチ1: 行番号の差分を利用する

連続する値をグループ化する際の定石として、全体の行番号と条件ごとの行番号の差分を利用する方法があります。最終的なクエリは以下の通りです。

SELECT
    trade_date,
    is_fall,
    CASE 
        WHEN is_fall = 0 THEN 0 
        ELSE ROW_NUMBER() OVER(PARTITION BY grp_id ORDER BY trade_date ASC) 
    END AS streak_len
FROM (
    SELECT 
        trade_date, 
        is_fall,
        ROW_NUMBER() OVER(ORDER BY trade_date DESC) - 
        ROW_NUMBER() OVER(PARTITION BY is_fall ORDER BY trade_date DESC) AS grp_id
    FROM market_data
) sub
ORDER BY trade_date DESC;

処理のステップごとの解説です。まず、日付の降順で全体の行番号（total_rn）と、is_fallごとの行番号（fall_rn）を取得します。

SELECT 
    trade_date, 
    is_fall,
    ROW_NUMBER() OVER(ORDER BY trade_date DESC) AS total_rn,
    ROW_NUMBER() OVER(PARTITION BY is_fall ORDER BY trade_date DESC) AS fall_rn
FROM market_data;

結果:

is_fall = 1の行に注目すると、連続している日付のグループではtotal_rnとfall_rnの差が一定になります。

SELECT 
    trade_date, 
    is_fall,
    CASE WHEN is_fall = 1 THEN
        ROW_NUMBER() OVER(ORDER BY trade_date DESC) - 
        ROW_NUMBER() OVER(PARTITION BY is_fall ORDER BY trade_date DESC)
    END AS grp_id
FROM market_data;

結果:

この差分をgrp_idとしてパーティションキーに利用し、is_fall = 1の場合のみそのグループ内で行番号を振ることで連続下落日数を求めます。

アプローチ2: 状態変化の検出と累積和を利用する

もう一つのアプローチとして、前行との比較で状態変化を検出し、その累積和でグループ化する方法があります。最終的なクエリは以下の通りです。

SELECT 
    trade_date, 
    is_fall,
    CASE 
        WHEN is_fall = 1 THEN ROW_NUMBER() OVER(PARTITION BY group_key ORDER BY trade_date ASC) 
        ELSE 0 
    END AS streak_len
FROM (
    SELECT 
        trade_date, 
        is_fall,
        SUM(is_boundary) OVER(ORDER BY trade_date DESC) AS group_key
    FROM (
        SELECT 
            trade_date, 
            is_fall,
            CASE 
                WHEN LAG(is_fall) OVER(ORDER BY trade_date DESC) = is_fall THEN 0 
                ELSE 1 
            END AS is_boundary
        FROM market_data
    ) t1
) t2
ORDER BY trade_date DESC;

ステップごとの解説です。まず、LAG関数を用いて前日のis_fallと比較します。値が変化していれば1（is_boundary）、同じであれば0とフラグを立てます。

SELECT 
    trade_date, 
    is_fall,
    CASE WHEN LAG(is_fall) OVER(ORDER BY trade_date DESC) = is_fall THEN 0 ELSE 1 END AS is_boundary
FROM market_data;

結果:

次に、この変化フラグの累積和を取ります。状態が変化するたびに累積和が増加するため、これが連続区間を表すグループキー（group_key）となります。

SELECT 
    trade_date, 
    is_fall,
    is_boundary,
    SUM(is_boundary) OVER(ORDER BY trade_date DESC) AS group_key
FROM (
    SELECT 
        trade_date, 
        is_fall,
        CASE WHEN LAG(is_fall) OVER(ORDER BY trade_date DESC) = is_fall THEN 0 ELSE 1 END AS is_boundary
    FROM market_data
) t1;

結果:

最後に、このgroup_keyでパーティションを切り、is_fall = 1の行に対して日付順に行番号を割り当てることで連続下落日数を計算します。