Klauzula SQL OVER () - kiedy i dlaczego jest przydatna?

    USE AdventureWorks2008R2;
GO
SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg'
    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count'
    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min'
    ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Max'
FROM Sales.SalesOrderDetail 
WHERE SalesOrderID IN(43659,43664);

Czytałem o tej klauzuli i nie rozumiem, dlaczego jej potrzebuję. Co robi ta funkcja Over? Co robi Partitioning By? Dlaczego nie mogę wykonać zapytania podczas pisania Group By SalesOrderID?

Państwo może używać GROUP BY SalesOrderID. Różnica polega na tym, że w przypadku GROUP BY można mieć tylko zagregowane wartości dla kolumn, które nie są uwzględnione w GROUP BY.

W przeciwieństwie do tego, używając funkcji agregujących w oknie zamiast GROUP BY, można pobrać zarówno wartości zagregowane, jak i niezagregowane. Oznacza to, że chociaż nie robisz tego w swoim przykładowym zapytaniu, możesz pobrać zarówno pojedyncze OrderQtywartości, jak i ich sumy, liczebności, średnie itp. W grupach o tych samych wartościach SalesOrderID.

Oto praktyczny przykład, dlaczego kruszywa okienne są świetne. Załóżmy, że musisz obliczyć, jaki procent sumy stanowi każda wartość. Bez agregatów w oknie trzeba najpierw wyprowadzić listę zagregowanych wartości, a następnie połączyć ją z powrotem z oryginalnym zestawem wierszy, tj. W ten sposób:

SELECT
  orig.[Partition],
  orig.Value,
  orig.Value * 100.0 / agg.TotalValue AS ValuePercent
FROM OriginalRowset orig
  INNER JOIN (
    SELECT
      [Partition],
      SUM(Value) AS TotalValue
    FROM OriginalRowset
    GROUP BY [Partition]
  ) agg ON orig.[Partition] = agg.[Partition]

Teraz spójrz, jak możesz zrobić to samo z agregatem okienkowym:

SELECT
  [Partition],
  Value,
  Value * 100.0 / SUM(Value) OVER (PARTITION BY [Partition]) AS ValuePercent
FROM OriginalRowset orig

O wiele łatwiejsze i czystsze, prawda?

OVERKlauzula jest potężny, że można mieć ponad agregaty różnych zakresach ( „okienkowych”), czy używać GROUP BYczy nie

Przykład: uzyskaj liczbę na SalesOrderIDi policz wszystkich

SELECT
    SalesOrderID, ProductID, OrderQty
    ,COUNT(OrderQty) AS 'Count'
    ,COUNT(*) OVER () AS 'CountAll'
FROM Sales.SalesOrderDetail 
WHERE
     SalesOrderID IN(43659,43664)
GROUP BY
     SalesOrderID, ProductID, OrderQty

Zdobądź różne COUNT, nieGROUP BY

SELECT
    SalesOrderID, ProductID, OrderQty
    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'CountQtyPerOrder'
    ,COUNT(OrderQty) OVER(PARTITION BY ProductID) AS 'CountQtyPerProduct',
    ,COUNT(*) OVER () AS 'CountAllAgain'
FROM Sales.SalesOrderDetail 
WHERE
     SalesOrderID IN(43659,43664)

Gdybyś chciał tylko GRUPOWAĆ WEDŁUG SalesOrderID, nie byłbyś w stanie uwzględnić kolumn ProductID i OrderQty w klauzuli SELECT.

Klauzula PARTITION BY umożliwia rozbicie funkcji agregujących. Jednym z oczywistych i przydatnych przykładów byłoby wygenerowanie numerów linii dla linii zamówienia w zamówieniu:

SELECT
    O.order_id,
    O.order_date,
    ROW_NUMBER() OVER(PARTITION BY O.order_id) AS line_item_no,
    OL.product_id
FROM
    Orders O
INNER JOIN Order_Lines OL ON OL.order_id = O.order_id

(Moja składnia może się nieznacznie różnić)

Otrzymasz wtedy coś takiego:

order_id    order_date    line_item_no    product_id
--------    ----------    ------------    ----------
    1       2011-05-02         1              5
    1       2011-05-02         2              4
    1       2011-05-02         3              7
    2       2011-05-12         1              8
    2       2011-05-12         2              1

Pozwól, że wyjaśnię na przykładzie, a będziesz mógł zobaczyć, jak to działa.

Zakładając, że masz następującą tabelę DIM_EQUIPMENT:

