Suche…


Parameter

Parameter Einzelheiten
PARTITION VON Das Feld bzw. die Felder, die auf PARTITION BY folgen, sind die, auf denen die 'Gruppierung' basiert

Bemerkungen

Die OVER-Klausel bestimmt ein Fenster oder eine Teilmenge einer Zeile innerhalb einer Abfrageergebnismenge. Eine Fensterfunktion kann angewendet werden, um einen Wert für jede Zeile in der Gruppe festzulegen und zu berechnen. Die OVER-Klausel kann verwendet werden mit:

  • Ranking-Funktionen
  • Aggregatfunktionen

So kann jemand aggregierte Werte berechnen, z. B. gleitende Durchschnitte, kumulierte Aggregate, laufende Summen oder ein Top N pro Gruppe.

In sehr abstrakter Weise können wir sagen, dass sich OVER wie GROUP BY verhält. OVER wird jedoch pro Feld / Spalte angewendet und nicht als Ganzes auf die Abfrage wie bei GROUP BY.

Hinweis 1: In SQL Server 2008 (R2) kann die ORDER BY-Klausel nicht mit Aggregatfensterfunktionen ( Verknüpfung ) verwendet werden.

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:

Kundennummer Gesamt Durchschn Anzahl Mindest Max
1 430 215 2 200 230
1 430 215 2 200 230

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:

Autor Titel Erstellungsdatum RANG DENSE_RANK ZEILENNUMMER
Autor 1 Buch 1 22/07/2016 1 1 1
Autor 1 Buch 2 22/07/2016 1 1 2
Autor 1 Buch 3 21/07/2016 3 2 3
Autor 1 Buch 4 21/07/2016 3 2 4
Autor 1 Buch 5 21/07/2016 3 2 5
Autor 1 Buch 6 07.04.2016 6 3 6
Autor 2 Buch 7 07.04.2016 1 1 1

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

Unsere Prüfungsdaten sind in Viertel pro Prüfung unterteilt

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.



Modified text is an extract of the original Stack Overflow Documentation
Lizenziert unter CC BY-SA 3.0
Nicht angeschlossen an Stack Overflow