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

タグが付けられた投稿をさらに検索

skaskakiz_IMJ

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|

satoo

こんな感じですかね。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
;

tokupon

コメントありがとうございます。
残念ながら、Redshift Dataflowが利用できる環境下ではありません。
また、当方がSQL初心者のため、お手数ですが、通常のMy SQLで構築する場合、どのようなソース文になるか、ご教授いただけますと幸いです。

skaskakiz_IMJ

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

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

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

„É¶„Éº„Ç∂„Éº02879

「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初心者とのことなのでこの３つを順に書いておいたほうが後々わかりやすそうですが、

全体を１つの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`

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－