Transformace

Teď nastává ta pravá detektivní práce. Snažíme se v našich sim_id a časových záznamech poznat turisty. V následujících týdnech se nám bude o SQL i zdát.

Turista v Krumlově nebydlí, nepracuje, Krumlovem jen neprojíždí, nebydlí ani nepracuje v okrese Český Krumov, ani v něm netráví pravidelně víkendy. Turista přijede, stráví v Krumlově den nebo více dní a odjede. V měsíčním horizontu tato návštěva Krumlova je pouze jednorázová.

O některých sim_id máme informace, jaké jsou jejich domácí, pracovní a víkendové BTSky, ty vyfiltrujeme jednoduše, ale co se sim_id, u kterých známe jen časové údaje? Nastává čas noření se do dat, počítání, kontrolování, dalšího noření se, přepočítávání, kontrolování, dokud nebudeme mít logiku za turistou opravdu vyladěnou.

Výstupem bude tabulka s agregovanými údaji pro každé sim_id, kterou použijeme jako podklad pro vizualizaci.

Hra s časem

Je nám jasné, že v naší analýze budou rozhodujícím kritériem hlavně počty dní, a hodin a také to, jestli se jedná o denní a noční dobu. Timestampy z tabulky BTS_sig nejsou ideální pro naše výpočty, proto je musíme transformovat na něco, s čím se už počítat dá. Navíc nám ukazují o 2 hodiny jiný čas, tak si je musíme upravit do naší časové zóny. Přidáme sloupce určující interval dne, hodiny a zda se jedná o den nebo noc. Noc si definujeme jako interval mezi půlnocí a šestou ranní. Použijeme SQL časové funkce.

Začínáme tvořit tabulku s mezivýpočty. Ty si průběžně kontrolujeme tak, že si vybereme pár náhodných sim_id splňující kritéria našeho výpočtu, najdeme je v tabulce BTS_sig2 a zhodnotíme selským rozumem, jestli dny a hodiny u daného sim_id opravdu odpovídají tomu, že by daný člověk mohl být turista v Krumlově. V případě, že velká skupina sim_id nevyhovuje, nezbývá nám, než kritéria upravit a přepsat selecty v SQL.

Náš turista v Krumlově nestráví více jak týden. Samozřejmě to neplatí stoprocentně, ale těch, co by Krumlově byli déle, bude zanedbatelné množství. Více než celkový součet dní v měsíci, rozhoduje rozdíl mezi prvním a posledním dnem, kdy jsme člověka v Krumlově zaznamenaly. Pokud v Krumlově turista stráví týden, tak je možné, že část svého pobytu stráví i výlety mimo Krumlov. Zajímá nás také, kolik po sobě následujích dní v Krumlově strávil.

KRITÉRIUM TURISTA Č. 1 : POSLEDNÍ DEN - PRVNÍ DEN < 8 A  POČET STRÁVENÝCH DNÍ > 0

Co znamená, že je někdo v Krumlově jeden den? Původní úvaha, že si řekneme, že pokud je v Krumlově víc, jak  2 hodiny, tak mu započítáme den, jinak je to člověk, co Krumlovem projel, se ukázala být mylná.  Nepočítá totiž s tím, že někdo může Krumlovem projet dvakrát denně (ráno a večer), a hodiny se nasčítají. Případně projíždět Krumlovem někdy kolem každé celé hodiny, kdy by se mu i krátký průjezd zaznamenal do dvou hodinových intervalů. Rozhodující bude, podobně jako u dnů, počet hodinových intervalů a rozdíl mezi první a poslední hodinou plus jedna (abychom započítali celý interval), který by neměl být o moc vyšší, než počet hodin. V opačném případě to chápeme jako den, kdy člověk Krumlovem jen projíždí.

KRITÉRIUM TURISTA Č. 2 : POČET HODINOVÝCH INTERVALŮ > 2 NEBO POMĚR ROZDÍLU MEZI POSLEDNÍ A PRVNÍ HODINOU PLUS JEDNA A CELKOVÝM POČTEM HODIN NENÍ VĚTŠÍ NEŽ 3/2

