I have a table where one row is – user’s name, place he attended, time when he was there
I need to select all pairs of places where any user was (if user was at place a and place b i need row like this: user, place a, place b, time at place a, time at place b)
I’m trying this script:
select t.user_name ,t.place_name as r1_place ,max(t.tm) as r1_tm ,t2.place_name as r2_place ,min(t2.tm) as r2_tm from schema.table as t join schema.table as t2 on t.user_name = t2.user_name and t.tm < t2.tm and t.place_name <> t2.place_name where t.tm between '2020-03-25 00:00:00' and '2020-03-25 15:00:00' and t2.tm between '2020-03-25 00:00:00' and '2020-03-25 15:00:00' group by t.user_name, t.place_name, t2.place_name
Seems like it gives me the right result, but it works really slow.
Can I optimize it somehow?