Článek přečtěte do 4 min.

Vzorce chování v časových řadách jsou něco, co mnoho analytiků chce najít v časových řadách. Analytik klikacího proudu chce najít sérii kliknutí, ke kterým došlo mezi příchodem na web, procházením jednoho nebo více článků na webu a konečným naplněním košíku a odhlášením. finanční analytik chce v křivkách hodnot akcií najít tvary V, které se skládají ze dvou nebo více řádků, kde hodnota akcií klesla, následovaných alespoň jedním řádkem, kde hodnota akcií opět vzrostla.

Zvláštnost této potřeby vyhledávání dat spočívá ve slovech „jeden nebo více“. Znamená to, že chceme skupinu po sobě jdoucích řádků, které sledují určitý vzorec chování (například: nejprve se stane událost „a“, pak se stane událost „b“, jednou nebo vícekrát, pak se může stát událost „c“ a nakonec událost stane se „d“). Hledáme skupiny řádků – logické oddíly časové řady – jejichž počet řádků předem neznáme. To je něco, s čím SQL – dokonce i SQL bohaté na analytické funkce – bojuje. Druhý parametr funkce LAG() OVER() může vyslovit pouze 1, nebo 2, nebo 3 atd. řádky; je to opravené. Nepomáhají ani klauzule RANGE BETWEEN nebo ROWS BETWEEN okenních rámů.

Z mých předchozích příspěvků v této sérii jste možná viděli, že jsem hrdý na řešení analytických problémů také v databázích s menší sadou nástrojů než Vertica. Myslím, že v posledních třech příkladech se mi to podařilo – nějak. Takže vás ujišťuji, že jsem také zkoušel rekurzivní dotazy, zatím bez úspěchu. Mým současným plánem, který doufám implementuji, pokud to čas dovolí, v nepříliš vzdálené budoucnosti, je použít uloženou proceduru, která udržuje proměnnou, která vytvoří řetězec vzoru pokaždé, když série řádků slibuje, že skončí jako vzor, ​​který hledám.

Abychom to ilustrovali pomocí výše uvedeného případu clickstream:

  • Dané ID uživatele se dostane na můj web zvenčí. To by mohl být začátek mého vzoru.
  • Stejný uživatel přejde na stránku článku, který nabízím – může to být druhý řádek mého vzoru.
  • Stejný uživatel přejde na jiný článek, který nabízím – stále to odpovídá mému vzoru.
  • Stejný uživatel přejde na můj třetí, čtvrtý a pátý článek – to stále odpovídá mému vzoru.
  • Stejný uživatel odejde od mého vzoru – aniž by naplnil nákupní košík, natož aby se odhlásil – toto není vzor, ​​který hledám, musím opustit celý vzor, ​​který jsem doposud nashromáždil.

Se stejným vzorem jako výše, jen s poslední odrážkou nahrazenou skutečností, že uživatel klikl na „Přidat do košíku“ a poté na „Pokladna“, chci celý tento vzor zachovat – abych zjistil, jak dlouho relace trvala ( rozdíl časového razítka mezi prvním a posledním řádkem), průměrný interval mezi kliknutími atd.

Co jsem ještě nevyřešil, je, jak vzor raději opustit, než si jej ponechat, alespoň elegantním a efektním způsobem. Z toho, co jste dosud četli, to vypadá, že by se mohla stát několikasetřádkovou uloženou procedurou.

Vertica způsob detekce vzorců chování

Pro tento typ požadavku má Vertica připravenou klauzuli MATCH(). Vertica byla poprvé publikována kolem roku 2006 a již tehdy byla součástí sady nástrojů. Jediný další DBMS, o kterém vím, že také něco takového podporuje, je Oracle, od jejich verze 12c, která byla zveřejněna v roce 2014. Může to být věc osobního vkusu, ale verze Oracle mi přijde méně intuitivní než verze Vertica.

