auto_krem

 SET hive.tez.container.SIZE = 6656
;

 DROP TABLE SANDBOX.TIM_DE22_RADIUS_00
;

 DROP TABLE SANDBOX.TIM_DE22_RADIUS_01
;

 DROP TABLE SANDBOX.TIM_DE22_RADIUS_02
;

 DROP TABLE SANDBOX.TIM_DE22_RADIUS_91
;

 DROP TABLE SANDBOX.TIM_DE22_RADIUS_92
;

 DROP TABLE SANDBOX.TIM_DE22_RADIUS_03
;

 DROP TABLE SANDBOX.TIM_DE22_RADIUS_04
;

 DROP TABLE SANDBOX.TIM_DE22_RADIUS_05
;

 DROP TABLE SANDBOX.TIM_DE22_RADIUS_06
;

 DROP TABLE SANDBOX.TIM_DE22_RADIUS_07
;

 DROP TABLE SANDBOX.TIM_DE22_RADIUS_08
;

 DROP TABLE SANDBOX.TIM_DE22_RADIUS_09
;

 DROP TABLE SANDBOX.TIM_DE22_RADIUS_10
;

 DROP TABLE SANDBOX.TIM_AUTO_KREM
;

 SET hivevar:START_DAY='2018-03-01'
;

 SET hivevar:END_DAY='2018-04-30'
;

 CREATE TABLE SANDBOX.TIM_DE22_RADIUS_00
AS
SELECT 55.725147 AS v_gps_x, 37.38045 AS v_gps_y, 1 AS k
;


 CREATE TABLE SANDBOX.TIM_DE22_RADIUS_01
AS
SELECT
  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,
  0 AS x_key,
  0 AS y_key,
  0.00113274 AS delta_north,
  0.00090098 AS delta_east,
  -0.00113274 AS delta_south,
  -0.00090098 AS delta_west,
  v_time_check
FROM codd_data.codd codd_kafka
WHERE DAY <= '2018-08-01'
AND DAY >= '2018-08-16'
AND PMOD(DATEDIFF(from_unixtime(unix_timestamp(v_time_check, 'dd.MM.yyyy HH:mm:ss'),'yyyy-MM-dd'), '2012-01-01'), 7) IN (0, 1, 2,3,4, 5, 6)
;


 CREATE TABLE SANDBOX.TIM_DE22_RADIUS_02
AS
SELECT 55.748596 AS v_gps_x, 37.624393 AS v_gps_y, 1 AS x_k, 1 AS y_k, 'Мост' AS c
;


 CREATE TABLE SANDBOX.TIM_DE22_RADIUS_91
AS
SELECT DAY,
  v_regno,
  camera_x,
  camera_y,
  INT(camera_x / delta_east / 10) AS x_key_zero,
  INT(camera_y / delta_north / 10) AS y_key_zero,
  INT(camera_x / delta_east / 10) + 1 AS x_key_plus,
  INT(camera_y / delta_north / 10) + 1 AS y_key_plus,
  INT(camera_x / delta_east / 10) - 1 AS x_key_minus,
  INT(camera_y / delta_north / 10) - 1 AS y_key_minus,
  delta_north,
  delta_east,
  delta_south,
  delta_west,
  v_time_check
FROM SANDBOX.TIM_DE22_RADIUS_01 one
;


 CREATE TABLE SANDBOX.TIM_DE22_RADIUS_92
AS
SELECT v_gps_x, v_gps_y, c,
INT(v_gps_x / 0.00090098 / 10) AS  x_k,
INT(v_gps_y / 0.00113274 / 10) AS  y_k
FROM SANDBOX.TIM_DE22_RADIUS_02 two
;


 CREATE TABLE SANDBOX.TIM_DE22_RADIUS_03
AS
SELECT '00' AS xx,
  v_regno,
  camera_x, camera_y, v_gps_x, v_gps_y, c,
  v_time_check, DAY
FROM SANDBOX.TIM_DE22_RADIUS_91 base
 JOIN SANDBOX.TIM_DE22_RADIUS_92 tgt1
 ON (x_k = x_key_zero  AND y_k = y_key_zero )
UNION ALL SELECT '0+' AS xx,
  v_regno,
  camera_x, camera_y, v_gps_x, v_gps_y, c,
  v_time_check, DAY
FROM SANDBOX.TIM_DE22_RADIUS_91 base
 JOIN SANDBOX.TIM_DE22_RADIUS_92 tgt1
 ON (x_k = x_key_zero  AND y_k = y_key_plus )
UNION ALL SELECT '++' AS xx,
  v_regno,
  camera_x, camera_y, v_gps_x, v_gps_y, c,
  v_time_check, DAY
