Eden od običajnih statističnih izračunov, ki se pojavljajo celo pri upravljanju baz podatkov, je percentil.
Percentil je statistična mera, ki nam omogoča, da nabor podatkov razdelimo na enake dele segmentov. Vloga percentilov je zagotoviti vpogled v porazdelitev podatkov, tako da razumemo, kako so vrednosti razporejene.
V tej vadnici se bomo naučili, kako lahko izračunamo percentile v SQL, da podatke razdelimo na različne segmente.
Vzorčna tabela
Začnimo z nastavitvijo osnovne tabele, ki vsebuje vzorčne podatke za namene predstavitve. To nam pomaga ponazoriti, kako se obnašajo različne metode izračuna percentilov in posledični rezultat.
Ustvarimo tabelo z imenom »izdelki«, ki vsebuje informacije o živilih. Klavzula »ustvari tabelo« je naslednja:
CREATE TABLE izdelki (
product_id INT PRIMARY KEY AUTO_INCREMENT,
ime_izdelka VARCHAR( 255 ),
kategorija VARCHAR( 255 ),
cena DECIMAL( 10 , 2 ),
količina INT,
datum_izteka DATE,
črtna koda BIGINT
);
Ko ustvarimo tabelo, lahko nadaljujemo in dodamo vzorčne podatke v tabelo. Uporabimo lahko naslednje izjave »vstavi«:
vstaviv
izdelki (ime_izdelka,
kategorija,
cena,
količina,
Datum veljavnosti,
črtna koda)
vrednote ( 'Kuharska kapa 25 cm' ,
'pekarna' ,
24.67 ,
57 ,
'2023-09-09' ,
2854509564204 );
vstavi
v
izdelki (ime_izdelka,
kategorija,
cena,
količina,
Datum veljavnosti,
črtna koda)
vrednote ( 'Prepeličja jajca - konzervirana' ,
'shramba' ,
17,99 ,
67 ,
'29.09.2023' ,
1708039594250 );
vstavi
v
izdelki (ime_izdelka,
kategorija,
cena,
količina,
Datum veljavnosti,
črtna koda)
vrednote ( 'Kava - Egg Nog Capuccino' ,
'pekarna' ,
92.53 ,
10 ,
'22.09.2023' ,
8704051853058 );
vstavi
v
izdelki (ime_izdelka,
kategorija,
cena,
količina,
Datum veljavnosti,
črtna koda)
vrednote ( 'Hruška - Bodeča' ,
'pekarna' ,
65.29 ,
48 ,
'2023-08-23' ,
5174927442238 );
vstavi
v
izdelki (ime_izdelka,
kategorija,
cena,
količina,
Datum veljavnosti,
črtna koda)
vrednote ( 'Pasta - Angel Hair' ,
'shramba' ,
48.38 ,
59 ,
'2023-08-05' ,
8008123704782 );
vstavi
v
izdelki (ime_izdelka,
kategorija,
cena,
količina,
Datum veljavnosti,
črtna koda)
vrednote ( 'Vino - Prosecco Valdobiaddene' ,
'proizvajati' ,
44.18 ,
3 ,
'2023-03-13' ,
6470981735653 );
Na koncu bi morali imeti naslednjo tabelo:
Percentil SQL
Kot lahko ugibate, se lahko način izračuna percentila razlikuje glede na pogon baze podatkov. Vendar je najpogostejša metoda uporaba funkcij PERCENTILE_DISC() in PERCENTILE_CONT().
Te funkcije so del standardne specifikacije SQL (2003). Zato ga morata podpirati PostgreSQL in Oracle.
PERCENTILE_CONT()
Začnimo s funkcijo PERCENTILE_CONT(). Ta funkcija nam omogoča izračun vrednosti percentilov kot del nabora podatkov.
Funkcija vrne interpolirane vrednosti, ki morda niso natančne za določeno podatkovno točko v vašem naboru podatkov.
Sintaksa funkcije je naslednja:
PERCENTILE_CONT(percentil) ZNOTRAJ SKUPINA ( NAROČITE BY ime_stolpca) OVER ();Funkcija sprejme naslednje parametre:
- Percentil – Določa želeno vrednost percentila (0,0 do 1,0).
- column_name – Označuje stolpec, za katerega želimo izračunati percentil.
- OVER () – Nastavi okensko funkcijo za določitev celotnega nabora podatkov.
Primer uporabe te funkcije je naslednji:
IZBERIPERCENTILE_CONT( 0,5 ) ZNOTRAJ SKUPINA ( NAROČITE PO ceni) NAD () KOT mediana
OD
izdelki;
Opomba: Dana poizvedba deluje samo v PostgreSQL, ker MySQL ne podpira uporabe WITHIN GROUP.
To izračuna 50 th percentil posredovanih podatkov.
PERCENTILE_DISC()
S funkcijo PERCENTILE_DISC() lahko izračunamo vrednost percentila kot diskretno vrednost neposredno iz nabora podatkov.
Funkcija vrne vrednost, ki ustreza dejanski podatkovni točki.
Sintaksa funkcije je naslednja (PostgreSQL):
PERCENTILE_DISC(percentil) ZNOTRAJ SKUPINA ( NAROČITE BY ime_stolpca) OVER ();Primer izhoda je naslednji:
IZBERIPERCENTILE_DISC( 0,25 ) ZNOTRAJ SKUPINA ( NAROČITE PO ceni) NAD () KOT percentil_25
OD
izdelki;
To bi moralo izračunati 25 th percentil podatkov.
Zaključek
Ta vadnica je pokrivala, kako uporabiti različne funkcije za izračun percentilov v bazah podatkov SQL.