時系列転位修復ケースを実装するSQL

1. 要件の説明

1 元のテーブル T1 のレコードの次の行の STARTDATE (r1 として記憶され、次の行は r2) が、前の行の ENDDATE よりも小さくなります。このようなレコードの場合、変換は次のように実行されます。

r1のSTARTDATE変更されず、 ENDDATE為r1 STARTDATE-1になります。

r2のS STARTDATE r1のENDDATEであり、 ENDDATE為r1のENDDATEである。

2 元のテーブル T1 の隣接する行に「時間の重複」がない場合 (つまり、1 の定義)、元のデータを変更せずに保持します。

 # テキスト版#T1
シーケンスID 開始日 終了日 番号
1 1 2021-04-20 2021-05-03 200
2 1 2021-05-01 2021-05-24 100
3 1 2021-05-18 2021-05-31 69
4 1 2021-05-20 2021-07-31 34
5 1 2021-08-05 2021-08-25 45
6 1 2021-08-15 2021-09-25 65
 
 
# 出力結果 ID STARTDATE ENDDATE NUM
1 2021-04-20 2021-04-30 200
1 2021-05-01 2021-05-02 300
1 2021-05-03 2021-05-17 100
1 2021-05-18 2021-05-19 169
1 2021-05-20 2021-05-23 203
1 2021-05-24 2021-05-30 103
1 2021-05-31 2021-07-30 34
1 2021-08-05 2021-08-14 45
1 2021-08-15 2021-08-25 110
1 2021-08-26 2021-09-25 65

2. アイデアの概要

1. 延長を要求する

シーケンスID 開始日 終了日 番号
1 1 2021-04-20 2021-05-03 200
2 1 2021-05-01 2021-05-24 100
3 1 2021-05-18 2021-05-31 69
4 1 2021-05-20 2021-07-31 34

ここでは、4 番目のレコードが 2 番目と 3 番目のレコードに重ねられています。

2. アイデアの概要

1) T0はアップ関数とダウン関数によって生成される時系列である

id new_DATE nextSTARTDATE preEndDATE rn      
1 2021-05-24 2021-05-03 1
1 2021-05-03 2021-05-24 2021-05-01 2
1 2021-05-01 2021-05-03 2021-04-20 3
1 2021-04-20 2021-05-01 4

2) 後続の修正に備えて、T0 の最後のレコードを取得します。

new_Date preENDDATE id
2021-05-24 2021-05-03 1

3) 通常は、後続の修正に備えて、元のデータで時間の重複がないレコードを抽出します。
現在のデモデータの記録はありませんが、コメント付きのコードは表示されます。

4) T_SerialではSTARTDATEとENDDATEの定義を統一し、T0を初めて改訂しました。

id 開始日 終了日
1 2021-04-20 2021-04-30
1 2021-05-01 2021-05-03
1 2021-05-04 2021-05-24

5) T2 は時間的に重複しないレコードを修正します (T0 の対応する値を削除し、対応する ENDDATE を更新します)。
現在の例の結果セットは空なので、修正は必要ありません。

6) T2 は T1 (元のテーブル) に関連付けられ、集計後に最終値が得られます。

開始日 終了日 数値
2021-04-20 2021-04-30 200
2021-05-01 2021-05-03 300
2021-05-04 2021-05-24 100

3. SQLコード

現在のデモバージョンは MySQL 8.0.23 です。CTE およびウィンドウ関数をサポートするSQL ServerおよびOracleでは、 Order byおよびADDDATEの構文を変更する必要があります。
ステップ0 テーブルを作成し、データを初期化する

存在する場合はテーブルを削除します test_ShenLiang2025;
テーブルtest_ShenLiang2025を作成します（
  seq int デフォルト NULL、
  id int デフォルト NULL、
  STARTDATE 日付 デフォルト NULL、
  ENDDATE 日付 デフォルト NULL、
  数値 int デフォルト NULL
)ENGINE=InnoDB デフォルト文字セット=utf8;
 
