Alcuni database sono sufficienti per implementare la impressionante MEDIANA () funzione di aggregazione. Ricordate che la mediana () è sligthly diverso da (e spesso più utile) del MEDIA () o AVG () (media).
Mentre la media è calcolato come SUM (exp) / COUNT (exp) , la mediana () indica che il 50% di tutti i valori del campione sono superiori alla mediana () , mentre l'altro 50% del set sono inferiori la mediana () .
Quindi, in altre parole, se si prende la seguente query:
Visualizza sorgentestampare ?
01.
A t (valore) AS (
02.
SELEZIONARE 1 DA DUAL UNION ALL
03.
SELEZIONA 2 DA DOPPIO UNION ALL
04.
SELEZIONA 3 FROM DUAL
05.
)
06.
SELEZIONA
07.
avg (valore),
08.
mediana (valore)
09.
DA
10.
t;
... Allora sia media e mediana sono gli stessi:
Visualizza sorgentestampare ?
1.
mediana avg
2.
2 2
Ma se pesantemente inclinare i tuoi dati come questo:
Visualizza sorgentestampare ?
01.
A t (valore) AS (
02.
SELEZIONARE 1 DA DUAL UNION ALL
03.
SELEZIONA 2 DA DOPPIO UNION ALL
04.
SELEZIONA 100 FROM DUAL
05.
)
06.
SELEZIONA
07.
avg (valore),
08.
mediana (valore)
09.
DA
10.
t;
Allora la vostra media sarà anche distorta, mentre il vostro mediana sarà ancora indicare dove la maggior parte dei valori sono nel campione
Visualizza sorgentestampare ?
1.
mediana avg
2.
34,333 2
Il campione di cui sopra è ovviamente statisticamente insignificante, ma si può facilmente capire che l'effetto può essere drammatico e rilevante, se si dispone di più dati:
Licenza dell'immagine CC-BY-SA 3.0. Caricato a Wikipedia per Cmglee
Licenza dell'immagine CC-BY-SA 3.0. Caricato a Wikipedia per Cmglee
L'effetto inclinazione è molto importante in statistica e per rendere qualsiasi delle rivendicazioni interessante nulla, utilizzando percentili è spesso più utile utilizzando medie. Prendete il reddito medio rispetto al reddito medio di un paese, per esempio. Mentre il reddito medio negli Stati Uniti (e in molti altri paesi) è in costante aumento, il reddito mediano ha visto un calo negli ultimi dieci anni . Ciò è dovuto alla ricchezza di essere fortemente sbilanciata verso il super-ricchi sempre più.
Questo blog non è di politica ma di Java e SQL, così torniamo nel calcolo dei fatti reali.
Utilizzo precentiles in SQL
Come abbiamo visto prima, la mediana () divide il campione in due gruppi della stessa dimensione e assume il valore "tra" i due gruppi. Questo valore particolare viene anche chiamato il 50 ° percentile per il 50% di tutti i valori del campione sono inferiori al MEDIANA () . Possiamo quindi stabilire:
MIN (exp) : Il 0-percentile
MEDIANA (exp) : Il 50 ° percentile
MAX (exp) : Il 100 ° percentile
Tutto quanto sopra sono casi particolari di percentili, e mentre MIN () e MAX () sono supportati in tutti i database SQL (e lo standard SQL), MEDIANA () non è nello standard SQL e supportato solo dalle seguenti banche dati jOOQ :
CUBRID
HSQLDB
Oracolo
Sybase SQL Anywhere
C'è un altro modo di calcolare il MEDIANA () in particolare, e qualsiasi tipo di percentile, in generale, nello standard SQL, e dal momento che PostgreSQL 9.4 anche in PostgreSQL utilizzando ...
Ordinato-impostare le funzioni di aggregazione
È interessante notare che, a parte le funzioni della finestra , è anche possibile specificare ORDER BY clausole ad alcune funzioni di aggregazione che aggregano i dati basati su insiemi ordinati.
Una tale funzione è lo standard SQL PERCENTILE_CONT funzione, che prende il percentile come argomento, e poi accetta un ulteriore all'interno del gruppo clausola che prende un ORDER BY clausola come argomento. Queste particolari funzioni insieme ordinato sono chiamati anche funzioni di distribuzione inverse , perché vogliamo trovare dove un particolare percentile si trova nella distribuzione di tutti i valori del campione ( se non hai paura della matematica, controllare l'articolo wikipedia )
Così, in PostgreSQL 9.4+, la mediana () funzione può essere emulato in questo modo:
Visualizza sorgentestampare ?
01.
A t (valore) AS (
02.
SELEZIONA 1 UNION ALL
03.
SELEZIONA 2 UNION ALL
04.
SELEZIONA 100
05.
)
06.
SELEZIONA
07.
avg (valore),
08.
PERCENTILE_CONT (0,5) ENTRO GROUP ( ORDER BY valore)
09.
DA
10.
t;
Questa sintassi interessante è standardizzato e può essere conosciuto per alcuni di voi da LISTAGG di Oracle () , che consente di aggregare i valori in stringhe concatenati:
Visualizza sorgentestampare ?
01.
A t (valore) AS (
02.
SELEZIONARE 1 DA DUAL UNION ALL
03.
SELEZIONA 2 DA DOPPIO UNION ALL
04.
SELEZIONA 100 FROM DUAL
05.
)
06.
SELEZIONA
07.
listagg (valore, ',' ) ENTRO GROUP ( ORDER BY valore)
08.
DA
09.
t;
Questa interrogazione produce semplicemente:
Visualizza sorgentestampare ?
1.
listagg
2.
---------
3.
1, 2, 100
Su un lato nota: LISTAGG () è, ovviamente, del tutto inutile, perché restituisce VARCHAR2 , che ha ancora una lunghezza massima di 4000 in Oracle. Inutile ...
Emulazione out-of-the-box con jOOQ
Come sempre, jOOQ emulerà questo genere di cose fuori dalla scatola. È possibile utilizzare il DSL.median () la funzione, o con l'imminente jOOQ 3.6, la nuova DSL.percentileCont () funzione per produrre lo stesso valore:
Visualizza sorgentestampare ?
1.
DSL.using (configurazione)
2.
. selezionare (
3.
mediana (T.VALUE),
4.
percentileCont (0,5) .withinGroupOrderBy (T.VALUE)
5.
)
6.
. da (T)
7.
. prendere ();
jOOQ è il modo migliore per scrivere SQL in Java
Nessun commento:
Posta un commento
Nota. Solo i membri di questo blog possono postare un commento.