Modulo 5 - Aggregazioni e Funzioni

in data
Modulo 5 - Aggregazioni e Funzioni Modulo 5 - Aggregazioni e Funzioni
Modulo 5 - Aggregazioni e Funzioni

Modulo 5: Aggregazioni e Funzioni

Benvenuto al Modulo 5! Qui impariamo a sintetizzare e analizzare dati su larga scala. Le aggregazioni ti permettono di calcolare statistiche (come somme o medie) su gruppi di record, mentre le funzioni scalari trasformano valori individuali. Questo è cruciale per report, analytics e business intelligence. Useremo le tabelle prodotti, clienti e ordini dai moduli precedenti – se non le hai, ricreale con i dati di esempio.

Approfondiamo i punti con sintassi, esempi e consigli. Tutti i comandi sono standard SQL; testali!

1. Funzioni Aggregate: COUNT, SUM, AVG, MIN, MAX

  • Panoramica: Queste funzioni operano su un set di valori e restituiscono un singolo risultato. Usale con SELECT per riepiloghi. Senza GROUP BY (prossimo punto), aggregano l'intera tabella.

  • COUNT: Conta righe (o non-NULL). COUNT(*) conta tutte; COUNT(colonna) solo non-NULL.

    • Esempio: SELECT COUNT(*) AS totale_prodotti FROM prodotti; (Restituisce 8, assumendo dati aggiunti).

  • SUM: Somma valori numerici.

    • Esempio: SELECT SUM(prezzo * quantita) AS valore_magazzino FROM prodotti; (Valore totale stock).

  • AVG: Media aritmetica.

    • Esempio: SELECT AVG(prezzo) AS media_prezzi FROM prodotti;.

  • MIN/MAX: Minimo/massimo.

    • Esempio: SELECT MIN(prezzo) AS piu_economico, MAX(prezzo) AS piu_caro FROM prodotti;.

  • Esempio combinato:

    SELECT 
    COUNT(id) AS num_ordini,
    SUM(quantita) AS totale_quantita,
    AVG(quantita) AS media_per_ordine
FROM ordini;
    SELECT 
    COUNT(id) AS num_ordini,
    SUM(quantita) AS totale_quantita,
    AVG(quantita) AS media_per_ordine
FROM ordini;

    Risultato (basato su dati esempio):

    num_ordini totale_quantita media_per_ordine
    5 8 1.6

  • Consigli: Aggrega solo numerici; per stringhe, usa funzioni scalari. Ignora NULL automaticamente.

2. Raggruppare Dati (GROUP BY) e Filtrare Gruppi (HAVING)

  • GROUP BY: Raggruppa righe con valori uguali in una colonna, applicando aggregazioni per gruppo.

    • Sintassi: GROUP BY colonna; dopo WHERE, prima ORDER BY.

  • Esempio base:

    SELECT categoria, COUNT(*) AS num_prodotti, SUM(quantita) AS stock_totale
FROM prodotti
GROUP BY categoria;
    SELECT categoria, COUNT(*) AS num_prodotti, SUM(quantita) AS stock_totale
FROM prodotti
GROUP BY categoria;

    Risultato:

    categoria num_prodotti stock_totale
    Elettronica 5 380
    Libri 2 350
    Casa 1 150

  • HAVING: Filtra gruppi post-aggregazione (simile a WHERE, ma per aggregati).

    • Esempio: HAVING COUNT(*) > 1; (solo categorie con >1 prodotto).

  • Esempio con join e HAVING:

    SELECT c.nome AS cliente, COUNT(o.id) AS num_ordini, SUM(o.quantita * p.prezzo) AS spesa_totale
FROM ordini o
INNER JOIN clienti c ON o.cliente_id = c.id
INNER JOIN prodotti p ON o.prodotto_id = p.id
GROUP BY c.nome
HAVING SUM(o.quantita * p.prezzo) > 500
ORDER BY spesa_totale DESC;
    SELECT c.nome AS cliente, COUNT(o.id) AS num_ordini, SUM(o.quantita * p.prezzo) AS spesa_totale
FROM ordini o
INNER JOIN clienti c ON o.cliente_id = c.id
INNER JOIN prodotti p ON o.prodotto_id = p.id
GROUP BY c.nome
HAVING SUM(o.quantita * p.prezzo) > 500
ORDER BY spesa_totale DESC;

    (Clienti con spesa >500, ordinati per spesa.)

  • Consigli: GROUP BY deve includere tutte le colonne non-aggregate in SELECT (regola ANSI). WHERE filtra prima del gruppo; HAVING dopo.

