Mode Blog (Italiano)

I timestamp sono cruciali per l’analisi aziendale per un motivo molto semplice: ti dicono quando le cose accadono. Immagina di cercare di scoprire le tendenze nei tuoi dati, come il traffico Web mensile, i guadagni trimestrali o il volume degli ordini giornalieri senza sapere quando si sono verificati eventi. Sarebbe un incubo.

PostgreSQL offre una varietà di funzioni di data per manipolare i timestamp. Per separare l’utile dall’oscuro, stiamo condividendo gli how-to per le funzioni di data Postgres più utilizzate e gli scenari di business in cui sono utili.,

Il più frequentemente utilizzato Postgres funzioni di data e gli scenari di business in cui sono venuto a portata di mano:

• Arrotondamento timestamp con DATE_TRUNC
• Trovare eventi relativi al tempo presente, con NOW() e CURRENT_DATE
• Isolare ora del giorno e giorno della settimana con ESTRATTO
• Calcolo del tempo trascorso con ETÀ

Abbiamo fatto i dati per ciascun esempio è disponibile in Modalità di deposito Pubblico. Prova ogni funzione data in modalità come si lavora il vostro senso attraverso questi esempi. Registrati per un account in Modalità Studio e aprire un nuovo rapporto per iniziare.,

Arrotondamento dei timestamp con DATE_TRUNC

La funzioneDATE_TRUNC arrotonda un valore di timestamp a un intervallo specificato, che consente di contare gli eventi. È possibile arrotondare un timestamp per le seguenti unità di tempo:

• microsecondo
• millisecondo
• secondo
• minuti
• ore
• giorno
• settimana
• mese
• quarto
• anno
• decennio
• secolo
• millenium

DATE_TRUNC sintassi simile a questo: DATE_TRUNC('interval',timestamp).,

Ad esempio,SELECT DATE_TRUNC('day','2015-04-12 14:44:18')restituirebbe un risultato di2015-04-12 00:00:00.

Per una spiegazione più dettagliata di DATE_TRUNC (e un riferimento stampabile che puoi tenere alla tua scrivania!), dai un’occhiata a questo post.

Come è cambiato il traffico web nel tempo?

Prova DATE_TRUNC per te interrogando la tabella modeanalytics.web_events, che contiene record di esempio di visite al sito web, inclusa una colonnaoccurred_at. È possibile isolare il mese della visita con DATE_TRUNC.,

Per restituire un conteggio delle visite web ogni mese per canale, aggiungere la colonna channel e un’istruzione COUNT all’istruzione SELECT, quindi raggruppare per month e channel. (Poiché month e channel sono i primi due valori nell’istruzioneSELECT, puoiGROUP BY 1,2), in questo modo:

Infine, usaORDER BY 1,2 per organizzare i tuoi risultati in ordine cronologico (per mese) e alfabetico (per canale).,

In Modalità, è possibile creare un grafico a linee per visualizzare i risultati della query.

Modalità Analytics

Ricerca di eventi relativi al tempo presente con NOW() e CURRENT_DATE

La funzione dataNOW() restituisce il timestamp corrente in UTC (se il fuso orario non è specificato). È possibile sottrarre gli intervalli da NOW() per estrarre gli eventi accaduti nell’ultima ora, nell’ultimo giorno, nell’ultima settimana, ecc.

L’esecuzione diSELECT NOW() alle 9:00 UTC dell ‘ 11 ottobre 2016 comporterebbe2016-10-11 09:00:00.,

La funzione CURRENT_DATE restituisce solo la data corrente, non l’intero timestamp. L’esecuzione diSELECT CURRENT_DATE alle 9:00 UTC dell ‘ 11 ottobre 2016 restituirebbe2016-10-11.

Quali ordini sono stati effettuati nelle ultime 12 ore?

La tabella demo.ordini contiene record di esempio di tutti gli ordini, tra cui una colonnaoccurred_at timestamp in UTC.,

Per trovare gli ordini effettuati nelle ultime 12 ore, utilizzare una clausola WHERE per restituire solo gli ordini che sono stati inseriti dopo o esattamente (>=) il timestamp corrente (NOW()) meno un intervallo di 12 ore.,

SELECT * FROM demo.orders WHERE occurred_at >= NOW() - interval '12 hour'

Modalità di Analytics

In aggiunta a hour, è possibile utilizzare uno dei seguenti intervalli:

• microsecondi
• millisecondi
• secondo
• minuti
• ore
• giorno
• settimana
• mese
• anno
• decennio
• secolo.
• millennium

È anche possibile combinare diversi intervalli nella stessa espressione come questa:
interval '4 hours 3 minutes'

Quello che gli ordini sono stati collocati ieri?,

È possibile utilizzare la stessa tabella per trovare gli ordini di ieri combinando le funzioni DATE_TRUNCe CURRENT_DATE.

Inizia usando una funzioneDATE_TRUNC per arrotondare i valorioccurred_at per giorno (dal momento che vogliamo sapere se qualcosa è successo ieri). Quindi utilizzare una clausolaWHERE per restituire solo valori in cui iloccurred_at giorno è uguale alla data corrente (utilizzando la funzioneCURRENT_DATE) meno un intervallo di un giorno.,

SELECT * FROM demo.orders WHERE DATE_TRUNC('day',occurred_at) = CURRENT_DATE - interval '1 day'

Analisi della modalità

Isolamento dell’ora del giorno e del giorno della settimana con ESTRATTO

La funzione data EXTRACT consente di isolare i sottocampi come l’anno o l’ora dai timestamp.

Ecco la sintassi: EXTRACT(subfield FROM timestamp). L’esecuzione di EXTRACT(month FROM '2015-02-12') restituirebbe un risultato di 2.

