de560_volokolam_research

DROP TABLE sandbox_yurbasov.de560_volokolam_research_01
;

DROP TABLE sandbox_yurbasov.de560_volokolam_research_02
;

DROP TABLE sandbox_yurbasov.de560_volokolam_research_03
;

DROP TABLE sandbox_yurbasov.de560_volokolam_research_04
;

DROP TABLE sandbox_yurbasov.de560_volokolam_research_05
;

DROP TABLE sandbox_yurbasov.de560_volokolam_research_06
;

DROP TABLE sandbox_yurbasov.de560_volokolam_research_07
;

DROP TABLE sandbox_yurbasov.de560_volokolam_research_08
;

DROP TABLE sandbox_yurbasov.de560_volokolam_research_09
;

DROP TABLE sandbox_yurbasov.de560_volokolam_research_10
;

DROP TABLE sandbox_yurbasov.de560_volokolam_research_11
;

DROP TABLE sandbox_yurbasov.de560_volokolam_research_12
;

DROP TABLE sandbox_yurbasov.de560_volokolam_research
;

CREATE TABLE sandbox_yurbasov.de560_volokolam_research_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 v_time_check, v_regno, v_camera_id,
concat(DAY, "%0", CAST(round((unix_timestamp(v_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
WHERE DAY BETWEEN '2018-10-29' AND '2018-11-19' AND v_direction = 0
AND v_camera_place IN ('Волоколамское ш., д.63, с.5, в центр, МГО № 171, г. Москва'
,'Волоколамское ш., д.80, с.1, из центра, г. Москва'
,'Волоколамское ш., д.7, в центр, г. Москва'
,'Волоколамское ш., д.10, из центра, г. Москва')
;

CREATE TABLE sandbox_yurbasov.de560_volokolam_research_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_research_01
WHERE v_camera_place = 'Волоколамское ш., д.63, с.5, в центр, МГО № 171, г. Москва'
;

CREATE TABLE sandbox_yurbasov.de560_volokolam_research_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_research_01
WHERE v_camera_place = 'Волоколамское ш., д.7, в центр, г. Москва'
;

CREATE TABLE sandbox_yurbasov.de560_volokolam_research_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_research_01
WHERE v_camera_place = 'Волоколамское ш., д.80, с.1, из центра, г. Москва'
;

CREATE TABLE sandbox_yurbasov.de560_volokolam_research_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_research_01
WHERE v_camera_place = 'Волоколамское ш., д.10, из центра, г. Москва'
;

CREATE TABLE sandbox_yurbasov.de560_volokolam_research_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_research_02
JOIN sandbox_yurbasov.de560_volokolam_research_03 ON v_regno_2 = v_regno_3
WHERE v_time_check_2 <= v_time_check_3
;

CREATE TABLE sandbox_yurbasov.de560_volokolam_research_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_research_02
JOIN sandbox_yurbasov.de560_volokolam_research_03 ON v_regno_2 = v_regno_3
WHERE v_time_check_2 > v_time_check_3
;

CREATE TABLE sandbox_yurbasov.de560_volokolam_research_08
AS
SELECT * FROM sandbox_yurbasov.de560_volokolam_research_06
UNION ALL
SELECT * FROM sandbox_yurbasov.de560_volokolam_research_07
;

CREATE TABLE sandbox_yurbasov.de560_volokolam_research_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_research_04
JOIN sandbox_yurbasov.de560_volokolam_research_05 ON v_regno_4 = v_regno_5
WHERE v_time_check_4 <= v_time_check_5
;

CREATE TABLE sandbox_yurbasov.de560_volokolam_research_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_research_04
JOIN sandbox_yurbasov.de560_volokolam_research_05 ON v_regno_4 = v_regno_5
WHERE v_time_check_4 > v_time_check_5
;

CREATE TABLE sandbox_yurbasov.de560_volokolam_research_11
AS
SELECT * FROM sandbox_yurbasov.de560_volokolam_research_09
UNION ALL
SELECT * FROM sandbox_yurbasov.de560_volokolam_research_10
;

-- таблицу к формату: "дата", "время", "направление", "количество фиксаций ",
-- где в столбце "направление" вместо местоположения камеры необходимо указать квадрат,
-- которому она принадлежала и направление (в центр, из центра).
CREATE TABLE sandbox_yurbasov.de560_volokolam_research_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_research_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_research_11 GROUP BY part_9, v_case_9
;

CREATE TABLE sandbox_yurbasov.de560_volokolam_research
AS
SELECT * FROM sandbox_yurbasov.de560_volokolam_research_12
;