Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH filtered AS (
- 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
- JOIN utrack.driver d ON d.id = p.driver_id
- JOIN utrack."user" u ON u.id = d.user_id
- WHERE
- u.uuid='621-663-840' and
- (cast(null AS timestamp) IS NULL OR p.point_time >= CAST(null AS timestamp))
- AND (cast(null AS timestamp) IS NULL OR p.point_time <= CAST(null AS timestamp))
- ),
- with_percentage AS (
- SELECT
- filtered.*,
- uuid = lead(uuid) OVER (ORDER BY uuid, point_time DESC) AS user_group,
- (EXTRACT('epoch' FROM point_time - lead(point_time) OVER (ORDER BY uuid, point_time DESC)) - h * 60 ) / (h * 6 ) * 10 AS percentage
- FROM filtered WHERE NOT (h IS NULL OR h = 0)
- ORDER BY point_time )
- SELECT uuid, id, point_time, provider, is_network_available, server_time, h, lat, lng, percentage from with_percentage
- WHERE
- NOT user_group OR percentage between -99999999 and 99999999
- ORDER BY uuid, point_time;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement