In my code below, I have commented out the lines which I believe are redundant ordering clauses. I have checked the results (row values) with and without commenting, and the results are the same. I was just wondering if there is ANY scenario where the two order by's that I have commented out in the second and fourth CTE are not really redundant.
speed_dataset as (
select uc_id, imei, points_geom, time_created, st_distance((points_geom::geography),lag(points_geom::geography) over (partition by imei order by time_created))
/ nullif(( EXTRACT(EPOCH FROM time_created) - EXTRACT(EPOCH FROM LAG(time_created) OVER(PARTITION BY imei ORDER BY time_created)))::FLOAT8,0) as speed
from orig_dataset
order by imei,time_created
)
,
subset_speed as (
select uc_id, ROW_NUMBER() OVER (ORDER BY (time_created)) AS row_id, speed, imei,points_geom ,time_created
from speed_dataset sd
where speed < 0.1 or speed between 0.75 and 2
--order by time_created
)
,
leading_speeds as (
select *,lead (speed) over (partition by imei order by time_created) as lead_speed from subset_speed
)
,
subset_cr as (
select * from leading_speeds
where
(
(speed < 0.1 and lead_speed between 0.75 and 2)
or
(speed between 0.75 and 2 and lead_speed < 0.1)
)
--order by imei,time_created
)
,
clustering as(
SELECT uc_id,row_id,imei, speed, points_geom ,time_created, ST_ClusterDBSCAN(st_transform(points_geom,24313),eps := 150, minPoints := 3)
OVER(ORDER BY row_id) AS cluster_id FROM subset_cr
)
Your intuition is right. As a rule, you should never have an ORDER BY in a CTE or a view definition unless you use it in conjunction with DISTINCT ON (..) or LIMIT/FETCH FIRST ... ROWS ONLY.