Come aggregare i dati mensili e annuali utilizzando la funzione COUNT in SQL

La funzione COUNT di SQL è uno strumento fondamentale per aggregare efficacemente il numero di record in un database. In questo articolo, spiegheremo come aggregare i dati mensili e annuali con esempi concreti. L’aggregazione dei dati è una competenza essenziale per l’intelligence aziendale e la creazione di report. Spiegheremo in modo chiaro, con esempi pratici che utilizzano dati reali, dalle basi di SQL fino agli usi avanzati.

Uso di base della funzione COUNT

La funzione COUNT è una funzione di aggregazione di SQL che restituisce il numero di valori non NULL in una colonna specificata. Un uso comune è ottenere il numero totale di record in una tabella o il numero di record che soddisfano determinate condizioni. Di seguito è riportata la sintassi di base e un esempio semplice.

Sintassi di base

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

Esempio di utilizzo

Ad esempio, per ottenere il numero totale di clienti dalla tabella dei clienti (customers), il seguente query può essere utilizzato.

SELECT COUNT(*)
FROM customers;

Questa query restituisce il numero totale di record nella tabella customers. È anche possibile ottenere il numero di record che soddisfano determinate condizioni.

Esempio con condizioni specifiche

Ad esempio, se si desidera ottenere il numero di clienti attivi, si può procedere nel seguente modo.

SELECT COUNT(*)
FROM customers
WHERE status = 'active';

Questa query restituisce il numero di clienti il cui status è ‘active’. Questo rende semplice l’aggregazione dei dati basata su condizioni specifiche.

Come aggregare i dati mensilmente

L’aggregazione dei dati mensile è molto importante per comprendere le tendenze dei dati in un periodo specifico. Qui spiegheremo come utilizzare la funzione COUNT di SQL insieme alla clausola GROUP BY per aggregare i dati mensili.

Sintassi di base

SELECT EXTRACT(YEAR FROM date_column) AS year, EXTRACT(MONTH FROM date_column) AS month, COUNT(*)
FROM table_name
GROUP BY EXTRACT(YEAR FROM date_column), EXTRACT(MONTH FROM date_column)
ORDER BY year, month;

Esempio di utilizzo

Ad esempio, per ottenere il numero di ordini mensili dalla tabella degli ordini (orders), la query seguente può essere utilizzata.

SELECT EXTRACT(YEAR FROM order_date) AS year, EXTRACT(MONTH FROM order_date) AS month, COUNT(*)
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
ORDER BY year, month;

Questa query aggrega il numero di ordini per ogni anno e mese, basandosi sulla colonna order_date della tabella orders, e mostra i risultati ordinati per anno e mese.

Esempio avanzato

È anche possibile specificare ulteriori condizioni, ad esempio aggregare il numero di ordini mensili per un determinato cliente.

SELECT EXTRACT(YEAR FROM order_date) AS year, EXTRACT(MONTH FROM order_date) AS month, COUNT(*)
FROM orders
WHERE customer_id = 123
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
ORDER BY year, month;

Questa query aggrega il numero di ordini mensili per un cliente specifico (customer_id 123). Aggiungendo condizioni, è possibile ottenere analisi più dettagliate dei dati.

Come aggregare i dati annualmente

L’aggregazione annuale dei dati è utile per comprendere le tendenze a lungo termine. Spiegheremo come utilizzare la funzione COUNT e la clausola GROUP BY per aggregare i dati annualmente.

Sintassi di base

SELECT EXTRACT(YEAR FROM date_column) AS year, COUNT(*)
FROM table_name
GROUP BY EXTRACT(YEAR FROM date_column)
ORDER BY year;

Esempio di utilizzo

Ad esempio, per ottenere il numero di ordini annuali dalla tabella degli ordini (orders), il seguente query può essere utilizzato.

SELECT EXTRACT(YEAR FROM order_date) AS year, COUNT(*)
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date)
ORDER BY year;

Questa query aggrega il numero di ordini per ogni anno, basandosi sulla colonna order_date della tabella orders, e mostra i risultati ordinati per anno.

Esempio avanzato

Aggiungendo ulteriori condizioni, è possibile eseguire aggregazioni più dettagliate dei dati annuali. Ad esempio, per aggregare il numero di ordini annuali per una categoria di prodotto specifica.

SELECT EXTRACT(YEAR FROM order_date) AS year, COUNT(*)
FROM orders
WHERE product_category = 'Electronics'
GROUP BY EXTRACT(YEAR FROM order_date)
ORDER BY year;

Questa query aggrega il numero di ordini per la categoria di prodotto ‘Electronics’ per ogni anno. Aggiungendo condizioni specifiche, si possono ottenere approfondimenti più mirati.