3. Funzioni Scalari: STRING, DATE, MATH (es. CONCAT, DATE_FORMAT)

  • Panoramica: Funzioni che operano su singoli valori (non aggregano). Variano per RDBMS, ma core è standard.

  • Stringhe: Manipola testo.

    • CONCAT: Unisce stringhe. CONCAT(nome, ' - ', categoria) AS descrizione;.
    • UPPER/LOWER: Maiuscole/minuscole. UPPER(nome);.
    • SUBSTRING: Estrae parte. SUBSTRING(nome, 1, 3); (primi 3 char).
    • Esempio: SELECT CONCAT('Prodotto: ', nome, ' a €', prezzo) FROM prodotti;.

  • Date: Gestisci date/ora.

    • DATE_FORMAT (MySQL): Formatta. DATE_FORMAT(data_ordine, '%Y-%m') AS mese_ordine;.
    • NOW(): Data corrente. SELECT NOW();.
    • DATEDIFF: Differenza giorni. DATEDIFF(NOW(), data_ordine);.
    • Esempio: SELECT data_ordine, DATE_ADD(data_ordine, INTERVAL 7 DAY) AS scadenza FROM ordini;.

  • Math: Calcoli numerici.

    • ROUND: Arrotonda. ROUND(prezzo, 0);.
    • ABS: Assoluto. ABS(prezzo - 100);.
    • RAND: Random. SELECT * FROM prodotti ORDER BY RAND() LIMIT 1; (prodotto casuale).

  • Esempio misto:

    SELECT 
    UPPER(c.nome) AS cliente_maiuscolo,
    ROUND(SUM(p.prezzo * o.quantita), 2) AS totale_arrotondato,
    DATE_FORMAT(o.data_ordine, '%d/%m/%Y') AS data_formattata
FROM ordini o
INNER JOIN clienti c ON o.cliente_id = c.id
INNER JOIN prodotti p ON o.prodotto_id = p.id
GROUP BY c.nome, o.data_ordine;
    SELECT 
    UPPER(c.nome) AS cliente_maiuscolo,
    ROUND(SUM(p.prezzo * o.quantita), 2) AS totale_arrotondato,
    DATE_FORMAT(o.data_ordine, '%d/%m/%Y') AS data_formattata
FROM ordini o
INNER JOIN clienti c ON o.cliente_id = c.id
INNER JOIN prodotti p ON o.prodotto_id = p.id
GROUP BY c.nome, o.data_ordine;

  • Consigli: Controlla docs del tuo RDBMS (es. MySQL ha STR_TO_DATE, PostgreSQL ha TO_CHAR). Combina con aggregazioni per query potenti.

Obiettivi del Modulo

  • Analizzare dati aggregati per insights (es. statistiche vendite).
  • Usare funzioni per trasformare e formattare output.
  • Combinare con join per report complessi.

Esercizi Suggeriti

  • Esercizio 1: Calcola conteggio, somma stock e media prezzi per categoria in prodotti, filtra gruppi con stock >100.
  • Esercizio 2: Per ordini, raggruppa per cliente: num_ordini, spesa_totale (usa join), HAVING num_ordini >1.
  • Esercizio 3: Usa funzioni scalari: CONCAT nome cliente e città, ROUND prezzi, formatta date ordini.
  • Bonus: Aggiungi una colonna data_ordine con date casuali, calcola età ordini con DATEDIFF.

English version

Module 5: Aggregations and Functions

Welcome to Module 5! Here we learn to synthesize and analyze data at scale. Aggregations let you calculate statistics (such as sums or averages) on groups of records, while scalar functions transform individual values. This is crucial for reporting, analytics and business intelligence. We'll use the products, customers and orders tables from the previous modules – if you don't have them, recreate them with the example data.

We delve deeper into the points with syntax, examples and advice. All commands are standard SQL; test them!

1. Aggregate Functions: COUNT, SUM, AVG, MIN, MAX

  • Overview: These functions operate on a set of values and return a single result. Use them with SELECT for summaries. Without GROUP BY (next point), they aggregate the entire table.

  • COUNT: Count rows (or non-NULL). COUNT(*) counts all; COUNT(column) non-NULL only.

  • Example: SELECT COUNT(*) AS product_total FROM products; (Returns 8, assuming added data).

  • SUM: Sum numerical values.

  • Example: SELECT SUM(price * quantity) AS stock_value FROM products; (Total stock value).

  • AVG: Arithmetic mean.

  • Example: SELECT AVG(price) AS average_prices FROM products;.

  • MIN/MAX: Minimum/maximum.

  • Example: SELECT MIN(price) AS cheapest, MAX(price) AS most_expensive FROM products;.

  • Combined example:

SELECT 
COUNT(id) AS num_orders, 
SUM(quantity) AS total_quantity, 
AVG(quantity) AS average_per_order 
FROM orders; 
SELECT 
COUNT(id) AS num_orders, 
SUM(quantity) AS total_quantity, 
AVG(quantity) AS average_per_order 
FROM orders;

Result (based on example data):

num_orders total_quantity average_per_order
5 8 1.6
  • Tips: Aggregate numbers only; for strings, use scalar functions. Ignore NULL automatically.

