Tässä opetusohjelmassa opimme PARTITION BY -lauseen toiminnasta SQL:ssä ja selvitämme, kuinka voimme käyttää sitä osioiden tietojen tarkempaan osajoukkoon.
Syntaksi:
Aloitetaan PARTITION BY -lauseen syntaksista. Syntaksi voi riippua kontekstista, jossa käytät sitä, mutta tässä on yleinen syntaksi:
VALITSE sarake1, sarake2, ...
YLI (OSIO OSIO-sarake1, osio_sarake2, ...)
FROM taulukon_nimi
Annettu syntaksi edustaa seuraavia elementtejä:
- sarake1, sarake2 – Tämä viittaa sarakkeisiin, jotka haluamme sisällyttää tulosjoukkoon.
- PARTITION BY -sarakkeet – Tämä lauseke määrittelee, kuinka haluamme osioida tai ryhmitellä tiedot.
Esimerkkitiedot
Luodaan perustaulukko esimerkkitiedoilla, jotka osoittavat PARTITION BY -lauseen käytön. Tätä esimerkkiä varten luodaan perustaulukko, joka tallentaa tuotetiedot.
CREATE TABLE tuotteet (
product_id INT PRIMARY KEY AUTO_INCREMENT,
tuotteen_nimi VARCHAR( 255 ),
kategoria VARCHAR( 255 ),
hinta DECIMAL( 10 , 2 ),
määrä INT,
expiration_date DATE,
viivakoodi BIGINT
);
lisää
sisään
tuotteet (tuotteen_nimi,
kategoria,
hinta,
määrä,
viimeinen käyttöpäivä,
viivakoodi)
arvot ( 'Kokin hattu 25cm' ,
'leipomo' ,
24.67 ,
57 ,
'2023-09-09' ,
2854509564204 );
lisää
sisään
tuotteet (tuotteen_nimi,
kategoria,
hinta,
määrä,
viimeinen käyttöpäivä,
viivakoodi)
arvot ( Viiriäisten munat - purkitettu ,
'ruokakomero' ,
17.99 ,
67 ,
'2023-09-29' ,
1708039594250 );
lisää
sisään
tuotteet (tuotteen_nimi,
kategoria,
hinta,
määrä,
viimeinen käyttöpäivä,
viivakoodi)
arvot ( 'Kahvi - Egg Nog Capuccino' ,
'leipomo' ,
92,53 ,
10 ,
'2023-09-22' ,
8704051853058 );
lisää
sisään
tuotteet (tuotteen_nimi,
kategoria,
hinta,
määrä,
viimeinen käyttöpäivä,
viivakoodi)
arvot ( 'Päärynä - Piikikäs' ,
'leipomo' ,
65,29 ,
48 ,
'2023-08-23' ,
5174927442238 );
lisää
sisään
tuotteet (tuotteen_nimi,
kategoria,
hinta,
määrä,
viimeinen käyttöpäivä,
viivakoodi)
arvot ( 'Pasta - enkelihiukset' ,
'ruokakomero' ,
48,38 ,
59 ,
'2023-08-05' ,
8008123704782 );
lisää
sisään
tuotteet (tuotteen_nimi,
kategoria,
hinta,
määrä,
viimeinen käyttöpäivä,
viivakoodi)
arvot ( 'Viini - Prosecco Valdobiaddene' ,
'tuottaa' ,
44.18 ,
3 ,
'2023-03-13' ,
6470981735653 );
lisää
sisään
tuotteet (tuotteen_nimi,
kategoria,
hinta,
määrä,
viimeinen käyttöpäivä,
viivakoodi)
arvot ( 'Leivonnaiset - ranskalaiset minilajitelmat' ,
'ruokakomero' ,
36,73 ,
52 ,
'2023-05-29' ,
5963886298051 );
lisää
sisään
tuotteet (tuotteen_nimi,
kategoria,
hinta,
määrä,
viimeinen käyttöpäivä,
viivakoodi)
arvot ( 'Oranssi - purkitettu, mandariini' ,
'tuottaa' ,
65,0 ,
1 ,
'2023-04-20' ,
6131761721332 );
lisää
sisään
tuotteet (tuotteen_nimi,
kategoria,
hinta,
määrä,
viimeinen käyttöpäivä,
viivakoodi)
arvot ( 'Sianliha - lapa' ,
'tuottaa' ,
55,55 ,
73 ,
'2023-05-01' ,
9343592107125 );
lisää
sisään
tuotteet (tuotteen_nimi,
kategoria,
hinta,
määrä,
viimeinen käyttöpäivä,
viivakoodi)
arvot ( 'Dc Hikiage Hira Huba' ,
'tuottaa' ,
56.29 ,
53 ,
'2023-04-14' ,
3354910667072 );
Kun meillä on mallitietojen asetukset, voimme jatkaa ja käyttää PARTITION BY -lausetta.
Peruskäyttö
Oletetaan, että haluamme laskea edellisen taulukon kunkin tuoteluokan kokonaismäärät. Voimme käyttää PARTITION BY:tä jakaaksemme tuotteet yksilöllisiin luokkiin ja määrittää sitten kunkin luokan kokonaismäärän.
Esimerkki on seuraava:
VALITSE
tuotteen nimi,
kategoria,
määrä,
SUMMA(määrä) YLI (OSIO luokan mukaan) AS total_items
FROM
Tuotteet;
Huomaa, että annetussa esimerkissä osiimme tiedot käyttämällä 'luokka' -saraketta. Käytämme sitten SUM()-aggregaattifunktiota määrittääksemme kunkin luokan kokonaismäärät erikseen. Tulos näyttää kunkin luokan kohteiden kokonaismäärän.
PARTITION BY -lauseen käyttäminen
Yhteenvetona voidaan todeta, että PARTITION BY -lauseen yleisin käyttötapaus on ikkunatoimintojen yhteydessä. Ikkunatoimintoa sovelletaan jokaiseen osioon erikseen.
Joitakin PARTITION BY:n kanssa käytettäviä yleisiä ikkunatoimintoja ovat seuraavat:
- SUM() – Laske kunkin osion sarakkeen summa.
- AVG() – Laske kunkin osion sarakkeen keskiarvo.
- COUNT() – Laskee kunkin osion rivien lukumäärän.
- ROW_NUMBER() – Anna yksilöllinen rivinumero kunkin osion jokaiselle riville.
- RANK() – Määritä arvo jokaiselle riville kunkin osion sisällä.
- DENSE_RANK() – Määritä tiheä arvo jokaiselle riville kunkin osion sisällä.
- NTILE() – Jaa tiedot kvantileihin kunkin osion sisällä.
Se siitä!
Johtopäätös
Tässä opetusohjelmassa opimme työskentelemään PARTITION BY -lauseen kanssa SQL:ssä tietojen jakamiseksi eri segmenteiksi ja sitten tietyn toiminnon soveltamiseksi jokaiseen tuloksena olevaan osioon erikseen.