Tento článek pojednává o klauzuli SQL PARTITION BY a zejména rozdíl s GROUP BY ve výběrovém příkazu. Rovněž prozkoumáme různé případy použití SQL PARTITION BY.
Pomocí SQL PARTITION BY rozdělíme výsledkovou sadu na oddíly a provedeme výpočet u každé podmnožiny dělených dat.
Příprava vzorku Data
Pojďme vytvořit tabulku objednávek v mé ukázkové databázi SQLShackDemo a vložit záznamy, abychom mohli psát další dotazy.
K vložení ukázkových dat do tohoto článku používám ApexSQL Generate. Klikněte pravým tlačítkem na tabulku Objednávky a vygenerujte testovací data.
Spustí generování ApexSQL. Vygeneroval jsem skript pro vložení dat do tabulky Objednávky. Spusťte tento skript a vložte 100 záznamů do tabulky Objednávky.
Jakmile provedeme příkazy vložení, můžeme vidět data v tabulce Objednávky na následujícím obrázku.
K seskupení výsledků podle zadaného sloupce použijeme klauzuli SQL GROUP BY a k výpočtu požadovaných hodnot použijeme agregační funkce jako Avg (), Min (), Max ().
Seskupit podle syntaxe funkcí
|
1
2
3
4
|
SELECT výraz, agregační funkce ()
Z tabulek
KDE podmínky
Výraz GROUP BY
|
Předpokládejme, že chceme v tabulce Objednávky najít následující hodnoty
- Minimální hodnota objednávky ve městě
- Maximální hodnota objednávky ve městě
- Průměrná hodnota objednávky ve městě
K výpočtu těchto hodnot proveďte následující dotaz s klauzulí GROUP BY.
|
1
2
3
4
5
6
|
VÝBĚR Customercity,
AVG (Orderamount) AS AvgOrderAmount,
MIN (OrderAmount) AS MinOrderAmount,
SUM ( Množství objednávky) TotalOrderAmount
OD.
SKUPINA PODLE Customercity;
|
Na následujícím snímku obrazovky můžeme vidět průměr, minimum a maximální hodnoty seskupené podle CustomerCity.
Nyní chceme do výstupu přidat sloupec CustomerName a OrderAmount. Přidejte tyto sloupce do příkazu select a spusťte následující kód.
|
1
2
3
4
5
6
|
SELECT Customercity, CustomerName, OrderAmount,
AVG (Orderamount) AS AvgOrderAmount,
MIN (OrderAmount) AS MinOrderAmount,
SUM (Orderamount) TotalOrderAmount
OD.
SKUPINA PODLE Customercity;
|
Po provedení tohoto dotazu se zobrazí chybová zpráva . V klauzuli SQL GROUP BY můžeme použít sloupec v příkazu select, pokud se také používá v klauzuli Group by. Nepovoluje žádný sloupec v klauzuli select, který není součástí klauzule GROUP BY.
Můžeme použít SQL Klauzule PARTITION BY k vyřešení tohoto problému. Pojďme to prozkoumat dále v další části.
SQL PARTITION BY
Můžeme použít klauzuli SQL PARTITION BY s klauzulí OVER k určení sloupce, na kterém musíme provést agregaci . V předchozím příkladu jsme použili Group By with CustomerCity sloupec a vypočítané průměrné, minimální a maximální hodnoty.
Zopakujme tento scénář s klauzulí SQL PARTITION BY pomocí následujícího dotazu.
|
1
2
3
4
5
|
VÝBĚR Customercity,
AVG (Orderamount) NAD (PARTITION BY Customercity) AS AvgOrderAmount,
MIN (OrderAmount) OVER (PARTITION BY Customercity) AS MinOrderAmount,
SUM (OrderAount) OVER (PARTITION BY Customercity) TotalOrderAmount
FROM.;
|
Na výstupu dostaneme agregované hodnoty podobné SKUPINA Doložkou. Můžete si všimnout rozdílu ve výstupu výstupu klauzule SQL PARTITION BY a GROUP BY.
|
Seskupit podle |
SQL PARTITION BY |
|
Omezený počet záznamů získáme pomocí klauzule Group By |
Všechny záznamy v tabulce získáváme pomocí klauzule PARTITION BY. |
|
Dává jeden řádek na skupinu v sadě výsledků. Například získáme výsledek pro každou skupinu CustomerCity v klauzuli GROUP BY. |
Poskytuje agregované sloupce s každým záznamem v zadané tabulce. V tabulce Objednávky máme 15 záznamů. Ve výstupu dotazu SQL PARTITION BY také získáme 15 řádků spolu s hodnotami Min, Max a průměr. |
V předchozím příkladu se zobrazí chybová zpráva, pokud se pokusíme přidat sloupec, který není součástí klauzule GROUP BY.
Můžeme přidat požadované sloupce do příkazu select s klauzulí SQL PARTITION BY . Přidejte sloupce CustomerName a OrderAmount a proveďte následující dotaz.
|
1
2
3
4
5
6
7
|
VÝBĚR Customercity,
CustomerName,
OrderAmount,
AVG (Orderamount) OVER (PARTITION BY Customercity) AS AvgOrderAmount ,
MIN (OrderAmount) NAD (PARTITION BY Customercity) AS MinOrderAmount,
SUM (OrderAount) OVER (PARTITION BY Customercity) TotalOrderAmount
FROM.;
|
Dostaneme sloupec CustomerName a OrderAmount spolu s výstupem agregovaná funkce. Získáváme také všechny řádky dostupné v tabulce Objednávky.
Na následujícím snímku obrazovky můžete pro CustomerCity Chicago provádět agregace (průměr, min. a max.) a udává hodnoty v příslušných sloupcích.
Podobně můžeme použít i další agregační funkce, například jako počet pro zjištění celkového počtu objednávek v konkrétním městě s klauzulí SQL PARTITION BY.
|
1
2
3
4
5
6
7
8
|
SELECT Customercity,
CustomerName,
OrderAmount,
POČET (OrderID) NAD (PARTITION BY Customercity) AS CountOfOrders,
AVG (Orderamount) OVER (PARTITION BY Customercity) AS AvgOrderAmount,
MIN (OrderAmount) OVER (PARTITIO N BY Customercity) AS MinOrderAmount,
SUM (Orderamount) OVER (PARTITION BY Customercity) TotalOrderAmount
FROM.;
|
Vidíme počty objednávek pro konkrétní město. Například máme dvě objednávky z města Austin; ve sloupci CountofOrders zobrazuje hodnotu 2.
klauzule PARTITION BY s ROW_NUMBER ()
můžeme použijte klauzuli SQL PARTITION BY s funkcí ROW_NUMBER (), abyste měli číslo řádku každého řádku. Definujeme následující parametry pro použití ROW_NUMBER s klauzulí SQL PARTITION BY.
- PARTITION BY sloupec – V tomto příkladu chceme rozdělit data na sloupec CustomerCity
- Order By : Ve sloupci OBJEDNAT PODLE definujeme sloupec nebo podmínku, která definuje číslo řádku. V tomto příkladu chceme seřadit data ve sloupci OrderAmount
|
1
2
3
4
5
6
7
8
9
10
|
SELECT Customercity,
CustomerName,
ROW_NUMBER () NAD (PARTITION BY Customercity
ORDER BY OrderAmount DESC) AS „Row Number“,
OrderAmount,
COUNT (OrderID) NAD (PARTITION BY Customercity) AS CountOfOrders,
AVG (Orderamount) OVER (PARTITION BY Customercity) AS AvgOrderAmount,
MIN (OrderAmount) OVER (PARTITION BY Customercity) AS MinOrderAmount,
SUM (Orderamount) OVER (PARTITION BY Customercity) TotalOrderAmount
FROM.;
|
Na následujícím snímku obrazovky uvidíme aplikaci CustomerCity Chicago , máme řádek číslo 1 pro objednávku s nejvyšší částkou 7577,90. poskytuje číslo řádku se sestupným OrderAmount.
Klauzule PARTITION BY s kumulativní celkovou hodnotou
Předpokládejme, že chcete získat kumulativní součet za objednávky v oddílu.Kumulativní součet by měl být aktuálního řádku a následujícího řádku v oddílu.
Například ve městě Chicago, máme čtyři objednávky.
|
CustomerCity |
CustomerName |
Hodnocení |
OrderAmount |
Kumulativní celkový počet řádků |
Kumulativní součet |
|
Chicago |
Marvin |
1. a 2. místo |
|||
|
Chicago |
Lawrence |
Rank 2 + 3 |
|||
|
Chicago |
Alex |
3. a 4. místo |
|||
|
Chicago |
Jerome |
4. místo |
V následujícím dotazu jsme zadali klauzuli ROWS načíst aktuální řádek (pomocí AKTUÁLNÍHO ŘÁDKU) a další řádek (pomocí 1 NÁSLEDUJÍCÍHO). Dále vypočítá součet na těchto řádcích pomocí součtu (Orderamount) s oddílem na CustomerCity (pomocí OVER (PARTITION BY Customercity ORDER BY OrderAmount DESC).
|
1
2
3
4
5
6
7
|
SELECT Customercity,
CustomerName,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) AS „Row Number“,
CONVERT (VARCHAR (20), SUM (orderamount) OVER) (PARTITION BY Customercity
ORDER BY OrderAmount DESC ROWS MEZ AKTUÁLNÍ ŘADY A 1 NÁSLEDUJÍCÍ), 1) AS CumulativeTotal,
|
Podobně můžeme vypočítat kumulativní průměr pomocí následující dotaz s klauzulí SQL PARTITION BY.
|
1
2
3
4
5
6
7
|
SELECT Customercity,
CustomerName,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) AS „Number Row“,
CONVERT (VARCHAR (20), AVG (orderamount) OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC ROWS BETWEEN ROW ROW AND 1 FOLLOWING) , 1) AS CumulativeAVG
|
ŘÁDY NEBEZPEČNÉ PŘEDCHÁZEJÍCÍ s klauzulí PARTITION BY
K výběru řádku v oddílu můžeme použít ŘÁDKY BEZOBRANÉ PRECEDOVÁNÍ s klauzulí SQL PARTITION BY před aktuální řádek a nejvyšší hodnota r ow po aktuálním řádku.
V následující tabulce vidíme pro řádek 1; v tomto oddílu nemá žádný řádek s vysokou hodnotou. Kumulativní průměrná hodnota je tedy stejná jako u OrderAmount řádku 1.
U řádku 2 vyhledává aktuální hodnotu řádku (7199,61) a nejvyšší hodnotu řádku 1 (7577,9). Vypočítá průměr pro tyto dvě částky.
U řádku 3 hledá aktuální hodnotu (6847,66) a vyšší hodnotu částky než tato hodnota, která je 7199,61 a 7577,90. Vypočítá průměr z nich a vrátí se.
|
CustomerCity |
CustomerName |
Rank |
OrderAmount |
Kumulativní průměrné řádky |
Kumulativní průměr |
|
Chicago |
Marvin |
1. místo |
|||
|
Chicago |
Lawrence |
Rank 1 + 2 |
|||
|
Chicago |
Alex |
Pořadí 1 + 2 + 3 |
|||
|
Chicago |
Jerome |
Pořadí 1 + 2 + 3 + 4 |
Proveďte následující dotaz a získáte tento výsledek s našimi ukázkovými daty.
|
1
2
3
4
5
6
7
8
|
SELECT Customercity,
CustomerName,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) AS „Row Number“,
CONVERT (VARCHAR (20), AVG (orderamount) OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC ROWS UNBOUNDED PRECEDING), 1) AS CumulativeAvg
FROM.;
|
Závěr
V tomto článku jsme prozkoumali klauzuli SQL PARTIION BY a její srovnání s klauzulí GROUP BY. Na několika příkladech jsme se také naučili jeho použití. Doufám, že vám tento článek bude užitečný, a neváhejte se na ně zeptat v komentářích níže.
- Autor
- Poslední příspěvky
Je tvůrcem jedné z největších bezplatných online sbírek článků na jedno téma se svou 50dílnou sérií věnovanou skupinám dostupnosti na serveru SQL Server. Na základě svého příspěvku pro komunitu SQL Server byl oceněn různými oceněními, včetně prestižního ocenění „Nejlepší autor roku“ průběžně v letech 2020 a 2021 na serveru SQLShack.
Raj se vždy zajímá o nové výzvy, takže pokud potřebujete poradit pomoc s jakýmkoli tématem zahrnutým v jeho spisech, lze ho kontaktovat na [email protected]
Zobrazit všechny příspěvky od Rajendra Gupta
- Časové limity relací na serveru SQL Server vždy dostupné Skupiny – 8. února 2021
- Provádění upgradů menších a hlavních verzí pro AWS RDS SQL Server – 29. ledna 2021
- Nasazení instancí AWS RDS PostgreSQL – 27. ledna 2021