Oracle SQL:检查给定日期的活跃用户数量(检查分组字段最近的日期)

Question

给出一个保存用户状态历史记录的表:

<表类> ID USERID 修改 状态 tbody><<tr>1101.01.2020的2101.07.2020活动3204.08.2020活动4204.06.2020活动5201.08.2020的6201.10.2020活动7301.09.2020的

您可以使用row_number()来获取该日期的最后状态:

select count(*)
from (select t.*,
row_number() over (partition by userid order by modified desc) as seqnum
from my_table t
where t.modified <= date '2020-07-01'
) t
where seqnum = 1 and status = 'Active';

另一个选项是关联子查询:

select count(*)
from my_table t
where t.modified = (select max(t2.modified)
from my_table t2
where t2.userid = t.userid and
t2.modified <= date '2020-07-01'
) and
t.status = 'Active';

或者，您可以使用两个级别的聚合:

select count(*)
from (select userid,
max(status) keep (dense_rank first order by modified desc) as status
from my_table t
where t.modified <= date '2020-07-01'
group by userid
) t
where status = 'Active';

由于您正在查找与提供的日期最接近的活动用户计数，因此以下操作可以工作。

select count(distinct userid) 
from table
where modified <= '01.07.2020'
and status='Active'