Come raggruppare i dati per settimana in PostgreSQL

Avete bisogno di analizzare i dati per settimana in PostgreSQL? Ecco come raggruppare i record utilizzando le funzioni di data integrate per ottenere informazioni chiare e basate sul tempo.

Il raggruppamento dei dati per settimana è un requisito comune nei report. Viene spesso utilizzato nei report aziendali per tracciare metriche come le entrate settimanali, le iscrizioni degli utenti o il traffico di un sito web. Il raggruppamento per settimana aiuta ad attenuare le fluttuazioni giornaliere e fornisce una visione più chiara delle tendenze nel tempo. È particolarmente utile quando si confrontano le prestazioni su intervalli di tempo coerenti, come la crescita "da settimana a settimana" o l'analisi della coorte settimanale.

PostgreSQL offre strumenti potenti per lavorare con le date, ma il raggruppamento per settimana, soprattutto quando si ha bisogno di un giorno di inizio specifico o di una zona oraria, può essere difficile se non si ha familiarità con le funzioni giuste.

Questo articolo spiega come raggruppare per settimana in PostgreSQL usando date_trunc() e date_bin(), come regolare i fusi orari e come impostare un giorno di inizio settimana personalizzato come mercoledì o giovedì.

Volete acquisire competenze pratiche e solide su PostgreSQL partendo da zero? Provate la tracciaSQL dalla A alla Z in PostgreSQL su LearnSQL.it. È stato progettato per portarvi da principianti a utenti SQL esperti, con esercizi reali e una guida passo-passo.

Raggruppare per settimana con date_trunc()

La funzione date_trunc() è il modo più comune per raggruppare i dati per settimana in PostgreSQL. Tronca un timestamp o una data all'inizio di un'unità di tempo specificata, come un giorno, una settimana o un mese.

Utilizzo di base:

date_trunc('week', some_date)

Restituisce una nuova data che rappresenta l'inizio della settimana ISO, che è lunedì alle 00:00:00.

Esempio:

Supponiamo di avere una colonna created_at e di voler contare le iscrizioni per settimana:

SELECT
  date_trunc('week', created_at) AS week_start,
  COUNT(*) AS signups
FROM users
GROUP BY week_start
ORDER BY week_start;

Questa query raggruppa gli utenti in base alla settimana in cui si sono iscritti, con ogni settimana che inizia il lunedì.

Ecco come potrebbe apparire l'output di questa query:

Si noti che il risultato di date_trunc('week', some_date) include sia la data che l'ora, con l'ora impostata a 00:00:00 (mezzanotte).

Raggruppamento per settimana in un fuso orario specifico

Quando si lavora con i timestamp, soprattutto nelle applicazioni globali, la consapevolezza del fuso orario è essenziale. L'attività di un utente potrebbe avvenire la domenica sera in un fuso orario, ma apparire come lunedì mattina in UTC. Questo può influenzare il raggruppamento dei dati in settimane, portando a rapporti errati se si ignorano i fusi orari.

Per impostazione predefinita, PostgreSQL raggruppa i timestamp in base al fuso orario della sessione, che di solito viene impostato quando viene stabilita la connessione. Ciò significa che date_trunc('week', some_timestamptz_column) utilizzerà il fuso orario corrente della sessione. Se non si imposta esplicitamente il fuso orario, PostgreSQL utilizzerà quello predefinito dalla configurazione del server o del client.

Per garantire la coerenza, soprattutto nei report, si può usare la clausola AT TIME ZONE per convertire i timestamp in un fuso orario specifico prima di troncarli.

Esempio di utilizzo:

SELECT
  date_trunc('week', created_at AT TIME ZONE 'UTC') AS week_start_utc,
  COUNT(*) AS signups
FROM users
GROUP BY week_start_utc
ORDER BY week_start_utc;

Questa query tronca ogni timestamp di created_at all'inizio della settimana in UTC, assicurando che i raggruppamenti settimanali siano coerenti tra i vari fusi orari.

Se si lavora con una colonna timestamptz e si desidera raggruppare in base a un'ora locale specifica, utilizzare questa opzione:

date_trunc('week', created_at AT TIME ZONE 'America/New_York')

Questo allinea le settimane all'inizio del lunedì nell'ora locale di New York.

Giorni di inizio settimana personalizzati (ad esempio, mercoledì o giovedì)

