modeblogg

tidsstämplar är avgörande för affärsanalys av en mycket enkel anledning: de berättar när saker händer. Tänk dig att försöka suss ut trender i dina data, som månatlig webbtrafik, eller kvartalsvisa intäkter eller daglig ordervolym utan att veta när händelser inträffade. Det vore en mardröm.

PostgreSQL erbjuder en mängd olika datumfunktioner för att manipulera tidsstämplar. För att skilja det Användbara från det obskyra delar vi hur de används för de mest använda Postgres datumfunktionerna och affärsscenarierna där de är till nytta.,

de mest använda Postgres datumfunktionerna och affärsscenarierna där de är till nytta:

• avrundning av tidsstämplar med DATE_TRUNC
• hitta händelser i förhållande till nuvarande tid med nu() och CURRENT_DATE
• isolera timme-av-dag och veckodag med extrakt
• beräkning tid förflutit med ålder

Vi har gjort data för varje exempel tillgängliga i läget offentliga lager. Prova varje datumfunktion i läge när du arbetar dig igenom dessa exempel. Registrera dig för ett konto i Mode Studio och öppna en ny rapport för att börja.,

avrundning av tidsstämplar med DATE_TRUNC

funktionenDATE_TRUNC Avrundar ett tidsstämpelvärde till ett angivet intervall, vilket gör att du kan räkna händelser. Du kan avrunda en tidsstämpel till följande tidsenheter:

• mikrosekund
• millisekund
• andra
• minut
• timme
• dag
• vecka
• månad
• kvartal
• år
• decennium
• century
• Millenium

syntaxen DATE_TRUNC ser ut så här: DATE_TRUNC('interval',timestamp).,

till exempel skulle SELECT DATE_TRUNC('day','2015-04-12 14:44:18') returnera ett resultat av 2015-04-12 00:00:00.

för en mer detaljerad förklaring avDATE_TRUNC (och en utskrivbar referens som du kan hålla vid ditt skrivbord!), kolla in det här inlägget.

hur har webbtrafiken förändrats över tiden?

provaDATE_TRUNC själv genom att fråga tabellen modeanalytics.web_events, som innehåller exempelposter för webbplatsbesök, inklusive enoccurred_at – kolumn. Du kan isolera månaden för besöket med DATE_TRUNC.,

för att returnera ett antal webbbesök varje månad per kanal, Lägg tillchannel – kolumnen och enCOUNT tillSELECT – satsen, gruppera sedan medmonth ochchannel. (Eftersom månad och kanal är de två första värdena i ditt SELECT – uttalande kan du GROUP BY 1,2), så här:

använd slutligen ORDER BY 1,2 för att organisera dina resultat kronologiskt (per månad) och alfabetiskt (per kanal).,

i läge kan du bygga ett linjediagram för att visualisera frågeresultaten.

Mode Analytics

att hitta händelser i förhållande till nuvarande tid med NOW() och CURRENT_DATE

NOW() date funktionen returnerar aktuell tidsstämpel i UTC (om tidszonen är ospecificerad). Du kan subtrahera intervall från NOW() för att dra händelser som hände under den senaste timmen, den sista dagen, den sista veckan etc.

SELECT NOW()klockan 9:00 UTC den 11 oktober 2016 skulle resultera i2016-10-11 09:00:00.,

funktionenCURRENT_DATE returnerar endast aktuellt datum, inte hela tidsstämpeln. Att köra SELECT CURRENT_DATEklockan 9:00 UTC den 11 oktober skulle 2016 returnera2016-10-11.

vilka beställningar placerades under de senaste 12 timmarna?

tabellen demo.ordrar innehåller exempelposter för alla order, inklusive enoccurred_at tidsstämpelkolumn i UTC.,

för att hitta order som placerats under de senaste 12 timmarna, använd enWHERE – klausul för att returnera endast order som placerades efter eller exakt vid (>=) den aktuella tidsstämpeln (NOW()) minus ett intervall på 12 timmar.,

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

Mode Analytics

förutomhour kan du använda något av följande intervall:

• mikrosekunder
• millisekunder
• andra
• minut
• timme
• dag
• vecka
• månad
• år
• decennium
• talet
• Millennium

Du kan också kombinera olika intervall i samma uttryck så här:
interval '4 hours 3 minutes'

vilka order placerades igår?,

Du kan använda samma tabell för att hitta gårdagens order genom att kombinera funktionernaDATE_TRUNC ochCURRENT_DATE.

börja med att använda enDATE_TRUNC funktion för att runda dinaoccurred_at värden per dag (eftersom vi vill veta om något hände igår). Använd sedan en WHERE – klausul för att returnera endast värden däroccurred_at – dagen är lika med det aktuella datumet (med funktionenCURRENT_DATE) minus ett intervall på en dag.,

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

Mode Analytics

