Konvertieren Sie die NORMALIZE Teradata-Temporalfunktion in Amazon Redshift SQL

Erstellt von Po Hong () AWS

Übersicht

NORMALIZEist eine Teradata-Erweiterung des Standards. ANSI SQL Wenn eine SQL Tabelle eine Spalte mit einem PERIODDatentyp enthält, werden Werte, die sich in dieser Spalte treffen oder sich überschneiden, zu einer einzigen Periode NORMALIZEkombiniert, die mehrere einzelne Periodenwerte konsolidiert. Um sie verwenden zu können NORMALIZE, muss mindestens eine Spalte in der SQL SELECTListe den temporalen PERIODDatentyp von Teradata haben. Weitere Informationen NORMALIZEdazu finden Sie in der Teradata-Dokumentation. 

Amazon Redshift unterstützt diese Funktionalität nicht NORMALIZE, aber Sie können diese Funktionalität mithilfe der systemeigenen SQL Syntax und der LAGFensterfunktion in Amazon Redshift implementieren. Dieses Muster konzentriert sich auf die Verwendung der NORMALIZETeradata-Erweiterung mit der ON MEETS OVERLAPS OR-Bedingung, dem beliebtesten Format. Es erklärt, wie diese Funktion in Teradata funktioniert und wie sie in die native Amazon Redshift Redshift-Syntax konvertiert werden kann. SQL

Voraussetzungen und Einschränkungen

Voraussetzungen

Architektur

Quelltechnologie-Stack

Zieltechnologie-Stack

Zielarchitektur

Eine allgemeine Architektur für die Migration einer Teradata-Datenbank zu Amazon Redshift finden Sie im Muster Migrieren einer Teradata-Datenbank zu Amazon Redshift mithilfe von Datenextraktionsagenten. AWS SCT Die Migration konvertiert die NORMALIZETeradata-Phrase nicht automatisch in Amazon Redshift. SQL Sie können diese Teradata-Erweiterung konvertieren, indem Sie die Richtlinien in diesem Muster befolgen.

Tools

Code

Sehen Sie sich die folgende Tabellendefinition in Teradata an NORMALIZE, um das Konzept und die Funktionalität von zu veranschaulichen:

CREATE TABLE systest.project
     (    emp_id        INTEGER,
          project_name  VARCHAR(20),
          dept_id       INTEGER,
          duration      PERIOD(DATE)
     );

Führen Sie den folgenden SQL Code aus, um Beispieldaten in die Tabelle einzufügen:

BEGIN TRANSACTION;

INSERT INTO systest.project VALUES (10, 'First Phase', 1000,  PERIOD(DATE '2010-01-10', DATE '2010-03-20') );
INSERT INTO systest.project VALUES (10, 'First Phase', 2000,  PERIOD(DATE '2010-03-20', DATE '2010-07-15') );

INSERT INTO systest.project VALUES (10, 'Second Phase', 2000,  PERIOD(DATE '2010-06-15', DATE '2010-08-18') );
INSERT INTO systest.project VALUES (20, 'First Phase', 2000,  PERIOD(DATE '2010-03-10', DATE '2010-07-20') );

INSERT INTO systest.project VALUES (20, 'Second Phase', 1000,  PERIOD(DATE '2020-05-10', DATE '2020-09-20') );

END TRANSACTION;

Ergebnisse:

select * from systest.project order by 1,2,3;
 
 *** Query completed. 4 rows found. 4 columns returned.
 *** Total elapsed time was 1 second.
 
     emp_id  project_name              dept_id  duration
-----------  --------------------  -----------  ------------------------
         10  First Phase                  1000  ('10/01/10', '10/03/20')        
         10  First Phase                  2000  ('10/03/20', '10/07/15')
         10  Second Phase                 2000  ('10/06/15', '10/08/18')
         20  First Phase                  2000  ('10/03/10', '10/07/20')
         20  Second Phase                 1000  ('20/05/10', '20/09/20')

Teradata-Anwendungsfall NORMALIZE

Fügen Sie nun die NORMALIZESQLTeradata-Klausel zur Anweisung hinzu: SELECT

SELECT NORMALIZE ON MEETS OR OVERLAPS emp_id, duration 
FROM systest.project 
ORDER BY 1,2;

Diese NORMALIZEOperation wird für eine einzelne Spalte (emp_id) ausgeführt. Für emp_id=10 verschmelzen die drei sich überschneidenden Periodenwerte in der Kategorie Dauer wie folgt zu einem einzigen Periodenwert:  

     emp_id  duration
-----------  ------------------------
         10  ('10/01/10', '10/08/18')
         20  ('10/03/10', '10/07/20')
         20  ('20/05/10', '20/09/20')

Die folgende SELECTAnweisung führt eine Operation mit project_name und dept_id aus. NORMALIZE Beachten Sie, dass die SELECTListe nur eine Spalte enthält, nämlich Dauer. PERIOD

SELECT NORMALIZE project_name, dept_id, duration 
FROM systest.project;

Ausgabe:

project_name              dept_id  duration
--------------------  -----------  ------------------------
First Phase                  1000  ('10/01/10', '10/03/20')
Second Phase                 1000  ('20/05/10', '20/09/20')
First Phase                  2000  ('10/03/10', '10/07/20')
Second Phase                 2000  ('10/06/15', '10/08/18')

Amazon Redshift Redshift-Äquivalent SQL

Amazon Redshift unterstützt derzeit den PERIODDatentyp in einer Tabelle nicht. Stattdessen müssen Sie ein PERIODTeradata-Datenfeld wie folgt in zwei Teile unterteilen: start_date, end_date:  

CREATE TABLE systest.project
     (    emp_id        INTEGER,
          project_name  VARCHAR(20),
          dept_id       INTEGER,
          start_date  DATE,
          end_date    DATE
     );

Fügen Sie Beispieldaten in die Tabelle ein:

BEGIN TRANSACTION;
 
INSERT INTO systest.project VALUES (10, 'First Phase', 1000,  DATE '2010-01-10', DATE '2010-03-20' );
INSERT INTO systest.project VALUES (10, 'First Phase', 2000,  DATE '2010-03-20', DATE '2010-07-15');
 
INSERT INTO systest.project VALUES (10, 'Second Phase', 2000,  DATE '2010-06-15', DATE '2010-08-18' );
INSERT INTO systest.project VALUES (20, 'First Phase', 2000,  DATE '2010-03-10', DATE '2010-07-20' );
 
INSERT INTO systest.project VALUES (20, 'Second Phase', 1000,  DATE '2020-05-10', DATE '2020-09-20' );
 
END TRANSACTION;

Ausgabe:

 emp_id | project_name | dept_id | start_date |  end_date
--------+--------------+---------+------------+------------
     10 | First Phase  |    1000 | 2010-01-10 | 2010-03-20
     10 | First Phase  |    2000 | 2010-03-20 | 2010-07-15
     10 | Second Phase |    2000 | 2010-06-15 | 2010-08-18
     20 | First Phase  |    2000 | 2010-03-10 | 2010-07-20
     20 | Second Phase |    1000 | 2020-05-10 | 2020-09-20
(5 rows)

Um die NORMALIZEKlausel von Teradata neu zu schreiben, können Sie die LAGFensterfunktion in Amazon Redshift verwenden. Diese Funktion gibt die Werte für eine Zeile mit einem bestimmten Offset über (vor) der aktuellen Zeile in der Partition zurück.

Sie können die LAGFunktion verwenden, um jede Zeile zu identifizieren, mit der eine neue Periode beginnt, indem Sie ermitteln, ob eine Periode mit der vorherigen Periode übereinstimmt oder sich mit ihr überschneidet (0, wenn ja und 1, wenn nein). Wenn dieses Flag kumulativ zusammengefasst wird, stellt es eine Gruppen-ID bereit, die in der äußeren Group By-Klausel verwendet werden kann, um das gewünschte Ergebnis in Amazon Redshift zu erzielen.  

Hier ist ein Beispiel für eine Amazon Redshift SQL Redshift-Anweisung, die LAG() verwendet:

SELECT emp_id, start_date, end_date, 
            (CASE WHEN start_date <= LAG(end_date) OVER (PARTITION BY emp_id ORDER BY start_date, end_date) THEN 0 ELSE 1 END) AS GroupStartFlag
FROM systest.project 
ORDER BY 1,2;

Ausgabe:

 emp_id | start_date |  end_date  | groupstartflag
--------+------------+------------+----------------
     10 | 2010-01-10 | 2010-03-20 |              1
     10 | 2010-03-20 | 2010-07-15 |              0
     10 | 2010-06-15 | 2010-08-18 |              0
     20 | 2010-03-10 | 2010-07-20 |              1
     20 | 2020-05-10 | 2020-09-20 |              1
(5 rows)

Die folgende Amazon Redshift SQL Redshift-Anweisung normalisiert nur die Spalte emp_id:

SELECT T2.emp_id, MIN(T2.start_date) as new_start_date, MAX(T2.end_date) as new_end_date
FROM 
( SELECT T1.*, SUM(GroupStartFlag) OVER (PARTITION BY emp_id ORDER BY start_date ROWS UNBOUNDED PRECEDING) As GroupID
FROM ( SELECT emp_id, start_date, end_date, 
            (CASE WHEN start_date <= LAG(end_date) OVER (PARTITION BY emp_id ORDER BY start_date, end_date) THEN 0 ELSE 1 END) AS GroupStartFlag
FROM systest.project ) T1
) T2
GROUP BY T2.emp_id, T2.GroupID
ORDER BY 1,2;

Ausgabe:  

 emp_id | new_start_date | new_end_date
--------+----------------+------------------------------------
     10 | 2010-01-10     | 2010-08-18
     20 | 2010-03-10     | 2010-07-20
     20 | 2020-05-10     | 2020-09-20
(3 rows)

Die folgende Amazon Redshift SQL Redshift-Anweisung normalisiert die Spalten project_name und dept_id:

SELECT T2.project_name, T2.dept_id, MIN(T2.start_date) as new_start_date, MAX(T2.end_date) as new_end_date
FROM 
( SELECT T1.*, SUM(GroupStartFlag) OVER (PARTITION BY project_name, dept_id ORDER BY start_date ROWS UNBOUNDED PRECEDING) As GroupID
FROM ( SELECT project_name, dept_id, start_date, end_date, 
            (CASE WHEN start_date <= LAG(end_date) OVER (PARTITION BY project_name, dept_id ORDER BY start_date, end_date) THEN 0 ELSE 1 END) AS GroupStartFlag
FROM systest.project ) T1
) T2
GROUP BY T2.project_name, T2.dept_id, T2.GroupID
ORDER BY 1,2,3;

Ausgabe:

 project_name | dept_id | new_start_date | new_end_date
--------------+---------+----------------+--------------
 First Phase  |    1000 | 2010-01-10     | 2010-03-20
 First Phase  |    2000 | 2010-03-10     | 2010-07-20
 Second Phase |    1000 | 2020-05-10     | 2020-09-20
 Second Phase |    2000 | 2010-06-15     | 2010-08-18
(4 rows)

Epen

Zugehörige Ressourcen

Referenzen

Tools

Partner