de560_volokolam_radio

DROP TABLE sandbox_yurbasov.de560_volokolam_radio_01
;

DROP TABLE sandbox_yurbasov.de560_volokolam_radio_02
;

DROP TABLE sandbox_yurbasov.de560_volokolam_radio_03
;

DROP TABLE sandbox_yurbasov.de560_volokolam_radio_04
;

DROP TABLE sandbox_yurbasov.de560_volokolam_radio_05
;

DROP TABLE sandbox_yurbasov.de560_volokolam_radio_06
;

DROP TABLE sandbox_yurbasov.de560_volokolam_radio_07
;

DROP TABLE sandbox_yurbasov.de560_volokolam_radio_08
;

DROP TABLE sandbox_yurbasov.de560_volokolam_radio_09
;

DROP TABLE sandbox_yurbasov.de560_volokolam_radio_10
;

DROP TABLE sandbox_yurbasov.de560_volokolam_radio_11
;

DROP TABLE sandbox_yurbasov.de560_volokolam_radio_12
;

DROP TABLE sandbox_yurbasov.de560_volokolam_radio
;

CREATE TABLE sandbox_yurbasov.de560_volokolam_radio_01
-- Период дат: 29.10.2018 – 19.11.2018.
-- Форма представления: Дата (ДД.ММ.ГГГГ)
-- Время (шестичасовые интервалы: 00:00, 06:00, 12:00, 18:00)
-- Направление (от камеры из квадрата №1 к камере в квадрат №2 или наоборот)
-- Кол-во ФВФ на камеру по направлению.
-- Квадрат 1
-- 1) В центр: id = 29312, v_camera_place = 'Волоколамское ш., д.63, с.5, в центр, МГО № 171, г. Москва'
-- 2) Из центра: id = 32912, v_camera_place = 'Волоколамское ш., д.80, с.1, из центра, г. Москва'
-- Квадрат 2
-- 3) В центр: id = 100900440416, v_camera_place = 'Волоколамское ш., д.7, в центр, г. Москва'
-- 4) Из центра: id = 100901100716, v_camera_place = 'Волоколамское ш., д.10, из центра, г. Москва'
AS
SELECT time_check AS v_time_check, regno AS v_regno, camera AS v_camera_id,
concat(DAY, "%0", CAST(round((unix_timestamp(time_check) - unix_timestamp('2018-01-01 00:00:00', 'yyyy-MM-dd hh:mm:ss'))/360) % 4 * 6 AS string), ":00:00") AS part FROM codd_data.codd_radio
WHERE DAY >= '2018-10-29' AND DAY <= '2018-11-19' AND direction = 0
AND camera IN ('PR-29312-1','PR-32912-1', 'PR-100900440416-1','PR-100901100716-1')
;

CREATE TABLE sandbox_yurbasov.de560_volokolam_radio_02
AS
SELECT part AS part_2, v_time_check AS v_time_check_2, v_regno AS v_regno_2, v_camera_id AS v_camera_id_2,
 'В центр' v_case_2 FROM sandbox_yurbasov.de560_volokolam_radio_01
WHERE v_camera_id = 'PR-29312-1'
;

CREATE TABLE sandbox_yurbasov.de560_volokolam_radio_03
AS
SELECT part AS part_3, v_time_check AS v_time_check_3, v_regno AS v_regno_3, v_camera_id AS v_camera_id_3,
 'В центр' v_case_3 FROM sandbox_yurbasov.de560_volokolam_radio_01
WHERE v_camera_id = 'PR-100900440416-1'
;

CREATE TABLE sandbox_yurbasov.de560_volokolam_radio_04
AS
SELECT part AS part_4, v_time_check AS v_time_check_4, v_regno AS v_regno_4, v_camera_id AS v_camera_id_4,
 'Из центра' v_case_4 FROM sandbox_yurbasov.de560_volokolam_radio_01
WHERE v_camera_id = 'PR-32912-1'
;

