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

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

1. 要件の説明

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

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

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

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 結果を表示する

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

実行結果:

以上で、時系列ずれ修復のSQL実装事例の詳細説明は終了です。より関連性の高いSQL時系列ずれ修復生成事例コンテンツについては、123WORDPRESS.COMの過去の記事を検索するか、以下の関連記事を引き続き閲覧してください。今後とも123WORDPRESS.COMをよろしくお願いいたします。

以下もご興味があるかもしれません:
  • mysql と oracle のデフォルトのトランザクション分離レベルの説明
  • Mysql と Oracle でよく使用される複数テーブルの変更ステートメントの概要
  • 3つの主要データベース(Mysql、SqlServer、Oracle)の違いについて簡単に説明します。
  • mysql、mssql、oracle のページング クエリ メソッドの詳細な説明

<<:  ページ下部のフッターを修正する方法(複数の方法)

>>:  ElementUIテーブルのヘッダーアイコンにフローティングプロンプトを追加します。

推薦する

CSS 表示属性のインラインブロックレイアウト実装の詳細な説明

CSS 表示プロパティ注: !DOCTYPE が指定されている場合、Internet Explore...

Vueはスクロールロードテーブルを実装します

目次成果を達成する転がり荷重知識備蓄コンポーネントのパッケージ1. コンポーネントの命名2. 小道具...

Vueルーティングはページステータスを復元する操作メソッドを返します

ルートパラメータ、ルートナビゲーションガード: ページが戻ったときに検索結果を保持する需要シナリオ:...

Vue3 Vue CLI マルチ環境設定

目次1. はじめに2. 切り替え1. 開発および本番環境の設定ファイルを追加する2. 複数の環境をサ...

ウェブデザインにおけるグリッドシステム

グリッドシステムの形成1692年、新しく即位したフランス国王ルイ14世は、フランスの印刷技術のレベル...

Node8 における AsyncHooks 非同期ライフサイクル

Async Hooks は Node8 の新機能です。NodeJs の非同期リソースのライフサイクル...

MySQL サーバー 5.7.20 のインストールと設定方法のグラフィック チュートリアル

この記事ではMySQL 5.7.20のインストールと設定方法を記録し、皆さんと共有します1. MyS...

Vueはログインジャンプを実装する

この記事では、ログインジャンプを実装するためのVueの具体的なコードを例として紹介します。具体的な内...

Linux で MySQL をインストールする簡単な方法

Linux に MySQL をインストールする方法をオンラインで検索すると、多くの方法が表示されまし...

Alibaba Cloud OSS アクセス権設定(RAM 権限制御)実装

シナリオmyBuket の static/material/ ディレクトリなど、Alibaba Cl...

Linuxサーバ侵入緊急対応記録(概要)

最近、お客様から支援の依頼を受けました。管理されている通信コンピュータ ルームから、サーバーの 1 ...

Vueでキャッシュされたページを管理する方法

目次問題1: 破壊1. 破壊する方法2. いつ破壊するか2.1 解決策1: route.queryを...

Vue は PDF ファイルのオンライン プレビューを実装します (pdf.js/iframe/embed を使用)

序文現在、私はコースウェア PPT のオンライン プレビューを必要とする高品質のコースに取り組んでい...

MySQL 5.7.20 のインストールと設定方法のグラフィック チュートリアル (win10)

この記事では、MySQL 5.7.20のインストールと設定方法を参考までに紹介します。具体的な内容は...

SQL実行ステップの詳細な分析

SQL実行ステップの詳細な分析まず、ステートメントが実行される順序を見てみましょう。 (8)選択する...