Convertire la funzionalità Teradata RESET WHEN in Amazon Redshift SQL

Creato da Po Hong (AWS)

Riepilogo

RESET WHEN è una funzionalità di Teradata utilizzata nelle funzioni analitiche delle finestre SQL. È un'estensione dello standard ANSI SQL. RESET WHEN determina la partizione su cui opera una funzione di finestra SQL in base a una condizione specificata. Se la condizione restituisce TRUE, viene creata una nuova sottopartizione dinamica all'interno della partizione di finestra esistente. Per ulteriori informazioni su RESET WHEN, consultate la documentazione di Teradata.

Amazon Redshift non supporta RESET WHEN nelle funzioni delle finestre SQL. Per implementare questa funzionalità, devi convertire RESET WHEN nella sintassi SQL nativa in Amazon Redshift e utilizzare più funzioni annidate. Questo modello dimostra come utilizzare la funzionalità Teradata RESET WHEN e come convertirla nella sintassi SQL di Amazon Redshift. 

Prerequisiti e limitazioni

Prerequisiti

Architettura

Stack tecnologico di origine

Stack tecnologico Target

Architettura

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 RESET WHEN in Amazon Redshift SQL. Puoi convertire questa estensione Teradata seguendo le linee guida nella sezione successiva.

Strumenti

Codice

Per illustrare il concetto di RESET WHEN, si consideri la seguente definizione di tabella in Teradata:

create table systest.f_account_balance                                
( account_id integer NOT NULL,
  month_id integer,
  balance integer )
unique primary index (account_id, month_id);

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

BEGIN TRANSACTION;
Insert Into systest.f_account_balance values (1,1,60);
Insert Into systest.f_account_balance values (1,2,99);
Insert Into systest.f_account_balance values (1,3,94);
Insert Into systest.f_account_balance values (1,4,90);
Insert Into systest.f_account_balance values (1,5,80);
Insert Into systest.f_account_balance values (1,6,88);
Insert Into systest.f_account_balance values (1,7,90);
Insert Into systest.f_account_balance values (1,8,92);
Insert Into systest.f_account_balance values (1,9,10);
Insert Into systest.f_account_balance values (1,10,60);
Insert Into systest.f_account_balance values (1,11,80);
Insert Into systest.f_account_balance values (1,12,10);
END TRANSACTION;

La tabella di esempio contiene i seguenti dati:

Per ogni account, supponiamo che tu voglia analizzare la sequenza di aumenti mensili consecutivi del saldo. Quando il saldo di un mese è inferiore o uguale al saldo del mese precedente, è necessario azzerare il contatore e riavviare il sistema.

Caso d'uso Teradata RESET WHEN

Per analizzare questi dati, Teradata SQL utilizza una funzione finestra con un aggregato annidato e una frase RESET WHEN, come segue:

SELECT account_id, month_id, balance,
 ( ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY month_id
RESET WHEN balance <= SUM(balance) over (PARTITION BY account_id ORDER BY month_id ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) ) -1 ) as balance_increase
FROM systest.f_account_balance
ORDER BY 1,2;

Output:

La query viene elaborata come segue in Teradata:

SQL equivalente ad Amazon Redshift

Amazon Redshift non supporta la frase RESET WHEN in una funzione di finestra analitica SQL.  Per ottenere lo stesso risultato, è necessario riscrivere Teradata SQL utilizzando la sintassi SQL nativa di Amazon Redshift e sottoquery annidate, come segue: 

SELECT account_id, month_id, balance,
   (ROW_NUMBER() OVER(PARTITION BY account_id, new_dynamic_part ORDER BY month_id) -1) as balance_increase
FROM
( SELECT account_id, month_id, balance, prev_balance,
SUM(dynamic_part) OVER (PARTITION BY account_id ORDER BY month_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) As new_dynamic_part
FROM ( SELECT account_id, month_id, balance,
SUM(balance) over (PARTITION BY account_id ORDER BY month_id ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) as prev_balance,
(CASE When balance <= prev_balance Then 1 Else 0 END) as dynamic_part
FROM systest.f_account_balance ) A
) B
ORDER BY 1,2;

Poiché Amazon Redshift non supporta le funzioni di finestra annidata nella clausola SELECT di una singola istruzione SQL, è necessario utilizzare due sottoquery annidate.

Dopo queste modifiche, Amazon Redshift SQL genera lo stesso output di Teradata.

Epiche

Risorse correlate

Riferimenti

Strumenti

Partner