InnoMarts logo
InnoMarts
Fast websites
← Back to course

Funzioni aggregate: contare, sommare e trovare numeri utili

Funzioni aggregate: contare, sommare e trovare numeri utili

Bentornato.

Nella lezione precedente hai imparato JOIN.

Hai imparato a leggere dati da tabelle collegate.

Molto importante.

Ora possiamo fare domande a PostgreSQL come:

Mostrami i prodotti con i nomi delle categorie.
Mostrami gli ordini con i nomi dei clienti.
Mostrami i post con i nomi degli autori.

Ottimo.

Ma a volte non vogliamo vedere ogni singola riga.

A volte vogliamo risposte.

Per esempio:

Quanti prodotti abbiamo?
Qual è il valore totale di tutti i prodotti?
Qual è il prezzo medio?
Qual è il prodotto più economico?
Qual è il prodotto più costoso?
Quanti prodotti ci sono in ogni categoria?

Qui entrano in gioco le funzioni aggregate.

Le funzioni aggregate prendono molte righe e restituiscono un risultato utile.

Molto educate.

Molto produttive.

Come un contabile stanco con superpoteri.

Cosa imparerai

In questa lezione imparerai:

Alla fine di questa lezione sarai in grado di fare a PostgreSQL domande utili per progetti reali.

Non solo:

Mostrami tutto.

Ma:

Riassumi questo caos e dimmi cosa conta davvero.

Questo è un grande passo avanti.

Il database non è più solo una scatola dove metti dati.

Diventa un assistente che sa contare.

E questo è utile.

Perché gli esseri umani non sono sempre bravissimi a contare.

Soprattutto dopo il quarto caffè.

Preparare il database

Apri PostgreSQL:

sudo -iu postgres psql

Collegati al tuo database:

\c learning_postgresql

Se il database non esiste, crealo:

CREATE DATABASE learning_postgresql;

Poi collegati:

\c learning_postgresql

Ora elimina le vecchie tabelle, se esistono:

DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;

Eliminiamo prima products perché dipende da categories.

PostgreSQL ricorda le relazioni.

A volte meglio delle persone.

Creare le tabelle

Crea la tabella categories:

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) UNIQUE NOT NULL
);

Crea la tabella products:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price NUMERIC(10, 2) CHECK (price >= 0),
  quantity INTEGER CHECK (quantity >= 0),
  category_id INTEGER REFERENCES categories(id)
);

Questa tabella ha:

È già qualcosa di abbastanza realistico.

Non è ancora un sistema completo per un negozio.

Ma è sufficiente per far lavorare PostgreSQL un po’.

In modo sano.

Didattico.

Senza chiamare i sindacati.

Inserire i dati

Inserisci le categorie:

INSERT INTO categories (name)
VALUES
  ('Electronics'),
  ('Furniture'),
  ('Office'),
  ('Kitchen');

Inserisci i prodotti:

INSERT INTO products (name, price, quantity, category_id)
VALUES
  ('Laptop', 900.00, 5, 1),
  ('Mouse', 25.00, 30, 1),
  ('Keyboard', 70.00, 15, 1),
  ('Desk Chair', 150.00, 8, 2),
  ('Bookshelf', 120.00, 4, 2),
  ('Notebook', 5.00, 100, 3),
  ('Pen', 2.00, 200, 3),
  ('Whiteboard', 80.00, 3, 3);

Controlla i dati:

SELECT * FROM categories;
SELECT * FROM products;

Nota una cosa.

La categoria Kitchen non ha prodotti.

Questo ci servirà dopo.

Povera Kitchen.

Aspetta ancora un cucchiaio.

Cosa sono le funzioni aggregate?

Le funzioni aggregate calcolano un risultato partendo da molte righe.

Per esempio, questa tabella ha molti prodotti.

Ma possiamo chiedere:

Quanti prodotti ci sono?

PostgreSQL può restituire un solo numero.

Questa è aggregazione.

Le funzioni aggregate più comuni sono:

COUNT
SUM
AVG
MIN
MAX

Rispondono a domande come:

Quanti?
Quanto in totale?
Qual è la media?
Qual è il valore più piccolo?
Qual è il valore più grande?

Sono utilissime in dashboard, report, pannelli admin, API, fatture, statistiche e strumenti aziendali.

Praticamente ovunque le persone vogliono numeri.

