SQL Pivot - umíte převádět řádky na sloupce

Tento článek o SQL Pivot je komplexní průvodce, jak převést data na úrovni řádků na sloupcová data s podrobnými příklady.

Relační ukládat obrovské množství dat ve formě tabulek. Tyto tabulky mohou mít libovolný počet řádků a sloupců. Ale co kdybyste museli změnit data na úrovni řádků na sloupcová? V tomto článku o SQL Pivot vám ukážu, jak můžete převést řádky na sloupec na serveru SQL.

V tomto článku se budeme zabývat následujícími tématy:





Co je PIVOT v SQL?

PIVOT se používá k otočení hodnoty tabulky převedením jedinečných hodnot jednoho sloupce na více sloupců. Používá se k otočení řádků na hodnoty sloupců a v případě potřeby spustí agregace zbývajících hodnot sloupce.

UNPIVOT se naproti tomu používá k provádění opačných operací. Používá se tedy k převodu sloupců konkrétní tabulky na hodnoty sloupců.



Pokud v tomto článku pokračujeme, pochopíme syntaxi SQL Pivot.

Syntax:

VYBERTE NonPivoted ColumnName, [First Pivoted ColumnName] AS ColumnName, [Second Pivoted ColumnName] AS ColumnName, [Third Pivoted ColumnName] AS ColumnName, ... [Last Pivoted ColumnName] AS ColumnName FROM (SELECT query which produce the data) AS [alias pro počáteční dotaz] PIVOT ([AggregationFunction] (ColumName) FOR [ColumnName sloupce, jehož hodnoty se stanou hlavičkami sloupců] IN ([First Pivoted ColumnName], [Second Pivoted ColumnName], [Third Pivoted ColumnName] ... [last otočený sloupec])) AS [alias pro kontingenční tabulku]

Tady,můžete také použít Klauzule ORDER BY seřadit hodnoty vzestupně nebo sestupně. Nyní, když víte, co je PIVOT v SQL a jeho základní syntaxe, pojďme se posunout vpřed a uvidíme, jak jej používat.

Příklady

Pro vaše lepší pochopení zvážím následující tabulku, abych vám vysvětlil všechny příklady.



Tabulka dodavatele:

ID dodavatele Den výroby Náklady Zákaznické identifikační číslo ID nákupu
jeden121230jedenáctP1
2dvacet jedna154322P2
3322. 3. 4. 5jedenáctP3
414876522P1
542345233P3
631543133P1
7412342jedenáctP2
854365422P2
9331234jedenáctP3
1056683233P2

Napíšeme jednoduchý dotaz, abychom načetli průměrné náklady vynaložené každým zákazníkem.

VYBERTE IDZákazníka, AVG (náklady) JAKO PRŮMĚRNÝCENOZákazníka OD SKUPINY dodavatelů PODLE IDZákazníka

Výstup:

Zákaznické identifikační číslo AverageCostofCustomer
jedenáct1787,75
224654
335238,33

Nyní řekněme, že chceme otočit výše uvedenou tabulku. Zde se hodnoty sloupce CustomerID stanou záhlavími sloupců.

- Vytvořte kontingenční tabulku s jedním řádkem a třemi sloupci VYBERTE 'PrůměrnéCostofCustomer' JAKO Cost_According_To_Customers, [11], [22], [33] Z (VYBERTE ID zákazníka, Náklady Z Dodavatelů) JAKO SourceTable PIVOT (AVG (Náklady) ZA ID zákazníka v ( [11], [22], [33])) JAKO kontingenční tabulka

Výstup:

Náklady_zaznamenávání_zákazníků jedenáct2233
AverageCostofCustomer 1787,7546545238,33

Poznámka: Když používáte agregační funkce s PIVOTem nebudou při výpočtu agregace brány v úvahu hodnoty null.

To byl základní příklad, ale pojďme nyní pochopit, jak fungovala klauzule PIVOT.

Fungování klauzule PIVOT

