SQL Server ed ALM su database


Il blog di Alessandro Alpi
Archivio Posts
Anno 2018

Anno 2017

Anno 2016

Anno 2015

Anno 2014

Anno 2013

Anno 2012

Anno 2011

Anno 2010

Anno 2009

Anno 2008

Anno 2007

Anno 2006

Funzioni di ranking, esempi di utilizzo

Le funzioni di ranking sono una delle novità di SQL Server 2005 più utili e più potenti.
Con esse si possono creare contatori autoincrementanti oppure si possono raggruppare in sottoset di dati resultset più grandi o ancora, creare classifiche basate su raggruppamenti definiti, aumentando così il controllo sui record.
Una delle problematiche che ho incontrato ultimamente (in alcuni sviluppi su DataWareHouse) è la presenza di record duplicati su flussi di input di una determinata procedura. Le funzioni di ranking sono state utilissime per determinare quali righe "inutili" potessero essere cancellate.
Altro problema affrontato è stato quello relativo ai rapporti bancari, in una tabella che ne gestisce gli storici. Siccome i suddetti rapporti possono ripresentarsi nel tempo, è stato necessario utilizzare funzioni di ranking per ricavare solamente uno di essi come "valido", in base a logiche definite dal cliente.

Per un help della ROW_NUMBER() che utilizzeremo, consultare il seguente link:
http://msdn2.microsoft.com/en-us/library/ms186734.aspx

Facciamo un esempio del primo caso.
Supponiamo di avere una tabella semplicissima, con due campi, id valore. Ammettiamo di dover cancellare gli eventuali valori duplicati da questa tabella, che chiamiamo Tabella1
 




CREATE TABLE [dbo].[Tabella1](
      [id] [int] IDENTITY(1,1) NOT NULL,
      [valore] [varchar](50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]

Vediamo ora come ricavare i doppi:

SELECT 
  *, NumeroRiga = ROW_NUMBER() OVER(PARTITION BY valore ORDER BY id)
FROM Tabella1

Ecco il risultato della select:



id          valore      NumeroRiga
----------- ----------- ------------
1           Valore1     1
2           Valore1     2
3           Valore2     1
4           Valore3     1
9           Valore3     2
10          Valore3     3
5           Valore4     1
6           Valore5     1
7           Valore6     1
8           Valore6     2



Un modo per eliminarli potrebbe essere utilizzare anche le Common Table Expression:



;WITH Doppi AS
(
  -- ricavo il rownumber per i doppi (NumeroRiga)
  SELECT 
     *, NumeroRiga = ROW_NUMBER() OVER(PARTITION BY valore ORDER BY id) 
  FROM Tabella1
)
 
-- cancello direttamente dalla CTE Doppi, indicando a video
-- tramite la clausola OUTPUT i record cancellati
DELETE
FROM Doppi
OUTPUT DELETED.id, DELETED.valore
WHERE NumeroRiga > 1
-- se NumeroRiga è maggiore di 1, cancello, in quanto nella OVER,
-- ho definito il criterio di partizionamento sul campo "valore" e
-- quindi il ROW_NUMBER() aumenta di 1 solo se "valore" è lo stesso




In questo modo, tramite la OUTPUT abbiamo il seguente risultato (i dati che sono stati cancellati):



id          valore     
----------- ---------
2           Valore1   
9           Valore3   
10          Valore3   
8           Valore6    



Abbiamo visto come sia facile utilizzare le ranking functions per ottenere criteri che possono diventare anche complessi veramente. Ora, l'esempio è molto ristretto e semplificato, ma sono certo che alcuni avranno un valido motivo per provare la sintassi sopra indicata.



Inoltre con queste funzioni potrebbe risultare semplice ricavare il primo di record che rispetta un determinato ordinamento con un determinato partizionamento dei dati, ad esempio il primo utente che ha effettuato un login su un sito, oppure il primo tra gli n ordini di vendita in un determinato periodo, e via discorrendo..
Ricordiamo anche le altre due novità introdotte da SQL Server 2005, quali la CTE (common table expression) e la clausola OUTPUT, molto utile per gestire logging e controlli vari sui dati definiti nelle operazioni di cancellazione, modifica, inserimento.



Alla prossima!

Categoria: Transact-SQL
lunedì, 10 lug 2006 Ore. 02.32
Statistiche
  • Views Home Page: 481.051
  • Views Posts: 866.852
  • Views Gallerie: 498.935
  • n° Posts: 484
  • n° Commenti: 273



















Copyright © 2002-2007 - Blogs 2.0
dotNetHell.it | Home Page Blogs
ASP.NET 2.0 Windows 2003