E poi discutono dei numeri in riunione.

Classico.

COUNT

COUNT conta le righe.

Esegui:

SELECT COUNT(*)
FROM products;

Risultato:

count
-----
8

COUNT(*) significa:

Conta tutte le righe.

Non gli interessa una colonna specifica.

Conta semplicemente le righe.

Semplice.

Forte.

Senza drammi.

COUNT con un alias

La colonna del risultato si chiama count.

Va bene.

Ma possiamo renderla più chiara:

SELECT COUNT(*) AS total_products
FROM products;

Risultato:

total_products
--------------
8

Molto meglio.

Gli alias rendono i risultati più facili da capire.

Il te del futuro ti ringrazierà.

Il te del futuro è già stanco.

Aiutalo.

COUNT su una colonna

Puoi anche contare una colonna specifica:

SELECT COUNT(category_id) AS products_with_category
FROM products;

Questo conta le righe in cui category_id non è NULL.

Nei nostri dati ogni prodotto ha una categoria.

Quindi il risultato è:

products_with_category
----------------------
8

Ma c’è una differenza importante.

COUNT(*) conta tutte le righe.

COUNT(column) conta solo le righe in cui quella colonna non è NULL.

Questo conta molto.

Perché NULL non è zero.

NULL significa sconosciuto o mancante.

PostgreSQL lo tratta con attenzione.

Come dovrebbe.

NULL non è un numero.

È un piccolo fantasma del database.

Esempio con NULL

Inserisci un prodotto senza categoria:

INSERT INTO products (name, price, quantity, category_id)
VALUES ('Mystery Box', 50.00, 2, NULL);

Ora esegui:

SELECT COUNT(*) AS total_products
FROM products;

Otterrai:

total_products
--------------
9

Ora esegui:

SELECT COUNT(category_id) AS products_with_category
FROM products;

Otterrai:

products_with_category
----------------------
8

Perché?

Perché Mystery Box ha category_id = NULL.

Quindi COUNT(category_id) non lo conta.

Questo è importante.

Molto importante.

Importante da database.

Non tipo “ricordati di comprare il latte”.

Più tipo “perché il mio report è sbagliato?” importante.

SUM

SUM somma i valori.

Per esempio, possiamo calcolare il numero totale di articoli in magazzino:

SELECT SUM(quantity) AS total_items
FROM products;

Questo somma tutte le quantità.

Risultato esempio:

total_items
-----------
367

Puoi anche calcolare il valore totale del magazzino.

Per ogni prodotto ci serve:

price * quantity

Poi sommiamo il risultato:

SELECT SUM(price * quantity) AS total_stock_value
FROM products;

Risultato esempio:

total_stock_value
-----------------
8945.00

Questo è utile.

Ora PostgreSQL non sta solo conservando dati.

Sta calcolando informazioni utili.

Molto professionale.

Molto “qui forse qualcuno deve pagare una fattura”.

AVG

AVG calcola il valore medio.

Esempio:

SELECT AVG(price) AS average_price
FROM products;

PostgreSQL può restituire molte cifre decimali.

Per renderlo più pulito, usa ROUND:

SELECT ROUND(AVG(price), 2) AS average_price
FROM products;

Risultato esempio:

average_price
-------------
155.78

Il numero esatto dipende dai tuoi dati.

AVG è utile per domande come:

Qual è il prezzo medio dei prodotti?
Qual è il totale medio degli ordini?
Qual è lo stipendio medio?
Qual è il livello medio di caos in questo progetto?

PostgreSQL può rispondere alle prime tre.

Per la quarta serve supporto emotivo.

MIN

MIN trova il valore più piccolo.

Esempio:

SELECT MIN(price) AS cheapest_price
FROM products;

Risultato:

cheapest_price
--------------
2.00

Questo ci dice il prezzo più basso.

Ma non ci dice quale prodotto ha quel prezzo.

Per quello possiamo usare ORDER BY e LIMIT:

SELECT name, price
FROM products
ORDER BY price ASC
LIMIT 1;

Risultato:

name | price
-----|------
Pen  | 2.00

MIN dà il valore minimo.

ORDER BY con LIMIT può mostrare tutta la riga.

Entrambi sono utili.

Strumenti diversi.

Stessa cassetta degli attrezzi.

MAX

MAX trova il valore più grande.

Esempio:

