SQLについて

以下の内容を実現するには、どのようなSQLを記述すれば、実現可能でしょうか?
SQLが分かる方、よろしくお願いいたします。

table1
+---------+------------+------------------+
| rank | order_n_id | application_date |
+---------+------------+------------------+
| 1 | 196161 | 2019/8/23 |
| 2 | 196161 | 2019/11/5 |
| 3 | 196161 | 2019/12/20 |
| 4 | 279460 | 2019/9/3 |
| 5 | 337280 | 2019/9/18 |
| 6 | 337280 | 2019/11/6 |
| 7 | 337280 | 2019/11/12 |
+---------+------------+------------------+
          ↓

+---------+------------+--------------------------+
| rank | order_n_id | application_date | LT |
+---------+------------+--------------------------+
| 1 | 196161 | 2019/8/23 | 1 |
| 2 | 196161 | 2019/11/5 | 74 |
| 3 | 196161 | 2019/12/20 | 45 |
| 4 | 279460 | 2019/9/3 | 1 |
| 5 | 337280 | 2019/9/18 | 1 |
| 6 | 337280 | 2019/11/6 | 49 |
| 7 | 337280 | 2019/11/12 | 6 |
+---------+------------+------------------+-------+


+------------+--------+
| order_n_id | 平均LT |
+------------+--------+
| 196161 | 59.5 |
| 279460 | 1 |
| 337280 | 27.5 |
+------------+--------+

※rank:連番
※LT:経過日数
※LT=1とは初回申込という定義

【実現したいこと】
①同じorder_n_id内での経過日数を「LT」として出力したい

②order_n_idでgroup byした場合、1を除く、LTの平均値を出力したい
196161であれば、(74+45)/2=59.5

