Clickhouse магия

Подключение

А вот код! 
library(RClickhouse)

con <- DBI::dbConnect(RClickhouse::clickhouse(), host="localhost",
                      port = 9000,
                      dbname = "WIFI",
                      user = "default",
                      password = "useruser", compression = "lz4")

Данные

У нас много строк даже после очистки:

А вот код! 
DBI::dbGetQuery(con, "SELECT count(*)
                             FROM mainlog")
    count()
1 299965940

Пример данных

А вот код! 
res <- DBI::dbGetQuery(con, "SELECT (*)
                             FROM mainlog limit 10")
knitr::kable(res)
guid tm router_mac user_mac signal router_id
b7e87fd6-49e9-41a7-9645-4ae25f120138 2023-01-01 03:00:00.000 +0300 48:8F:5A:AE:A1:D9 68:D7:9A:A2:74:60 -73.0 0648078a-9d45-4577-af14-12b49e8f017b
014f1da1-e200-4fc5-869a-a695836a2fe5 2023-01-01 03:00:00.000 +0300 48:8F:5A:AE:A1:D9 6C:5A:B0:A1:DB:8E -68.0 0648078a-9d45-4577-af14-12b49e8f017b
9d5dfa10-f9fd-4dae-ac86-c935381a02b4 2023-01-01 03:00:00.000 +0300 B8:69:F4:6B:FD:E9 60:A4:B7:4D:89:37 -75.0 07190dec-be71-44a2-8d64-c24fb51ebc7b
14ed8300-18f3-42e6-9a92-e458edc2fac9 2023-01-01 03:00:00.000 +0300 CC:2D:E0:82:B9:07 80:2A:A8:76:08:1C -74.0 8a823f01-771f-420e-8971-928cba314a24
35a91e47-d8a2-4642-8002-a5298702f12e 2023-01-01 03:00:00.000 +0300 CC:2D:E0:82:B9:40 38:17:66:10:29:3C -73.0 3d9490f4-fb08-4c4c-80ec-ca744a6d9ab4
63eb92c4-3f13-45bc-b055-421dd6787ce6 2023-01-01 03:00:00.000 +0300 CC:2D:E0:82:B9:40 D0:37:45:C1:E1:1E -66.0 3d9490f4-fb08-4c4c-80ec-ca744a6d9ab4
63d60bf2-6cef-4d32-92ef-8dfba4c63a82 2023-01-01 03:00:01.000 +0300 48:8F:5A:AC:81:1D 68:D7:9A:BE:E3:20 -57.0 8a734674-e451-4fe1-8b50-3b3b46e334a1
1928f1cd-5aef-427e-ac19-5295441827bb 2023-01-01 03:00:01.000 +0300 48:8F:5A:AE:A1:D9 00:1E:43:90:F8:65 -68.0 0648078a-9d45-4577-af14-12b49e8f017b
da4c5fcb-3006-48ae-931c-28c911bd9179 2023-01-01 03:00:01.000 +0300 B8:69:F4:6B:FD:E9 8E:41:A1:E8:8C:B6 -74.0 07190dec-be71-44a2-8d64-c24fb51ebc7b
511284de-fa1b-42e6-b47f-2ee43079d2cc 2023-01-01 03:00:02.000 +0300 48:8F:5A:AE:A1:D9 5A:1A:86:06:3C:F7 -75.0 0648078a-9d45-4577-af14-12b49e8f017b

Общий heatmap

А вот код! 
library(dplyr)

hmap_tbl <- tbl(con, "mainlog") %>% 
  group_by(router_id) %>% 
  summarise(n=n()) %>% 
  collect()

readr::write_csv2(hmap_tbl, "data/all_routers_popularity_annual.csv")

Строим heatmap

Добавим данных из роутеров

А вот код! 
routers_dist <- readr::read_csv("https://storage.yandexcloud.net/roadgraph/marshruts_lengts.csv")


rdst2upl <- routers_dist %>% 
  select(from_guid, to_guid, length)

#DBI::dbWriteTable(con, "rdst", rdst2upl)

tbl(con, "rdst") %>% 
  head %>% 
  collect()
# A tibble: 6 × 3
  from_guid                            to_guid                            length
  <chr>                                <chr>                               <dbl>
1 0648078a-9d45-4577-af14-12b49e8f017b 6422a0a5-2c2d-4610-bebc-91722ea37…  4994.
2 0648078a-9d45-4577-af14-12b49e8f017b b17aefd3-8431-4054-a0b5-b0a26eeb9…  3354.
3 0648078a-9d45-4577-af14-12b49e8f017b 92c1cc9e-cfa4-4ef0-91f0-c0a158f54…  5807.
4 0648078a-9d45-4577-af14-12b49e8f017b f0058c02-034f-429a-b932-8638089d8…  3551.
5 0648078a-9d45-4577-af14-12b49e8f017b 37cea6a6-eaaa-4e12-9b4b-b444163a2…  1666.
6 0648078a-9d45-4577-af14-12b49e8f017b 07190dec-be71-44a2-8d64-c24fb51eb…   959.

Реализация LAG/LEAD в Clickhouse – пока отсутствует

https://www.programmersought.com/article/532510185734/

– поэтому пока …

Proof-of-Concept решение

Идея

А вот код! 
df <- readr::read_csv2("https://storage.yandexcloud.net/roadgraph/wifi_logs_2023_01_01_202312081852.csv")
А вот код! 
df %>% 
  mutate(tm = as.POSIXct(tm)) %>% 
  select(tm, router_mac, user_mac) %>% 
  arrange(user_mac) %>% 
  head(10) %>% 
  knitr::kable()
tm router_mac user_mac
2023-01-01 19:27:19 B8:69:F4:6B:FD:B6 00:00:00:10:00:00
2023-01-01 16:45:03 CC:2D:E0:F3:8C:6E 00:00:00:16:FA:9E
2023-01-01 16:46:19 48:8F:5A:AE:A1:D9 00:00:00:16:FA:9E
2023-01-01 19:48:00 48:8F:5A:AE:A1:D9 00:00:00:16:FA:9E
2023-01-01 19:47:56 CC:2D:E0:F3:8C:6E 00:00:00:16:FA:9E
2023-01-01 17:44:50 48:8F:5A:AE:A1:27 00:00:D4:02:1C:F8
2023-01-01 07:36:52 48:8F:5A:AE:A1:D9 00:02:42:FB:17:E0
2023-01-01 07:36:48 48:8F:5A:AE:A1:27 00:02:42:FB:17:E0
2023-01-01 07:37:13 48:8F:5A:AE:A1:27 00:02:42:FB:17:E0
2023-01-01 07:37:18 48:8F:5A:AE:A1:D9 00:02:42:FB:17:E0

Если посмотреть на отсортированные данные – то можно наблдать как юзеры отмечаются на разных роутерахв разное время. Можно замерить лаг (разницу между прошлым временем и текущим), а также подсчитать расстояние между БС, на которой была предыдущая отметка.

Очевидно, что

\[V = S/t\]

Выполняем для всех на 01-01-2023

А вот код! 
monitoring_df <- df %>% 
  mutate(tm = as.POSIXct(tm)) %>% 
  group_by(user_mac, .drop = FALSE) %>% 
  arrange(tm) %>% 
  summarise(tm, lagtime = lag(tm), lagpoint = lag(router_id), router_id) %>% 
  filter(lagpoint!=router_id)

monitoring_df_w_dist <- left_join(monitoring_df, rdst2upl, by = c("lagpoint" = "from_guid", "router_id" = "to_guid")) %>% 
  mutate(time_delta = (tm - lagtime) %>% as.numeric()) %>% 
  mutate(velocity = length / time_delta) %>% 
  filter(velocity<25)

Получили скорость в районе роутера

Теперь сгруппируем по роутерам и определеим средняя скорость в разные интервалы времени

Утро

А вот код! 
utro <- "2023-01-01 11:00:00"

library(lubridate)

utro_df <- monitoring_df_w_dist %>% 
  filter(ymd_hms(tm) < ymd_hms(utro)) %>% 
  group_by(router_id) %>% 
  summarise(vel = median(velocity))

write.csv2(utro_df, file = "data/utro_2023_01_01.csv")

День

А вот код! 
utro <- "2023-01-01 11:00:00"
ve4er <- "2023-01-01 17:00:00"

library(lubridate)

den_df <- monitoring_df_w_dist %>% 
  filter(ymd_hms(tm) > ymd_hms(utro) & ymd_hms(tm) < ymd_hms(ve4er))%>% 
  group_by(router_id) %>% 
  summarise(vel = median(velocity))

write.csv2(den_df, file = "data/den_2023_01_01.csv")

Вечер

А вот код! 
ve4er <- "2023-01-01 17:00:00"

library(lubridate)

ve4er_df <- monitoring_df_w_dist %>% 
  filter(ymd_hms(tm) > ymd_hms(ve4er))%>% 
  group_by(router_id) %>% 
  summarise(vel = median(velocity))

write.csv2(ve4er_df, file = "data/ve4er_2023_01_01.csv")