az időbélyegek nagyon egyszerű okból elengedhetetlenek az üzleti elemzéshez: elmondják, mikor történnek a dolgok. Képzelje el, hogy megpróbálja kiszűrni az adatok trendjeit, például a havi webes forgalmat, a negyedéves bevételeket vagy a napi rendelési mennyiséget anélkül, hogy tudná, mikor történtek események. Rémálom lenne.
A PostgreSQL számos dátumfüggvényt kínál az időbélyegek manipulálásához. Külön a hasznos, a homályos, megosztjuk how-tos a leggyakrabban használt Postgres dátum funkciók, üzleti lehetőség, ahol jól jönnek.,
A leggyakrabban használt Postgres dátum funkciók, üzleti lehetőség, ahol ők jól jön:
- Kerekítés időbélyegek a DATE_TRUNC
- a Megállapítás események relatív, hogy a jelen idő a MOST() pedig CURRENT_DATE
- Leválasztó óra-a-nap mint nap-a héten KIVONAT
- Számító eltelt idő, a KOR
tettünk az adatokat az egyes példa áll rendelkezésre a Mód Nyilvános Raktár. Próbálja ki az egyes dátumfunkciókat módban, miközben ezeken a példákon keresztül dolgozik. Regisztráljon egy fiókot Mode Studio és nyisson meg egy új jelentést kezdeni.,
az időbélyegek kerekítése a DATE_TRUNC
a DATE_TRUNC
függvény egy időbélyeg értéket egy meghatározott intervallumra kerekít, amely lehetővé teszi az események számlálását. Lehet kerek egy időbélyeg, hogy a következő egység idő:
- mikroszekundum
- milliszekundum
- második
- perc
- óra
- napot
- heti
- hónap
- negyedév
- év
- évtized
- század
- millenium
A DATE_TRUNC
szintaxis néz ki: DATE_TRUNC('interval',timestamp)
.,
például SELECT DATE_TRUNC('day','2015-04-12 14:44:18')
2015-04-12 00:00:00
eredményt ad vissza.
a DATE_TRUNC
részletesebb magyarázatáért (valamint egy nyomtatható hivatkozásért, amelyet az asztalánál tarthat!), nézd meg ezt a bejegyzést.
hogyan változott a webes forgalom az idő múlásával?
próbálja ki aDATE_TRUNC
a táblázat modeanalytics lekérdezésével.web_events, amely tartalmazza a weboldal látogatásainak mintadokumentumait, beleértve a occurred_at
oszlopot. A látogatás hónapját a DATE_TRUNC
segítségével izolálhatja.,
ahhoz, hogy csatornánként havonta visszaadja a látogatások számát, adja hozzá a channel
oszlopot és a COUNT
SELECT
nyilatkozatot, majd a month
és channel
. (Mivel a hónap és a csatorna az első két érték a SELECT
nyilatkozatban, akkor GROUP BY 1,2
), mint ez:
végül használja a az eredmények időrendben (hónaponként) és betűrendben (csatornánként) rendezéséhez.,
módban vonaldiagramot készíthet a lekérdezési eredmények megjelenítéséhez.
Mode Analytics
Események keresése a jelen időhöz képest a NOW() és CURRENT_DATE
The NOW()
date függvény az aktuális időbélyeget adja vissza UTC-ben (ha az időzóna nincs megadva). A NOW()
intervallumokat kivonhatja az utolsó órában, az utolsó napon, az utolsó héten stb.
Running SELECT NOW()
at 9:00am UTC on October 11th, 2016 would result in 2016-10-11 09:00:00
.,
a CURRENT_DATE
függvény csak az aktuális dátumot adja vissza, nem a teljes időbélyeget. Futás SELECT CURRENT_DATE
9:00-kor UTC október 11-én, 2016-ban visszatérne 2016-10-11
.
milyen megrendeléseket helyeztek el az elmúlt 12 órában?
a táblázat demo.a megrendelések az összes megrendelés mintadokumentumait tartalmazzák, beleértve a occurred_at
időbélyeg oszlopot UTC-ben.,
az utolsó 12 órában elhelyezett megrendelések megkereséséhez használjon egy WHERE
záradékot, hogy csak azokat a megrendeléseket adja vissza, amelyeket a (>=
) az aktuális időbélyeg (NOW()
) mínusz 12 órás intervallum.,
SELECT * FROM demo.orders WHERE occurred_at >= NOW() - interval '12 hour'
Üzemmód Analytics
amellett, hogy a hour
, akkor használja az alábbi időközönként:
- mikroszekundum
- milliszekundum
- második
- perc
- óra
- napot
- heti
- hónap
- év
- évtized
- század
- millenniumi
Azt is kombinálni a különböző időközönként ugyanaz a kifejezés, mint ez:interval '4 hours 3 minutes'
Mi a parancsot helyezték tegnap?,
ugyanazt a táblázatot használhatja a tegnapi megrendelések megtalálásához a DATE_TRUNC
és CURRENT_DATE
funkciók kombinálásával.
Kezdje aDATE_TRUNC
függvény használatával, hogy kerekítse aoccurred_at
értékeket nappal (mivel tudni akarjuk, hogy történt-e valami tegnap). Ezután használjon egy WHERE
záradékot, hogy csak olyan értékeket adjon vissza, ahol a occurred_at
nap megegyezik az aktuális dátummal (a CURRENT_DATE
függvény használatával) mínusz egy nap intervallum.,
SELECT * FROM demo.orders WHERE DATE_TRUNC('day',occurred_at) = CURRENT_DATE - interval '1 day'
Üzemmód Analytics
Leválasztó óra-a-nap mint nap-a héten KIVONAT
A EXTRACT
dátum funkció lehetővé teszi, hogy különíteni almezők például évben vagy óra időbélyegek.
itt van a szintaxis: EXTRACT(subfield FROM timestamp)
. A EXTRACT(month FROM '2015-02-12')
futtatása a 2
eredményt adja vissza.
ne feledje, hogy míg az alábbi példa az almező órájára (a nap órájára) összpontosít, sok más alterület áll rendelkezésére, a millennium
to microsecond
., A rendelkezésre álló alterületek átfogó listáját itt tekintheti meg.
hány megrendelést helyeznek el a nap minden órájában?
előfordulhat, hogy egy teljesítési központot működtető vállalat több alkalmazottat szeretne alkalmazni, amikor a megrendelések nagy része megérkezik. Annak megállapításához, hogy a megrendelések mikor kerülnek a nap folyamán, használhatja a EXTRACT
függvényt és a hour
almezőt, hogy elkülönítse a napi órát (0-tól 23-ig), amelyben a megrendelés történt.,
SELECT EXTRACT(hour from occurred_at) AS hour FROM demo.orders
használja a COUNT
függvényt a rendelésekhez, majd GROUP BY
óra. (Mivel az óra az első érték a SELECT
nyilatkozatában, akkorGROUP BY 1
).
SELECT EXTRACT(hour from occurred_at) AS hour, COUNT(*) AS orders FROM demo.orders GROUP BY 1
végül az eredmények egymás utáni rendezéséhez használja aORDER BY 1
.
SELECT EXTRACT(hour from occurred_at) AS hour, COUNT(*) AS orders FROM demo.orders GROUP BY 1 ORDER BY 1
és itt vannak az eredmények! Úgy tűnik, segíthet, ha néhány pluszmunkás korán reggel és ebédidőben dolgozik.,
Mode Analytics
mi az átlagos napi rendelési mennyiség?
a megrendelések átlagos mennyiségének hétköznap történő meghatározásához használja a EXTRACT
és a dow
almezőt a hét napjának (0-6-tól, ahol 0 vasárnap van) elkülönítéséhez, amelyben a sorrend történt.
SELECT EXTRACT(dow from occurred_at) AS dow FROM demo.orders
ezután kerekítse a rendelési időbélyeget naponta DATE_TRUNC
., A COUNT
dow
ésday
visszaadja a naponta leadott rendelések számát a megfelelő hétnappal együtt.
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
Az átlagos hétköznap rendelési kötet megtalálásához használja az előző lekérdezést alkeretként (aliased as a
). Vegyük a megrendelések átlagát (a AVG()
függvény használatával), majd használjuk a WHERE
záradékot a szombat és vasárnap szűréséhez.
a nagy számú diagramok kiválóan alkalmasak összesített mutatók megjelenítésére., Annak érdekében, hogy szemmel tartsa a rendelési hangerőt, gyűjtsön össze ilyen mutatókat egy műszerfalba.
Mode Analytics
az életkorral eltelt idő kiszámítása
a AGE
dátum funkció kiszámítja, hogy egy esemény milyen régen történt. A szintaxis nagyon egyszerű: alkalmazza a AGE()
– ot egyetlen időbélyegre, a lekérdezés pedig visszaadja az esemény bekövetkezése óta eltelt időt. A SELECT AGE( '2010-01-01' )
futtatása 2011. január 1 – jén a 1 years 0 months 0 days
eredményt adná vissza.,
AGE()
azt is meghatározhatja, hogy mennyi idő telt el két esemény között. Ahelyett, hogy egyetlen időbélyeget helyezne a zárójelbe, helyezze be mindkét időbélyeget (a legutóbbi időbélyegzővel kezdve), majd vesszővel különítse el őket. SELECT AGE( '2012-12-01',
‘2010-01-01’)
2 years 11 months 0 days
.
vegye figyelembe, hogy aAGE
függvény alkalmazása egyenértékű az időbélyegek kivonásával:SELECT '2012-12-01' - '2010-01-01'
.
hány éves az ügyfélszámla?,
tegyük fel, hogy értékesítési csapata személyre szabja az üdvözleteket annak alapján, hogy az ügyfél mennyi ideig használja a terméket. A AGE
funkció segítségével megtalálhatja, hogy mennyi idő telt el a fiók létrehozása óta.
a táblázat modeanalytics.customer_accounts tartalmazza rekordok minta ügyfél számlák. Válassza ki a fióknevek oszlopát (name
), majd alkalmazza a AGE()
függvényt az időbélyegek oszlopára, amely megmutatja, mikor jött létre minden fiók (created
).,
SELECT name, AGE(created) AS account_age FROM modeanalytics.customer_accounts
Mode Analytics
mennyi ideig tart a felhasználóknak, hogy havonta átlagosan kitöltsék profiljukat?
a táblázat modeanalytics.profilecreationevents tartalmaz minta adatokat a felhasználók, akik létrehoztak egy profilt, beleértve a start and end timestamps.
ahhoz, hogy megtalálja a profil kitöltésének átlagos idejét minden hónapban, kezdje azzal, hogy megtalálja az egyes felhasználók számára a profil kitöltéséhez szükséges időt, valamint azt a hónapot, amelyben a profil létrehozásának folyamata megkezdődött. Először kerekítse astarted_at
időbélyeget havonta, a DATE_TRUNC
függvény használatával., Ezután keresse meg a started_at
– tól ended_at
– ig eltelt időt az egyes profilokhoz a AGE
függvény használatával.
keresse meg az átlagot minden hónapban a AVG
függvény alkalmazásával az eltelt időértékre (a AGE
utasítás) és csoportosítva havonta.
az értékek következetes egységbe történő visszaadásához alkalmazza aEXTRACT
függvényt és az epoch almezőt az értékeire, hogy másodpercek alatt visszaadja az eredményeket.
Mode Analytics
Ajánlott cikkek
- Hogyan Mester Anti Csatlakozik alkalmazhat Üzleti Problémák
- Gondolkodás SQL vs Gondolkodás Python
- DATE_TRUNC: EGY SQL Timestamp Funkció számíthat