Microsoft SQL Server
OVER clausule

parameters

Opmerkingen

Aggregatiefuncties gebruiken met OVER

Aan de hand van de tabel Auto's berekenen we het totale, maximale, minimale en gemiddelde geldbedrag dat elke klant heeft uitgegeven en we hebben meerdere keren (COUNT) meegenomen voor reparatie.

Id Klant-id MechanicId Model Status Totale kosten

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'

Houd er rekening mee dat het gebruik van OVER op deze manier de geretourneerde rijen niet verzamelt. De bovenstaande zoekopdracht retourneert het volgende:

De dubbele rij (en) zijn mogelijk niet zo handig voor rapportagedoeleinden.

Als u gewoon gegevens wilt verzamelen, kunt u beter de GROUP BY-clausule gebruiken in combinatie met de juiste aggregatiefuncties, bijvoorbeeld:

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

Cumulatieve som

Met behulp van de artikelverkooptabel proberen we erachter te komen hoe de verkoop van onze artikelen stijgt door de datums. Hiertoe berekenen we de cumulatieve som van de totale omzet per artikelorder op de verkoopdatum.

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

Aggregatiefuncties gebruiken om de meest recente records te vinden

Met behulp van de bibliotheekdatabase proberen we voor elke auteur het laatste boek te vinden dat aan de database is toegevoegd. Voor dit eenvoudige voorbeeld gaan we uit van een altijd toenemende ID voor elke toegevoegde record.

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

In plaats van RANK kunnen twee andere functies worden gebruikt om te bestellen. In het vorige voorbeeld is het resultaat hetzelfde, maar ze geven verschillende resultaten wanneer de volgorde meerdere rijen voor elke rang geeft.

• RANK() : duplicaten krijgen dezelfde rang, de volgende rang houdt rekening met het aantal duplicaten in de vorige rang
• DENSE_RANK() : duplicaten krijgen dezelfde rang, de volgende rang is altijd één hoger dan de vorige
• ROW_NUMBER() : geeft elke rij een unieke 'rang', waarbij de duplicaten willekeurig worden 'geclassificeerd'

Als de tabel bijvoorbeeld een niet-unieke kolom CreationDate had en de bestelling op basis daarvan was gedaan, de volgende 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

Kan resulteren in:

Gegevens verdelen in emmers met gelijke verdeling met behulp van NTILE

Laten we zeggen dat u examenscores hebt voor verschillende examens en u deze per examen in kwartielen wilt verdelen.

-- 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 werkt geweldig als je echt een bepaald aantal emmers nodig hebt en elk tot ongeveer hetzelfde niveau gevuld. Merk op dat het triviaal zou zijn om deze scores in percentielen te scheiden door eenvoudig ntile(100) .