Aggregazione più dettagliata

In alcuni casi, può essere utile combinare l’aggregazione per anno e mese. La query per farlo è la seguente.

SELECT EXTRACT(YEAR FROM order_date) AS year, EXTRACT(MONTH FROM order_date) AS month, COUNT(*)
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
ORDER BY year, month;

Questa query aggrega il numero di ordini per ogni anno e mese. Questo permette un’analisi dei dati più granulare.

Combinazione di COUNT e GROUP BY

La combinazione della funzione COUNT e della clausola GROUP BY è uno strumento molto potente per l’aggregazione dei dati in SQL. Spiegheremo come aggregare i dati per gruppi specifici utilizzando GROUP BY.

Sintassi di base

SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
ORDER BY COUNT(*) DESC;

Esempio di utilizzo

Ad esempio, per aggregare il numero di clienti per città dalla tabella dei clienti (customers), il seguente query può essere utilizzato.

SELECT city, COUNT(*)
FROM customers
GROUP BY city
ORDER BY COUNT(*) DESC;

Questa query aggrega il numero di clienti per ogni città nella tabella customers, mostrando i risultati ordinati in ordine decrescente in base al numero di clienti.

Utilizzo di più colonne

È possibile aggregare i dati utilizzando più colonne. Ad esempio, per aggregare il numero di clienti per città e anno di registrazione.

SELECT city, EXTRACT(YEAR FROM signup_date) AS year, COUNT(*)
FROM customers
GROUP BY city, EXTRACT(YEAR FROM signup_date)
ORDER BY city, year;

Questa query aggrega il numero di clienti per ogni città e anno, basandosi sulle colonne city e signup_date della tabella customers.

Uso della clausola HAVING

Utilizzando la clausola HAVING insieme a GROUP BY, è possibile filtrare solo i gruppi che soddisfano determinati criteri. Ad esempio, per mostrare solo le città con almeno 50 clienti.

SELECT city, COUNT(*)
FROM customers
GROUP BY city
HAVING COUNT(*) >= 50
ORDER BY COUNT(*) DESC;

Questa query mostra solo le città con almeno 50 clienti, raggruppando i dati per la colonna city. La clausola HAVING permette di impostare condizioni sui risultati dell’aggregazione.

La combinazione di COUNT e GROUP BY consente di aggregare e analizzare efficacemente le informazioni presenti nel database.

Query di esempio con dati reali

Presentiamo alcune query di esempio utilizzando dati reali per dimostrare l’uso pratico di COUNT e GROUP BY. Utilizzeremo un database fittizio di ordini per fornire esempi concreti.

Struttura della tabella degli ordini

Iniziamo esaminando la struttura della tabella degli ordini (orders). Questa tabella contiene le seguenti colonne:

• order_id (ID dell’ordine)
• customer_id (ID del cliente)
• order_date (Data dell’ordine)
• product_category (Categoria del prodotto)
• amount (Importo dell’ordine)

Dati di esempio

CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    order_date DATE,
    product_category VARCHAR(50),
    amount DECIMAL(10, 2)
);

INSERT INTO orders (order_id, customer_id, order_date, product_category, amount) VALUES
(1, 101, '2023-01-15', 'Electronics', 299.99),
(2, 102, '2023-02-20', 'Books', 19.99),
(3, 103, '2023-01-22', 'Electronics', 149.99),
(4, 104, '2023-03-15', 'Clothing', 79.99),
(5, 105, '2023-03-30', 'Books', 9.99);

Query per aggregare il numero di ordini mensili

La seguente query aggrega il numero di ordini mensili dalla tabella degli ordini.

SELECT EXTRACT(YEAR FROM order_date) AS year, EXTRACT(MONTH FROM order_date) AS month, COUNT(*)
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
ORDER BY year, month;

Risultati della query

Query per aggregare l’importo totale degli ordini annuali

La seguente query aggrega l’importo totale degli ordini per anno.

SELECT EXTRACT(YEAR FROM order_date) AS year, SUM(amount) AS total_amount
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date)
ORDER BY year;

Risultati della query

Query per aggregare il numero di ordini per categoria di prodotto

Per aggregare il numero di ordini per categoria di prodotto, si può utilizzare la seguente query.

SELECT product_category, COUNT(*)
FROM orders
GROUP BY product_category
ORDER BY COUNT(*) DESC;

Risultati della query

Questi esempi possono servire come riferimento per eseguire aggregazioni efficaci sui dati reali.

Esempi avanzati: aggregazione con più condizioni

Combinando più condizioni, è possibile ottenere analisi dei dati più dettagliate. Presentiamo alcuni esempi avanzati di aggregazione con più condizioni.

Query per aggregare il numero di ordini per categoria di prodotto e mese

