Mode Blog (Polski)

znaczniki czasu są kluczowe dla analizy biznesowej z bardzo prostego powodu: mówią, kiedy coś się dzieje. Wyobraź sobie próbę wykrycia trendów w danych, takich jak miesięczny ruch w sieci, kwartalne zarobki lub dzienny wolumen zamówień, nie wiedząc, kiedy wystąpiły zdarzenia. To byłby koszmar.

PostgreSQL oferuje wiele funkcji do manipulowania znacznikami czasu. Aby oddzielić przydatne od niejasnych, udostępniamy instrukcje dotyczące najczęściej używanych funkcji datowania Postgres i scenariuszy biznesowych, w których się przydają.,

najczęściej używane funkcje daty Postgres i scenariusze Biznesowe, w których się przydają:

• Zaokrąglanie znaczników czasu za pomocą DATE_TRUNC
• znajdowanie zdarzeń względem czasu teraźniejszego za pomocą NOW() I CURRENT_DATE
• izolowanie godziny dnia i dnia tygodnia za pomocą wyciągu
• Obliczanie czasu, jaki upłynął z wiekiem

stworzyliśmy dane dla każdego przykładu dostępne w trybie public Warehouse. Wypróbuj każdą funkcję daty w trybie podczas pracy nad tymi przykładami. Zarejestruj konto w Mode Studio i otwórz nowy raport, aby rozpocząć.,

Zaokrąglanie znaczników czasu za pomocą DATE_TRUNC

funkcjaDATE_TRUNC zaokrągla wartość znacznika czasu do określonego interwału, co pozwala na zliczanie zdarzeń. Znacznik czasu można zaokrąglić do następujących jednostek czasu:

• mikrosekunda
• milisekunda
• sekunda
• minuta
• godzina
• dzień
• tydzień
• kwartał
• rok
• wiek
• Millenium

składnia DATE_TRUNC wygląda następująco: DATE_TRUNC('interval',timestamp).,

na przykładSELECT DATE_TRUNC('day','2015-04-12 14:44:18') zwróci wynik2015-04-12 00:00:00.

aby uzyskać bardziej szczegółowe wyjaśnienieDATE_TRUNC (I drukowalny numer referencyjny można zachować przy biurku!), zobacz ten post.

Jak ruch w sieci zmieniał się w czasie?

WypróbujDATE_TRUNC dla siebie pytając o tabelę modeanalytics.web_events, który zawiera przykładowe zapisy odwiedzin strony, w tym kolumnę occurred_at. Możesz wyizolować miesiąc wizyty za pomocą DATE_TRUNC.,

aby zwrócić liczbę odwiedzin każdego miesiąca według kanału, Dodaj kolumnę channel I COUNT do SELECT, a następnie Grupuj według month I channel. (Ponieważ miesiąc i Kanał są dwiema pierwszymi wartościami w instrukcjiSELECT, możeszGROUP BY 1,2), tak:

na koniec użyjORDER BY 1,2, aby uporządkować wyniki chronologicznie (według miesiąca) i alfabetycznie (według kanału).,

w trybie można utworzyć wykres liniowy, aby wizualizować wyniki zapytań.

Analiza trybu

wyszukiwanie zdarzeń względem czasu teraźniejszego za pomocą funkcji NOW() i CURRENT_DATE

funkcjaNOW() date zwraca bieżący znacznik czasu w UTC (jeśli Strefa czasowa jest nieokreślona). Możesz odjąć odstępy od NOW(), aby pobrać zdarzenia, które miały miejsce w ciągu ostatniej godziny, ostatniego dnia, ostatniego tygodnia itp.

uruchomienieSELECT NOW() o godzinie 9:00 RANO UTC w dniu 11 października 2016 r.spowodowałoby2016-10-11 09:00:00.,

CURRENT_DATE funkcja zwraca tylko bieżącą datę, a nie cały znacznik czasu. Uruchomienie SELECT CURRENT_DATEo godzinie 9:00 RANO UTC w dniu 11 października 2016 r.spowoduje powrót 2016-10-11.

jakie zamówienia zostały złożone w ciągu ostatnich 12 godzin?

demo tabeli.zamówienia zawierają przykładowe zapisy wszystkich zamówień, w tym kolumnę znacznika czasuoccurred_at w UTC.,

aby znaleźć zamówienia złożone w ciągu ostatnich 12 godzin, użyj klauzuliWHERE, aby zwrócić tylko zamówienia złożone po lub dokładnie w (>=) aktualnego znacznika czasu (NOW()) minus 12 godzin.,

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

Analiza trybu

oprócz hour możesz użyć dowolnego z następujących interwałów:

• mikrosekund
• milisekund
• sekundy
• minuty
• godziny
• dzień
• tydzień
• miesiąc
• rok
• dekada
• wiek
• Millennium

Możesz również łączyć różne interwały w tym samym wyrażeniu w następujący sposób:
interval '4 hours 3 minutes'

jakie zamówienia zostały złożone wczoraj?,

możesz użyć tej samej tabeli, aby znaleźć wczorajsze zamówienia, łącząc funkcjeDATE_TRUNCICURRENT_DATE.

zacznij od użycia funkcjiDATE_TRUNC do zaokrąglania wartości occurred_at według dnia (ponieważ chcemy wiedzieć, czy coś wydarzyło się wczoraj). Następnie użyj klauzuliWHERE, aby zwrócić tylko wartości, w którychoccurred_at dzień jest równy bieżącej dacie (używając funkcji CURRENT_DATE) minus jeden dzień.,

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

Analiza trybu

