Microsoft SQL Server
Clausola OVER

Parametri

Osservazioni

Utilizzo delle funzioni di aggregazione con OVER

Usando la tabella Cars , calcoleremo l'importo totale, massimo, minimo e medio di denaro speso da ogni cliente e molte volte (COUNT) ha portato una macchina per ripararla.

Id CustomerId MechanicId Status Status Costo totale

SELECT CustomerId,  
       SUM(TotalCost) OVER(PARTITION BY CustomerId) AS Total,
       AVG(TotalCost) OVER(PARTITION BY CustomerId) AS Avg,
       COUNT(TotalCost) OVER(PARTITION BY CustomerId) AS Count,
       MIN(TotalCost) OVER(PARTITION BY CustomerId) AS Min,
       MAX(TotalCost) OVER(PARTITION BY CustomerId) AS Max
  FROM CarsTable
 WHERE Status = 'READY'

Fai attenzione che l'utilizzo di OVER in questo modo non aggregherà le righe restituite. La query sopra riportata restituirà quanto segue:

La / e riga / e duplicata / i potrebbe non essere quella utile ai fini della segnalazione.

Se si desidera semplicemente aggregare i dati, sarà meglio utilizzare la clausola GROUP BY insieme alle funzioni aggregate appropriate. Ad esempio:

SELECT CustomerId,  
       SUM(TotalCost) AS Total,
       AVG(TotalCost) AS Avg,
       COUNT(TotalCost) AS Count,
       MIN(TotalCost) AS Min,
       MAX(TotalCost)  AS Max
  FROM CarsTable
 WHERE Status = 'READY'
GROUP BY CustomerId

Somma cumulativa

Usando la Tabella delle vendite degli articoli, cercheremo di scoprire come le vendite dei nostri articoli stanno aumentando attraverso le date. Per fare ciò calcoleremo la somma cumulativa delle vendite totali per ordine dell'articolo entro la data di vendita.

SELECT item_id, sale_Date 
       SUM(quantity * price) OVER(PARTITION BY item_id ORDER BY sale_Date ROWS BETWEEN UNBOUNDED PRECEDING) AS SalesTotal
  FROM SalesTable

Utilizzo delle funzioni di aggregazione per trovare i record più recenti

Utilizzando il database delle biblioteche , cerchiamo di trovare l'ultimo libro aggiunto al database per ogni autore. Per questo semplice esempio, assumiamo un ID sempre crescente per ogni record aggiunto.

SELECT MostRecentBook.Name, MostRecentBook.Title
FROM ( SELECT Authors.Name,
              Books.Title,
              RANK() OVER (PARTITION BY Authors.Id ORDER BY Books.Id DESC) AS NewestRank
       FROM Authors
       JOIN Books ON Books.AuthorId = Authors.Id
     ) MostRecentBook
WHERE MostRecentBook.NewestRank = 1

Invece di RANK, è possibile utilizzare altre due funzioni per ordinare. Nell'esempio precedente il risultato sarà lo stesso, ma danno risultati diversi quando l'ordine dà più righe per ogni grado.

• RANK() : i duplicati ottengono lo stesso valore, il grado successivo prende in considerazione il numero di duplicati nel grado precedente
• DENSE_RANK() : i duplicati ottengono lo stesso valore, il grado successivo è sempre superiore a quello precedente
• ROW_NUMBER() : assegnerà a ciascuna riga un 'rank' unico, 'classifica' i duplicati casualmente

Ad esempio, se la tabella aveva una colonna non univoca CreationDate e l'ordine è stato eseguito in base a tale, la seguente query:

SELECT Authors.Name,
       Books.Title,
       Books.CreationDate,
       RANK() OVER (PARTITION BY Authors.Id ORDER BY Books.CreationDate DESC) AS RANK,
       DENSE_RANK() OVER (PARTITION BY Authors.Id ORDER BY Books.CreationDate DESC) AS DENSE_RANK,
       ROW_NUMBER() OVER (PARTITION BY Authors.Id ORDER BY Books.CreationDate DESC) AS ROW_NUMBER,
FROM Authors
JOIN Books ON Books.AuthorId = Authors.Id

Potrebbe comportare:

Dividere i dati in bucket ugualmente partizionati usando NTILE

Supponiamo che tu disponga di punteggi degli esami per diversi esami e che tu voglia dividerli in quartili per esame.

-- Setup data:
declare @values table(Id int identity(1,1) primary key, [Value] float, ExamId int)
insert into @values ([Value], ExamId) values
(65, 1), (40, 1), (99, 1), (100, 1), (90, 1), -- Exam 1 Scores
(91, 2), (88, 2), (83, 2), (91, 2), (78, 2), (67, 2), (77, 2) -- Exam 2 Scores

-- Separate into four buckets per exam:
select ExamId, 
       ntile(4) over (partition by ExamId order by [Value] desc) as Quartile, 
       Value, Id 
from @values 
order by ExamId, Quartile

ntile funziona alla grande quando hai davvero bisogno di un certo numero di secchi e ognuno è riempito all'incirca allo stesso livello. Si noti che sarebbe banale separare questi punteggi in percentili semplicemente usando ntile(100) .