Categories
Database Development

How to optimize selection of pairs from one column of the table (self-join)?

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?

Leave an answer

Your email address will not be published. Required fields are marked *