Auto-Hamovniki-2

CREATE TABLE sandbox.yurbasov_auto_hamovniki2 AS
-- RADIUS = 0.1 km
-- ,acos(sin((90.0 - v_gps_y) * pi() / 180.0) * sin((90.0 - nextgpsy) * pi() / 180.0)
--       * cos(radians(v_gps_x) - radians(nextgpsx)) +
--           cos((90.0 - v_gps_y) * pi() / 180.0) * cos((90.0 - nextgpsy) * pi() / 180.0)) * 6371000 as distance
-- UNITS: meters
-- Низ:
-- 55.722374_37.554565
-- 55.719994_37.565254
-- 55.719994_37.565254
-- 55.72389_37.556355
-- 55.71737_37.573135
-- 55.719307_37.571976
-- Лево:
-- 55.728832_37.55376
-- 55.73025_37.55647
-- 55.732586_37.56022
-- 55.73375_37.563133
-- 55.738377_37.570057
-- 55.74216_37.57295
-- Верх:
-- 55.74254_37.58467
-- 55.73767_37.58789
-- 55.736633_37.589302
-- 55.735397_37.594067
-- 55.735035_37.594757
-- 55.735043_37.595882
-- 55.73404_37.596207
-- Право:
-- 55.73205_37.59539
-- 55.7254_37.589767
-- 55.724308_37.58871
-- 55.7226_37.58692
-- 55.71958_37.58365
-- 55.71746_37.580692
WITH base1
AS (SELECT
  from_unixtime(unix_timestamp(v_time_check, 'dd.MM.yyyy HH:mm:ss'), 'yyyy-MM-dd') AS DAY,
  v_regno,
  CASE WHEN codd_kafka.v_gps_y > 55 THEN codd_kafka.v_gps_y ELSE codd_kafka.v_gps_x END AS camera_x,
  CASE WHEN codd_kafka.v_gps_y > 55 THEN codd_kafka.v_gps_x ELSE codd_kafka.v_gps_y END AS camera_y,
  lead(v_time_check, 1) OVER (partition BY v_regno ORDER BY v_time_check) AS r_previous_overall,
  v_time_check
FROM codd_data.codd codd_kafka
WHERE (from_unixtime(unix_timestamp(v_time_check, 'dd.MM.yyyy HH:mm:ss'),'yyyy-MM-dd') <= '2018-02-28'
AND from_unixtime(unix_timestamp(v_time_check, 'dd.MM.yyyy HH:mm:ss'),'yyyy-MM-dd') >= '2018-01-01')
), tgt1 AS
(
-- Низ:
-- 55.722374_37.554565
-- 55.719994_37.565254
-- 55.719994_37.565254
-- 55.72389_37.556355
-- 55.71737_37.573135
-- 55.719307_37.571976
SELECT 55.722374 AS v_gps_x, 37.554565 AS v_gps_y
UNION ALL SELECT 55.719994 AS v_gps_x, 37.565254 AS v_gps_y
UNION ALL SELECT 55.719994 AS v_gps_x, 37.565254 AS v_gps_y
UNION ALL SELECT 55.72389 AS v_gps_x, 37.556355 AS v_gps_y
UNION ALL SELECT 55.71737 AS v_gps_x, 37.573135 AS v_gps_y
UNION ALL SELECT 55.719307 AS v_gps_x, 37.571976 AS v_gps_y
-- Лево:
-- 55.728832_37.55376
-- 55.73025_37.55647
-- 55.732586_37.56022
-- 55.73375_37.563133
-- 55.738377_37.570057
-- 55.74216_37.57295
UNION ALL SELECT 55.728832 AS v_gps_x, 37.55376 AS v_gps_y
UNION ALL SELECT 55.73025 AS v_gps_x, 37.55647 AS v_gps_y
UNION ALL SELECT 55.732586 AS v_gps_x, 37.56022 AS v_gps_y
UNION ALL SELECT 55.73375 AS v_gps_x, 37.563133 AS v_gps_y
UNION ALL SELECT 55.738377 AS v_gps_x, 37.570057 AS v_gps_y
UNION ALL SELECT 55.74216 AS v_gps_x, 37.57295 AS v_gps_y
-- Верх:
-- 55.74254_37.58467
-- 55.73767_37.58789
-- 55.736633_37.589302
-- 55.735397_37.594067
-- 55.735035_37.594757
-- 55.735043_37.595882
-- 55.73404_37.596207
UNION ALL SELECT 55.74254 AS v_gps_x, 37.58467 AS v_gps_y
UNION ALL SELECT 55.73767 AS v_gps_x, 37.58789 AS v_gps_y
UNION ALL SELECT 55.736633 AS v_gps_x, 37.589302 AS v_gps_y
UNION ALL SELECT 55.735397 AS v_gps_x, 37.594067 AS v_gps_y
UNION ALL SELECT 55.735035 AS v_gps_x, 37.594757 AS v_gps_y
UNION ALL SELECT 55.735043 AS v_gps_x, 37.595882 AS v_gps_y
UNION ALL SELECT 55.73404 AS v_gps_x, 37.596207 AS v_gps_y
-- Право:
-- 55.73205_37.59539
-- 55.7254_37.589767
-- 55.724308_37.58871
-- 55.7226_37.58692
-- 55.71958_37.58365
-- 55.71746_37.580692
UNION ALL SELECT 55.73205 AS v_gps_x, 37.59539 AS v_gps_y
UNION ALL SELECT 55.7254 AS v_gps_x, 37.589767 AS v_gps_y
UNION ALL SELECT 55.724308 AS v_gps_x, 37.58871 AS v_gps_y
UNION ALL SELECT 55.7226 AS v_gps_x, 37.58692 AS v_gps_y
UNION ALL SELECT 55.71958 AS v_gps_x, 37.58365 AS v_gps_y
UNION ALL SELECT 55.71746 AS v_gps_x, 37.580692 AS v_gps_y
), pre1 AS
(SELECT
  v_regno,
  camera_x, camera_y,
  MAX(abs(round((unix_timestamp(v_time_check) - unix_timestamp(r_previous_overall))/60, 0))) AS t_diff_next,
  weekofyear(v_time_check) AS w_no,
  COUNT(*) AS cnt
FROM base1
JOIN tgt1
ON acos(sin((90.0 - v_gps_y) * pi() / 180.0) * sin((90.0 - camera_y) * pi() / 180.0) * cos(radians(v_gps_x) - radians(camera_x))
       + cos((90.0 - v_gps_y) * pi() / 180.0) * cos((90.0 - camera_y) * pi() / 180.0)) * 6371000 <= 100
GROUP BY v_regno,
         camera_x, camera_y,
         weekofyear(v_time_check)),
