Microsoft SQL Server
ÖVER klausul

parametrar

Anmärkningar

Använda aggregeringsfunktioner med OVER

Med hjälp av biltabellen kommer vi att beräkna den totala, max, min och genomsnittliga summa pengar som varje kund använt och har många gånger (COUNT) som hon tog med sig en bil för reparation.

Id KundId Mekanisk Modell Status Total kostnad

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'

Se upp för att använda ÖVER på detta sätt inte kommer att samla de rader som returneras. Ovanstående fråga returnerar följande:

De duplicerade raderna är kanske inte så användbara för rapporteringsändamål.

Om du bara vill aggregera data kommer du bättre att använda GROUP BY-klausulen tillsammans med lämpliga aggregerade funktioner, t.ex.

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

Kumulativ summa

Med hjälp av artikelförsäljningstabellen kommer vi att försöka ta reda på hur försäljningen av våra artiklar ökar genom datum. För att göra det kommer vi att beräkna den kumulativa summan av den totala försäljningen per artikelorder efter försäljningsdatumet.

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

Använd aggregeringsfunktioner för att hitta de senaste posterna

Med hjälp av biblioteksdatabasen försöker vi hitta den sista boken som lagts till i databasen för varje författare. För detta enkla exempel antar vi en alltid ökande id för varje post som läggs till.

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

I stället för RANK kan två andra funktioner användas för att beställa. I det föregående exemplet blir resultatet detsamma, men de ger olika resultat när beställningen ger flera rader för varje rang.

• RANK() : dubbletter får samma rang, nästa rang tar hänsyn till antalet duplikat i föregående rang
• DENSE_RANK() : duplikat får samma rang, nästa rang är alltid högre än föregående
• ROW_NUMBER() : ger varje rad en unik 'rang', 'rangordnar' duplikaten slumpmässigt

Till exempel, om tabellen hade en icke-unik kolumn CreationDate och beställningen gjordes baserad på detta, följande fråga:

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 resultera i:

Dela upp data i lika fördelade skopor med NTILE

Låt oss säga att du har betyg för flera tentor och att du vill dela upp dem i kvartiler per tentamen.

-- 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 fungerar bra när du verkligen behöver ett fast antal hinkar och var och en fylls till ungefär samma nivå. Lägg märke till att det skulle vara trivialt att separera dessa poäng i percentiler genom att helt enkelt använda ntile(100) .