De SQL OVER()-clausule – wanneer en waarom is het nuttig?

Ik heb over die clausule gelezen en ik begrijp niet waarom ik die nodig heb.
Wat doet de functie OVER? Wat doet Partitioning By?
Waarom kan ik geen vraag stellen door GROUP BY SalesOrderIDte schrijven?


Antwoord 1, autoriteit 100%

U kuntGROUP BY SalesOrderIDgebruiken. Het verschil is dat u met GROUP BY alleen de geaggregeerde waarden kunt hebben voor de kolommen die niet zijn opgenomen in GROUP BY.

Als u daarentegen statistische functies in vensters gebruikt in plaats van GROUP BY, kunt u zowel geaggregeerde als niet-geaggregeerde waarden ophalen. Dat wil zeggen, hoewel u dat niet doet in uw voorbeeldquery, kunt u zowel individuele OrderQty-waarden als hun sommen, tellingen, gemiddelden enz. ophalen over groepen van dezelfde SalesOrderIDs .

Hier is een praktisch voorbeeld van waarom vensteraggregaten geweldig zijn. Stel dat u moet berekenen welk percentage van een totaal elke waarde is. Zonder vensteraggregaten zou u eerst een lijst met geaggregeerde waarden moeten afleiden en deze vervolgens weer samenvoegen met de oorspronkelijke rijenset, d.w.z. als volgt:

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]

Kijk nu hoe u hetzelfde kunt doen met een aggregaat met vensters:

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

Veel gemakkelijker en schoner, nietwaar?


Antwoord 2, autoriteit 46%

De OVER-clausule is krachtig omdat u aggregaten kunt hebben over verschillende bereiken (“windowing”), of u nu een GROUP BYgebruikt of niet

Voorbeeld: krijg telling per SalesOrderIDen telling van alle

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

Krijg verschillende COUNTs, geen GROUP 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)

Antwoord 3, autoriteit 32%

Als u alleen op de SalesOrderID wilt GROUPEREN, kunt u de kolommen ProductID en OrderQty niet opnemen in de SELECT-clausule.

Met de clausule PARTITION BY kunt u uw geaggregeerde functies opsplitsen. Een voor de hand liggend en nuttig voorbeeld zou zijn als u regelnummers voor orderregels op een order wilt genereren:

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

(Mijn syntaxis kan iets afwijken)

Je krijgt dan zoiets terug als:

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

Antwoord 4, autoriteit 29%

Laat me het uitleggen met een voorbeeld, dan kun je zien hoe het werkt.

Ervan uitgaande dat u de volgende tabel DIM_EQUIPMENT heeft:

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

Run onder SQL

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

Het resultaat zou zijn zoals hieronder

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

Kijk wat er is gebeurd.

Je kunt zonder Group By op YEAR tellen en matchen met ROW.

Een andere interessante MANIER om hetzelfde resultaat te krijgen als zoals hieronder met behulp van WITH-clausule, WITH werkt als in-line VIEW en kan de query vereenvoudigen, vooral complexe, wat hier echter niet het geval is, omdat ik alleen maar probeer het gebruik te tonen

p>

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;

Antwoord 5, autoriteit 12%

De OVER-clausule in combinatie met PARTITION BY geeft aan dat de voorgaande functieaanroep analytisch moet worden uitgevoerd door de geretourneerde rijen van de query te evalueren. Zie het als een inline GROUP BY-instructie.

OVER (PARTITION BY SalesOrderID)geeft aan dat voor de functie SUM, AVG, etc… de waarde OVER een subset van de geretourneerde records van de query wordt geretourneerd, en PARTITION die subset DOOR de buitenlandse sleutel SalesOrderID.

Dus we zullen elk OrderQty-record SOMMEN voor ELKE UNIEKE SalesOrder-ID, en die kolomnaam wordt ‘Totaal’ genoemd.

Het is VEEL efficiënter dan het gebruik van meerdere inline weergaven om dezelfde informatie te vinden. U kunt deze zoekopdracht in een inline-weergave plaatsen en vervolgens op Totaal filteren.

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

Antwoord 6, autoriteit 3%

  • Ook wel Query Petition-clausule genoemd.
  • Vergelijkbaar met de Group By-clausule

    • verdeel gegevens in stukken (of partities)
    • scheiden door partitiegrenzen
    • functie werkt binnen partities
    • opnieuw geïnitialiseerd bij het overschrijden van de scheidingsgrens

Syntaxis:
functie (…) OVER (PARTITIE DOOR col1 col3,…)

  • Functies

    • Bekende functies zoals COUNT(), SUM(), MIN(), MAX(), enz.
    • Ook nieuwe functies (bijv. ROW_NUMBER(), RATION_TO_REOIRT(), enz.)

Meer info met voorbeeld: http://msdn.microsoft.com/en-us /bibliotheek/ms189461.aspx


Antwoord 7, autoriteit 3%

