strominka

CREATE TABLE sandbox.yurbasov_asana201801221430 AS
WITH pr AS
    (SELECT
        from_unixtime(unix_timestamp(v_time_check, 'dd.MM.yyyy HH:mm:ss'),'yyyy-MM-dd') AS DAY
        , v_regno
        , concat(v_gps_y, '_', v_gps_x) AS camera_id
        , v_gps_y AS camera_x
        , v_gps_x AS camera_y
        , v_time_check
        , lag(v_time_check, 1) OVER (partition BY v_regno ORDER BY v_time_check) AS l_previous_overall
        , lead(v_time_check, 1) OVER (partition BY v_regno ORDER BY v_time_check) AS r_previous_overall
    FROM codd_stg.codd_kafka
    WHERE from_unixtime(unix_timestamp(v_time_check, 'dd.MM.yyyy HH:mm:ss'),'yyyy-MM-dd') >= '2017-07-10'
  AND from_unixtime(unix_timestamp(v_time_check, 'dd.MM.yyyy HH:mm:ss'),'yyyy-MM-dd') <= '2017-12-31'
     ),
    pre AS (
        SELECT DAY
        , v_regno
        , camera_id
        , camera_x
        , camera_y
        , v_time_check
        , l_previous_overall
        , r_previous_overall
        , lag(camera_id, 1) OVER (partition BY v_regno ORDER BY v_time_check) AS l_cam
        , lead(camera_id, 1) OVER (partition BY v_regno ORDER BY v_time_check) AS r_cam
        , lag(camera_x, 1) OVER (partition BY v_regno ORDER BY v_time_check) AS l_cam_x
        , lead(camera_x, 1) OVER (partition BY v_regno ORDER BY v_time_check) AS r_cam_x
        , lag(camera_y, 1) OVER (partition BY v_regno ORDER BY v_time_check) AS l_cam_y
        , lead(camera_y, 1) OVER (partition BY v_regno ORDER BY v_time_check) AS r_cam_y
        FROM pr
        ),
     time_diff AS (
        SELECT
              DAY
            , v_regno
            , camera_id
            , camera_x
            , camera_y
            , v_time_check
            , abs(round((unix_timestamp(v_time_check) - unix_timestamp(l_previous_overall))/60, 0)) AS t_diff_prev
            , abs(round((unix_timestamp(v_time_check) - unix_timestamp(r_previous_overall))/60, 0)) AS t_diff_next
            , l_cam
            , r_cam
            , l_cam_x
            , r_cam_x
            , l_cam_y
            , r_cam_y
        FROM pre
         ),
     filtered_car AS(
        SELECT
              DAY
            , v_regno
            , camera_id
            , camera_x
            , camera_y
            , v_time_check
            , l_cam
            , r_cam
            , l_cam_x
            , r_cam_x
            , l_cam_y
            , r_cam_y
            , t_diff_prev
            , t_diff_next
    FROM time_diff
    WHERE (camera_id IN ('55.79499_37.70351',
                            '55.79515_37.703358') AND r_cam IN('55.79255_37.68877',
                                                                '55.789967_37.68305​') AND t_diff_next >= 0 )
         OR ((camera_x >= 55.79499 - 0.02 AND camera_x <= 55.79499 + 0.02
         AND camera_y >= 37.70351 - 0.02 AND camera_y <= 37.70351 + 0.02
         OR camera_x >= 55.79515 - 0.02 AND camera_x <= 55.79515 + 0.02
         AND camera_y >= 37.703358 - 0.02 AND camera_y <= 37.703358 + 0.02) AND
         (r_cam_x >= 55.79255 - 0.02 AND r_cam_x <= 55.79255 + 0.02
         AND r_cam_y >= 37.68877 - 0.02 AND r_cam_y <= 37.68877 + 0.02
         OR r_cam_x >= 55.789967 - 0.02 AND r_cam_x <= 55.789967 + 0.02
         AND r_cam_y >= 37.68305 - 0.02 AND r_cam_y <= 37.68305 + 0.02)) AND t_diff_next >= 0 ),
afin AS (
    SELECT v_regno, COUNT(*) AS cnt
    FROM filtered_car
    GROUP BY v_regno
    ), f AS (
SELECT v_regno
FROM afin
WHERE cnt >= 1 -- 16
), par 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
), extra AS (
SELECT
    f.v_regno AS v_regno,
    sec.regno_hash AS regno_hash,
    sec.regno AS regno
FROM f
JOIN secret.regno AS sec ON f.v_regno = sec.regno_hash
-- WHERE (t_diff_prev >= 300 OR t_diff_next >= 300)
GROUP BY
v_regno, regno_hash, regno
)
SELECT
    extra.regno,
    par.phoneno
FROM extra
JOIN par ON extra.regno = par.carno
WHERE par.carno IS NOT NULL
GROUP BY
extra.regno,
par.phoneno