SQL OVER(Partition by) function

Jednym z zastosowań funkcji OVER() jest wykorzystanie funkcji grupujących bez klauzuli GROUP BY. W jednym zapytaniu możemy pokazać wyniki „nie pogrupowanego” zapytania i „dorzucić” do tych wyników funkcje agregujące.
Jak to wygląda w praktyce? Wyobraźmy sobie sytuację, że mamy zaprezentować wyniki sprzedaży. Do tej pory sytuacja wyglądała tak. Jeśli chcieliśmy zaprezentować zamówienia z listą produktów/pozycji w tym zamówieniu to nie wykorzystywaliśmy klauzuli GROUP BY, ale jeśli chcieliśmy np. zaprezentować wartość poszczególnych pozycji (czyli np. ilość szt. danego produktu * cena jednostkowa) albo zliczyć ilość pozycji w zamówieniu lub policzyć ilość szt. produktów w zamówieniu to korzystaliśmy z GROUP BY. Niestety nie dało się tych wyników przedstawić za pomocą jednego zapytania. Za pomocą funkcji OVER() jest to możliwe. Poniżej składnia funkcji OVER(Partition by)

SQL OVER(Partition by) składnia funkcji

SELECT
         kolumna1
        ,kolumna2
        ,kolumna3
        ,funkcjaAgregująca(argumentFunkcjiAgregującej) OVER(Partition by argumentOver)

argumentFunkcjiAgregującej – to nic innego jak określenie (w zależności od użytej funkcji agregującej) co ma zrobić dana funkcja
argumentOver – tutaj określamy kryteria grupowania

---

Przykład zastosowania funkcji OVER(Partition by) (baza AdventureWorks).

Wyświetl informacje o zamówieniach z grudnia 2005 r. Wyniki przedstaw w postaci: ID zamówienia , ID produktu , Cena jednostkowa , Liczba szt. produktu w pozycji , Wartość pozycji w zamówieniu , Wartość zamówienia , Licza pozycji w zamówieniu , Licza produktów w zamówieniu.

SE AdventureWorks2008R2

SELECT
     O.SalesOrderID AS [ID zamówienia]
    ,D.ProductID AS [ID produktu]
    ,D.UnitPrice AS [Cena jednostkowa]
    ,D.OrderQty AS [Liczba szt. produktu w pozycji]
    ,(D.UnitPrice * D.OrderQty) AS [Wartość pozycji w zamówieniu]
    ,SUM(D.UnitPrice * D.OrderQty) OVER(Partition by O.SalesOrderID ) AS [Wartość zamówienia]
    ,COUNT(*) OVER(Partition by O.SalesOrderID ) AS [Licza pozycji w zamówieniu]
    ,SUM(D.OrderQty) OVER(Partition by O.SalesOrderID ) AS [Licza produktów w zamówieniu]
FROM
    Sales.SalesOrderHeader O
    JOIN Sales.SalesOrderDetail D ON O.SalesOrderID = D.SalesOrderID
WHERE
    O.OrderDate BETWEEN '2005-12-01′ AND '2005-12-31′

---

Przykład zastosowania OVER(Partition by) (baza Northwind)

Wyświetl informacje o zamówieniach z lipca 1996 r. Wyniki przedstaw w postaci: ID zamówienia , ID produktu , Cena jednostkowa , Liczba szt. produktu w pozycji , Wartość pozycji w zamówieniu , Wartość zamówienia , Licza pozycji w zamówieniu , Licza produktów w zamówieniu.

SELECT
     OD.OrderID AS [ID zamówienia]
    ,ProductID AS [ID produktu]
    ,UnitPrice AS [Cena jednostkowa]
    ,Quantity AS [Liczba szt. produktu w pozycji]
    ,(UnitPrice * Quantity) AS [Wartość pozycji w zamówieniu]
    ,SUM(UnitPrice*Quantity) OVER(Partition by OD.OrderID ) AS [Wartość zamówienia]
    ,COUNT(*) OVER(Partition by OD.OrderID ) AS [Licza pozycji w zamówieniu]
    ,SUM(Quantity) OVER(Partition by OD.OrderID ) AS [Licza produktów w zamówieniu]
FROM
    [Order Details] OD
    JOIN Orders O ON OD.OrderID = O.OrderID
WHERE
    O.OrderDate BETWEEN '1996-07-01′ AND '1996-07-31′