SELECT MAX(price) AS highest_price
FROM products;

Risultato:

highest_price
-------------
900.00

Per trovare il prodotto più costoso:

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 1;

Risultato:

name   | price
-------|------
Laptop | 900.00

Molto bene.

PostgreSQL ha trovato la bestia costosa.

Probabilmente con luci RGB.

Più aggregazioni in una query

Puoi usare più funzioni aggregate nella stessa query.

Esempio:

SELECT
  COUNT(*) AS total_products,
  SUM(quantity) AS total_items,
  ROUND(AVG(price), 2) AS average_price,
  MIN(price) AS cheapest_price,
  MAX(price) AS highest_price
FROM products;

Questo restituisce una riga riassuntiva.

Esempio:

total_products | total_items | average_price | cheapest_price | highest_price
---------------|-------------|---------------|----------------|--------------
9              | 367         | 155.78        | 2.00           | 900.00

Perfetto per dashboard.

Una query.

Tanti numeri utili.

PostgreSQL fa lavoro d’ufficio senza nemmeno chiedere una sedia.

Rispetto.

GROUP BY

Fino ad ora le funzioni aggregate ci hanno dato un risultato per tutta la tabella.

Ma spesso vogliamo risultati per gruppo.

Per esempio:

Quanti prodotti ci sono in ogni categoria?

Per questo usiamo GROUP BY.

Esempio:

SELECT
  category_id,
  COUNT(*) AS products_count
FROM products
GROUP BY category_id;

Risultato esempio:

category_id | products_count
------------|---------------
1           | 3
2           | 2
3           | 3
            | 1

La categoria vuota significa NULL.

È il nostro Mystery Box.

Non ha categoria.

Molto misterioso.

Molto box.

GROUP BY con JOIN

category_id è utile per PostgreSQL.

Ma gli esseri umani vogliono i nomi delle categorie.

Quindi usiamo JOIN.

SELECT
  c.name AS category_name,
  COUNT(p.id) AS products_count
FROM categories AS c
JOIN products AS p
ON c.id = p.category_id
GROUP BY c.name;

Risultato:

category_name | products_count
--------------|---------------
Electronics   | 3
Furniture     | 2
Office        | 3

Molto più bello.

Ma nota una cosa.

Kitchen manca.

Perché?

Perché JOIN significa INNER JOIN.

Appaiono solo le righe che hanno una corrispondenza.

Kitchen non ha prodotti.

Quindi non appare.

Ancora povera Kitchen.

GROUP BY con LEFT JOIN

Se vogliamo mostrare tutte le categorie, anche quelle senza prodotti, usiamo LEFT JOIN.

SELECT
  c.name AS category_name,
  COUNT(p.id) AS products_count
FROM categories AS c
LEFT JOIN products AS p
ON c.id = p.category_id
GROUP BY c.name
ORDER BY c.name;

Risultato:

category_name | products_count
--------------|---------------
Electronics   | 3
Furniture     | 2
Kitchen       | 0
Office        | 3

Ora Kitchen appare.

Dettaglio importante:

Abbiamo usato:

COUNT(p.id)

Non:

COUNT(*)

Perché?

Perché con LEFT JOIN, COUNT(*) conterebbe anche la riga della categoria quando non c’è nessun prodotto.

Per le categorie senza prodotti, COUNT(*) potrebbe dare 1.

Sarebbe sbagliato.

COUNT(p.id) conta solo i prodotti reali.

Questo dettaglio salva i report.

E a volte anche il lavoro.

SUM con GROUP BY

Possiamo calcolare il valore del magazzino per categoria.

SELECT
  c.name AS category_name,
  SUM(p.price * p.quantity) AS stock_value
FROM categories AS c
LEFT JOIN products AS p
ON c.id = p.category_id
GROUP BY c.name
ORDER BY stock_value DESC;

Risultato esempio:

category_name | stock_value
--------------|------------
Electronics   | 6750.00
Furniture     | 1680.00
Office        | 1140.00
Kitchen       |

Kitchen non ha prodotti.

Quindi la somma è NULL.

Se vogliamo mostrare 0 invece di NULL, possiamo usare COALESCE.

SELECT
  c.name AS category_name,
  COALESCE(SUM(p.price * p.quantity), 0) AS stock_value
FROM categories AS c
LEFT JOIN products AS p
ON c.id = p.category_id
GROUP BY c.name
ORDER BY stock_value DESC;