Tieni presente che mentre l’esempio seguente si concentra sull’ora del sottocampo (ora del giorno), hai molti altri sottocampi a tua disposizione che vanno damillennium amicrosecond., È possibile controllare l’elenco completo dei sottocampi disponibili qui.

Quanti ordini vengono effettuati ogni ora del giorno?

Una società che gestisce un centro di evasione degli ordini potrebbe voler assumere più dipendenti quando arriva la maggior parte degli ordini. Per capire quando gli ordini vengono effettuati durante il giorno, è possibile utilizzare la funzione EXTRACT e il sottocampo hour per isolare l’ora del giorno (da 0 a 23) in cui si è verificato un ordine.,

SELECT EXTRACT(hour from occurred_at) AS hour FROM demo.orders

Utilizzare il COUNTfunzione per tally ordini, e poi GROUP BY ora. (Poiché hour è il primo valore nell’istruzioneSELECT, puoiGROUP BY 1).

SELECT EXTRACT(hour from occurred_at) AS hour, COUNT(*) AS orders FROM demo.orders GROUP BY 1

Infine, per organizzare i risultati in sequenza, utilizzare ORDER BY 1.

SELECT EXTRACT(hour from occurred_at) AS hour, COUNT(*) AS orders FROM demo.orders GROUP BY 1 ORDER BY 1

Ed ecco i risultati! Sembra che potrebbe aiutare ad avere alcuni lavoratori in più sull’orologio la mattina presto e intorno all’ora di pranzo.,

Mode Analytics

Qual è il volume medio degli ordini nei giorni feriali?

Per determinare il volume medio degli ordini che si sono verificati per giorno della settimana, utilizzareEXTRACTe il sottocampodow per isolare il giorno della settimana (da 0 a 6, dove 0 è domenica) in cui si è verificato un ordine.

SELECT EXTRACT(dow from occurred_at) AS dow FROM demo.orders

Quindi, arrotondare i timestamp dell’ordine per giorno con DATE_TRUNC., Prendendo unCOUNT di ordini raggruppati perdow eday restituirà il numero di ordini effettuati ogni giorno insieme al corrispondente giorno della settimana.

SELECT EXTRACT(dow from occurred_at) AS dow, DATE_TRUNC('day',occurred_at) AS day, COUNT(id) AS orders FROM demo.orders GROUP BY 1,2

Per trovare il volume medio degli ordini nei giorni feriali, utilizzare la query precedente come sottoquery (aliasa). Prendi la media degli ordini (usando la funzioneAVG()), quindi usa una clausolaWHERE per filtrare il sabato e la domenica.

I grafici di grandi numeri sono ottimi per la visualizzazione di metriche aggregate., Per tenere d’occhio il volume degli ordini, raccogli metriche come questa in un’unica dashboard.

Modalità Analytics

Calcolo del tempo trascorso con l’ETÀ

La funzione dataAGE calcola quanto tempo fa si è verificato un evento. La sintassi è piuttosto semplice: applica AGE() a un singolo timestamp e la tua query restituirà la quantità di tempo trascorso da quell’evento. L’esecuzione di SELECT AGE( '2010-01-01' )il 1 ° gennaio 2011 restituirebbe un risultato di1 years 0 months 0 days.,

AGE() può anche determinare quanto tempo è passato tra due eventi. Invece di inserire un singolo timestamp all’interno delle parentesi, inserire entrambi i timestamp (a partire dal timestamp più recente) e separarli con una virgola. L’esecuzione di SELECT AGE( '2012-12-01',‘2010-01-01’) restituirebbe 2 years 11 months 0 days.

Si noti che questa applicazione della funzioneAGE equivale a sottrarre i timestamp:SELECT '2012-12-01' - '2010-01-01'.

Quanti anni ha un account cliente?,

Supponiamo che il tuo team di vendita desideri personalizzare i saluti in base a quanto tempo il cliente ha utilizzato il tuo prodotto. È possibile trovare quanto tempo è trascorso dalla creazione dell’account utilizzando la funzioneAGE.

La tabella modeanalytics.customer_accounts contiene record di account cliente di esempio. Selezionare la colonna dei nomi account (name) e applicare la funzioneAGE() alla colonna dei timestamp che mostrano quando ogni account è stato creato (created).,

SELECT name, AGE(created) AS account_age FROM modeanalytics.customer_accounts

Mode Analytics

Quanto tempo ci vuole in media ogni mese per completare il proprio profilo?

La tabella modeanalytics.profilecreationevents contiene dati di esempio degli utenti che hanno creato un profilo, inclusi i timestamp di inizio e fine.

Per trovare il tempo medio per completare un profilo ogni mese, inizia trovando il tempo impiegato da ciascun utente per completare un profilo e il mese in cui è stato avviato il processo di creazione del profilo. Innanzitutto, arrotondare il timestampstarted_at per mese, utilizzando la funzioneDATE_TRUNC., Quindi, trovare il tempo trascorso da started_at a ended_at per ogni profilo utilizzando la funzione AGE.

Trova la media per ogni mese applicando la funzioneAVG al valore del tempo trascorso (l’istruzioneAGE) e raggruppando per mese.

Per restituire valori in un’unità coerente per la creazione di grafici, applicare la funzione EXTRACT e il sottocampo epoch ai valori per restituire i risultati come conteggio di secondi.

Mode Analytics

Articoli consigliati

• Come padroneggiare gli Anti join e applicarli ai problemi aziendali
• Pensare in SQL vs pensare in Python
• DATE_TRUNC: una funzione di timestamp SQL su cui puoi contare