VIN         MAKE    MODEL   YEAR    COLOR
-----------------------------------------
1234ASDF    Ford    Taurus  2008    White
1234JKLM    Chevy   Truck   2005    Green
5678ASDF    Ford    Mustang 2008    Yellow

Uruchom poniżej SQL

SELECT VIN,
  MAKE,
  MODEL,
  YEAR,
  COLOR ,
  COUNT(*) OVER (PARTITION BY YEAR) AS COUNT2
FROM DIM_EQUIPMENT

Wynik byłby taki, jak poniżej

VIN         MAKE    MODEL   YEAR    COLOR     COUNT2
 ----------------------------------------------  
1234JKLM    Chevy   Truck   2005    Green     1
5678ASDF    Ford    Mustang 2008    Yellow    2
1234ASDF    Ford    Taurus  2008    White     2

Zobacz, co się stało.

Możesz liczyć bez grupowania według YEAR i dopasować do ROW.

Kolejny interesujący SPOSÓB na uzyskanie tego samego wyniku, jeśli jak poniżej użycie klauzuli WITH, DZ działa jak VIEW in-line i może uprościć zapytanie, szczególnie złożone, co jednak nie ma miejsca w tym przypadku, ponieważ próbuję tylko pokazać użycie

 WITH EQ AS
  ( SELECT YEAR AS YEAR2, COUNT(*) AS COUNT2 FROM DIM_EQUIPMENT GROUP BY YEAR
  )
SELECT VIN,
  MAKE,
  MODEL,
  YEAR,
  COLOR,
  COUNT2
FROM DIM_EQUIPMENT,
  EQ
WHERE EQ.YEAR2=DIM_EQUIPMENT.YEAR;

Klauzula OVER w połączeniu z PARTITION BY stanowi, że poprzedzające wywołanie funkcji musi być wykonane analitycznie, oceniając zwrócone wiersze zapytania. Potraktuj to jako wbudowaną instrukcję GROUP BY.

OVER (PARTITION BY SalesOrderID) stwierdza, że ​​dla funkcji SUMA, ŚREDNIA, itd ..., zwraca wartość OVER podzbioru zwróconych rekordów z zapytania i PARTITION, który podzbiór według klucza obcego SalesOrderID.

Zsumujemy więc każdy rekord OrderQty dla KAŻDEGO UNIQUE SalesOrderID, a ta nazwa kolumny będzie miała nazwę „Razem”.

Jest to O WIELE bardziej efektywny sposób niż korzystanie z wielu widoków wbudowanych w celu uzyskania tych samych informacji. Możesz umieścić to zapytanie w widoku wbudowanym i filtrować według sumy.

SELECT ...,
FROM (your query) inlineview
WHERE Total < 200

• Nazywane również Query Petition klauzulą.

• Podobnie jak w Group Byklauzuli

  • rozbić dane na porcje (lub partycje)
  • oddzielone granicami partycji
  • funkcja działa w partycjach
  • ponownie inicjowane podczas przekraczania granicy podziału

Składnia:
funkcja (...) OVER (PARTITION BY col1 col3, ...)

• Funkcje

  • Znajome funkcje, takie jak COUNT(), SUM(), MIN(),MAX() , etc
  • Nowe funkcje oraz (na przykład ROW_NUMBER(), RATION_TO_REOIRT()etc.)

Więcej informacji z przykładem: http://msdn.microsoft.com/en-us/library/ms189461.aspx

prkey   whatsthat               cash   
890    "abb                "   32  32
43     "abbz               "   2   34
4      "bttu               "   1   35
45     "gasstuff           "   2   37
545    "gasz               "   5   42
80009  "hoo                "   9   51
2321   "ibm                "   1   52
998    "krk                "   2   54
42     "kx-5010            "   2   56
32     "lto                "   4   60
543    "mp                 "   5   65
465    "multipower         "   2   67
455    "O.N.               "   1   68
7887   "prem               "   7   75
434    "puma               "   3   78
23     "retractble         "   3   81
242    "Trujillo's stuff   "   4   85

To wynik zapytania. Tabela używana jako źródło jest taka sama, z wyjątkiem tego, że nie ma ostatniej kolumny. Ta kolumna jest ruchomą sumą trzeciej.

Pytanie:

SELECT prkey,whatsthat,cash,SUM(cash) over (order by whatsthat)
    FROM public.iuk order by whatsthat,prkey
    ;

(tabela staje się publiczna.iuk)

sql version:  2012

To trochę ponad poziom dbase (1986), nie wiem, dlaczego potrzeba było ponad 25 lat, aby go dokończyć.