如何实现Foursquare的“市长”功能功能 - 找到最近 N 天内得分最高的用户？

Question

在 Foursquare 中，过去 N 天内在某个地点得分最高的用户将被授予该地点的市长职位。

实现这一目标最有效的方法是什么？

用户可能已经检查了数百个地方。要显示某个用户所属的所有市长职位，需要将这数百个地点一一遍历，并查看每个地点最近60天内他的得分是否最高，这听起来非常低效。

是否有任何 SQL 或算法魔法可以快速执行该任务？

更新：我正在使用 MySQL 和 Django

Answer 1

我会将“当前专业”保留在位置表中，并不时更新。示例（我不知道数据模型是否正确）：

drop table place;
create table place(name varchar(20) primary key, major varchar(20));
insert into place values('NY', null), ('LA', null);
create index idx_p_m on place(major);

drop table visits;
create table visits(user varchar(20), place varchar(20), points int, day int);
create index idx_v_p on visits(place, day desc);
insert into visits values
  ('Ben', 'NY', 1, 100), 
  ('Ben', 'LA', 3, 102), 
  ('Joe', 'NY', 2, 103), 
  ('Joe', 'LA', 1, 104);

-- just to prove this is efficient  
explain  select user from visits v where v.place = 'NY' 
  and day > 90
  group by user 
  order by sum(points) desc 
  limit 1;

update place p set major = 
  (select user from visits v where p.name = v.place 
  and day > 90
  group by user 
  order by sum(points) desc 
  limit 1);

select * from place where major = 'Joe';
select * from place where name = 'LA';