Ora Kitchen mostra 0.

COALESCE restituisce il primo valore non nullo.

Quindi questo:

COALESCE(SUM(p.price * p.quantity), 0)

significa:

Se la somma esiste, mostrala.
Se la somma è NULL, mostra 0.

Molto utile.

Molto pratico.

Molto “per favore non rompere la mia dashboard”.

AVG con GROUP BY

Possiamo calcolare il prezzo medio per categoria:

SELECT
  c.name AS category_name,
  ROUND(AVG(p.price), 2) AS average_price
FROM categories AS c
LEFT JOIN products AS p
ON c.id = p.category_id
GROUP BY c.name
ORDER BY average_price DESC;

Risultato esempio:

category_name | average_price
--------------|---------------
Electronics   | 331.67
Furniture     | 135.00
Office        | 29.00
Kitchen       |

Ancora una volta, Kitchen non ha prodotti.

Quindi il prezzo medio è NULL.

Ed è corretto.

Non puoi calcolare una media da zero valori.

PostgreSQL si rifiuta di inventare numeri.

Bravo database.

HAVING

WHERE filtra le righe prima del raggruppamento.

HAVING filtra i gruppi dopo il raggruppamento.

Questo è importante.

Immagina di voler vedere le categorie con più di due prodotti.

Sbagliato:

SELECT
  category_id,
  COUNT(*) AS products_count
FROM products
WHERE COUNT(*) > 2
GROUP BY category_id;

Questo non funziona.

Perché?

Perché WHERE non può usare risultati aggregati.

Usa HAVING:

SELECT
  category_id,
  COUNT(*) AS products_count
FROM products
GROUP BY category_id
HAVING COUNT(*) > 2;

Risultato:

category_id | products_count
------------|---------------
1           | 3
3           | 3

HAVING filtra i gruppi.

Idea semplice.

Errore molto comune.

SQL ama l’ordine.

Gli umani amano indovinare.

SQL vince.

HAVING con JOIN

Ora mostriamo i nomi delle categorie.

SELECT
  c.name AS category_name,
  COUNT(p.id) AS products_count
FROM categories AS c
JOIN products AS p
ON c.id = p.category_id
GROUP BY c.name
HAVING COUNT(p.id) > 2
ORDER BY products_count DESC;

Risultato:

category_name | products_count
--------------|---------------
Electronics   | 3
Office        | 3

Questo significa:

Mostra solo le categorie che hanno più di due prodotti.

È utile per i report.

Per esempio:

Mostra le categorie attive.
Mostra i clienti con più di cinque ordini.
Mostra gli autori con più di dieci post.
Mostra i prodotti venduti più di cento volte.

HAVING serve per filtrare i risultati aggregati.

Non dimenticarlo.

Aspetta in silenzio.

Come un bibliotecario SQL molto serio.

WHERE e HAVING insieme

Puoi usare WHERE e HAVING nella stessa query.

Esempio:

SELECT
  c.name AS category_name,
  COUNT(p.id) AS products_count
FROM categories AS c
JOIN products AS p
ON c.id = p.category_id
WHERE p.price >= 10
GROUP BY c.name
HAVING COUNT(p.id) >= 2
ORDER BY products_count DESC;

Questo significa:

Prima tieni solo i prodotti con prezzo almeno 10.
Poi raggruppali per categoria.
Poi mostra solo le categorie con almeno due prodotti.

L’ordine conta.

WHERE lavora prima del raggruppamento.

HAVING lavora dopo il raggruppamento.

Ricordalo e SQL farà meno male.

Non mai.

Meno.

Errori comuni

Usare WHERE invece di HAVING

Sbagliato:

SELECT category_id, COUNT(*)
FROM products
WHERE COUNT(*) > 2
GROUP BY category_id;

Corretto:

SELECT category_id, COUNT(*)
FROM products
GROUP BY category_id
HAVING COUNT(*) > 2;

Usa WHERE per le righe normali.

Usa HAVING per i gruppi.

Dimenticare GROUP BY

Sbagliato:

SELECT category_id, COUNT(*)
FROM products;

PostgreSQL si lamenterà perché category_id non è aggregato e non è raggruppato.

Corretto:

SELECT category_id, COUNT(*)
FROM products
GROUP BY category_id;