f1 AS (SELECT pre1.v_regno, pre1.cnt FROM pre1 WHERE  t_diff_next >= 0),
xxxx1
AS (SELECT DISTINCT
  v_regno
FROM f1
WHERE cnt >= 12),
par1
AS (SELECT
  phoneno,
  carno
FROM (SELECT
  phoneno,
  carno,
  MAX(from_unixtime(unix_timestamp(parkingstart, 'dd.MM.yyyy HH:mm:ss'), 'yyyy-MM-dd')) AS max_date,
  COUNT(*) AS qty,
  ROW_NUMBER() OVER (PARTITION BY carno ORDER BY COUNT(*) DESC) AS rn
FROM parking_data.parking
WHERE phoneno != 'NULL'
AND from_unixtime(unix_timestamp(parkingstart, 'dd.MM.yyyy HH:mm:ss'), 'yyyy-MM-dd') >= '2017-01-01'
GROUP BY phoneno,
         carno) xx
WHERE rn = 1), extra1 AS (
SELECT
    xxxx1.v_regno AS v_regno
    , sec.regno_hash AS regno_hash
    , sec.regno AS regno
FROM xxxx1
 JOIN secret.regno AS sec ON xxxx1.v_regno = sec.regno_hash
 GROUP BY
 v_regno, regno_hash, regno
), res1 AS
(SELECT
  extra1.v_regno,
  extra1.regno,
  par1.phoneno
FROM extra1
JOIN par1
  ON extra1.v_regno = par1.carno
WHERE par1.carno IS NOT NULL
GROUP BY
extra1.v_regno, extra1.regno,
par1.phoneno), res AS
(SELECT res1.phoneno, res1.regno, res1.v_regno FROM res1)
SELECT s.phone, res.regno, res.v_regno FROM res JOIN secret.phones s ON s.phone_hash = res.phoneno