Funzioni di GRUPPO
Group Functions sono funzioni applicate su più dati e restituiscono un singolo risultato.
Sono posizionate dopo l'istruzione SELECT e possono essere presenti più di una nella stessa istruzione SELECT separate da virgola.
Le regole principali sono:
1) operano su un singolo gruppo di righe alla volta
2) vengono ignorate le righe con occorrenze a NULL a meno che non si usino le funzioni NVL, NVL2 e COALESCE. Vedere il post NULL
3) gli argomenti delle funzioni possono essere di tipo CHAR, VARCHAR2, NUMBER o DATE
4) DISTINCT permette di selezionare nelle funzioni solo i valori non duplicati mentre ALL considera tutti i valori, ed è il valore di default.
AVG, SUM, MIN e MAX, STDDEV e VARIANCE operano solo su dati Numerici mentre MIN e MAX possono essere usate anche per Char e Date.
VARIANCE calcola la varianza statistica mentre STDDEV calcola la deviazione standard, cioè il grado di deviazione dal valore medio del gruppo. Corrisponde alla radice quadrata della varianza.
select variance (PROD_LIST_PRICE)Var, stddev(PROD_LIST_PRICE) dev
from PRODUCTS;
VAR DEV
---------- ----------
84563,9124 290,798749
La seguente query calcola la deviazione standard del prezzo di listino dei prodotti che appartengono alla sottocategoria "Printer Supplies" ordinati per nome del prodotto.
select PROD_NAME ,PROD_LIST_PRICE , stddev(PROD_LIST_PRICE) OVER ( order by PROD_NAME ) as dev
from PRODUCTS
where PROD_SUBCATEGORY_ID= 2036 ;
PROD_NAME PROD_LIST_PRICE DEV
-------------------------------------------------- --------------- ----------------------------------
Model A3827H Black Image Cartridge 89,99 0
Model CD13272 Tricolor Ink Cartridge 36,99 37,4766594
Model NM500X High Yield Toner Cartridge 192,99 79,3242292
Model SM26273 Black Ink Cartridge 27,99 75,7759856
Da menzionare è anche la funzioni COUNT e l'utilizzo della clausola GROUP BY che è obbligatoria quando si utilizzano più funzioni di gruppo in cascata. Ad esempio se si calcola la media dei salari minimi di tutti i dipartimenti:
SELECT AVG(MIN(salary))
FROM employees ;
ORA-00978: funzione di gruppo in gerarchia senza GROUP BY
00978. 00000 - "nested group function without GROUP BY"
La query corretta è la seguente:
SELECT AVG(MIN(salary))
FROM employees
group by department_id;
Le funzioni di gruppo non possono essere usate nelle clausole WHERE o GROUP BY.
Ad esempio la seguente istruzione:
SELECT department_id,AVG(salary)
from EMPLOYEES
where AVG(SALARY) > 3000
group by DEPARTMENT_ID;
Restitusce il messaggio:
SQL Error: ORA-00934: group function is not allowed here
00934. 00000 - "group function is not allowed here"
Per risolvere usare la clausola HAVING.
SELECT department_id, AVG(salary)
FROM employees
group by DEPARTMENT_ID
HAVING AVG(salary) > 3000;
Sono posizionate dopo l'istruzione SELECT e possono essere presenti più di una nella stessa istruzione SELECT separate da virgola.
Le regole principali sono:
1) operano su un singolo gruppo di righe alla volta
2) vengono ignorate le righe con occorrenze a NULL a meno che non si usino le funzioni NVL, NVL2 e COALESCE. Vedere il post NULL
3) gli argomenti delle funzioni possono essere di tipo CHAR, VARCHAR2, NUMBER o DATE
4) DISTINCT permette di selezionare nelle funzioni solo i valori non duplicati mentre ALL considera tutti i valori, ed è il valore di default.
AVG, SUM, MIN e MAX, STDDEV e VARIANCE operano solo su dati Numerici mentre MIN e MAX possono essere usate anche per Char e Date.
VARIANCE calcola la varianza statistica mentre STDDEV calcola la deviazione standard, cioè il grado di deviazione dal valore medio del gruppo. Corrisponde alla radice quadrata della varianza.
select variance (PROD_LIST_PRICE)Var, stddev(PROD_LIST_PRICE) dev
from PRODUCTS;
VAR DEV
---------- ----------
84563,9124 290,798749
La seguente query calcola la deviazione standard del prezzo di listino dei prodotti che appartengono alla sottocategoria "Printer Supplies" ordinati per nome del prodotto.
select PROD_NAME ,PROD_LIST_PRICE , stddev(PROD_LIST_PRICE) OVER ( order by PROD_NAME ) as dev
from PRODUCTS
where PROD_SUBCATEGORY_ID= 2036 ;
PROD_NAME PROD_LIST_PRICE DEV
-------------------------------------------------- --------------- ----------------------------------
Model A3827H Black Image Cartridge 89,99 0
Model CD13272 Tricolor Ink Cartridge 36,99 37,4766594
Model NM500X High Yield Toner Cartridge 192,99 79,3242292
Model SM26273 Black Ink Cartridge 27,99 75,7759856
Da menzionare è anche la funzioni COUNT e l'utilizzo della clausola GROUP BY che è obbligatoria quando si utilizzano più funzioni di gruppo in cascata. Ad esempio se si calcola la media dei salari minimi di tutti i dipartimenti:
SELECT AVG(MIN(salary))
FROM employees ;
ORA-00978: funzione di gruppo in gerarchia senza GROUP BY
00978. 00000 - "nested group function without GROUP BY"
La query corretta è la seguente:
SELECT AVG(MIN(salary))
FROM employees
group by department_id;
Le funzioni di gruppo non possono essere usate nelle clausole WHERE o GROUP BY.
Ad esempio la seguente istruzione:
SELECT department_id,AVG(salary)
from EMPLOYEES
where AVG(SALARY) > 3000
group by DEPARTMENT_ID;
Restitusce il messaggio:
SQL Error: ORA-00934: group function is not allowed here
00934. 00000 - "group function is not allowed here"
Per risolvere usare la clausola HAVING.
SELECT department_id, AVG(salary)
FROM employees
group by DEPARTMENT_ID
HAVING AVG(salary) > 3000;
