Ik heb door de jaren heen GROUP BYgebruikt voor alle soorten verzamelde zoekopdrachten. Onlangs heb ik een aantal code reverse-engineered die PARTITION BYgebruikt om aggregaties uit te voeren. Bij het doorlezen van alle documentatie die ik kan vinden over PARTITION BY, lijkt het veel op GROUP BY, misschien met een beetje extra functionaliteit toegevoegd? Zijn het twee versies van dezelfde algemene functionaliteit, of zijn ze iets totaal anders?
Antwoord 1, autoriteit 100%
Ze worden op verschillende plaatsen gebruikt. GROUP BYpast de hele zoekopdracht aan, zoals:
select customerId, count(*) as orderCount
from Orders
group by customerId
Maar PARTITION BYwerkt gewoon op a vensterfunctie, zoals row_number:
select row_number() over (partition by customerId order by orderId)
as OrderNumberForThisCustomer
from Orders
Een GROUP BYvermindert normaal gesproken het aantal geretourneerde rijen door ze op te rollen en gemiddelden of sommen voor elke rij te berekenen. PARTITION BYheeft geen invloed op het aantal geretourneerde rijen, maar het verandert hoe het resultaat van een vensterfunctie wordt berekend.
Antwoord 2, autoriteit 59%
We kunnen een eenvoudig voorbeeld nemen.
Beschouw een tabel met de naam TableAmet de volgende waarden:
id firstname lastname Mark
-------------------------------------------------------------------
1 arun prasanth 40
2 ann antony 45
3 sruthy abc 41
6 new abc 47
1 arun prasanth 45
1 arun prasanth 49
2 ann antony 49
GROUP BY
De SQL GROUP BY-clausule kan worden gebruikt in een SELECT-instructie om te verzamelen
gegevens over meerdere records en groepeer de resultaten op een of meer
kolommen.In meer eenvoudige woorden wordt de GROUP BY-instructie gebruikt in combinatie met
de aggregatiefuncties om de resultaatset te groeperen op een of meer
kolommen.
Syntaxis:
SELECT expression1, expression2, ... expression_n,
aggregate_function (aggregate_expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n;
We kunnen GROUP BYtoepassen in onze tabel:
select SUM(Mark)marksum,firstname from TableA
group by id,firstName
Resultaten:
marksum firstname
----------------
94 ann
134 arun
47 new
41 sruthy
In onze echte tabel hebben we 7 rijen en wanneer we GROUP BY idtoepassen, groepeert de server de resultaten op basis van id:
In eenvoudige bewoordingen:
hier
GROUP BYvermindert normaal gesproken het aantal rijen dat wordt geretourneerd door te rollen
ze op en berekentSum()voor elke rij.
PARTITION BY
Laten we, voordat we naar PARTITION BY gaan, kijken naar de OVER-clausule:
Volgens de MSDN-definitie:
OVER-clausule definieert een venster of een door de gebruiker opgegeven reeks rijen binnen a
set met queryresultaten. Een vensterfunctie berekent vervolgens een waarde voor elke rij
in het raam. U kunt de OVER-component met functies gebruiken om te berekenen
geaggregeerde waarden zoals voortschrijdende gemiddelden, cumulatieve aggregaten,
lopende totalen, of een top N per groepsresultaten.
PARTITION BY zal het aantal geretourneerde rijen niet verminderen.
We kunnen PARTITION BY toepassen in onze voorbeeldtabel:
SELECT SUM(Mark) OVER (PARTITION BY id) AS marksum, firstname FROM TableA
Resultaat:
marksum firstname
-------------------
134 arun
134 arun
134 arun
94 ann
94 ann
41 sruthy
47 new
Kijk naar de resultaten – het verdeelt de rijen en retourneert allerijen, in tegenstelling tot GROUP BY.
Antwoord 3, autoriteit 12%
PARTITION BYrolt de gegevens niet echt op. Hiermee kunt u per groep iets resetten. U kunt bijvoorbeeld een ordinale kolom binnen een groep krijgen door te partitioneren in het groeperingsveld en rownum()te gebruiken over de rijen binnen die groep. Dit geeft je iets dat zich een beetje gedraagt als een identiteitskolom die wordt gereset aan het begin van elke groep.
Antwoord 4, autoriteit 9%
PARTITIE DOOR
Verdeelt de resultaatset in partities. De vensterfunctie wordt op elke partitie afzonderlijk toegepast en de berekening wordt opnieuw gestart voor elke partitie.
Te vinden via deze link: OVER-clausule
Antwoord 5, autoriteit 8%
Het biedt opgerolde gegevens zonder op te rollen
d.w.z. Stel dat ik de relatieve positie van de verkoopregio wil retourneren
Met PARTITION BY kan ik het verkoopbedrag voor een bepaalde regio enhet MAX-bedrag voor alle verkoopregio’s in dezelfde rij retourneren.
Dit betekent dat u herhaalde gegevens zult hebben, maar het kan geschikt zijn voor de eindgebruiker in die zin dat gegevens zijn geaggregeerd maar geen gegevens verloren zijn gegaan – zoals het geval zou zijn met GROUP BY.
Antwoord 6, autoriteit 7%
PARTITION BYis analytisch, terwijl GROUP BYgeaggregeerd is. Om PARTITION BYte gebruiken, moet je het bevatten met een OVER-clausule.
Antwoord 7, autoriteit 5%
Voor zover ik heb begrepen is Partition By bijna identiek aan Group By, maar met de volgende verschillen:
Die groep door groepeert in feite de resultaatset die één rij per groep retourneert, wat er daarom toe leidt dat SQL Server in de SELECT-lijst alleen geaggregeerde functies of kolommen toestaat die deel uitmaken van de group by-clausule (in welk geval SQL Server kan garanderen dat er zijn unieke resultaten voor elke groep).
Beschouw bijvoorbeeld MySQL dat het mogelijk maakt om in de SELECT-lijst kolommen te hebben die niet zijn gedefinieerd in de Group By-clausule, in welk geval er nog steeds één rij per groep wordt geretourneerd, maar als de kolom geen unieke resultaten heeft, dan is er is geen garantie wat de output zal zijn!
Maar met Partition By, hoewel de resultaten van de functie identiek zijn aan de resultaten van een geaggregeerde functie met Group By, krijgt u nog steeds de normale resultatenset, wat betekent dat u één rij per onderliggende rij krijgt, en niet één rij per groep, en hierdoor kan men kolommen hebben die niet uniek zijn per groep in de SELECT lijst.
Dus samenvattend zou Group By het beste zijn wanneer een uitvoer van één rij per groep nodig is, en Partition By zou het beste zijn wanneer men alle rijen nodig heeft maar toch de aggregatiefunctie op basis van een groep wil.
Natuurlijk kunnen er ook prestatieproblemen zijn, zie http://social.msdn.microsoft.com/Forums/ms-MY/transactsql/thread/0b20c2b5-1607-40bc-b7a7-0c60a2a55fba.
Antwoord 8
Als je GROUP BYgebruikt, zijn de resulterende rijen meestal kleiner dan de inkomende rijen.
Maar als u PARTITION BYgebruikt, moet het resulterende aantal rijen hetzelfde zijn als inkomend.
Antwoord 9
Stel dat we 14 records van de kolom namein tabel hebben
in GROUP BY
select name,count(*) as totalcount from person where name='Please fill out' group BY name;
het geeft een telling in een enkele rij, d.w.z. 14
maar in PARTITION BY
select row_number() over (partition by name) as total from person where name = 'Please fill out';
het zal 14 rijen toenemen in aantal
Antwoord 10
Kleine observatie. Automatiseringsmechanisme om dynamisch SQL te genereren met behulp van de ‘partition by’ is veel eenvoudiger te implementeren in relatie tot de ‘group by’. In het geval van ‘groeperen op’, moeten wij zorgen voor de inhoud van de kolom ‘selecteren’.
Sorry voor mijn Engels.
Antwoord 11
Het heeft echt verschillende gebruiksscenario’s.
Wanneer u GROUP BY gebruikt, voegt u enkele records voor de kolommen samen die hetzelfde zijn en krijgt u een aggregatie van de resultatenset.
Als je echter PARTITION BY gebruikt, is je resultatenset hetzelfde, maar je hebt alleen een aggregatie over de vensterfuncties en je voegt de records niet samen, je hebt nog steeds hetzelfde aantal records.
Hier is een nuttig artikel voor de rally waarin het verschil wordt uitgelegd:
http://alevryustemov.com/sql/sql-partition-by/
Antwoord 12
-- BELOW IS A SAMPLE WHICH OUTLINES THE SIMPLE DIFFERENCES
-- READ IT AND THEN EXECUTE IT
-- THERE ARE THREE ROWS OF EACH COLOR INSERTED INTO THE TABLE
-- CREATE A database called testDB
-- use testDB
USE [TestDB]
GO
-- create Paints table
CREATE TABLE [dbo].[Paints](
[Color] [varchar](50) NULL,
[glossLevel] [varchar](50) NULL
) ON [PRIMARY]
GO
-- Populate Table
insert into paints (color, glossLevel)
select 'red', 'eggshell'
union
select 'red', 'glossy'
union
select 'red', 'flat'
union
select 'blue', 'eggshell'
union
select 'blue', 'glossy'
union
select 'blue', 'flat'
union
select 'orange', 'glossy'
union
select 'orange', 'flat'
union
select 'orange', 'eggshell'
union
select 'green', 'eggshell'
union
select 'green', 'glossy'
union
select 'green', 'flat'
union
select 'black', 'eggshell'
union
select 'black', 'glossy'
union
select 'black', 'flat'
union
select 'purple', 'eggshell'
union
select 'purple', 'glossy'
union
select 'purple', 'flat'
union
select 'salmon', 'eggshell'
union
select 'salmon', 'glossy'
union
select 'salmon', 'flat'
/* COMPARE 'GROUP BY' color to 'OVER (PARTITION BY Color)' */
-- GROUP BY Color
-- row quantity defined by group by
-- aggregate (count(*)) defined by group by
select count(*) from paints
group by color
-- OVER (PARTITION BY... Color
-- row quantity defined by main query
-- aggregate defined by OVER-PARTITION BY
select color
, glossLevel
, count(*) OVER (Partition by color)
from paints
/* COMPARE 'GROUP BY' color, glossLevel to 'OVER (PARTITION BY Color, GlossLevel)' */
-- GROUP BY Color, GlossLevel
-- row quantity defined by GROUP BY
-- aggregate (count(*)) defined by GROUP BY
select count(*) from paints
group by color, glossLevel
-- Partition by Color, GlossLevel
-- row quantity defined by main query
-- aggregate (count(*)) defined by OVER-PARTITION BY
select color
, glossLevel
, count(*) OVER (Partition by color, glossLevel)
from paints