isolera timme-av-dag och dag-av-vecka med extrakt

EXTRACT datumfunktion kan du isolera delfält som år eller timme från tidsstämplar.

här är syntaxen: EXTRACT(subfield FROM timestamp). Att köra EXTRACT(month FROM '2015-02-12') skulle returnera ett resultat av 2.

tänk på att medan exemplet nedan fokuserar på delfältets timme (dagens timme), har du många andra delfält till ditt förfogande som sträcker sig från millennium till microsecond., Du kan kolla in den omfattande listan över tillgängliga delfält här.

hur många beställningar placeras varje timme på dagen?

ett företag som kör en uppfyllelse center kanske vill personal fler anställda när huvuddelen av order kommer in. För att ta reda på när beställningar placeras hela dagen kan du användaEXTRACT-funktionen ochhour-underfältet för att isolera timmen på dagen (från 0 till 23) där en order inträffade.,

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

använd funktionenCOUNT för att tally order, och sedanGROUP BY timme. (Eftersom hour är det första värdet i din SELECT – sats kan du GROUP BY 1).

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

slutligen, för att organisera dina resultat sekventiellt, användORDER BY 1.

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

och här är resultaten! Ser ut som det kan hjälpa att ha några extra arbetare på klockan tidigt på morgonen och runt lunchtid.,

Mode Analytics

vad är den genomsnittliga veckodagsordervolymen?

för att bestämma den genomsnittliga volymen av order som inträffade per vardag, användEXTRACT ochdow för att isolera veckodagen (från 0-6, där 0 är söndag) där en order inträffade.

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

nästa, runda orderstämplarna per dag medDATE_TRUNC., Att ta enCOUNT av order grupperade efterdow ochday returnerar antalet beställningar som läggs varje dag tillsammans med motsvarande veckodag.

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

för att hitta den genomsnittliga veckodagsordervolymen, använd föregående fråga som en underquery (Aliasa). Ta medelvärdet av order (med AVG() – funktionen) och använd sedan en WHERE – klausul för att filtrera bort lördagar och söndagar.

stora nummerdiagram är bra för att visa aggregerade mätvärden., För att hålla ett öga på ordervolymen, samla mätvärden som denna i en instrumentpanel.

Mode Analytics

beräkningstid som förflutit med ålder

datumfunktionen AGE beräknar hur länge sedan en händelse inträffade. Syntaxen är ganska enkel: använd AGE() till en enda tidsstämpel, och din fråga kommer att returnera tiden sedan händelsen ägde rum. Att köraSELECT AGE( '2010-01-01' ) den 1 januari 2011 skulle returnera ett resultat av1 years 0 months 0 days.,

AGE() kan också bestämma hur mycket tid som gått mellan två händelser. I stället för att sätta en enda tidsstämpel inom parentes, sätt in båda tidsstämplarna (börjar med den senaste tidsstämpeln) och separera dem med ett kommatecken. Running SELECT AGE( '2012-12-01',’2010-01-01’) skulle returnera 2 years 11 months 0 days.

Observera att denna tillämpning av funktionenAGE motsvarar subtraktion av tidsstämplarna:SELECT '2012-12-01' - '2010-01-01'.

hur gammal är ett kundkonto?,

anta att ditt säljteam vill anpassa hälsningar baserat på hur länge kunden har använt din produkt. Du kan hitta hur mycket tid som har gått sedan kontoskapandet med funktionen AGE.

tabellen modeanalytics.customer_accounts innehåller poster av exempel kundkonton. Välj kolumnen med kontonamn (name) och använd funktionenAGE() till kolumnen med tidsstämplar som visar när varje konto skapades (created).,

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

Mode Analytics

hur lång tid tar det för användare att slutföra sin profil varje månad, i genomsnitt?

tabellen modeanalytics.profilecreationevents innehåller exempeldata för användare som skapade en profil, inklusive start-och sluttidsstämplar.

för att hitta den genomsnittliga tiden för att slutföra en profil varje månad, börja med att hitta den tid det tog varje användare att slutföra en profil samt den månad då profilskapande processen startades. Först rundastarted_at tidsstämpel för månad, med funktionenDATE_TRUNC., Hitta sedan den tid som förflutit frånstarted_at tillended_at för varje profil med funktionenAGE.

hitta medelvärdet för varje månad genom att använda funktionenAVG till det tidsvärde som förflutit (dittAGE) och gruppera efter månad.

för att returnera värden i en konsekvent enhet för kartläggning, använd funktionenEXTRACT och epoch till dina värden för att returnera resultat som ett antal sekunder.

Mode Analytics

Rekommenderade artiklar

• hur man bemästrar Anti ansluter och tillämpa dem på affärsproblem
• tänkande i SQL vs tänkande i Python
• DATE_TRUNC: en SQL tidsstämpel funktion Du kan räkna med