La media in PostgreSQL: esempio pratico

In questo articolo vedremo un esempio pratico di media in PostgreSQL analizzando un esempio pratico.

Partendo dallo schema con due tabelle — products e reviews collegate da reviews.product_id → products.id—l’obiettivo è costruire una classifica dei 10 prodotti con il miglior voto medio, utilizzando in modo consapevole join, funzioni di aggregazione, clausole di filtro su gruppi e ordinamenti multipli.

La query base

SELECT 
    p.id,
    p.name,
    ROUND(AVG(r.rating), 2) AS avg_rating,
    COUNT(r.id) AS num_reviews
FROM products p
JOIN reviews r ON r.product_id = p.id
GROUP BY p.id, p.name
HAVING COUNT(r.id) > 0
ORDER BY avg_rating DESC, num_reviews DESC
LIMIT 10;

Cosa fa ogni parte

  • JOIN: l’INNER JOIN restituisce solo i prodotti che hanno almeno una recensione.
  • AVG(r.rating): calcola il voto medio (da 1 a 5). ROUND(..., 2) lo rende leggibile a due decimali.
  • COUNT(r.id): conta il numero di recensioni per prodotto; utile come metrica di supporto e per i pareggi.
  • GROUP BY: raggruppa le righe delle recensioni per prodotto, così che AVG e COUNT operino sul gruppo.
  • HAVING: filtra i gruppi; qui esclude prodotti senza recensioni, anche se con l’INNER JOIN è ridondante ma esplicito.
  • ORDER BY: ordina prima per voto medio decrescente, poi per numero di recensioni decrescente per rompere i pareggi.
  • LIMIT 10: restituisce i primi dieci prodotti.

Varianti comuni

1) Imporre una soglia minima di recensioni

Per evitare che prodotti con pochissime recensioni dominino la classifica, usa una soglia minima:

HAVING COUNT(r.id) >= 5

2) Includere prodotti senza recensioni

Con un LEFT JOIN mostri anche i prodotti che non hanno recensioni, ordinandoli in coda:

SELECT 
    p.id,
    p.name,
    ROUND(AVG(r.rating), 2) AS avg_rating,
    COUNT(r.id) AS num_reviews
FROM products p
LEFT JOIN reviews r ON r.product_id = p.id
GROUP BY p.id, p.name
ORDER BY AVG(r.rating) DESC NULLS LAST, num_reviews DESC
LIMIT 10;

Qui l’ordinamento usa la media non arrotondata per maggiore precisione, e NULLS LAST spinge in fondo i prodotti senza media (nessuna recensione).

3) Ordinare con maggiore precisione

Arrotondare in SELECT migliora la leggibilità ma può introdurre pareggi apparenti. Per ordinare in modo più “fine”, ordina sulla media non arrotondata e mostra la versione arrotondata:

SELECT 
    p.id,
    p.name,
    ROUND(AVG(r.rating), 2) AS avg_rating,
    COUNT(r.id) AS num_reviews
FROM products p
JOIN reviews r ON r.product_id = p.id
GROUP BY p.id, p.name
ORDER BY AVG(r.rating) DESC, num_reviews DESC
LIMIT 10;

4) Criteri di spareggio aggiuntivi

Dopo media e numero recensioni, puoi aggiungere un terzo criterio stabile (per esempio il nome):

ORDER BY AVG(r.rating) DESC, COUNT(r.id) DESC, p.name ASC

Prestazioni e indici consigliati

  • Indice sulla foreign key: CREATE INDEX ON reviews (product_id); accelera la join e l’aggregazione per prodotto.
  • Indice parziale (opzionale): se filtri spesso per soglie minime, l’aggregazione rimane l’operazione dominante; concentrare l’attenzione sull’indice della FK è di solito sufficiente.
  • Statistiche aggiornate: assicurati che ANALYZE sia eseguito regolarmente per piani di esecuzione ottimali.

Creare una vista per riuso

Per riutilizzare la classifica e semplificare il codice applicativo, puoi creare una vista:

CREATE OR REPLACE VIEW top_products_by_rating AS
SELECT 
    p.id,
    p.name,
    AVG(r.rating) AS avg_rating,
    COUNT(r.id) AS num_reviews
FROM products p
JOIN reviews r ON r.product_id = p.id
GROUP BY p.id, p.name;

La vista memorizza la logica; la classifica “Top 10” si ottiene all’uso:

SELECT 
    id, 
    name, 
    ROUND(avg_rating, 2) AS avg_rating, 
    num_reviews
FROM top_products_by_rating
ORDER BY avg_rating DESC, num_reviews DESC
LIMIT 10;

Alternative avanzate

Filtri robusti: limite di confidenza

Per classifiche più affidabili, puoi usare una stima tipo Wilson score o una penalizzazione bayesiana del voto medio. Un approccio semplice è fare una media pesata con una media globale m e una costante k (recensioni “virtuali”):

WITH stats AS (
  SELECT AVG(rating) AS global_avg FROM reviews
)
SELECT 
  p.id,
  p.name,
  ROUND( (AVG(r.rating) * COUNT(r.id) + s.global_avg *  k) / (COUNT(r.id) + k), 3 ) AS bayes_avg,
  COUNT(r.id) AS num_reviews
FROM products p
JOIN reviews r ON r.product_id = p.id
CROSS JOIN stats s
GROUP BY p.id, p.name, s.global_avg
ORDER BY bayes_avg DESC
LIMIT 10;

Scegli k (ad esempio 5 o 10) in base a quanto vuoi attenuare i prodotti con poche recensioni.

Verifiche di qualità dati

  • Garantisci che rating sia vincolato tra 1 e 5 con un CHECK o constraints applicativi.
  • Elimina eventuali recensioni duplicate o incongruenti prima dell’aggregazione.

Riepilogo operativo

  1. Usa l’INNER JOIN per la Top 10 “solo recensiti” o il LEFT JOIN per includere tutti i prodotti.
  2. Aggrega con AVG e COUNT, filtra con HAVING (soglia minima opzionale).
  3. Ordina per media (meglio non arrotondata), poi per numero di recensioni e, se serve, per nome.
  4. Ottimizza con un indice su reviews(product_id) e valuta una vista per riuso.
  5. Per classifiche più robuste, considera una media bayesiana o uno score di confidenza.
Torna su