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.)
- Bekende functies zoals
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.