En av de vanlige statistiske beregningene som kommer opp selv i databaseadministrasjon er en persentil.
En persentil er et statistisk mål som lar oss dele et datasett i like deler av segmenter. Persentilenes rolle er å gi et innblikk i datafordelingen som er hvordan vi forstår hvordan verdiene er spredt.
I denne opplæringen lærer vi hvordan vi kan beregne persentilene i SQL for å dele dataene inn i ulike segmenter.
Eksempeltabell
La oss starte med å sette opp en grunnleggende tabell som inneholder eksempeldata for demonstrasjonsformål. Dette hjelper oss til å illustrere hvordan de ulike metodene for å beregne persentilene oppfører seg og resultatet.
La oss lage en tabell kalt 'produkter' som inneholder dagligvareinformasjonen. 'Opprett tabell'-klausulen er som følger:
CREATE TABLE-produkter (
product_id INT PRIMARY KEY AUTO_INCREMENT,
produktnavn VARCHAR( 255 ),
kategori VARCHAR( 255 ),
pris DESIMAL( 10 , 2 ),
mengde INT,
expiration_date DATE,
strekkode BIGINT
);
Når vi har opprettet tabellen, kan vi fortsette og legge til eksempeldataene i tabellen. Vi kan bruke følgende 'sett inn'-utsagn:
sett inninn i
produkter (produktnavn,
kategori,
pris,
mengde,
utløpsdato,
strekkode)
verdier ( 'Kokkehatt 25 cm' ,
'bakeri' ,
24,67 ,
57 ,
'2023-09-09' ,
2854509564204 );
sett inn
inn i
produkter (produktnavn,
kategori,
pris,
mengde,
utløpsdato,
strekkode)
verdier ( Vaktelegg – hermetisert ,
'spiskammers' ,
17,99 ,
67 ,
'2023-09-29' ,
1708039594250 );
sett inn
inn i
produkter (produktnavn,
kategori,
pris,
mengde,
utløpsdato,
strekkode)
verdier ( 'Kaffe - Egg Nog Capuccino' ,
'bakeri' ,
92,53 ,
10 ,
'2023-09-22' ,
8704051853058 );
sett inn
inn i
produkter (produktnavn,
kategori,
pris,
mengde,
utløpsdato,
strekkode)
verdier ( 'Pære - stikkende' ,
'bakeri' ,
65,29 ,
48 ,
'2023-08-23' ,
5174927442238 );
sett inn
inn i
produkter (produktnavn,
kategori,
pris,
mengde,
utløpsdato,
strekkode)
verdier ( 'Pasta - Angel Hair' ,
'spiskammers' ,
48,38 ,
59 ,
'2023-08-05' ,
8008123704782 );
sett inn
inn i
produkter (produktnavn,
kategori,
pris,
mengde,
utløpsdato,
strekkode)
verdier ( 'Vin - Prosecco Valdobiaddene' ,
'produsere' ,
44,18 ,
3 ,
'2023-03-13' ,
6470981735653 );
På slutten bør du ha en tabell som følger:
SQL-persentil
Som du kan gjette, kan måten å beregne persentilen på, variere avhengig av databasemotoren. Den vanligste metoden er imidlertid å bruke funksjonene PERCENTILE_DISC() og PERCENTILE_CONT().
Disse funksjonene er en del av Standard SQL-spesifikasjonen (2003). Derfor er det bundet til å bli støttet av PostgreSQL og Oracle.
PERCENTILE_CONT()
La oss starte med PERCENTILE_CONT()-funksjonen. Denne funksjonen lar oss beregne persentilverdiene som en brøkdel av datasettet.
Funksjonen returnerer en interpolert verdi som kanskje ikke er presis for det spesifikke datapunktet i datasettet.
Funksjonssyntaksen er som følger:
PERCENTILE_CONT(persentil) INNEN GRUPPE ( REKKEFØLGE AV kolonnenavn) OVER ();Funksjonen godtar følgende parametere:
- Persentil – Den spesifiserer ønsket persentilverdi (0,0 til 1,0).
- kolonnenavn – Det angir kolonnen som vi ønsker å beregne persentilen for.
- OVER () – Den setter vindusfunksjonen til å spesifisere hele datasettet.
Et eksempel på hvordan du bruker denne funksjonen er som følger:
PLUKKE UTPERCENTILE_CONT( 0,5 ) INNEN GRUPPE ( REKKEFØLGE ETTER pris) OVER () SOM median
FRA
Produkter;
Merk: Den gitte spørringen fungerer bare i PostgreSQL siden MySQL ikke støtter bruken av WITHIN GROUP.
Dette beregner 50 th persentilen av de oppgitte dataene.
PERCENTILE_DISC()
Vi kan bruke funksjonen PERCENTILE_DISC() for å beregne persentilverdien som en diskret verdi direkte fra datasettet.
Funksjonen returnerer en verdi som tilsvarer et faktisk datapunkt.
Funksjonssyntaksen er som følger (PostgreSQL):
PERCENTILE_DISC(persentil) INNEN GRUPPE ( REKKEFØLGE AV kolonnenavn) OVER ();Et eksempelutgang er som følger:
PLUKKE UTPERCENTILE_DISC( 0,25 ) INNEN GRUPPE ( REKKEFØLGE ETTER pris) OVER () SOM persentil_25
FRA
Produkter;
Dette bør beregne 25 th persentil av dataene.
Konklusjon
Denne opplæringen dekket hvordan du bruker de ulike funksjonene til å beregne persentilene i SQL-databaser.