Se selezioni una colonna normale insieme a una funzione aggregata, di solito ti serve GROUP BY.

PostgreSQL è severo.

Ma giusto.

Più o meno.

Contare la cosa sbagliata con LEFT JOIN

Sbagliato per contare i prodotti per categoria:

SELECT
  c.name,
  COUNT(*) AS products_count
FROM categories AS c
LEFT JOIN products AS p
ON c.id = p.category_id
GROUP BY c.name;

Questo può contare una categoria senza prodotti come 1.

Meglio:

SELECT
  c.name,
  COUNT(p.id) AS products_count
FROM categories AS c
LEFT JOIN products AS p
ON c.id = p.category_id
GROUP BY c.name;

Conta la colonna della tabella che vuoi davvero contare.

Piccolo dettaglio.

Grande differenza.

Trappola classica da database.

Pratica

Conta tutti i prodotti:

SELECT COUNT(*) AS total_products
FROM products;

Calcola il numero totale di articoli:

SELECT SUM(quantity) AS total_items
FROM products;

Calcola il valore totale del magazzino:

SELECT SUM(price * quantity) AS total_stock_value
FROM products;

Trova il prezzo medio:

SELECT ROUND(AVG(price), 2) AS average_price
FROM products;

Trova il prezzo più basso e quello più alto:

SELECT
  MIN(price) AS cheapest_price,
  MAX(price) AS highest_price
FROM products;

Conta i prodotti per categoria:

SELECT
  c.name AS category_name,
  COUNT(p.id) AS products_count
FROM categories AS c
LEFT JOIN products AS p
ON c.id = p.category_id
GROUP BY c.name
ORDER BY c.name;

Calcola il valore del magazzino per categoria:

SELECT
  c.name AS category_name,
  COALESCE(SUM(p.price * p.quantity), 0) AS stock_value
FROM categories AS c
LEFT JOIN products AS p
ON c.id = p.category_id
GROUP BY c.name
ORDER BY stock_value DESC;

Mostra solo le categorie con più di due prodotti:

SELECT
  c.name AS category_name,
  COUNT(p.id) AS products_count
FROM categories AS c
JOIN products AS p
ON c.id = p.category_id
GROUP BY c.name
HAVING COUNT(p.id) > 2;

Esegui le query.

Cambia i numeri.

Aggiungi prodotti.

Elimina prodotti.

Guarda come cambiano i risultati.

Questa è la parte divertente.

Beh, divertente da database.

Una specie diversa di divertimento.

Mini sfida

Crea due tabelle:

customers
orders

Regole:

Crea le tabelle:

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  total NUMERIC(10, 2) CHECK (total >= 0),
  customer_id INTEGER REFERENCES customers(id)
);

Inserisci i dati:

INSERT INTO customers (name)
VALUES
  ('Anna'),
  ('Marco'),
  ('Sofia'),
  ('Luca');

INSERT INTO orders (total, customer_id)
VALUES
  (49.99, 1),
  (120.00, 1),
  (35.50, 2),
  (200.00, 2),
  (15.00, 2),
  (80.00, 3);

Ora scrivi query per:

Suggerimento per i clienti senza ordini:

SELECT
  c.name AS customer_name,
  COUNT(o.id) AS orders_count
FROM customers AS c
LEFT JOIN orders AS o
ON c.id = o.customer_id
GROUP BY c.name
HAVING COUNT(o.id) = 0;

Questo è SQL da mondo reale.

Clienti.

Ordini.

Totali.

Report.

Esattamente il tipo di cosa per cui i database sono nati.

Come fogli Excel.

Ma con più disciplina.

E meno celle unite misteriose.

Riassunto

Oggi hai imparato:

Questa è una lezione molto importante.

Ora puoi analizzare i dati.

Non solo leggerli.

Puoi costruire report.

Puoi costruire dashboard.

Puoi rispondere a domande aziendali.

Puoi far contare le cose a PostgreSQL mentre bevi il caffè.

Questa è civiltà.

Prossima lezione

Nella prossima lezione parleremo di indici e prestazioni di base.

Vedremo perché alcune query diventano lente.

Impareremo come gli indici aiutano PostgreSQL a trovare i dati più velocemente.

Perché quando il database ha dieci righe, tutto è veloce.

Quando ha dieci milioni di righe, PostgreSQL inizia a fare domande serie.

E anche tu dovresti.