2. Grouping Data (GROUP BY) and Filtering Groups (HAVING)

  • GROUP BY: Group rows with equal values in a column, applying aggregations by group.

  • Syntax: GROUP BY column; after WHERE, before ORDER BY.

  • Basic example:

SELECT category, COUNT(*) AS num_products, SUM(quantity) AS total_stock 
FROM products 
GROUP BY category; 
SELECT category, COUNT(*) AS num_products, SUM(quantity) AS total_stock 
FROM products 
GROUP BY category;

Result:

category num_products stock_total
Electronics 5 380
Books 2 350
Home 1 150

  • HAVING: Filter groups post-aggregation (similar to WHERE, but for aggregates).

  • Example: HAVING COUNT(*) > 1; (only categories with >1 product).

  • Example with join and HAVING:

SELECT c.name AS customer, COUNT(o.id) AS num_orders, SUM(o.quantity * p.price) AS total_spend 
FROM orders o 
INNER JOIN customers c ON o.customer_id = c.id 
INNER JOIN products p ON o.product_id = p.id 
GROUP BY c.name 
HAVING SUM(o.quantity * p.price) > 500 
ORDER BY total_expense DESC; 
SELECT c.name AS customer, COUNT(o.id) AS num_orders, SUM(o.quantity * p.price) AS total_spend 
FROM orders o 
INNER JOIN customers c ON o.customer_id = c.id 
INNER JOIN products p ON o.product_id = p.id 
GROUP BY c.name 
HAVING SUM(o.quantity * p.price) > 500 
ORDER BY total_expense DESC;

(Customers with spend >500, sorted by spend.)

  • Tips: GROUP BY must include all non-aggregated columns in SELECT (ANSI rule). WHERE filters before group; HAVING after.

3. Scalar Functions: STRING, DATE, MATH (e.g. CONCAT, DATE_FORMAT)

  • Overview: Functions that operate on single values (do not aggregate). They vary per RDBMS, but core is standard.

  • Strings: Manipulate text.

  • CONCAT: Join strings. CONCAT(name, ' - ', category) AS description;.

  • UPPER/LOWER: Upper/lower case. UPPER(name);.

  • SUBSTRING: Extract part. SUBSTRING(name, 1, 3); (first 3 chars).

  • Example: SELECT CONCAT('Product: ', name, ' to €', price) FROM products;.

  • Date: Manage dates/time.

  • DATE_FORMAT (MySQL): Format. DATE_FORMAT(order_date, '%Y-%m') AS order_month;.

  • NOW(): Current date. SELECT NOW();.

  • DATEDIFF: Day difference. DATEDIFF(NOW(), order_date);.

  • Example: SELECT order_date, DATE_ADD(order_date, INTERVAL 7 DAY) AS expiration FROM orders;.

  • Math: Numerical calculations.

  • ROUND: Rounds. ROUND(price, 0);.

  • ABS: Absolute. ABS(price - 100);.

  • RAND: Random. SELECT * FROM products ORDER BY RAND() LIMIT 1; (random product).

  • Mixed example:

SELECT 
UPPER(c.name) AS customer_uppercase, 
ROUND(SUM(p.price * o.quantity), 2) AS rounded_total, 
DATE_FORMAT(o.order_date, '%d/%m/%Y') AS formatted_date 
FROM orders o 
INNER JOIN customers c ON o.customer_id = c.id 
INNER JOIN products p ON o.product_id = p.id 
GROUP BY c.name, o.order_date; 
SELECT 
UPPER(c.name) AS customer_uppercase, 
ROUND(SUM(p.price * o.quantity), 2) AS rounded_total, 
DATE_FORMAT(o.order_date, '%d/%m/%Y') AS formatted_date 
FROM orders o 
INNER JOIN customers c ON o.customer_id = c.id 
INNER JOIN products p ON o.product_id = p.id 
GROUP BY c.name, o.order_date;
  • Tips: Check your RDBMS docs (e.g. MySQL has STR_TO_DATE, PostgreSQL has TO_CHAR). Combine with aggregations for powerful queries.

Module Objectives

  • Analyze aggregate data for insights (e.g. sales statistics).
  • Use functions to transform and format output.
  • Combine with joins for complex reports.

Suggested Exercises

  • Exercise 1: Calculate count day, sum stock and average prices by category in products, filter groups with stock >100.
  • Exercise 2: For orders, group by customer: num_orders, total_spend (use join), HAVING num_orders >1.
  • Exercise 3: Use scalar functions: CONCAT customer name and city, ROUND prices, format order dates.
  • Bonus: Add an order_date column with random dates, calculate order ages with DATEDIFF.

Puoi seguire anche il mio canale YouTube https://www.youtube.com/channel/UCoOgys_fRjBrHmx2psNALow/ con tanti video interessanti


I consigli che offriamo sono di natura generale. Non sono consigli legali o professionali. Quello che può funzionare per una persona potrebbe non essere adatto a un’altra, e dipende da molte variabili.

Commenti

Posta un commento