tidsstempler er afgørende for forretningsanalyse af en meget enkel grund: de fortæller dig, når ting sker. Forestil forsøger at suss ud tendenser i dine data, ligesom månedlige trafficebtrafik, eller kvartalsvise indtjening, eller daglig ordremængde uden at vide, hvornår begivenhederne fandt sted. Det ville være et mareridt.
Postgres .l tilbyder en række datofunktioner til manipulation af tidsstempler. For at adskille det nyttige fra det uklare deler vi vejledninger til de mest anvendte Postgres-datofunktioner og forretningsscenarier, hvor de kommer godt med.,
Det mest brugte Postgres dato, funktioner og business scenarier, hvor de kommer i handy:
- Afrunding tidsstempler med DATE_TRUNC
- finder begivenheder i forhold til den aktuelle tid med NU() og CURRENT_DATE
- Isolere time-for-dag og dag-for-uge-med-EKSTRAKT
- at Beregne tid, der er gået med ALDER
Vi har data for hvert eksempel findes i den måde, den Offentlige Lager. Prøv hver dato funktion i tilstand, som du arbejder dig vej gennem disse eksempler. Tilmeld dig en konto i tilstand Studio og åbne en ny rapport til at begynde.,
afrunding af tidsstempler med DATE_TRUNC
funktionenDATE_TRUNC
afrunder en tidsstempelværdi til et angivet interval, som giver dig mulighed for at tælle begivenheder. Du kan afrunde et tidsstempel til følgende enheder af gangen:
- mikrosekund
- millisekund
- andet
- minutter
- time
- dag
- uge
- måned
- kvartal
- år
- årti
- århundrede
- millenium
DATE_TRUNC
syntaksen ser sådan ud: DATE_TRUNC('interval',timestamp)
.,
For eksempel: SELECT DATE_TRUNC('day','2015-04-12 14:44:18')
ville returnere et resultat af 2015-04-12 00:00:00
.
For en mere detaljeret forklaring af DATE_TRUNC
(og en udskrivbar reference, du kan opbevare ved dit skrivebord!), tjek dette indlæg.
Hvordan har webebtrafik ændret sig over tid?
prøv DATE_TRUNC
for dig selv ved at forespørge tabellen modeanalytics.columneb_events, som indeholder eksempler på registreringer af websiteebstedsbesøg, herunder en occurred_at
kolonne. Du kan isolere besøgsmåneden med DATE_TRUNC
.,
for At vende tilbage en optælling af web besøg hver måned af kanal, skal du tilføje channel
kolonne og en COUNT
til SELECT
opgørelse, så gruppen af month
og channel
. (Da måned og kanal er de to første værdier i din SELECT
erklæring, kan du GROUP BY 1,2
), som dette:
Endelig, brug ORDER BY 1,2
for at organisere dine resultater i kronologisk rækkefølge (efter måned) og i alfabetisk rækkefølge (kanal).,
I tilstand kan du oprette et linjediagram for at visualisere forespørgselsresultaterne.
Mode Analytics
Find begivenheder i forhold til den aktuelle tid med NU() og CURRENT_DATE
NOW()
dato funktion returnerer den aktuelle tidsstempel i UTC (hvis den tidszone, der er angivet). Du kan trække intervaller fra NOW()
for at trække begivenheder, der skete inden for den sidste time, den sidste dag, den sidste uge osv.
løbSELECT NOW()
kl 9:00am UTC den 11.Oktober 2016 ville resultere i2016-10-11 09:00:00
.,
CURRENT_DATE
funktionen returnerer kun den aktuelle dato, ikke hele tidsstemplet. Løb SELECT CURRENT_DATE
kl 9:00am UTC den 11.Oktober 2016 ville vende tilbage 2016-10-11
.
hvilke ordrer blev placeret i de sidste 12 timer?
tabellen demo.ordrer indeholder sample records af alle ordrer, herunder enoccurred_at
tidsstempel kolonne i UTC.,
for At finde ordrer, der er afgivet inden for de sidste 12 timer, skal du anvende en WHERE
klausul til at vende tilbage kun ordrer, der blev placeret efter eller præcis på (>=
) det aktuelle tidsstempel (NOW()
) minus et interval af 12 timer.,
SELECT * FROM demo.orders WHERE occurred_at >= NOW() - interval '12 hour'
Mode Analytics
ud over at hour
, kan du bruge en af følgende intervaller:
- mikrosekunder
- millisekunder
- andet
- minutter
- time
- dag
- uge
- måned
- år
- årti
- århundrede
- millennium
Du kan også kombinere forskellige intervaller i samme udtryk som dette:interval '4 hours 3 minutes'
Hvad ordrer blev afgivet i går?,
Du kan bruge den samme tabel til at finde gårsdagens ordrer ved at kombinere DATE_TRUNC
og CURRENT_DATE
funktioner.
Start med at bruge en DATE_TRUNC
funktion til at runde din occurred_at
værdier om dagen (da vi vil vide, om der skete noget i går). Derefter bruge en WHERE
klausul om kun at returnere værdier, hvor occurred_at
dagen, er lig med den aktuelle dato (ved brug af CURRENT_DATE
funktion) med fradrag af et interval på en dag.,
SELECT * FROM demo.orders WHERE DATE_TRUNC('day',occurred_at) = CURRENT_DATE - interval '1 day'
Mode Analytics
Isolere time-for-dag og dag-for-uge-med-EKSTRAKT
EXTRACT
dato funktion giver dig mulighed for at isolere underfelter, såsom år eller time fra tidsstempler.
Her er syntaksen: EXTRACT(subfield FROM timestamp)
. Løb EXTRACT(month FROM '2015-02-12')
ville returnere et resultat af 2
.
husk, at selvom eksemplet nedenfor fokuserer på delfelt time (time-of-day), du har mange andre delfelter til din rådighed lige fra millennium
til microsecond
., Du kan tjekke den omfattende liste over tilgængelige underfelter her.
hvor mange ordrer er placeret hver time på dagen?
et firma, der driver et opfyldelsescenter, ønsker måske at bemande flere ansatte, når hovedparten af ordrer kommer ind. For at finde ud af, når ordrer er placeret over hele dagen, kan du bruge EXTRACT
funktion og hour
subfelt til at isolere time-of-day (fra 0 til 23), hvor en ordre indtraf.,
SELECT EXTRACT(hour from occurred_at) AS hour FROM demo.orders
Brug COUNT
funktion til at stemme overens ordrer, og derefter GROUP BY
time. (Da timen er den første værdi i din SELECT
erklæring, kan du GROUP BY 1
).
SELECT EXTRACT(hour from occurred_at) AS hour, COUNT(*) AS orders FROM demo.orders GROUP BY 1
endelig skal du bruge ORDER BY 1
for at organisere dine resultater i rækkefølge.
SELECT EXTRACT(hour from occurred_at) AS hour, COUNT(*) AS orders FROM demo.orders GROUP BY 1 ORDER BY 1
og her er resultaterne! Det ser ud til, at det kan hjælpe med at have nogle ekstra arbejdere på uret tidligt om morgenen og omkring frokosttid.,
Mode Analytics
Hvad er den gennemsnitlige ordremængde på hverdage?
for At bestemme den gennemsnitlige volumen af ordrer, der opstod efter ugedag, brug EXTRACT
og dow
subfelt til at isolere dag-for-uge (fra 0-6, hvor 0 er søndag), hvor en ordre indtraf.
SELECT EXTRACT(dow from occurred_at) AS dow FROM demo.orders
runde derefter ordretidsstemplerne om dagen med DATE_TRUNC
., At tage en COUNT
af ordrer, der er grupperet efter dow
og day
vil returnere antallet af ordrer hver dag sammen med den tilsvarende dag-for-uge.
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
for at finde den gennemsnitlige ordremængde på hverdage skal du bruge den forrige forespørgsel som en underforespørgsel (alias a
). Tag gennemsnittet af ordrer (ved hjælp af AVG()
funktion), og brug derefter en WHERE
klausul til at filtrere lørdage og søndage.
store Taldiagrammer er gode til visning af aggregerede målinger., For at holde øje med ordremængden skal du samle målinger som denne i et dashboard.
Mode Analytics
beregning af forløbet tid med alderen
AGE
datofunktion beregner, hvor længe siden en begivenhed opstod. Syntaksen er ret ligetil: Anvend AGE()
til en enkelt tidsstempel, og din forespørgsel returnerer den tid, siden begivenheden fandt sted. Løb SELECT AGE( '2010-01-01' )
den 1. januar 2011 ville returnere et resultat af 1 years 0 months 0 days
.,
AGE()
kan også bestemme, hvor meget tid der er gået mellem to begivenheder. I stedet for at sætte en enkelt tidsstempel inde i parenteserne, skal du indsætte begge tidsstempler (startende med den seneste tidsstempel) og adskille dem med et komma. Kører SELECT AGE( '2012-12-01',
‘2010-01-01’)
ville vende tilbage 2 years 11 months 0 days
.
Bemærk, at denne anvendelse af funktionen AGE
svarer til at trække tidsstemplerne fra: SELECT '2012-12-01' - '2010-01-01'
.
hvor gammel er en kundekonto?,
Antag, at dit salgsteam ønsker at tilpasse hilsener baseret på hvor længe kunden har brugt dit produkt. Du kan finde ud af, hvor meget tid der er gået siden kontooprettelsen ved hjælp af funktionen AGE
.
tabellen modeanalytics.customer_accounts indeholder registreringer af prøve kundekonti. Vælg kolonnen med kontonavne (name
), og anvend funktionenAGE()
til kolonnen med tidsstempler, der viser, hvornår hver konto blev oprettet (created
).,
SELECT name, AGE(created) AS account_age FROM modeanalytics.customer_accounts
Mode Analytics
hvor lang tid tager det brugere at udfylde deres profil hver måned i gennemsnit?
tabellen modeanalytics.profilecreationevents indeholder eksempler på data fra brugere, der oprettede en profil, herunder start-og sluttidsstempler.
for at finde den gennemsnitlige tid til at udfylde en profil hver måned, skal du starte med at finde den tid, det tog hver bruger at udfylde en profil såvel som den måned, hvor profiloprettelsesprocessen blev startet. Rund først started_at
tidsstempel efter måned ved hjælp af funktionen DATE_TRUNC
., Næste, finde den tid der er gået fra started_at
til ended_at
for hver profil ved hjælp af AGE
funktion.
Find gennemsnittet for hver måned, ved at anvende AVG
funktion til den forløbne tid værdi (din AGE
statement) og gruppering efter måned.
Hvis du vil returnere værdier i en ensartet enhed til kortlægning, skal du anvende underfeltet EXTRACT
funktion og epoch til dine værdier for at returnere resultater som et antal sekunder.
Mode Analytics
Anbefalede artikler
- sådan mestrer du Anti-sammenføjninger og anvender dem på forretningsproblemer
- tænkning i S andl vs tænkning i Python
- DATE_TRUNC: en S :L-Tidsstempelfunktion, du kan stole på