mysql - How to apply intersection grouped with index column in MYSQL

Question

I have a dataset of logged in users on a certain date

__ds__|__user_id__
  1   |  1
  1   |  2
  2   |  2
  2   |  3
  3   |  3
  3   |  1
  3   |  2

i want to perform intersection on user_id over two consecutive dates, to have users who logged in today as well as previous day, so my final out put will be:

__ds__|__user_id__
  2   |  2
  3   |  3
  3   |  2

I have wrote a query as:

    select distinct t1.ds,t1.user_id
    from user_table t1
    where t1.user_id in(
        select distinct user_id
        from user_table
        where ds = t1.ds-1 
    )

I applied distinct two time, which took too long time to run on big dataset. Is there more optimized way?

标签： mysql

Answer 1

我觉得可以是这样

select t2.ds,t2.user_id 
from user_table t1 
join user_table t2 on t1.user_id=t2.user_id and t2.ds=t1.ds+1 

对不对？