CREATE TABLE sandbox_yurbasov.de560_volokolam_radio_05
AS
SELECT part AS part_5, v_time_check AS v_time_check_5, v_regno AS v_regno_5, v_camera_id AS v_camera_id_5,
 'Из центра' v_case_5 FROM sandbox_yurbasov.de560_volokolam_radio_01
WHERE v_camera_id = 'PR-100901100716-1'
;

CREATE TABLE sandbox_yurbasov.de560_volokolam_radio_06
AS
SELECT part_2 AS part_6, v_time_check_2 AS v_time_check_6, v_regno_2 AS v_regno_6, v_camera_id_2 AS v_camera_id_6,
 v_case_2 AS v_case_6 FROM sandbox_yurbasov.de560_volokolam_radio_02
JOIN sandbox_yurbasov.de560_volokolam_radio_03 ON v_regno_2 = v_regno_3
WHERE v_time_check_2 <= v_time_check_3
;

CREATE TABLE sandbox_yurbasov.de560_volokolam_radio_07
AS
SELECT part_3 AS part_6, v_time_check_3 AS v_time_check_6, v_regno_3 AS v_regno_6, v_camera_id_3 AS v_camera_id_6,
 v_case_3 AS v_case_6 FROM sandbox_yurbasov.de560_volokolam_radio_02
JOIN sandbox_yurbasov.de560_volokolam_radio_03 ON v_regno_2 = v_regno_3
WHERE v_time_check_2 > v_time_check_3
;

CREATE TABLE sandbox_yurbasov.de560_volokolam_radio_08
AS
SELECT * FROM sandbox_yurbasov.de560_volokolam_radio_06
UNION ALL
SELECT * FROM sandbox_yurbasov.de560_volokolam_radio_07
;

CREATE TABLE sandbox_yurbasov.de560_volokolam_radio_09
AS
SELECT part_4 AS part_9, v_time_check_4 AS v_time_check_9, v_regno_4 AS v_regno_9, v_camera_id_4 AS v_camera_id_9
 v_case_4 AS v_case_9 FROM sandbox_yurbasov.de560_volokolam_radio_04
JOIN sandbox_yurbasov.de560_volokolam_radio_05 ON v_regno_4 = v_regno_5
WHERE v_time_check_4 <= v_time_check_5
;

CREATE TABLE sandbox_yurbasov.de560_volokolam_radio_10
AS
SELECT part_5 AS part_9, v_time_check_5 AS v_time_check_9, v_regno_5 AS v_regno_9, v_camera_id_5 AS v_camera_id_9
 v_case_5 AS v_case_9 FROM sandbox_yurbasov.de560_volokolam_radio_04
JOIN sandbox_yurbasov.de560_volokolam_radio_05 ON v_regno_4 = v_regno_5
WHERE v_time_check_4 > v_time_check_5
;

CREATE TABLE sandbox_yurbasov.de560_volokolam_radio_11
AS
SELECT * FROM sandbox_yurbasov.de560_volokolam_radio_09
UNION ALL
SELECT * FROM sandbox_yurbasov.de560_volokolam_radio_10
;

-- таблицу к формату: "дата", "время", "направление", "количество фиксаций ",
-- где в столбце "направление" вместо местоположения камеры необходимо указать квадрат,
-- которому она принадлежала и направление (в центр, из центра).
CREATE TABLE sandbox_yurbasov.de560_volokolam_radio_12
AS
SELECT split(part_6, "%")[0] AS dt, split(part_6, "%")[1] AS tm, v_case_6 AS dir, COUNT(v_time_check_6) AS cnt FROM sandbox_yurbasov.de560_volokolam_radio_08 GROUP BY part_6, v_case_6
UNION ALL
SELECT split(part_9, "%")[0] AS dt, split(part_9, "%")[1] AS tm, v_case_9 AS dir, COUNT(v_time_check_9) AS cnt FROM sandbox_yurbasov.de560_volokolam_radio_11 GROUP BY part_9, v_case_9
;

CREATE TABLE sandbox_yurbasov.de560_volokolam_radio
AS
SELECT * FROM sandbox_yurbasov.de560_volokolam_radio_12
;