FROM SANDBOX.TIM_DE22_RADIUS_91 base
 JOIN SANDBOX.TIM_DE22_RADIUS_92 tgt1
 ON (x_k = x_key_plus  AND y_k = y_key_plus )
UNION ALL SELECT '+0' AS xx,
  v_regno,
  camera_x, camera_y, v_gps_x, v_gps_y, c,
  v_time_check, DAY
FROM SANDBOX.TIM_DE22_RADIUS_91 base
 JOIN SANDBOX.TIM_DE22_RADIUS_92 tgt1
 ON (x_k = x_key_plus  AND y_k = y_key_zero )
UNION ALL SELECT '+-' AS xx,
  v_regno,
  camera_x, camera_y, v_gps_x, v_gps_y, c,
  v_time_check, DAY
FROM SANDBOX.TIM_DE22_RADIUS_91 base
 JOIN SANDBOX.TIM_DE22_RADIUS_92 tgt1
 ON (x_k = x_key_plus  AND y_k = y_key_minus)
UNION ALL SELECT '0-' AS xx,
  v_regno,
  camera_x, camera_y, v_gps_x, v_gps_y, c,
  v_time_check, DAY
FROM SANDBOX.TIM_DE22_RADIUS_91 base
 JOIN SANDBOX.TIM_DE22_RADIUS_92 tgt1
 ON (x_k = x_key_zero  AND y_k = y_key_minus)
UNION ALL SELECT '--' AS xx,
  v_regno,
  camera_x, camera_y, v_gps_x, v_gps_y, c,
  v_time_check, DAY
FROM SANDBOX.TIM_DE22_RADIUS_91 base
 JOIN SANDBOX.TIM_DE22_RADIUS_92 tgt1
 ON (x_k = x_key_minus AND y_k = y_key_minus)
UNION ALL SELECT '-0' AS xx,
  v_regno,
  camera_x, camera_y, v_gps_x, v_gps_y, c,
  v_time_check, DAY
FROM SANDBOX.TIM_DE22_RADIUS_91 base
 JOIN SANDBOX.TIM_DE22_RADIUS_92 tgt1
 ON (x_k = x_key_minus AND y_k = y_key_zero )
UNION ALL SELECT '-+' AS xx,
  v_regno,
  camera_x, camera_y, v_gps_x, v_gps_y, c,
  v_time_check, DAY
FROM SANDBOX.TIM_DE22_RADIUS_91 base
 JOIN SANDBOX.TIM_DE22_RADIUS_92 tgt1
 ON (x_k = x_key_minus AND y_k = y_key_plus )
;


 CREATE TABLE SANDBOX.TIM_DE22_RADIUS_04
AS
SELECT
  v_regno,
  camera_x, camera_y, c,
  v_time_check,
  COUNT(*) AS cnt
FROM SANDBOX.TIM_DE22_RADIUS_03 base
WHERE 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 * 10
GROUP BY v_regno,
         camera_x, camera_y, c,
         v_time_check
;


 CREATE TABLE SANDBOX.TIM_DE22_RADIUS_05
AS
SELECT pre1.v_regno, pre1.cnt, pre1.c FROM SANDBOX.TIM_DE22_RADIUS_04 pre1
;


 CREATE TABLE SANDBOX.TIM_DE22_RADIUS_06
AS
SELECT v_regno, c, cnt
FROM SANDBOX.TIM_DE22_RADIUS_04 f1
 WHERE cnt >= 16
;


 CREATE TABLE SANDBOX.TIM_DE22_RADIUS_07
AS
SELECT
    xxxx1.v_regno AS v_regno
    , sec.regno_hash AS regno_hash
    , sec.regno AS regno
FROM SANDBOX.TIM_DE22_RADIUS_06 xxxx1
 JOIN secret.regno AS sec ON xxxx1.v_regno = sec.regno_hash
 GROUP BY
 v_regno, regno_hash, regno
;


 CREATE TABLE SANDBOX.TIM_DE22_RADIUS_08
AS
SELECT
  extra1.v_regno,
  extra1.regno,
  par.phoneno
FROM SANDBOX.TIM_DE22_RADIUS_07 extra1
 JOIN city_event_data.d_calc0a_regno_x_phoneno par
  ON extra1.v_regno = par.carno
WHERE par.carno IS NOT NULL
GROUP BY
extra1.v_regno, extra1.regno,
par.phoneno
;


 CREATE TABLE SANDBOX.TIM_DE22_RADIUS_09
AS
    SELECT res1.phoneno, res1.regno, res1.v_regno FROM SANDBOX.TIM_DE22_RADIUS_08 res1
;

 CREATE TABLE SANDBOX.TIM_AUTO_KREM
AS
SELECT s.phone, res.regno, res.v_regno FROM SANDBOX.TIM_DE22_RADIUS_09 res JOIN secret.phones s ON s.phone_hash = res.phoneno
;