关于获取历史某一时刻符合某种条件的数据查询问题

Question

获取历史某一时间点/时间段符合 某种条件的数据查询问题

我们有个用户表(users), 用户有个状态(status)字段, 用户的状态可以在A,P,X,S,S,C 等切换.

建了一张表,存储用户切换的log, 大致是这样的:

现在的需求是: 查询在 2018-12-01日,所有为 A状态的用户.

要实现这个需求,得获取在 2018-12-01 之前的,所有用户的最后一条记录是切换为A的记录, 查询过于繁复,特别的是还涉及到连表情况.

想知道,如何从 表设计(重新设计数据表,可以多建表)上面优化,让这种查询能比较方便.

Answer 1

• 基本思路：按时间段从表a（原始表）查询数据，之后使用group by 按user分组统计，并获得用户在此时间段内最大的id值（假设id字段是自增，并能代表用户最后插入数据的标识，如不行就改用created时间字段），得到中间查询结果/表b：user_id,max(id);之后用max（id）关联回原表，查询出 b.user_id,b.max(id),a.new_status,并过滤最终a.new_status为A的结果
• 实现了按时间段查询，自然也就涵盖了按时间点查询
• 如果中间还需考虑性能问题（假设数据量已经大到已经成为性能问题的程度），建议：使用临时表保存原始表中，需要满足查询条件的字段及时间段内数据，必要时还可以对临时表加索引

示例：

CREATE TEMPORARY TABLE tmp_table(
    select id,user_id,new_status from tableA created bwtween "2018-12-01" and "2018-12-02"
);

##省去加索引环节

select a.id_max,a.user_id,b.new_status from 
   (select user_id,max(id) as id_max from tmp_table group by user_id) a ,tmp_table b
where a.id_max = b.id and b.new_status = 'A';

[and a.user_id=b.user_id 如果id字段无法能满足唯一标识及自增记录要求，需要引入created时间字段，那此条件需要增加上]

DROP TABLE tmp_table;