izolowanie godziny dnia i dnia tygodnia za pomocą wyciągu

EXTRACT funkcja date pozwala na wyizolowanie pól podrzędnych, takich jak rok lub godzina, ze znaczników czasu.

oto składnia: EXTRACT(subfield FROM timestamp). Uruchomienie EXTRACT(month FROM '2015-02-12') zwróci wynik 2.

pamiętaj, że podczas gdy poniższy przykład koncentruje się na godzinie subfielda (godzina dnia), masz do dyspozycji wiele innych subfieldów, od millennium do microsecond., Możesz sprawdzić pełną listę dostępnych podfolderów tutaj.

ile zamówień składa się co godzinę w ciągu dnia?

firma prowadząca centrum realizacji zamówień może chcieć zatrudnić więcej pracowników, gdy pojawi się większość zamówień. Aby dowiedzieć się, kiedy zamówienia są składane przez cały dzień, możesz użyć funkcji EXTRACT I podfolda hour, aby wyizolować godzinę dnia (od 0 do 23), w której nastąpiło zamówienie.,

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

Użyj funkcjiCOUNT do liczenia zamówień, a następnieGROUP BY hour. (Ponieważ hour jest pierwszą wartością w instrukcji SELECT, możesz GROUP BY 1).

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

wreszcie, aby uporządkować wyniki kolejno, użyjORDER BY 1.

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

a oto wyniki! Wygląda na to, że to może pomóc mieć dodatkowych pracowników na zegarze wcześnie rano i około czasu lunchu.,

Mode Analytics

jaki jest średni wolumen zamówień w dni powszednie?

aby określić średni wolumen zleceń, które wystąpiły w dzień powszedni, użyjEXTRACTIdow aby wyizolować dzień tygodnia (od 0-6, gdzie 0 to niedziela), w którym nastąpiło zlecenie.

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

następnie zaokrąglaj znaczniki czasu zamówienia według dnia za pomocąDATE_TRUNC., BiorącCOUNT zamówień zgrupowanych wedługdow Iday zwróci liczbę zamówień złożonych każdego dnia wraz z odpowiednim dniem tygodnia.

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

aby znaleźć średni wolumen zamówień w dniu tygodnia, użyj poprzedniego zapytania jako zapytania podrzędnego (aliased jakoa). Weź średnią zamówień (używając funkcji AVG()), a następnie użyj klauzuli WHERE, aby odfiltrować soboty i niedziele.

wykresy dużych liczb świetnie nadają się do wyświetlania zagregowanych metryk., Aby mieć oko na wolumen zamówień, Zbierz takie metryki w jednym pulpicie nawigacyjnym.

Analiza trybu

Obliczanie czasu, jaki upłynął z wiekiem

funkcjaAGE date oblicza, jak dawno miało miejsce zdarzenie. Składnia jest dość prosta: zastosuj AGE() do pojedynczej znacznika czasu, a twoje zapytanie zwróci ilość czasu od tego zdarzenia. Uruchomienie SELECT AGE( '2010-01-01' ) w dniu 1 stycznia 2011 r. zwróci wynik 1 years 0 months 0 days.,

AGE() może również określić, ile czasu minęło między dwoma zdarzeniami. Zamiast umieszczać pojedynczy znacznik czasu w nawiasach, Wstaw oba znaczniki czasu (zaczynając od ostatniego znacznika czasu) i oddziel je przecinkiem. Uruchomienie SELECT AGE( '2012-12-01',’2010-01-01′) zwróci 2 years 11 months 0 days.

zauważ, że ta aplikacja funkcji AGE jest równoważna odejmowaniu znaczników czasu: SELECT '2012-12-01' - '2010-01-01'.

ile lat ma konto klienta?,

Załóżmy, że twój zespół sprzedaży chce spersonalizować pozdrowienia na podstawie tego, jak długo klient korzysta z twojego produktu. Możesz sprawdzić, ile czasu upłynęło od utworzenia konta, korzystając z funkcji AGE.

tabela modeanalytics.customer_accounts zawiera zapisy przykładowych kont klientów. Wybierz kolumnę nazw kont (name) I zastosuj funkcję AGE() do kolumny znaczników czasu pokazujących, kiedy każde konto zostało utworzone (created).,

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

Mode Analytics

ile czasu zajmuje użytkownikom średnio co miesiąc uzupełnianie profilu?

tabela modeanalytics.profilecreationevents zawiera przykładowe dane użytkowników, którzy utworzyli profil, w tym znaczniki czasu rozpoczęcia i zakończenia.

aby znaleźć średni czas ukończenia profilu w każdym miesiącu, zacznij od znalezienia czasu potrzebnego każdemu użytkownikowi do ukończenia profilu, a także miesiąca, w którym rozpoczęto proces tworzenia profilu. Najpierw zaokrąglstarted_at znacznik czasu według miesiąca, używając funkcji DATE_TRUNC., Następnie znajdź czas, jaki upłynął od started_at do ended_at dla każdego profilu za pomocą funkcji AGE.

Znajdź średnią dla każdego miesiąca, stosując funkcję AVG do wartości czasu, który upłynął (TwojeAGE) I grupując według miesięcy.

aby zwracać wartości w spójnej jednostce do tworzenia wykresów, zastosuj pole podrzędne funkcji i epoch EXTRACT do wartości, aby zwrócić wyniki jako liczbę sekund.

Mode Analytics

Polecane artykuły

• Jak opanować anty połączenia i zastosować je do problemów biznesowych
• myślenie w SQL vs myślenie w Pythonie
• DATE_TRUNC: funkcja znacznika czasu SQL, na którą możesz liczyć