時系列転位修復ケースを実装する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テーブルのヘッダーアイコンにフローティングプロンプトを追加します。

推薦する

良いデザインについて

<br />「良いデザインとは何か」と答える 1 万人に対して、少なくとも 1 万 1 ...

Angularの動的コンポーネントの詳細な説明

目次使用シナリオ達成方法1. 動的コンポーネントを配置する場所2. コンポーネントのインスタンスを取...

DOM操作テーブルの例(DOMはテーブルを作成します)

1. HTML タグを使用してテーブルを作成します。コードをコピーコードは次のとおりです。 <...

HTMLテーブルで、各セルに異なる色と幅を設定します

設定が有効にならない場合が多いため、幅や高さなどをテーブル内で直接設定しないことをお勧めします。スタ...

macOS での MySQL 8.0.17 のインストールと簡単な設定チュートリアル

私が書いた内容が理解できない場合は、インターネット上に理解できるチュートリアルがない可能性があります...

MySQL Truncate の使用方法の詳細な説明

目次MySQL 切り捨ての使用1. 構文を切り捨てる2. Truncateの使用上の注意3. TRU...

Vueデータ双方向バインディング実装方法

目次1. はじめに2. コードの実装2.1 目的分析2.2 実装プロセス2.2.1 エントリーコード...

sysbenchツールによるMySQLデータベースのパフォーマンステストの実装方法

1. 背景Sysbench は、システムのハードウェア パフォーマンスをテストできるストレス テスト...

シェアしたい絶妙なApple風無料アイコン素材18セット

Apple マグカップのアイコンと追加機能 HD ストレージボックス – アドオンパックセイバースノ...

WeChatアプレットがテキストスクロールを実装

この記事の例では、WeChatアプレットでテキストスクロールを実装するための具体的なコードを参考まで...

MySQL ストアド プロシージャのエラー処理例の詳細な説明

この記事では、例を使用して MySQL ストアド プロシージャのエラー処理について説明します。ご参考...

MySQL 1130例外、リモートログインできない解決策

目次質問: 1. リモートログイン権限を有効にする: 2. MySQLの権限を更新します。 3. テ...

MySQL max_allowed_pa​​cket 設定

max_allowed_pa​​cket は、受け入れるパケットのサイズを設定するために使用される ...

Windows Server 2019 のセットアップ方法 (画像とテキスト付き)

1. Windows Server 2019 のインストールVmware に Windows Se...

Prometheus を使用して、MySQL の自動増分主キーの残りの使用可能パーセンテージをカウントします。

最近、本番環境のデータベースがログデータを狂ったように書き込み、主キー値のオーバーフローを引き起こし...