Microsoft SQL Server
Clause OVER

Paramètres

Remarques

Utilisation des fonctions d'agrégation avec OVER

En utilisant le tableau des voitures , nous calculons le montant total, maximum, minimum et moyen que chaque client a dépensé et reçu plusieurs fois (NOMBRE). Elle a apporté une voiture pour la réparation.

Id CustomerId MechanicId Statut du modèle Coût total

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'

Attention, l'utilisation de OVER de cette manière ne regroupera pas les lignes renvoyées. La requête ci-dessus renverra les éléments suivants:

La ou les lignes dupliquées peuvent ne pas être utiles à des fins de reporting.

Si vous souhaitez simplement agréger des données, il est préférable d'utiliser la clause GROUP BY avec les fonctions d'agrégat appropriées. Par exemple:

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

Somme cumulée

À l'aide du tableau des ventes d'articles , nous essaierons de savoir comment les ventes de nos articles augmentent au fil des dates. Pour ce faire, nous calculerons la somme cumulée des ventes totales par commande d'articles par date de vente.

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

Utilisation des fonctions d'agrégation pour trouver les enregistrements les plus récents

En utilisant la base de données de la bibliothèque , nous essayons de trouver le dernier livre ajouté à la base de données pour chaque auteur. Pour cet exemple simple, nous supposons un identifiant toujours incrémenté pour chaque enregistrement ajouté.

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

Au lieu de RANK, deux autres fonctions peuvent être utilisées pour commander. Dans l'exemple précédent, le résultat sera le même, mais ils donnent des résultats différents lorsque le classement donne plusieurs lignes pour chaque rang.

• RANK() : les doublons ont le même rang, le rang suivant prend en compte le nombre de doublons du rang précédent
• DENSE_RANK() : les doublons ont le même rang, le rang suivant est toujours supérieur au précédent
• ROW_NUMBER() : donnera à chaque ligne un "rang" unique, "classant" les doublons de manière aléatoire

Par exemple, si la table comportait une colonne non unique CreationDate et que le classement a été effectué sur cette base, la requête suivante:

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

Pourrait entraîner:

Division de données en compartiments à partition égale dans NTILE

Disons que vous avez des notes d'examen pour plusieurs examens et que vous souhaitez les diviser en quartiles par examen.

-- 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 fonctionne très bien lorsque vous avez vraiment besoin d'un nombre défini de compartiments et que chacun est rempli à peu près au même niveau. Notez qu'il serait trivial de séparer ces scores en percentiles en utilisant simplement ntile(100) .