Advertisement
Guest User

Untitled

a guest
Oct 12th, 2018
80
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 1.03 KB | None | 0 0
  1. WITH filtered AS (
  2. SELECT u.uuid, p.id, p.point_time, p.provider, p.is_network_available, p.server_time, p.h, p.lat, p.lng FROM utrack.point p
  3.     JOIN utrack.driver d ON d.id = p.driver_id
  4.     JOIN utrack."user" u ON u.id = d.user_id
  5.     WHERE  
  6.         u.uuid='621-663-840' and
  7.         (cast(null AS timestamp) IS NULL OR p.point_time >= CAST(null AS timestamp))
  8.         AND (cast(null AS timestamp) IS NULL OR p.point_time <= CAST(null AS timestamp))
  9. ),
  10. with_percentage AS (
  11.     SELECT  
  12.         filtered.*,
  13.         uuid = lead(uuid) OVER (ORDER BY uuid, point_time DESC) AS user_group,
  14.         (EXTRACT('epoch' FROM point_time - lead(point_time) OVER (ORDER BY uuid, point_time DESC)) - h * 60 ) / (h * 6 ) * 10 AS percentage
  15.     FROM filtered WHERE NOT (h IS NULL OR h = 0)
  16.     ORDER BY point_time )
  17. SELECT uuid, id, point_time, provider, is_network_available, server_time, h, lat, lng, percentage from with_percentage
  18. WHERE  
  19.     NOT user_group OR percentage between -99999999 and 99999999
  20. ORDER BY uuid, point_time;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement