Convertire la funzionalità temporale Teradata NORMALIZE in Amazon Redshift SQL

Creato da Po Hong (AWS)

Riepilogo

NORMALIZE è un'estensione Teradata dello standard ANSI SQL. Quando una tabella SQL include una colonna con un tipo di dati PERIOD, NORMALIZE combina i valori che corrispondono o si sovrappongono in quella colonna, per formare un unico periodo che consolida più valori di periodo individuali. Per utilizzare NORMALIZE, almeno una colonna nell'elenco SQL SELECT deve essere del tipo di dati TEMPORAL PERIOD di Teradata. Per ulteriori informazioni su NORMALIZE, vedere la documentazione di Teradata. 

Amazon Redshift non supporta NORMALIZE, ma puoi implementare questa funzionalità utilizzando la sintassi SQL nativa e la funzione finestra LAG in Amazon Redshift. Questo modello si concentra sull'utilizzo dell'estensione Teradata NORMALIZE con la condizione ON MEETS OR OVERLAPS, che è il formato più popolare. Spiega come funziona questa funzionalità in Teradata e come può essere convertita nella sintassi SQL nativa di Amazon Redshift.

Prerequisiti e limitazioni

Prerequisiti

Architettura

Stack tecnologico di origine

Stack tecnologico Target

Architettura di destinazione

Per un'architettura di alto livello per la migrazione di un database Teradata ad Amazon Redshift, consulta lo schema Migrare un database Teradata su Amazon Redshift utilizzando gli agenti di estrazione dati AWS SCT. La migrazione non converte automaticamente la frase Teradata NORMALIZE in Amazon Redshift SQL. Puoi convertire questa estensione Teradata seguendo le linee guida riportate in questo schema.

Strumenti

Codice

Per illustrare il concetto e la funzionalità di NORMALIZE, si consideri la seguente definizione di tabella in Teradata:

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

Eseguite il seguente codice SQL per inserire dati di esempio nella tabella:

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;

Risultati:

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')

Caso d'uso Teradata NORMALIZE

Ora aggiungi la clausola Teradata NORMALIZE SQL all'istruzione SELECT:

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

Questa operazione NORMALIZE viene eseguita su una singola colonna (emp_id). Per emp_id=10, i tre valori di periodo sovrapposti in termini di durata si fondono in un unico valore di periodo, come segue:  

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

La seguente istruzione SELECT esegue un'operazione NORMALIZE su project_name e dept_id. Si noti che l'elenco SELECT contiene solo una colonna PERIOD, la durata.

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

Output:

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')

SQL equivalente ad Amazon Redshift

Amazon Redshift attualmente non supporta il tipo di dati PERIOD in una tabella. È invece necessario dividere un campo di dati TERADATA PERIOD in due parti: start_date, end_date, come segue:  

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

Inserite dati di esempio nella tabella:

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;

Output:

 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)

Per riscrivere la clausola NORMALIZE di Teradata, puoi utilizzare la funzione LAG window in Amazon Redshift. Questa funzione restituisce i valori di una riga con un determinato offset al di sopra (prima) della riga corrente nella partizione.

È possibile utilizzare la funzione LAG per identificare ogni riga che inizia un nuovo periodo determinando se un periodo corrisponde o si sovrappone al periodo precedente (0 se sì e 1 se no). Quando questo flag viene sommato cumulativamente, fornisce un identificatore di gruppo che può essere utilizzato nella clausola Group By esterna per ottenere il risultato desiderato in Amazon Redshift.  

Ecco un esempio di istruzione SQL di Amazon Redshift che utilizza LAG ():

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;

Output:

 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)

La seguente istruzione SQL di Amazon Redshift si normalizza solo sulla colonna 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;

Output:  

 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)

La seguente istruzione SQL di Amazon Redshift si normalizza su entrambe le colonne project_name e 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;

Output:

 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)

Epiche

Risorse correlate

Riferimenti

Strumenti

Partner