Při následné vizualizaci se ukázalo, že nám do neturistů spadá hodně Asiatů. Je to dáno stylem jejich cestování  "letem světem Evropou za 3 dny. Vyfotím se před památkou a frčím dál ". Pro ně jsme kritérium zmírnily tak, že i Číňan, který v Krumlově stráví hodinu je turista :)

V naší analýze chceme počítat i noci, které turista v Krumlově strávil. Tady jsme postupovaly velice jednoduše. Noc započítáme turistovi, když najdeme alespoň jeden záznam v nočním intervalu 0 - 6 hodin, který jsme si již nadefinovaly dříve. 

Turisti

Z tabulky BTS_sig2  tak získáváme následující informace pro jednotlivá unikátní sim_id: celkový počet dní, pro které existuje záznam, první a poslední den, počet dní, které strávil v Krumlově, počet dní, kdy projel a počet nocí. Ty si uložíme v několika krocích za použití dočasných tabulek do separátní tabulky, ve které si ještě připravíme sloupec pro napojení informací o zemích tak, že oddělíme první 3 znaky ze sim_id .

Nyní můžeme naši výpočtovou tabulku obohatit o informace o zemích původu sim_id a pro česká sim_id také o údaje o segmentu, věku, pohlaví a místech nejčastějšího výskytu doma, v práci a o víkendech, vyhodíme sim_id přístrojů, o kterých víme, že nejsou telefony. Vytvoříme flag turisty pro sim_id, která splňují naše časová kritéria, a kritéria podle lokalit nejčastějšího výskytu českých sim_id a 

Vodáci

Krumlovem protéka řeka Vltava, která v posledních letech zaznamenala obrovský vodácký boom a nás zajímalo, jestli z našich krumlovských turistů dokážeme určit vodáky. Vytipovali jsme si lokality proti proudu řeky (Vyšší Brod, Rožmberk, Zátoň, Větřní) a holky z T-Mobilu nám dodaly další soubor DA_kempy_sig o zhruba 5 mil. řádcích s timestampy a sim_id, která se 31. 7. - 31. 8. objevila na BTSkách obsluhujících naše vytipovaná území. Signalizační data z kempů si upravíme stejným způsobem jako ta z Krumlova. Musíme navíc přepočítat lte_cell_id podle vzorce od T-Mobile. Zas se noříme do dat, abychom poznaly vodáka.

Vodákovi (tedy aspoň tomu normálnímu) trvá maximálně 4 dny, než připluje do Krumlova a ty jsou po sobě jdoucí. 

KRITÉRIUM VODÁK Č.1 : POSLEDNÍ DEN - PRVNÍ DEN < 5

Použily jsme hodinové kritérium z turistů pro započítatelnost dne.

KRITÉRIUM VODÁK Č. 2 : POČET HODINOVÝCH INTERVALŮ > = 2 NEBO ROZDÍL MEZI PRVNÍ A POSLEDNÍ HODINOU <= 2/3 POČTU HODINOVÝCH INTERVALŮ 

A snažily jsme se najít ty sim_id, jejichž poslední BTSka předtím, než se objevili v Krumlově byla Větřní (= město těsně před Krumlovem, s velikou ošklivou fabrikou, jejímuž proplutí se žádný vodák nevyhne).

KRITÉRIUM VODÁK Č. 3: POSLEDNÍ BTSKA POSLEDNÍ DEN NA VODÁCKÝCH BTSKÁCH JE VĚTŘNÍ

A zde je výsledný select.

Další destinace

Abychom zjistily, kam turisté mají namířeno, potom co opustí Český Krumlov, vyžádaly jsme si dodatečná data pro turistická sim_id o BTSkách. Poslaly jsme našim koučkám seznam turistických sim_id s datem posledního dne v Krumlově. Od nich jsme následně dostaly informace o vysílačích, na kterých byli turisté v 10 hodin večer v den odjezdu a v 7 hodin ráno následující den. Opět jsme musely přepočítat ID vysílačů a napojit informace o místech, kde vysílače jsou. Řada turistů již žádný záznam o vysílači u sebe neměla, což znamená, že opustili síť T-Mobile a odjeli do zahraničí anebo neměli zapnutý telefon. 

A máme data připravená pro vizualizaci. 

Tady je malá ukázka toho, jak naše výsledná tabulka vypadá.