časová razítka jsou pro obchodní analýzu klíčová z velmi jednoduchého důvodu: říkají vám, kdy se věci dějí. Představte si, že se snažíte zjistit trendy ve vašich datech, jako je měsíční webový provoz nebo čtvrtletní výdělky nebo denní objem objednávek, aniž byste věděli, kdy k událostem došlo. Byla by to noční můra.
PostgreSQL nabízí řadu datových funkcí pro manipulaci s časovými razítky. Chcete-li oddělit užitečné od obskurních, sdílíme návody pro nejčastěji používané funkce Data Postgres a obchodní scénáře, kde se hodí.,
nejčastěji Se používá Postgres datum, funkce a obchodní scénáře, kde přijdou vhod:
- Zaokrouhlování časových razítek s DATE_TRUNC
- Vyhledání událostí vzhledem k současné době s NOW() a CURRENT_DATE
- Izolace hodinu-den-týden s EXTRACT
- Výpočet uplynulého času s VĚKEM
Jsme udělali data pro každý příklad je k dispozici v Režimu Veřejného Skladu. Vyzkoušejte každou funkci data v režimu, jak budete pracovat si cestu přes tyto příklady. Zaregistrujte se k účtu v Mode Studio a otevřete novou zprávu.,
Zaokrouhlování časových razítek s DATE_TRUNC
DATE_TRUNC
funkce zaokrouhlí hodnota timestamp na zadaném intervalu, což umožňuje počítat události. Můžete zaokrouhlit časové razítko tyto jednotky času:
- mikrosekundu
- milisekundu
- druhá
- minut
- hodiny
- den
- týden
- měsíc
- čtvrtletí
- rok
- deset let
- . století
- millenium
DATE_TRUNC
syntaxe vypadá takto: DATE_TRUNC('interval',timestamp)
.,
například, SELECT DATE_TRUNC('day','2015-04-12 14:44:18')
vrátí výsledek 2015-04-12 00:00:00
.
pro podrobnější vysvětlení DATE_TRUNC
(a tisknutelný odkaz, který můžete mít u svého stolu!), podívejte se na tento příspěvek.
jak se v průběhu času změnil webový provoz?
zkuste DATE_TRUNC
pro sebe dotazováním na tabulku modeanalytics.web_events, který obsahuje ukázkové záznamy návštěv webových stránek, včetně sloupceoccurred_at
. Měsíc návštěvy můžete izolovat pomocí DATE_TRUNC
.,
vrátit počítat web navštíví každý měsíc podle kanálu, přidáme channel
sloupec a COUNT
SELECT
prohlášení, pak skupiny pomocí month
channel
. (Vzhledem k tomu, měsíc a kanál jsou první dvě hodnoty v SELECT
prohlášení, můžete GROUP BY 1,2
), jako je tohle:
Konečně, použijte ORDER BY 1,2
organizovat své výsledky chronologicky (podle měsíců) a abecedně (podle kanálu).,
V režimu můžete vytvořit linkový graf pro vizualizaci výsledků dotazu.
Režim Analytics
Hledání událostí vzhledem k současné době s NOW() a CURRENT_DATE
NOW()
funkce date vrací aktuální časové razítko v UTC (v případě, že časové pásmo je nespecifikovaná). Intervaly můžete odečíst od NOW()
a vytáhnout události, které se staly během poslední hodiny, posledního dne, posledního týdne atd.
Running SELECT NOW()
at 9: 00am UTC on October 11th, 2016 would result in 2016-10-11 09:00:00
.,
funkceCURRENT_DATE
vrací pouze aktuální datum, nikoli celé časové razítko. Běh SELECT CURRENT_DATE
v 9:00am UTC 11. října 2016 by se vrátil 2016-10-11
.
jaké objednávky byly zadány za posledních 12 hodin?
tabulka demo.objednávky obsahují ukázkové záznamy všech objednávek ,včetně sloupceoccurred_at
timestamp v UTC.,
najít objednávek za posledních 12 hodin, použijte WHERE
klauzule vrátí pouze objednávky, které byly umístěny po nebo přesně v (>=
) aktuální časové razítko (NOW()
) minus intervalu 12 hodin.,
SELECT * FROM demo.orders WHERE occurred_at >= NOW() - interval '12 hour'
Režim Analytics
kromě toho, aby hour
, můžete použít některý z následujících intervalech:
- mikrosekund
- ms
- druhá
- minut
- hodiny
- den
- týden
- měsíc
- rok
- deset let
- . století
- tisíciletí
můžete také kombinovat různé intervaly, ve stejný výraz, jako je tento:interval '4 hours 3 minutes'
Jaké rozkazy byly umístěny včera?,
můžete použít stejné tabulky najít včerejší objednávky kombinací DATE_TRUNC
CURRENT_DATE
funkce.
Začněte tím, že pomocí DATE_TRUNC
funkce zaokrouhlit occurred_at
hodnoty den (protože chceme vědět, jestli se něco stalo včera). Pak použijte WHERE
klauzule vrátí hodnoty jen tam, kde occurred_at
den se rovná aktuální datum (pomocí CURRENT_DATE
funkce) minus intervalu jednoho dne.,
SELECT * FROM demo.orders WHERE DATE_TRUNC('day',occurred_at) = CURRENT_DATE - interval '1 day'
Režim Analytics
Izolace hodinu-den-týden s EXTRACT
EXTRACT
datum funkce umožňuje izolovat podpole jako rok nebo hodinu z časové značky.
zde je syntaxe: EXTRACT(subfield FROM timestamp)
. Spuštění EXTRACT(month FROM '2015-02-12')
by vrátilo výsledek 2
.
Mějte na paměti, že zatímco níže uvedený příklad se zaměřuje na podpole hodinu (hodina-den), máte mnoho dalších podpolí k dispozici v rozmezí od millennium
microsecond
., Kompletní seznam dostupných polí si můžete prohlédnout zde.
kolik objednávek je umístěno každou hodinu dne?
společnost provozující plnící centrum by mohla chtít zaměstnat více zaměstnanců, když přijde většina objednávek. Zjistit, kdy jsou objednávky po celý den, můžete použít EXTRACT
funkce hour
podpole izolovat hodinu dne (od 0 do 23) v kterém pořadí došlo.,
SELECT EXTRACT(hour from occurred_at) AS hour FROM demo.orders
Použití COUNT
funkce pro záznam objednávek, a pak GROUP BY
hodiny. (Vzhledem k tomu, hodina je první hodnota v SELECT
prohlášení, můžete GROUP BY 1
).
SELECT EXTRACT(hour from occurred_at) AS hour, COUNT(*) AS orders FROM demo.orders GROUP BY 1
nakonec Uspořádejte své výsledky postupně pomocí ORDER BY 1
.
SELECT EXTRACT(hour from occurred_at) AS hour, COUNT(*) AS orders FROM demo.orders GROUP BY 1 ORDER BY 1
a zde jsou výsledky! Vypadá to, že by to mohlo pomoci mít nějaké další pracovníky na hodiny brzy ráno a kolem oběda.,
Mode Analytics
jaký je průměrný objem objednávek v týdnu?
určit průměrný objem zakázek, které se vyskytly ve všední den, použít EXTRACT
dow
podpole izolovat den v týdnu (0-6, kde 0 je neděle), v němž objednávky došlo.
SELECT EXTRACT(dow from occurred_at) AS dow FROM demo.orders
dále zaokrouhlete denní časová razítka pomocí DATE_TRUNC
., Přičemž COUNT
objednávky seskupené podle dow
day
vrátí počet objednávek každý den, spolu s odpovídající den v týdnu.
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
průměrný všední den, aby hlasitost, použijte tlačítko předchozí dotaz jako poddotaz (aliasem jako a
). Vezměte průměr objednávek (pomocí funkce AVG()
) a poté pomocí klauzuleWHERE
odfiltrujte soboty a neděle.
grafy velkých čísel jsou skvělé pro zobrazení agregovaných metrik., Chcete-li sledovat hlasitost objednávky, shromážděte metriky, jako je tato, do jednoho řídicího panelu.
Mode Analytics
výpočet času uplynulého s věkem
AGE
date funkce počítá, jak dávno došlo k události. Syntaxe je velmi jednoduchá: naneste AGE()
do jediné časové razítko, a váš dotaz vrátí doba od té události došlo. Spuštění SELECT AGE( '2010-01-01' )
1. ledna 2011 by vrátilo výsledek 1 years 0 months 0 days
.,
AGE()
může také určit, kolik času uplynulo mezi dvěma událostmi. Namísto vložení jediného časového razítka do závorek vložte obě časová razítka (počínaje posledním časovým razítkem) a oddělte je čárkou. Běh SELECT AGE( '2012-12-01',
‚2010-01-01‘)
vrátí 2 years 11 months 0 days
.
Všimněte si, že tato aplikace funkce AGE
je ekvivalentní odečtení časových značek: SELECT '2012-12-01' - '2010-01-01'
.
jak starý je zákaznický účet?,
Předpokládejme, že váš prodejní tým chce přizpůsobit pozdravy na základě toho, jak dlouho zákazník používá váš produkt. Kolik času uplynulo od vytvoření účtu pomocí funkce AGE
.
tabulka modeanalytics.customer_accounts obsahuje záznamy o vzorových zákaznických účtech. Vyberte sloupec názvy účtů (name
) a použít AGE()
funkce sloupce časová razítka ukazuje, když každý účet byl vytvořen (created
).,
SELECT name, AGE(created) AS account_age FROM modeanalytics.customer_accounts
Mode Analytics
jak dlouho trvá uživatelům dokončení jejich profilu každý měsíc v průměru?
tabulka modeanalytics.profilecreationevents obsahuje ukázková data uživatelů, kteří vytvořili profil, včetně časových razítek start a end.
průměrný čas na dokončení profilu každý měsíc, začít tím, že najít čas to trvalo, každý uživatel dokončit profilu, stejně jako měsíc, ve kterém vytvoření profilu byl zahájen proces. Nejprve zaokrouhlete časové razítko started_at
podle měsíce pomocí funkce DATE_TRUNC
., Dále najděte čas uplynulý z started_at
na ended_at
pro každý profil pomocí funkce AGE
.
Najděte průměru za každý měsíc použití AVG
funkce uplynulý čas hodnota (AGE
prohlášení) a seskupení měsíce.
K návratu hodnot v konzistentní jednotky pro tvorbu grafů, použít EXTRACT
funkce a epocha podpole vaše hodnoty se vrátí výsledky, jako počet sekund.
Mode Analytics
Doporučené články
- Jak Zvládnout Anti Připojí a Aplikovat Je na Obchodní Problémy
- Myšlení v SQL vs Myšlení v Pythonu
- DATE_TRUNC: SQL časové Razítko Funkce, kterou Může Počítat