SQL zur Implementierung der Wiederherstellung einer Zeitreihenversetzung

SQL zur Implementierung der Wiederherstellung einer Zeitreihenversetzung

1. Anforderungsbeschreibung

1 Das STARTDATE der nächsten Datensatzzeile in einem Datensatz der Originaltabelle T1 (gespeichert als r1, die nächste Zeile ist r2) ist kleiner als das ENDDATE der vorherigen Zeile. Für solche Datensätze wird die Konvertierung wie folgt durchgeführt:

STARTDATE von r1 bleibt unverändert ENDDATE為r1 STARTDATE-1

STARTDATE von r2 ist ENDDATE von r1 ENDDATE為r1 ENDDATE von r1.

2 Wenn in benachbarten Zeilen der Originaltabelle T1 keine „zeitliche Überlappung“ vorliegt ( d. h. die Definition von 1), bleiben die Originaldaten unverändert. 

 # Textversion#T1
Sequenz-ID Startdatum Enddatum Nummer
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 20.05.2021 31.07.2021 34
5 1 2021-08-05 2021-08-25 45
6 1 2021-08-15 2021-09-25 65
 
 
# Ausgabeergebnis-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. Ideenübersicht

1. Verlängerung der Nachfrage

SEQ ID STARTDATUM ENDDATUM NUM
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 20.05.2021 31.07.2021 34

Dabei wird der 4. Datensatz über den 2. und 3. Datensatz gelegt.

2. Ideenübersicht

1) T0 ist die von den Auf- und Ab-Funktionen erzeugte Zeitreihe

id neues_DATE nächstesSTARTDATE preEndDATE rn      
1 24.05.2021 03.05.2021 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) last ruft den letzten Datensatz in T0 ab, um ihn für die nachfolgende Korrektur vorzubereiten.

neues_Datum vorENDDATE ID
2021-05-24 2021-05-03 1

3) „Normal“ extrahiert die Datensätze ohne zeitliche Überlappung in den Originaldaten, als Vorbereitung für eine nachfolgende Korrektur.
Es gibt keine Aufzeichnung der aktuellen Demonstrationsdaten, aber der Code mit Kommentaren kann erscheinen.

4) T_Serial vereinheitlichte die Definition von STARTDATE und ENDDATE und überarbeitete T0 zum ersten Mal.

id STARTDATUM ENDDATUM
1 2021-04-20 2021-04-30
1 2021-05-01 2021-05-03
1 2021-05-04 2021-05-24

5) T2 korrigiert die Datensätze, die sich zeitlich nicht überschneiden (löscht den entsprechenden Wert von T0 und aktualisiert das entsprechende ENDDATE).
Der aktuelle Beispiel-Ergebnissatz ist leer, d. h. es ist keine Korrektur erforderlich.

6) T2 wird mit T1 (Originaltabelle) verknüpft und der Endwert wird nach der Aggregation erhalten.

STARTDATUM ENDDATUM NUM
2021-04-20 2021-04-30 200
2021-05-01 2021-05-03 300
2021-05-04 2021-05-24 100

3. SQL-Code

Die aktuelle Demoversion ist MySQL 8.0.23. SQL Server und Oracle , die CTE und Fensterfunktionen unterstützen, müssen die Syntax von Order by und ADDDATE ändern.
Schritt 0 Erstellen Sie eine Tabelle und initialisieren Sie die Daten

Tabelle löschen, wenn vorhanden, test_ShenLiang2025;
CREATE TABLE test_ShenLiang2025 (
  seq int DEFAULT NULL,
  id int DEFAULT NULL,
  STARTDATE Datum DEFAULT NULL,
  ENDDATE Datum DEFAULT NULL,
  NUM int DEFAULT NULL
)ENGINE=InnoDB STANDARD-CHARSET=utf8;
 
