Come raggruppare i dati per settimana in SQL Server

Questo è un contenuto aggiuntivo per il corso LearnSQL.it Analisi del comportamento dei clienti in SQL Server.

In questo corso abbiamo mostrato come analizzare il ciclo di vita dei clienti (acquisizione, conversione, attività, fidelizzazione e abbandono) con SQL. Abbiamo parlato delle coorti di registrazione dei clienti, ovvero dei gruppi di clienti che si sono registrati nello stesso periodo (ad esempio, la stessa settimana o lo stesso mese). L'analisi delle coorti di registrazione dei clienti consente di vedere le tendenze di registrazione e di mettere in relazione le coorti di registrazione con le campagne di marketing.

In pratica, le coorti di registrazione più convenienti sono quelle settimanali. Le coorti di registrazione annuali, trimestrali o mensili sono troppo imprecise per un'analisi significativa. D'altra parte, le coorti di registrazione giornaliere o orarie sono troppo specifiche. Le coorti di registrazione settimanali sono di solito giuste.

In questo articolo esamineremo i diversi modi per raggruppare i dati per settimana in SQL Server.

Come si raggruppano i dati per settimana in SQL Server?

SQL Server offre una funzione chiamata DATEPART(), che restituisce una parte specifica(anno, trimestre, mese, settimana, ora, minuto, ecc.) di una data specifica.

Per raggruppare i clienti registrati nel 2018 in base alla settimana, è possibile utilizzare questa query:

SELECT
  DATEPART(week, RegistrationDate) AS Week,
  COUNT(CustomerID) AS Registrations
FROM Customers
WHERE '20180101' <= RegistrationDate
  AND RegistrationDate < '20190101'
GROUP BY DATEPART(week, RegistrationDate)
ORDER BY DATEPART(week, RegistrationDate);

Come si può vedere, la funzione DATEPART() richiede due argomenti: datepart (cioè l'identificatore della parte desiderata) e la data da cui estrarre la parte.

La funzione DATEPART() ha due argomenti datepart che restituiscono i dati della settimana:

• week (abbreviato anche wk, ww).
• iso_week (abbreviato anche isowk, isoww).

Tra poco spiegheremo le differenze tra questi due tipi. Ma prima dobbiamo parlare di un'altra impostazione.

L'impostazione DATEFIRST

L'impostazione DATEFIRST indica a SQL Server quale giorno della settimana considerare come primo giorno della settimana. DATEFIRST può essere uno dei seguenti valori:

L'impostazione DATEFIRST dipende dalla versione linguistica di SQL Server. Il valore predefinito per l'inglese americano è 7 (cioè domenica).

È possibile modificare il valore di DATEFIRST in questo modo:

SET DATEFIRST 1

DATEFIRST è un'impostazione di sessione, il che significa che è possibile modificarla senza influenzare gli altri utenti.

È possibile trovare il valore corrente dell'impostazione DATEFIRST con la funzione @@DATEFIRST:

SELECT @@DATEFIRST;

Risultato:

7

Il primo giorno della settimana è il 7, cioè la domenica.

Utilizzo di DATEPART() con la settimana

Ora che conosciamo l'impostazione DATEFIRST, vediamo come funziona DATEPART() con settimana.

Il comportamento di DATEPART() con settimana dipende dal giorno che DATEFIRST ha impostato come primo giorno della settimana. Le settimane di ogni anno sono numerate separatamente. La settimana 1 è quella che contiene (ma non necessariamente inizia con) il 1° gennaio.

Vediamo un esempio. L'immagine mostra tre diversi calendari per il mese di gennaio 2019; ognuno di essi indica un giorno della settimana diverso come primo giorno della settimana:

• Il primo calendario indica la domenica come primo giorno della settimana, come si usa di solito negli Stati Uniti.
• Il secondo calendario indica il lunedì come primo giorno della settimana, come tipicamente utilizzato in Europa.
• Il terzo calendario usa il giovedì come primo giorno della settimana. (Solo come esempio di settimana non tipica).

DATEPART() con la settimana utilizza la numerazione settimanale mostrata nell'immagine:

• Se la domenica è il primo giorno della settimana (DATEFIRST = 7), la settimana 2 inizia domenica 6 gennaio e termina sabato 12 gennaio.
• Se il lunedì è il primo giorno della settimana (DATEFIRST = 1), la Settimana 2 inizia lunedì 7 gennaio e termina domenica 13 gennaio.
• Se il giovedì è il primo giorno della settimana (DATEFIRST = 4), la seconda settimana inizia giovedì 3 gennaio e termina mercoledì 9 gennaio.

Un uso tipico di DATEPART() con settimana è quello di raggruppare i dati per settimana tramite la clausola GROUP BY. Si usa anche nella clausola SELECT per visualizzare il numero della settimana. Date un'occhiata alla query qui sotto e al suo risultato:

SELECT
  DATEPART(week, RegistrationDate) AS Week,
  COUNT(CustomerID) AS Registrations
FROM Customers
WHERE '20180101' <= RegistrationDate
  AND RegistrationDate < '20190101'
GROUP BY DATEPART(week, RegistrationDate)
ORDER BY DATEPART(week, RegistrationDate);

Se i numeri della settimana sono illeggibili, consultare l'articolo Come ottenere il primo giorno della settimana.

Si noti che per DATEPART() con settimana, la settimana in cui finisce l'anno e inizia quello successivo è spesso divisa. In altre parole, gli ultimi giorni di dicembre sono collocati nella settimana 52/53 dell'anno precedente, mentre i primi giorni di gennaio sono nella settimana 1 del nuovo anno.

L'uso di DATEPART() con iso_week

DATEPART() ha un altro argomento datepart per le settimane: iso_week. Questo argomento gestisce le settimane secondo lo standard ISO 8601, che è uno standard internazionale per lo scambio di dati di data e ora.

Nello standard ISO 8601, le settimane iniziano il lunedì. La settimana 1 di un anno è la settimana in cui si verifica il primo giovedì di quell'anno. Questo stile di numerazione delle settimane è tipicamente utilizzato nei Paesi europei.

Ad esempio, la settimana 1 del 2017 va da lunedì 2 gennaio a domenica 8 gennaio. È importante notare che se il 1° gennaio cade di venerdì, sabato o domenica, viene considerato come parte della settimana 52/53 dell'anno precedente.

Ecco il calendario di gennaio 2017:

Ancora una volta, di solito si usa DATEPART() con iso_week in GROUP BY e SELECT:

SELECT
  DATEPART(iso_week, RegistrationDate) AS Week,
  COUNT(CustomerID) AS Registrations
FROM Customers
WHERE '20180101' <= RegistrationDate
  AND RegistrationDate < '20190101'
GROUP BY DATEPART(iso_week, RegistrationDate)
ORDER BY DATEPART(iso_week, RegistrationDate);

Se i numeri delle settimane non sono leggibili, consultare l'articolo su Come ottenere il primo giorno della settimana.