Dus in eenvoudige bewoordingen:
De Over-clausule kan worden gebruikt om niet-geaggregeerde waarden samen met geaggregeerde waarden te selecteren.

Partition BY, ORDER BYinside, en ROWS of RANGEmaken deel uit van OVER() by-clausule.

partitionering door wordt gebruikt om gegevens te partitioneren en vervolgens deze venster-, geaggregeerde functies uit te voeren, en als we geen partitie hebben met de gehele resultaatset wordt beschouwd als een enkele partitie.

OVER-clausule kan worden gebruikt met rangschikkingsfuncties (rang, rijnummer, dichte_rangschikking…), geaggregeerde functies zoals (AVG, Max, Min, SUM…etc) en analysefuncties zoals (First_Value, Last_Value en enkele andere) .

Laten we de basissyntaxis van de OVER-clausule bekijken

OVER (   
       [ <PARTITION BY clause> ]  
       [ <ORDER BY clause> ]   
       [ <ROW or RANGE clause> ]  
      )  

VERDELING DOOR:
Het wordt gebruikt om gegevens te partitioneren en bewerkingen uit te voeren op groepen met dezelfde gegevens.

BESTEL OP:
Het wordt gebruikt om de logische volgorde van gegevens in partities te definiëren. Als we geen Partitie specificeren, wordt de volledige resultatenset beschouwd als een enkele partitie

:
Dit kan worden gebruikt om aan te geven welke rijen in een partitie moeten worden beschouwd bij het uitvoeren van de bewerking.

Laten we een voorbeeld nemen:

Hier is mijn dataset:

Id          Name                                               Gender     Salary
----------- -------------------------------------------------- ---------- -----------
1           Mark                                               Male       5000
2           John                                               Male       4500
3           Pavan                                              Male       5000
4           Pam                                                Female     5500
5           Sara                                               Female     4000
6           Aradhya                                            Female     3500
7           Tom                                                Male       5500
8           Mary                                               Female     5000
9           Ben                                                Male       6500
10          Jodi                                               Female     7000
11          Tom                                                Male       5500
12          Ron                                                Male       5000

Dus laat me verschillende scenario’s uitvoeren en kijken hoe de gegevens worden beïnvloed en ik zal van een moeilijke syntaxis naar een eenvoudige gaan