Struktura klauzule MATCH vypadá takto:

  1. Úvodní klíčové slovo MATCH a otevřená závorka
  2. klauzule PARTITION BY … ORDER BY … jako v jakékoli funkci OLAP
  3. klauzuli DEFINE, která se skládá z klíčového slova DEFINE následovaného několika čárkami oddělenými definicemi událostí. Tyto definice událostí se skládají z programátorského slova (tj. identifikátor SQL) dle vašeho výběru, za kterým následuje klíčové slovo AS a libovolný booleovský výraz
  4. klauzuli PATTERN skládající se z klíčového slova PATTERN, programátorského slova dle vašeho výběru, klíčového slova AS a dále identifikátorů definovaných v klauzuli DEFINE výše, v pořadí, v jakém se mají objevit, doplněné podle potřeby hvězdičkou – což znamená nula , jednou nebo vícekrát, znaménko plus – což znamená jednou nebo vícekrát. A pokud vám to připomíná syntaxi regulárního výrazu, přesně tak to funguje.
  5. uzavírací závorku

Pokud si zapamatujete pět výše uvedených bodů, nemusíte se vracet do manuálu, abyste kódovali klauzuli MATCH. Jednoduchost syntaxe, která vyjadřuje nepochybně složitý požadavek na dotaz, považuji za působivou.

Klauzule MATCH() v akci.

Vstupem je tento výřez dat senzoru ze spalovacího motoru. Během prototypové fáze nového vozu chtějí automobiloví inženýři analyzovat příchozí vzduchovou hmotu – v hmotnosti za jednotku času – během neúspěšných pokusů o nastartování motoru. Data jedné nebo více testovacích jízd, možná jednoho nebo více aut, jsou v datasetu a testovací jízda může trvat několik dní a získáme několik stovek měření za sekundu a auto. Vymazali jsme všechny sloupce s údaji snímačů kromě vzduchové hmotnosti a otáček klikového hřídele motoru za minutu – to jsou otáčky za minutu. A my chceme odfiltrovat pouze oddíly, které odpovídají neúspěšnému pokusu o spuštění. Výřez ze vstupních dat, se kterými pracujeme, tedy vypadá takto:

Samozřejmě, to je ve skutečnosti časové razítko, včetně dne, měsíce a roku. Všimněte si, že v tomto částečném pohledu máme stále stejné vozidlo a dvě testovací jízdy, na které bylo vzato. Samozřejmě je mnohem větší a obsahuje miliony řádků, které jsou pro náš účel v reálu zcela nepoužitelné.

Jak poznáme neúspěšný pokus o start?

  • Při startu se motor začne otáčet: v jedné řadě jsou otáčky na 0, v další nad 0.
  • Motor se chvíli točí – jednou nebo vícekrát, ale nikdy ne rychlostí dostatečnou k tomu, aby se motor sám nažhavil a roztočil vlastním spalováním: tik-over neboli volnoběžné otáčky, které v našem případě je 650 ot./min.
  • Motor se přestane otáčet: v jedné řadě jsou otáčky nad nulou, v další jsou na 0.

Kandidátské vzory jsem samozřejmě záměrně vložil do výše uvedeného výřezu – za den testovací jízdy se tak často nevyskytují. Všimnete si skupin řad, kde se motor otáčí, orámovaných řadami, kde se motor netočí. Všimněte si skupiny v testovací jízdě 27, která začíná v 00:00:00 a končí v 00:01:40. Vypadá jako všechny ostatní skupiny, ale má dva řádky nad 650 ot./min., a proto je potřeba vyřadit celý. Sledování 32 řad je proveditelný úkol – zvláště pokud se skládá pouze z kandidátských vzorů. Ve skutečnosti hledáme hromadu jehel v kupce sena.

Co s tím teď uděláme? Spouštíme tento komentovaný dotaz:

A toto je výsledek celého cvičení:

Všimněte si, že jsme našli jeden vzor pro testovací jízdu 27 – první kandidát na tuto jednotku měl dva řádky nad 650 ot./min a byl vyřazen – a dva vzory v testovací jízdě 29. Event_name je užitečná část zprávy, která ilustruje, proč vzor Hledal jsem se shoduje.

Zdroj: Vertica