Vai al contenuto principale

Smanettare con le funzioni di data in SQLite

In questo tutorial, scopri come usare le funzioni di data in SQLite.
· 3 min leggi

Scopri con l'IA

Alcune delle funzioni più utili nelle implementazioni di SQL in Postgres (come Amazon Redshift) sono DATE_DIFF e DATE_TRUNC:

  • DATE_DIFF restituisce il tempo trascorso tra due date diverse. Ad esempio, il codice seguente restituisce il numero di giorni tra date1 e date2:
DATE_DIFF('day', date1, date2)

DATE_DIFF è ottima per calcolare il numero di giorni dall’iscrizione alla cancellazione o il numero di ore dall’accesso alla disconnessione.

  • DATE_TRUNC tronca una data al giorno, settimana, mese o anno più vicino. Ad esempio, il codice seguente restituisce il lunedì più vicino al timestamp my_timestamp:
DATE_TRUNC('week', my_timestamp)

DATE_TRUNC è perfetta per aggregare i dati. Per esempio, possiamo usarla per trovare il numero di Monthly Active Users (MAU) troncando alla data di inizio mese più vicina.

Ma non tutte le implementazioni di SQL hanno queste ottime funzioni. Per il nostro codice Learn SQL from Scratch, usiamo SQLite, un’implementazione leggera di SQL che può essere eseguita su una singola istanza Docker. SQLite è perfetta per backend di siti web e progetti piccoli, ma le mancano le mie due funzioni preferite. Per fortuna, ci sono dei workaround.

Per emulare DATE_DIFF, possiamo usare una funzione poco conosciuta chiamata juliandate. Secondo Wikipedia, "il Julian Day Number (JDN) è l’intero assegnato a un intero giorno solare nel conteggio dei giorni giuliani a partire da mezzogiorno del tempo universale, con il numero giuliano 0 assegnato al giorno che inizia a mezzogiorno di lunedì 1 gennaio 4713 a.C.". Convertendo una data in un numero in virgola mobile, possiamo usare la sottrazione per trovare la differenza tra due timestamp.

julian day number

Possiamo persino convertire la risposta in ore moltiplicando per 24 o in minuti moltiplicando per 24 * 60.

Possiamo emulare parte della funzionalità di DATE_TRUNC usando strftime. Questa funzione converte un timestamp in una stringa con un dato formato di output.

%d giorno del mese: 00 %f secondi frazionari: SS.SSS %H ora: 00-24 %j giorno dell’anno: 001-366 %J numero del giorno giuliano %m mese: 01-12 %M minuti: 00-59 %s secondi dal 1970-01-01 %S secondi: 00-59 %w giorno della settimana 0-6 con domenica==0 %W settimana dell’anno: 00-53 %Y anno: 0000-9999

Normalmente potremmo usarla per convertire tra diversi formati di timestamp, ad esempio da YYYY-MM-DD a MM-DD-YYYY:

strftime('%M-%D-%Y', mydate)

Ma possiamo scegliere con astuzia il formato per troncare al punto appropriato. Per esempio, se vogliamo troncare al mese, possiamo fare:

strftime('%M/%Y', mydate)

Oppure possiamo troncare alla settimana usando:

strftime('%Y-%w', mydate)

Con questi due semplici trucchi, puoi usare SQLite per alcune delle stesse ottime analisi che fai con Amazon Redshift!

Se vuoi saperne di più sulle basi di SQL, segui il corso di DataCamp Intro to SQL for Data Science e dai un’occhiata al nostro SQL Tutorial for Beginners.

Argomenti

SQL

Correlato

Mostra altro →