INSERT INTO test_ShenLiang2025 VALUES ('1', '1', '2021-04-20', '2021-05-03', '200');
INSERT INTO test_ShenLiang2025 VALUES ('2', '1', '2021-05-01', '2021-05-24', '100');
INSERT INTO test_ShenLiang2025 VALUES ('3', '1', '2021-05-18', '2021-05-31', '69');
INSERT INTO test_ShenLiang2025 VALUES ('4', '1', '2021-05-20', '2021-07-31', '34');
INSERT INTO test_ShenLiang2025 VALUES ('5', '1', '2021-08-05', '2021-08-25', '45');
INSERT INTO test_ShenLiang2025 VALUES ('6', '1', '2021-08-15', '2021-09-25', '65');

Schritt 1: Erstellen Sie einen temporären Ergebnisset, um eine Zeitreihe zu generieren.

MIT T0 ALS(
SELECT-ID, 
   neues_DATUM,
   LEAD(NEW_DATE,1) ÜBER (PARTITION BY ID ORDER BY NEW_DATE ) nächstesSTARTDATE,
   LAG(NEUES_DATUM,1) ÜBER (PARTITION NACH ID ORDER NACH NEUES_DATUM) preENDDATE,
   ROW_NUMBER()OVER(PARTITION BY ID ORDER BY new_DATE DESC) rn
   AUS
  (
  Wählen Sie DISTINCT ID,STARTDATE neues_DATE FROM test_ShenLiang2025    
   WHERE seq in (1,2) – Sie können Kommentare hinzufügen, um zu überprüfen, ob nur zwei Datensätze aus der Originaltabelle übernommen werden.
  Wählen Sie DISTINCT ID,ENDDATE neues_DATE FROM test_ShenLiang2025
   WHERE seq in (1,2) -- Sie können Kommentare hinzufügen, um zu bestätigen, dass derzeit nur 2 Datensätze in der Originaltabelle verwendet werden ORDER BY new_DATE 
  )A
),letzte AS
(AUSWÄHLEN neues_DATUM,preENDDATE,id
AB T0 
Wobei nextSTARTDATE NULL ist
),normale AS
(
 WÄHLEN SIE * AUS
 (
 SELECT-ID, 
    Enddatum,
    LEAD(STARTDATE,1) OVER (PARTITION BY ID ORDER BY ENDDATE ) nextSTARTDATE,
    LAG(ENDDATE,1) OVER (PARTITION BY ID ORDER BY ENDDATE) preENDDATE
    VON test_ShenLiang2025
 )A
 WO ENDDATE > preENDDATE UND ENDDATE < nextSTARTDATE
),T_Serial AS (
 
SELECT ID,ADDDATE(preENDDATE, INTERVAL 1 DAY ) STARTDATE,
neues_DATUM ENDDATE
VON zuletzt 
 
UNION
 
Wählen Sie bottom_2.ID,bottom_2.new_DATE STARTDATE,
FALL, WENN rn =3, DANN bottom_2.nextSTARTDATE 
 SONST ADDDATE(bottom_2.nextSTARTDATE, INTERVAL -1 DAY ) END ENDDATE
VON zuletzt 
BEITRETEN T0 bottom_2
ON bottom_2.nextSTARTDATE<=last.preENDDATE UND bottom_2.id = last.id
),T2 AS(
WÄHLEN SIE B.ID, B.STARTDATE, B.ENDDATE AUS
  (
   WÄHLEN SIE A.*, ROW_NUMBER() ÜBER (PARTITION NACH ID, STARTDATE ORDER BY ENDDATE) rn
   AUS
   (
   WÄHLEN SIE A.ID, A.STARTDATE, A.ENDDATE
   VON T_Seriell A
   LINKS JOIN normal B
   BEI A.STARTDATE = B.ENDDATE UND A.ID = B.ID
   Wobei B.ENDDATE NULL ist
 
   UNION 
    
   WÄHLEN SIE A.ID, A.STARTDATE, B.ENDDATE   
   VON T_Seriell A
   INNER JOIN normal B
   ON ADDDATE(A.ENDDATE, INTERVAL 1 DAY ) = B.ENDDATE UND A.ID = B.ID    
   )A
  )B, wobei rn =1
)

Schritt 2: Verknüpfen Sie die Zeitreihe mit der Originaltabelle, um das NUM-Feld zu generieren.

