Microsoft SQL Server
PONAD Klauzula

Parametry

Uwagi

Używanie funkcji agregacji z OVER

Korzystając z Tabeli samochodów , obliczymy całkowitą, maksymalną, minimalną i średnią kwotę, jaką każdy klient wydał i wiele razy (COUNT) przyniósł samochód do naprawy.

Identyfikator klienta Mechanik Status modelu Koszt całkowity

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'

Uwaga: użycie OVER w ten sposób nie spowoduje agregacji zwróconych wierszy. Powyższe zapytanie zwróci następujące informacje:

Zduplikowane wiersze mogą nie być przydatne do celów sprawozdawczych.

Jeśli chcesz po prostu agregować dane, lepiej skorzystasz z klauzuli GROUP BY wraz z odpowiednimi funkcjami agregującymi, np .:

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

Suma skumulowana

Korzystając z tabeli sprzedaży produktów , postaramy się dowiedzieć, w jaki sposób sprzedaż naszych produktów rośnie w różnych terminach. W tym celu obliczymy Skumulowaną Suma całkowitej sprzedaży na zamówienie na Przedmiot według daty sprzedaży.

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

Korzystanie z funkcji agregacji do znajdowania najnowszych rekordów

Korzystając z Bibliotecznej bazy danych , staramy się znaleźć ostatnią książkę dodaną do bazy danych dla każdego autora. W tym prostym przykładzie zakładamy zawsze wzrostowy identyfikator dla każdego dodanego rekordu.

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

Zamiast RANK można zamówić dwie inne funkcje. W poprzednim przykładzie wynik będzie taki sam, ale dają różne wyniki, gdy kolejność daje wiele wierszy dla każdej rangi.

• RANK() : duplikaty uzyskują tę samą rangę, kolejna ranga uwzględnia liczbę duplikatów z poprzedniej rangi
• DENSE_RANK() : duplikaty uzyskują tę samą rangę, kolejna ranga jest zawsze o jeden wyższa od poprzedniej
• ROW_NUMBER() : nada każdemu wierszowi unikalny „stopień”, „ranking” losowy duplikatów

Na przykład, jeśli tabela miała nieunikalną kolumnę CreationDate i na tej podstawie uporządkowano, następujące zapytanie:

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

Może to spowodować:

Dzielenie danych na równo podzielone segmenty za pomocą NTILE

Powiedzmy, że masz wyniki egzaminów z kilku egzaminów i chcesz podzielić je na kwartyle na egzamin.

-- 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 działa świetnie, gdy naprawdę potrzebujesz określonej liczby wiader i wypełnionych do mniej więcej tego samego poziomu. Zauważ, że podzielenie tych wyników na percentyle byłoby trywialne za pomocą ntile(100) .