Microsoft SQL Server
ÜBER Klausel

Parameter

Bemerkungen

Aggregationsfunktionen mit OVER verwenden

Anhand der Autos-Tabelle berechnen wir den Gesamtbetrag, die Höchst-, Mindest- und Durchschnittsbeträge, die jeder Kunde ausgegeben hat, und er hat (COUNT) viele Male (COUNT), die er mit einem Auto zur Reparatur gebracht hat.

Id CustomerId MechanicId-Modellstatus Gesamtkosten

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'

Beachten Sie, dass bei Verwendung von OVER auf diese Weise die zurückgegebenen Zeilen nicht zusammengefasst werden. Die obige Abfrage gibt Folgendes zurück:

Die duplizierten Zeilen sind möglicherweise nicht für Berichtszwecke nützlich.

Wenn Sie einfach Daten zusammenfassen möchten, können Sie die GROUP BY-Klausel zusammen mit den entsprechenden Aggregatfunktionen verwenden. ZB:

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

Kumulierte Summe

Anhand der Artikelverkaufstabelle versuchen wir herauszufinden, wie der Verkauf unserer Artikel im Laufe der Zeit steigt. Dazu berechnen wir die kumulierte Summe des Gesamtumsatzes pro Artikelbestellung bis zum Verkaufsdatum.

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

Aggregationsfunktionen verwenden, um die neuesten Datensätze zu finden

Mit der Bibliotheksdatenbank versuchen wir, das letzte der Datenbank hinzugefügte Buch für jeden Autor zu finden. Für dieses einfache Beispiel nehmen wir für jeden hinzugefügten Datensatz eine immer inkrementierende ID an.

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

Anstelle von RANK können zwei andere Funktionen zum Bestellen verwendet werden. Im vorherigen Beispiel ist das Ergebnis dasselbe, aber sie ergeben unterschiedliche Ergebnisse, wenn die Reihenfolge mehrere Reihen für jeden Rang ergibt.

• RANK() : Duplikate erhalten denselben Rang, der nächste Rang berücksichtigt die Anzahl der Duplikate im vorherigen Rang
• DENSE_RANK() : Duplikate erhalten denselben Rang, der nächste Rang ist immer um einen höheren als der vorherige
• ROW_NUMBER() : Jede Zeile erhält einen eindeutigen 'Rang', der die Duplikate zufällig ROW_NUMBER()

Wenn die Tabelle beispielsweise über eine nicht eindeutige Spalte CreationDate verfügt und darauf basierend die Reihenfolge festgelegt wurde, wird die folgende Abfrage angezeigt:

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

Könnte dazu führen:

Teilen von Daten in gleich partitionierte Buckets mit NTILE

Nehmen wir an, Sie haben Prüfungsergebnisse für mehrere Prüfungen und möchten diese pro Quartal in Quartile unterteilen.

-- 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 funktioniert ntile wenn Sie wirklich eine bestimmte Anzahl von Eimern benötigen und jedes auf ungefähr das gleiche Niveau gefüllt ist. Beachten Sie, dass es trivial wäre, diese Werte durch einfaches Verwenden von ntile(100) in Perzentile zu trennen.