Analytic functions, LAG, running total, cursor, SQLCLR, …

  • [アーティクル]

Qualche anno fa (sono quasi 5 !!!) scrivevo di cursori, complessità lineari, complessità esponenziali, SQLCLR, … qui e qui.

Nei due post del blog analizzavo alcune possibili soluzioni (con cursore, con subquery senza cursore, con un’implementazione SQLCLR) ad un problema di running total :

image

La domanda, a questo punto, potrebbe nascere spontanea: perchè, a distanza di tempo, riprendo questi argomenti?

Perchè SQL Server 2012 aggiunge alcune funzioni analitiche che, ad esempio, permettono di gestire il concetto di “precedente” e “successivo” e, quindi, consentono di leggere (in un set opportunamente ordinato secondo le nostre necessità) i valori necessari a risolvere problemi come quello in oggetto.

La funzione LAG (vedi qui), in grado di accedere alla riga precedente, fa perfettamente al caso nostro.

Partendo dal codice TSQL presente nel primo post linkato in alto, ecco come potrei risolvere (senza subquery, cursori, soluzioni CLR) il problema con SQL Server 2012:

 DECLARE @StartDate datetime , 
        @EndDate datetime;

SET @StartDate = GETDATE( );  
SELECT customerID , 
       qty , 
       qty + ISNULL( LAG( qty )
          OVER( 
             PARTITION BY customerID 
             ORDER BY idRecord 
             ) , 0 )AS qtySum
  FROM orders
  ORDER BY customerID , idRecord;

SET @EndDate = GETDATE( );
SELECT DATEDIFF( ms , @StartDate , @EndDate )
    AS 'Execution time in ms';

In termini di performance, senza prendere come assoluti i valori di una serie di test tra la soluzione con subquery e questa (LAG), ecco i risultati:

image

Direi assolutamente imbattibile!

Comments

  • Anonymous
    December 26, 2013
    Are you sure your query -using LAG- returns sub totals?
  • Anonymous
    December 26, 2013
    Sorry I mean running totals