Come emulare la mediana () Funzione di aggregazione Utilizzando inverse Funzioni di distribuzione

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