mysql - 检索具有每组最新更改的记录

Question

我有一张表位置:

user | timestamp | state | other_data
-------------------------------------
1      100         1       some_data
1      200         1       some_data
1      300         0       some_data
1      400         0       some_data
2      100         0       some_data
2      200         0       some_data

这是一个位置跟踪应用程序。一个位置有两个 state(1 表示“用户在范围内”，0 表示“用户超出范围”)。

现在我想检索用户位置状态最后一次更改的时间。user = 1

示例

user | timestamp | state | other_data
-------------------------------------
1      300         0       some_data

因为这是第一个具有与“当前”(时间戳 400)记录相同的 state 值的位置更新。

更高级别的描述:我想向用户显示类似“自 [timestamp] 以来您一直在/超出范围”的内容

当然越快越好

Answer 1

我会使用排名对行进行排序，然后选择排名第一的行的 min 时间戳。

select user,min(timestamp) as timestamp,min(state) as state
from 
(select l.*,@rn:=case when @user=user and @state=state then @rn
                      when @user<>user then 1
                 else @rn+1 end as rnk
 ,@user:=user,@state:=state
 from locations l 
 cross join (select @rn:=0,@user:='',@state:='') r
 order by user,timestamp desc
) t
where rnk=1
group by user