Comments

  • Redshift Dataflowが利用できることが前提になりますが、以下で実現できると思います。

     

    ①LAGウインドウ関数を利用すると、ひとつ上のレコードの値を参照できるので

    以下のような形でデータを取得します。

    +---------+------------+------------------+
    | rank | order_n_id | application_date |application_date_prev|
    +---------+------------+------------------+
    | 1 | 196161 | 2019/8/23 |null|
    | 2 | 196161 | 2019/11/5 | 2019/8/23|
    | 3 | 196161 | 2019/12/20 |2019/11/5|
    | 4 | 279460 | 2019/9/3 |null|
    | 5 | 337280 | 2019/9/18 |null|
    | 6 | 337280 | 2019/11/6 |2019/9/18|
    | 7 | 337280 | 2019/11/12 |2019/11/6|

     

    あとはDATEDIFF 関数とcase文を使えば取得したい形になると思います。

    https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_Examples_of_LAG_WF.html

     

    ②Countウインドウ関数で、order_n_id毎の件数を各レコードに付与し、

     where句で件数が2件以上かつLTが1のものを除外しAVG(LT)で算出できると思います。

     +---------+------------+--------------------------+
    | rank | order_n_id | application_date | LT |count|
    +---------+------------+--------------------------+
    | 1 | 196161 | 2019/8/23 | 1 |3|  →除外
    | 2 | 196161 | 2019/11/5 | 74 |3|
    | 3 | 196161 | 2019/12/20 | 45 |3|
    | 4 | 279460 | 2019/9/3 | 1 |1|
    | 5 | 337280 | 2019/9/18 | 1 |3|→除外
    | 6 | 337280 | 2019/11/6 | 49 |3|
    | 7 | 337280 | 2019/11/12 | 6 |3|

  • こんな感じですかね。Redshift環境です。


    WITH tmp1 AS
    ( SELECT
    rank
    ,ROW_NUMBER() OVER( PARTITION BY order_n_id ORDER BY application_date) AS row_num
    ,order_n_id
    ,application_date
    FROM table1
    )
    SELECT
    t1.rank
    ,t1.order_n_id
    ,t1.application_date
    ,CASE
    WHEN t1.row_num = 1 THEN 1
    ELSE DATEDIFF(DAY,t2.application_date,t1.application_date)
    END AS LT
    FROM tmp1 t1
    LEFT JOIN tmp1 t2
    ON t1.order_n_id = t2.order_n_id
    AND t1.row_num = t2.row_num + 1
    ORDER BY 1,2;


    WITH tmp1 AS
    ( SELECT
    rank
    ,ROW_NUMBER() OVER( PARTITION BY order_n_id ORDER BY application_date) AS row_num
    ,order_n_id
    ,application_date
    FROM table1
    ),
    tmp2 AS
    ( SELECT
    t1.rank
    ,t1.order_n_id
    ,t1.application_date
    ,CASE
    WHEN t1.row_num = 1 THEN 1
    ELSE DATEDIFF(DAY,t2.application_date,t1.application_date)
    END AS LT
    FROM tmp1 t1
    LEFT JOIN tmp1 t2
    ON t1.order_n_id = t2.order_n_id
    AND t1.row_num = t2.row_num + 1
    ),
    tmp3 AS
    ( SELECT order_n_id,AVG(lt * 1.0) AS lt
    FROM ( SELECT * FROM tmp2 WHERE LT <> 1 AND row_num <> 1)
    GROUP BY order_n_id
    )
    SELECT
    tmp4.order_n_id
    ,CASE
    WHEN tmp3.lt IS NULL THEN 1
    ELSE tmp3.lt
    END AS lt
    FROM ( SELECT DISTINCT order_n_id FROM tmp1 ) tmp4
    LEFT JOIN tmp3
    ON tmp4.order_n_id = tmp3.order_n_id
    ;
  • コメントありがとうございます。
    残念ながら、Redshift Dataflowが利用できる環境下ではありません。
    また、当方がSQL初心者のため、お手数ですが、通常のMy SQLで構築する場合、どのようなソース文になるか、ご教授いただけますと幸いです。

  • MySQL Dataflowではウインドウ関数が利用できないので、難しいですね。

    Magic ETLでランクとウィンドウ処理を利用すると実現できるかもしれません。

    お役にたてずすみません。

  • 「LTの合計値」は、間で何回更新されようと 初日から日付最大日までの経過日数ですよね!?
    なので、全てのレコード相互間のLTをいちいち算出する必要は無く
    最大日と最小日の差を更新回数で割れば平均値となるでしょう。
     
    順に書きますと
    `transform_data_1` id毎にグループ化して最小日・最大日・レコード数を抽出する
     
    SELECT `order_n_id`,
        MIN(`application_date`) AS 'min_date',
        MAX(`application_date`) AS 'max_date',
        COUNT(`order_n_id`) AS 'count'
    FROM `table1`
    GROUP BY `order_n_id`;
     
    `transform_data_1`の結果
    order_n_id | min_date | max_date | count
    196161 | 2019年8月23日 | 2019年12月20日| 3
    279460 | 2019年9月 3日 | 2019年 9月 3日| 1
    337280 | 2019年9月18日 | 2019年11月12日| 3
     
     
    `transform_data_2` 最大日と最小日の間隔日数を求める
     
    SELECT
        `order_n_id`,
        DATEDIFF(`max_date`,`min_date`) AS 'LT_sum',
        `count`
    FROM `transform_data_1`
     
    `transform_data_2`の結果 ※初日も更新回数にカウントされるが日数の差が 0なので総間隔日数には影響しない
    order_n_id | LT_sum | count
    196161 | 119 | 3
    279460 |   0 | 1
    337280 |  55 | 3
     
     
    `output_dataset_1` 間隔日数を更新回数(レコード数 -1 )で割る
              但し、レコード数=1(つまり更新回数 0回)の場合の平均値は常に 1とする
    SELECT
        `order_n_id`,
        CASE
            WHEN `count` = 1 THEN 1
            ELSE `LT_sum` / (`count`-1)
        END AS 'LT_AVG'
    FROM `transform_data_2`
     
    `output_dataset_1`の結果
    order_n_id | LT_AVG
    196161 | 59.5000
    279460 |  1.0000
    337280 | 27.5000
     
    ------------------------------------------------
    SQL初心者とのことなのでこの3つを順に書いておいたほうが後々わかりやすそうですが、
    全体を1つのSQLでまとめるとこうなります。
     
    SELECT
     `order_n_id`,
        CASE
         WHEN  COUNT(`order_n_id`)  = 1 THEN 1
            ELSE DATEDIFF(MAX(`application_date`),MIN(`application_date`))/(COUNT(`order_n_id`)-1)
        END AS 'LT_AVG'
    FROM `table1`
    GROUP BY `order_n_id`
    ------------------------------------------------