La funzione date_trunc('week', some_date) di PostgreSQL allinea sempre le settimane all'inizio del lunedì, seguendo la definizione di settimana ISO. Questo è fisso e non può essere modificato direttamente tramite date_trunc().

Se la vostra reportistica richiede che le settimane inizino in un giorno diverso, come mercoledì o giovedì, dovrete applicare un semplice workaround: spostare la data prima del troncamento e poi spostarla di nuovo dopo.

Logica della soluzione

Per far iniziare la settimana in un giorno specifico, sottrarre il numero di giorni appropriato prima del troncamento e poi aggiungerlo di nuovo:

-- Week starting on Wednesday
date_trunc('week', some_date - interval '2 days') + interval '2 days'

In questo modo si sposta il calendario indietro di 2 giorni (quindi il lunedì diventa sabato), si tronca all'inizio della settimana modificata (sabato), quindi si sposta nuovamente in avanti a mercoledì.

Esempi di giorni di inizio settimana diversi

Usare questo schema all'interno delle clausole SELECT, GROUP BY o ORDER BY per allineare i dati alla struttura settimanale preferita.

Raggruppamento flessibile di date e ore con date_bin() (PostgreSQL 14+)

PostgreSQL 14 ha introdotto una nuova potente funzione: date_bin(). Questa funzione offre un maggiore controllo rispetto a date_trunc() per il raggruppamento temporale, soprattutto se si desidera che le settimane inizino in una data specifica o in un giorno della settimana personalizzato.

date_bin() consente di raggruppare i timestamp in periodi di tempo uguali, come settimane, giorni o ore, a partire da una data e un'ora a scelta. Questo punto di partenza è chiamato ancoraggio.

L'ancora definisce quando inizia il primo gruppo. PostgreSQL costruisce poi gli altri gruppi in base a questo, sia in avanti che indietro nel tempo. Se l'ancora è il 1° gennaio 2025 e si raggruppa per 7 giorni, PostgreSQL creerà intervalli di una settimana a partire da quella data e anche intervalli precedenti che si adattano allo stesso schema.

Sintassi

date_bin(interval, timestamp, anchor)

• interval: la lunghezza di ciascun gruppo (ad esempio '7 days')
• timestamp: la colonna che si sta raggruppando (ad esempio, created_at)
• anchor: la data e l'ora esatte a cui il raggruppamento deve allinearsi

Esempio: Raggruppamento per intervalli di 1 settimana a partire dal 1° gennaio

SELECT
  date_bin('7 days', created_at, TIMESTAMP '2025-01-01') AS custom_week,
  COUNT(*) AS signups
FROM users
GROUP BY custom_week
ORDER BY custom_week;

Questo raggruppa gli utenti in intervalli di 7 giorni a partire dal 1° gennaio 2025, indipendentemente dal giorno della settimana. È possibile utilizzare qualsiasi data di ancoraggio, ad esempio l'inizio dell'anno fiscale o il giorno di lancio del prodotto.

Usate date_bin() se volete che le vostre settimane inizino in un giorno personalizzato o in una data esatta, se avete bisogno di intervalli di tempo non basati su unità di calendario (ad esempio, ogni 10 giorni o 6 ore) o se volete avere il pieno controllo su come vengono raggruppati i dati basati sul tempo.

Se si utilizza PostgreSQL 14 o più recente, date_bin() è un'alternativa flessibile che può rendere i rapporti settimanali più accurati e in linea con le vostre esigenze specifiche.

Conclusione

Il raggruppamento per settimana in PostgreSQL può essere semplice o altamente personalizzabile, a seconda delle esigenze. È possibile utilizzare date_trunc() per le settimane ISO standard (che iniziano di lunedì) o applicare piccole modifiche per far iniziare le settimane in qualsiasi giorno. Per una flessibilità ancora maggiore, come l'impostazione di una data di ancoraggio specifica o l'uso di intervalli di tempo non standard,date_bin() (disponibile in PostgreSQL 14 e successivi) è lo strumento migliore per questo lavoro.

Capire come lavorare con le date, i fusi orari e la logica di raggruppamento è essenziale per chiunque faccia seriamente analisi di dati o report in PostgreSQL.

Se siete pronti ad affinare le vostre competenze in PostgreSQL con la pratica del mondo reale, date un'occhiata al programma SQL dalla A alla Z in PostgreSQL su LearnSQL.it. Si tratta di un percorso di apprendimento pratico e graduale che vi aiuterà a passare dalle basi alle tecniche più avanzate, come quelle trattate in questo articolo.