Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE sandbox.yurbasov_park_nightparad AS
- -- Необходимо выгрузить автомобилистов кто парковался на парковке 0306 >=6 раз по выходным за последние два месяца
- WITH a AS
- (
- SELECT parkingstart, phoneno AS full_hash, carno FROM parking_data.parking m
- WHERE placecode IN ( '3205', '0304', '0303', '0302', '9005', '3202', '0301', '3201', '9036', '3001', '2101' )
- AND PMOD(DATEDIFF(from_unixtime(unix_timestamp(parkingstart, 'dd.MM.yyyy HH:mm:ss'),'yyyy-MM-dd'), '2012-01-01'), 7) IN (0, 5, 6)
- AND from_unixtime(unix_timestamp(parkingstart, 'dd.MM.yyyy HH:mm:ss'),'yyyy-MM-dd') >= '2018-03-01'
- AND from_unixtime(unix_timestamp(parkingstart, 'dd.MM.yyyy HH:mm:ss'),'yyyy-MM-dd') <= '2018-04-31'
- AND (from_unixtime(unix_timestamp(parkingstart, 'dd.MM.yyyy HH:mm:ss'),'HH:mm:ss') >= '21:00:00'
- AND from_unixtime(unix_timestamp(parkingstart, 'dd.MM.yyyy HH:mm:ss'),'HH:mm:ss') <= '23:59:59'
- OR from_unixtime(unix_timestamp(parkingstart, 'dd.MM.yyyy HH:mm:ss'),'HH:mm:ss') >= '00:00:00'
- AND from_unixtime(unix_timestamp(parkingstart, 'dd.MM.yyyy HH:mm:ss'),'HH:mm:ss') <= '03:00:00')
- ), b AS
- (
- SELECT COUNT(a.parkingstart) AS cnt, a.full_hash, a.carno FROM a GROUP BY full_hash, carno
- ), c AS
- (
- SELECT cnt, full_hash, carno, s.phone FROM b JOIN secret.phones s ON s.phone_hash = b.full_hash WHERE b.cnt >= 6
- ), res AS
- (
- SELECT cnt, carno, phone FROM c JOIN secret.regno s ON s.regno_hash = c.carno
- ) SELECT * FROM res
Add Comment
Please, Sign In to add comment