Jak můžete výše odkázat, při vytváření PIVOT TABLE musíte postupovat podle následujících kroků:

  • Vyberte sloupce pro otočení
  • Poté vyberte zdrojovou tabulku.
  • Použijte operátor PIVOT a poté použijte agregační funkce.
  • Uveďte kontingenční hodnoty.

Vyberte sloupce pro otočení

Zpočátku musíme určit pole, která mají být zahrnuta do našich výsledků. V našem příkladu jsem považoval sloupec AverageCostofCustomer v kontingenční tabulce. Poté jsme vytvořili další tři sloupce se záhlavími sloupců 11, 22 a 33. Příklad -

VYBERTE 'PrůměrnýCoofofonik' JAKO Cost_According_To_Customers, [11], [22], [33]

Vyberte zdrojovou tabulku

Dále musíte zadat příkaz SELECT, který vrátí zdrojová data kontingenční tabulky. V našem příkladu vracíme CustomerID a náklady z tabulky Dodavatelé.

(VYBERTE IDZákazníka, náklady od dodavatelů) JAKO Zdrojová tabulka

Použijte operátor PIVOT a poté použijte agregační funkce

Dále musíte určit agregační funkci, která se má použít při vytváření kontingenční tabulky. V našem příkladu jsem k výpočtu průměrných nákladů použil funkci AVG.

PIVOT (AVG (cena)

Uveďte kontingenční hodnoty

Nakonec musíte zmínit hodnoty, které musí být zahrnuty do výsledné kontingenční tabulky. Tyto hodnoty budou použity jako záhlaví sloupců v kontingenční tabulce.

PRO ID zákazníka ([11], [22], [33])) JAKO kontingenční tabulka

Tak fungují operátoři PIVOT. Pokračujeme v tomto článku na SQL PIVOT, pojďme pochopit, jak se liší od SQL UNPIVOT.

SQL UNPIVOT

Operátor SQL UNPIVOT se používá k provedení opačné operace než operace PIVOT. Používá se k otočení dat sloupce na data na úrovni řádků. Syntaxe UNPIVOT je podobná syntaxi PIVOT. Jediný rozdíl je v tom, že musíte použít ' UNPIVOT ” .

Příklad:

Vytvořme tabulku se sloupci SupplierID, AAA, BBB a CCC. Také vložte několik hodnot.

CREATE TABLE sampletable (SupplierID int, AAA int, BBB int, CCC int) GO INSERT INTO sampletable VALUES (1,3,5,6) INSERT INTO sampletable VALUES (2,9,2,8) INSERT INTO sampletable VALUES (3, 8,1,7) GO

Výstup:

ID dodavatele AAA BBB CCC
jeden356
2928
38jeden7

Nyní řekněme, že chceme otočit stůl. Chcete-li to provést, můžete odkázat na následující kód:

VYBERTE ID dodavatele, zákazníky, produkty OD (VYBRAT dodavatel, AAA, BBB, CCC OD vzorkovatelné) p UNPIVOT (produkty PRO ZÁKAZNÍKY V (AAA, BBB, CCC)) JAKO příklad GO
ID dodavatele Zákazníci produkty

jeden

AAA

3

jeden

BBB

5

jeden

CCC

6

2

AAA

9

2

BBB

2

2

CCC

8

3

AAA

8

3

BBB

jeden

3

google data scientist interview questions

CCC

7

Takto můžete použít SQL PIVOT a UNPIVOT. Tímto skončíme tento článek. Doufám, že jste pochopili, jak používat SQL. Pokud se chcete dozvědět více o MySQL a seznamte se s touto relační databází open-source, podívejte se na naši který přichází s živým tréninkem vedeným instruktorem a zkušenostmi z reálného projektu. Toto školení vám pomůže porozumět MySQL do hloubky a pomůže vám dosáhnout zvládnutí předmětu.

Máte na nás dotaz? Uveďte to prosím v sekci komentářů tohoto článku o SQL Pivot a ozvu se vám.