test_ShenLiang2025 に値 ('1'、'1'、'2021-04-20'、'2021-05-03'、'200') を挿入します。
test_ShenLiang2025 に値 ('2'、'1'、'2021-05-01'、'2021-05-24'、'100') を挿入します。
test_ShenLiang2025 に値 ('3'、'1'、'2021-05-18'、'2021-05-31'、'69') を挿入します。
test_ShenLiang2025 に値 ('4'、'1'、'2021-05-20'、'2021-07-31'、'34') を挿入します。
test_ShenLiang2025 に値 ('5'、'1'、'2021-08-05'、'2021-08-25'、'45') を挿入します。
test_ShenLiang2025 に値 ('6'、'1'、'2021-08-15'、'2021-09-25'、'65') を挿入します。

ステップ 1 時系列を生成するために一時的な結果セットを構築します。

T0 と(
SELECT ID、 
   新しい日付、
   LEAD(NEW_DATE,1) OVER (PARTITION BY ID ORDER BY NEW_DATE ) nextSTARTDATE、
   LAG(NEW_DATE,1) OVER (PARTITION BY ID ORDER BY NEW_DATE ) preENDDATE、
   ROW_NUMBER()OVER(PARTITION BY ID ORDER BY new_DATE DESC) rn
   から
  （
  DISTINCT ID、STARTDATE new_DATE を test_ShenLiang2025 から選択します。    
   WHERE seq in (1,2) - コメントを追加して、元のテーブル内の 2 つのレコードのみが取得されることを確認できます。
  DISTINCT ID、ENDDATE new_DATE を test_ShenLiang2025 から選択します。
   WHERE seq in (1,2) -- コメントを追加して、元のテーブルで現在 2 つのレコードのみが ORDER BY new_DATE で取得されていることを確認できます。 
  )あ
）、最後のAS
( SELECT new_DATE,preENDDATE,id
T0から 
nextSTARTDATEがNULLの場合
）、通常のAS
（
 *から選択
 （
 SELECT ID、 
    終了日、
    LEAD(STARTDATE,1) OVER (PARTITION BY ID ORDER BY ENDDATE ) nextSTARTDATE、
    LAG(ENDDATE,1) OVER (PARTITION BY ID ORDER BY ENDDATE ) preENDDATE
    test_ShenLiang2025より
 )あ
 ENDDATE > preENDDATE かつ ENDDATE < nextSTARTDATE の場合
),T_シリアルAS (
 
SELECT ID,ADDDATE(preENDDATE, INTERVAL 1 DAY) STARTDATE,
new_DATE 終了日
最後から 
 
連合
 
bottom_2.ID、bottom_2.new_DATE STARTDATEを選択します。
rn =3 の場合、bottom_2.nextSTARTDATE 
 それ以外の場合はADDDATE(bottom_2.nextSTARTDATE, INTERVAL -1 DAY) END ENDDATE
最後から 
JOIN T0 ボトム_2
bottom_2.nextSTARTDATE<=last.preENDDATE かつ bottom_2.id = last.id の場合
),T2 AS(
B.ID、B.STARTDATE、B.ENDDATEを選択
  （
   SELECT A.*,ROW_NUMBER()OVER(PARTITION BY ID,STARTDATE ORDER BY ENDDATE) rn
   から
   （
   A.ID、A.STARTDATE、A.ENDDATEを選択します
   T_シリアルAより
   LEFT JOIN 通常 B
   A.STARTDATE = B.ENDDATE かつ A.ID = B.ID の場合
   B.ENDDATEがNULLの場合
 
   連合 
    
   A.ID、A.STARTDATE、B.ENDDATEを選択します   
   T_シリアルAより
   INNER JOIN 通常 B
   ON ADDDATE(A.ENDDATE, INTERVAL 1 DAY) = B.ENDDATE かつ A.ID = B.ID    
   )あ
  )B ただしrn =1
）

ステップ 2: 時系列を元のテーブルに関連付けて、NUM フィールドを生成します。

T2からT2.STARTDATE、T2.ENDDATE、SUM(T1.NUM) TOTALを選択します。
test_ShenLiang2025 T1に参加する
T2.STARTDATE>=T1.STARTDATEの場合 
 かつ、T2.ENDDATE<=T1.ENDDATE
T2.STARTDATE、T2.ENDDATE でグループ化
T2.開始日で注文

ステップ4 結果を表示する