La seguente query aggrega il numero di ordini mensili per categoria di prodotto.

SELECT product_category, EXTRACT(YEAR FROM order_date) AS year, EXTRACT(MONTH FROM order_date) AS month, COUNT(*)
FROM orders
GROUP BY product_category, EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
ORDER BY product_category, year, month;

Risultati della query

Query per aggregare l’importo totale degli ordini annuali per cliente

La seguente query aggrega l’importo totale degli ordini annuali per ogni cliente.

SELECT customer_id, EXTRACT(YEAR FROM order_date) AS year, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id, EXTRACT(YEAR FROM order_date)
ORDER BY customer_id, year;

Risultati della query

Query per filtrare l’aggregazione in base a condizioni specifiche

Ad esempio, per aggregare solo gli ordini con un importo minimo di 100 dollari per mese, si può utilizzare la seguente query.

SELECT EXTRACT(YEAR FROM order_date) AS year, EXTRACT(MONTH FROM order_date) AS month, COUNT(*)
FROM orders
WHERE amount >= 100
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
ORDER BY year, month;

Risultati della query

Combinazione di diversi metodi di aggregazione

Infine, ecco un esempio di combinazione di diversi metodi di aggregazione. La seguente query aggrega il numero di clienti per città e la media dell’importo degli ordini per ciascuna città.

SELECT city, COUNT(customer_id) AS customer_count, AVG(amount) AS average_order_amount
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY city
ORDER BY customer_count DESC;

Risultati della query

Attraverso questi esempi avanzati, è possibile imparare ad eseguire aggregazioni più complesse per un’analisi dettagliata e multi-prospettica dei dati.

Esercizi

Per approfondire la comprensione, provate a risolvere i seguenti esercizi. Questi esercizi mirano a migliorare le competenze pratiche nell’aggregazione dei dati utilizzando la funzione COUNT e la clausola GROUP BY in SQL.

Esercizio 1: Aggregare il numero di clienti per anno

La tabella dei clienti (customers) contiene il customer_id e la signup_date. Create una query per aggregare il numero di nuovi clienti per ogni anno.

-- Soluzione esempio
SELECT EXTRACT(YEAR FROM signup_date) AS year, COUNT(*)
FROM customers
GROUP BY EXTRACT(YEAR FROM signup_date)
ORDER BY year;

Esercizio 2: Aggregare l’importo totale degli ordini per categoria di prodotto

La tabella degli ordini (orders) contiene le colonne product_category e amount. Create una query per aggregare l’importo totale degli ordini per ogni categoria di prodotto.

-- Soluzione esempio
SELECT product_category, SUM(amount) AS total_amount
FROM orders
GROUP BY product_category
ORDER BY total_amount DESC;

Esercizio 3: Aggregare il numero di ordini per mese in un anno specifico

Creare una query per aggregare il numero di ordini per ciascun mese del 2023 dalla tabella degli ordini (orders).

-- Soluzione esempio
SELECT EXTRACT(MONTH FROM order_date) AS month, COUNT(*)
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2023
GROUP BY EXTRACT(MONTH FROM order_date)
ORDER BY month;

Esercizio 4: Aggregare l’importo totale degli ordini annuali per cliente

Unite la tabella dei clienti (customers) e la tabella degli ordini (orders) per creare una query che aggrega l’importo totale degli ordini annuali per cliente.

-- Soluzione esempio
SELECT customers.customer_id, EXTRACT(YEAR FROM orders.order_date) AS year, SUM(orders.amount) AS total_amount
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.customer_id, EXTRACT(YEAR FROM orders.order_date)
ORDER BY customers.customer_id, year;

Esercizio 5: Mostrare solo le categorie di prodotto con almeno 50 ordini

Creare una query per mostrare solo le categorie di prodotto con almeno 50 ordini dalla tabella degli ordini (orders).

-- Soluzione esempio
SELECT product_category, COUNT(*) AS order_count
FROM orders
GROUP BY product_category
HAVING COUNT(*) >= 50
ORDER BY order_count DESC;

Questi esercizi aiutano a migliorare le competenze pratiche nell’uso di COUNT e GROUP BY per l’aggregazione dei dati. Provate a risolverli e controllate se le vostre query sono corrette.

Conclusione

In questo articolo, abbiamo spiegato in dettaglio come utilizzare la funzione COUNT di SQL per aggregare i dati mensili e annuali. Combinando COUNT con GROUP BY, è possibile aggregare e analizzare efficacemente i dati del database. Abbiamo trattato dalle basi fino agli esempi avanzati, includendo query di esempio e esercizi pratici. Utilizzate queste conoscenze per migliorare le vostre competenze nell’intelligence aziendale e nella creazione di report.