Select *,SUM(salary) Over(order by salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sum_sal from employees
Id          Name                                               Gender     Salary      sum_sal
----------- -------------------------------------------------- ---------- ----------- -----------
6           Aradhya                                            Female     3500        3500
5           Sara                                               Female     4000        7500
2           John                                               Male       4500        12000
3           Pavan                                              Male       5000        32000
1           Mark                                               Male       5000        32000
8           Mary                                               Female     5000        32000
12          Ron                                                Male       5000        32000
11          Tom                                                Male       5500        48500
7           Tom                                                Male       5500        48500
4           Pam                                                Female     5500        48500
9           Ben                                                Male       6500        55000
10          Jodi                                               Female     7000        62000

Let gewoon op het deel sum_sal. Hier gebruik ik de volgorde van salaris en gebruik “RANGE TUSSEN ONGEKENDE VOORAFGAANDE EN HUIDIGE RIJ”.
In dit geval gebruiken we geen partitie, dus volledige gegevens worden als één partitie behandeld en we bestellen op salaris.
En het belangrijkste hier is ONBEGREPEN VOORAFGAANDE EN HUIDIGE RIJ. Dit betekent dat wanneer we de som berekenen, van de startrij tot de huidige rij voor elke rij.
Maar als we rijen zien met salaris 5000 en naam = “Pavan”, zou het idealiter 17000 moeten zijn en voor salaris = 5000 en naam = Mark zou het 22000 moeten zijn. Maar aangezien we RANGEgebruiken en in in dit geval, als het soortgelijke elementen vindt, beschouwt het ze als dezelfde logische groep en voert het een bewerking uit en kent het waarde toe aan elk item in die groep. Dat is de reden waarom we dezelfde waarde hebben voor salaris = 5000. De engine ging naar salaris=5000 en Naam=Ron en berekende de som en wees het vervolgens toe aan alle salaris=5000.

Select *,SUM(salary) Over(order by salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sum_sal from employees
   Id          Name                                               Gender     Salary      sum_sal
----------- -------------------------------------------------- ---------- ----------- -----------
6           Aradhya                                            Female     3500        3500
5           Sara                                               Female     4000        7500
2           John                                               Male       4500        12000
3           Pavan                                              Male       5000        17000
1           Mark                                               Male       5000        22000
8           Mary                                               Female     5000        27000
12          Ron                                                Male       5000        32000
11          Tom                                                Male       5500        37500
7           Tom                                                Male       5500        43000
4           Pam                                                Female     5500        48500
9           Ben                                                Male       6500        55000
10          Jodi                                               Female     7000        62000

Dus met RIJEN TUSSEN ONGEBONDEN VOORAFGAANDE EN HUIDIGE RIJHet verschil is voor items met dezelfde waarde in plaats van ze samen te groeperen. Het berekent SOM van de startrij tot de huidige rij en behandelt items niet met hetzelfde waarde anders, zoals BEREIK

Select *,SUM(salary) Over(order by salary) as sum_sal from employees
Id          Name                                               Gender     Salary      sum_sal
----------- -------------------------------------------------- ---------- ----------- -----------
6           Aradhya                                            Female     3500        3500
5           Sara                                               Female     4000        7500
2           John                                               Male       4500        12000
3           Pavan                                              Male       5000        32000
1           Mark                                               Male       5000        32000
8           Mary                                               Female     5000        32000
12          Ron                                                Male       5000        32000
11          Tom                                                Male       5500        48500
7           Tom                                                Male       5500        48500
4           Pam                                                Female     5500        48500
9           Ben                                                Male       6500        55000
10          Jodi                                               Female     7000        62000

Deze resultaten zijn hetzelfde als

Select *, SUM(salary) Over(order by salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sum_sal from employees

Dat komt omdat Over(volgorde op salaris)slechts een kortere weg is van Over(volgorde op salarisBEREIK TUSSEN ONGEBONDEN VOORAFGAANDE EN HUIDIGE RIJ)
Dus waar we gewoon Order opspecificeren zonder ROWS of RANGE, wordt standaard RANGE TUSSEN UNBOUNDED PRECEDING EN CURRENT ROWgebruikt.

Opmerking: dit is alleen van toepassing op functies die daadwerkelijk RANGE/ROW accepteren. ROW_NUMBER en enkele anderen accepteren bijvoorbeeld geen RANGE/ROW en in dat geval komt dit niet in beeld.

Tot nu toe zagen we dat de Over-clausule met een order by Range/ROWS aanneemt en dat de syntaxis er ongeveer zo uitziet RANGE TUSSEN UNBOUNDED VOORAFGAANDE EN HUIDIGE RIJ
En het is eigenlijk aan het rekenen tot aan de huidige rij vanaf de eerste rij. Maar wat als het waarden wil berekenen voor de gehele gegevenspartitie en deze voor elke kolom wil hebben (dat is van de 1e rij tot de laatste rij). Hier is de vraag daarvoor

Select *,sum(salary) Over(order by salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as sum_sal from employees
Id          Name                                               Gender     Salary      sum_sal
----------- -------------------------------------------------- ---------- ----------- -----------
1           Mark                                               Male       5000        62000
2           John                                               Male       4500        62000
3           Pavan                                              Male       5000        62000
4           Pam                                                Female     5500        62000
5           Sara                                               Female     4000        62000
6           Aradhya                                            Female     3500        62000
7           Tom                                                Male       5500        62000
8           Mary                                               Female     5000        62000
9           Ben                                                Male       6500        62000
10          Jodi                                               Female     7000        62000
11          Tom                                                Male       5500        62000
12          Ron                                                Male       5000        62000

In plaats van CURRENT ROW, specificeer ik UNBOUNDED FOLLOWINGwaarmee de engine wordt opgedragen om te berekenen tot de laatste record van partitie voor elke rij.

Komt u nu op uw punt over wat is OVER() met lege accolades?

Het is slechts een kortere weg voor Over (volgorde op salaris RIJEN TUSSEN ONGEBONDEN VOORAFGAANDE EN ONGEBONDEN VOLGENDE)

Hier specificeren we indirect om al mijn resultatensets als een enkele partitie te behandelen en vervolgens berekeningen uit te voeren van het eerste record tot het laatste record van elke partitie.

Select *,Sum(salary) Over() as sum_sal from employees
Id          Name                                               Gender     Salary      sum_sal
----------- -------------------------------------------------- ---------- ----------- -----------
1           Mark                                               Male       5000        62000
2           John                                               Male       4500        62000
3           Pavan                                              Male       5000        62000
4           Pam                                                Female     5500        62000
5           Sara                                               Female     4000        62000
6           Aradhya                                            Female     3500        62000
7           Tom                                                Male       5500        62000
8           Mary                                               Female     5000        62000
9           Ben                                                Male       6500        62000
10          Jodi                                               Female     7000        62000
11          Tom                                                Male       5500        62000
12          Ron                                                Male       5000        62000

Ik heb hier een video over gemaakt en als je geïnteresseerd bent, kun je die bezoeken.
https://www.youtube.com/watch?v=CvVenuVUqto&t= 1177s

Bedankt,
Pavan Kumar Aryasomayajulu
HTTP://xyzcoder.github.io


Antwoord 8

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

Dat is het resultaat van een zoekopdracht. Tabel die als bron wordt gebruikt, is dezelfde, behalve dat deze geen laatste kolom heeft. Deze kolom is een bewegende som van de derde.

Vraag:

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

(tabel gaat als public.iuk)

sql version:  2012

Het is iets meer dan dbase (1986) niveau, ik weet niet waarom er meer dan 25 jaar nodig was om het af te maken.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Other episodes