Recherche…


Paramètres

Paramètre Détails
PARTITION PAR Le champ qui suit PARTITION BY est celui sur lequel le «groupement» sera basé

Remarques

La clause OVER détermine une fenêtre ou un sous-ensemble de ligne dans un ensemble de résultats de requête. Une fonction de fenêtre peut être appliquée pour définir et calculer une valeur pour chaque ligne de l'ensemble. La clause OVER peut être utilisée avec:

  • Fonctions de classement
  • Fonctions d'agrégat

Ainsi, une personne peut calculer des valeurs agrégées telles que des moyennes mobiles, des agrégats cumulatifs, des totaux cumulés ou les N meilleurs résultats par groupe.

De manière très abstraite, nous pouvons dire que OVER se comporte comme GROUP BY. Cependant, OVER est appliqué par champ / colonne et non à la requête dans son ensemble, comme le fait GROUP BY.

Remarque # 1: Dans SQL Server 2008 (R2), ORDER BY Clause ne peut pas être utilisé avec les fonctions de fenêtre d'agrégat ( lien ).

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:

N ° de client Total Avg Compter Min Max
1 430 215 2 200 230
1 430 215 2 200 230

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:

Auteur Titre Date de création RANG DENSE_RANK ROW_NUMBER
Auteur 1 Livre 1 22/07/2016 1 1 1
Auteur 1 Livre 2 22/07/2016 1 1 2
Auteur 1 Livre 3 21/07/2016 3 2 3
Auteur 1 Livre 4 21/07/2016 3 2 4
Auteur 1 Livre 5 21/07/2016 3 2 5
Auteur 1 Livre 6 04/07/2016 6 3 6
Auteur 2 Livre 7 04/07/2016 1 1 1

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

Nos données d'examen divisées en quartiles par examen

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) .



Modified text is an extract of the original Stack Overflow Documentation
Sous licence CC BY-SA 3.0
Non affilié à Stack Overflow