Wählen Sie T2.STARTDATE,T2.ENDDATE,SUM(T1.NUM) TOTAL FROM T2
BEITRETEN test_ShenLiang2025 T1
AM T2.STARTDATE>=T1.STARTDATE 
 UND T2.ENDDATE<=T1.ENDDATE
GRUPPIEREN NACH T2.STARTDATE,T2.ENDDATE
BESTELLEN BIS T2.STARTDATE

Schritt 4 Ergebnisse anzeigen

STARTDATUM ENDDATUM NUM
2021-04-20 2021-04-30 200
2021-05-01 2021-05-03 300
2021-05-04 2021-05-24 100

Ausführungsergebnis:

Damit ist dieser Artikel mit der detaillierten Erläuterung des SQL-Implementierungsfalls zur Wiederherstellung von Zeitreihenfehlausrichtungen abgeschlossen. Weitere relevante Inhalte zu SQL-Zeitfehlausrichtungen und Wiederherstellungsfällen finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!

Das könnte Sie auch interessieren:
  • Beschreibung der Standardtransaktionsisolationsebene von MySQL und Oracle
  • Zusammenfassung häufig verwendeter Anweisungen zur Änderung mehrerer Tabellen in MySQL und Oracle
  • Eine kurze Diskussion über die Unterschiede zwischen den drei wichtigsten Datenbanken: Mysql, SqlServer und Oracle
  • Detaillierte Erläuterung der Paging-Abfragemethoden in MySQL, MSSQL und Oracle

<<:  HTML verwendet das Titelattribut, um Text anzuzeigen, wenn die Maus darüber schwebt

>>:  Fügen Sie eine schwebende Eingabeaufforderung für das Kopfzeilensymbol in der ElementUI-Tabelle hinzu

Artikel empfehlen

js, um die vollständigen Schritte der Konvertierung von Chinesisch nach Pinyin durchzuführen

Ich habe js verwendet, um ein Paket zur Konvertie...

Detaillierte Erklärung zur Überwachung von MySQL-Anweisungen

Schnelles Lesen Warum müssen wir SQL-Anweisungen ...

HTML-Tabellen-Markup-Tutorial (38): Rahmenfarben-Attribut der Kopfzeile BORDERCOLOR

Um die Tabelle zu verschönern, können Sie für die...

Detaillierte Einführung in den DOCTYPE-Typ

<br />Wir deklarieren DOCTYPE in HTML normal...

So verwenden Sie JSX zum Implementieren von Karussellkomponenten (Front-End-Komponentenbildung)

Bevor wir JSX zum Erstellen eines Komponentensyst...

Detaillierte Installation und Verwendung der Virtuoso-Datenbank unter Linux

Ich habe kürzlich einige Dinge zu verknüpften Dat...

Implementierung eines einfachen Rechners auf Basis von JavaScript

In diesem Artikel wird der spezifische JavaScript...

Vue verwendet die Methode in der Referenzbibliothek mit Quellcode

Der offizielle Quellcode von monaco-editor-vue la...

Detaillierte Erklärung der Softwarekonfiguration mit Docker-Compose unter Linux

Vorwort In diesem Artikel werden einige Docker-Co...

Eine kurze Analyse des Unterschieds zwischen ref und toRef in Vue3

1. ref wird kopiert, die Ansicht wird aktualisier...

So installieren Sie OpenSuse auf Virtualbox

Die virtuelle Maschine wird auf dem Hostcomputer ...

Alibaba Cloud beantragt ein kostenloses SSL-Zertifikat (https) von Cloud Shield

Da das Projekt den https-Dienst nutzen muss, habe...

Kurze Analyse von MySQL Union und Union All

In der Datenbank führen sowohl die Schlüsselwörte...

Dieser Artikel zeigt Ihnen, wie die Speicherbereinigung in JavaScript funktioniert

Inhaltsverzeichnis 1. Übersicht 2. Speicherverwal...

Detaillierte Erklärung der Datentypen in den JavaScript-Grundlagen

Inhaltsverzeichnis 1. Datentyp 1.1 Warum brauchen...