查询指定日期活跃用户：基于起止日期记录表的SQL优化问题

针对你面临的需求——要准确找出指定日期处于任务进行中的用户（停止任务当天仍视为活跃，启动当天也视为活跃），这里有两种比UNION更高效的优化方案，避免多次扫描表的开销：

方案一：基于聚合函数的状态判断 #

核心思路是计算每个用户在目标日期前的最后一次start和stop时间，通过对比这两个时间来判断用户是否活跃：

-- 替换下面的 '2020-01-19' 为你要查询的目标日期
SELECT status_user_id
FROM (
    SELECT 
        status_user_id,
        MAX(CASE WHEN status_activity = 'start' THEN status_date END) AS last_start,
        MAX(CASE WHEN status_activity = 'stop' THEN status_date END) AS last_stop
    FROM registration_statuses
    WHERE status_date <= '2020-01-19'
    GROUP BY status_user_id
) AS user_status_summary
WHERE 
    -- 最后一次操作是start，且在目标日期前 → 活跃
    (last_start > COALESCE(last_stop, '1900-01-01')) 
    -- 最后一次操作是stop，且stop日期就是目标日期 → 当天仍视为活跃
    OR (last_stop = '2020-01-19');

逻辑说明： #

• 子查询通过聚合得到每个用户截止到目标日期的最新启动和停止时间
• COALESCE(last_stop, '1900-01-01')处理从未停止过的用户（此时last_stop为NULL，用一个早于所有可能日期的值代替）
• 满足以下任一条件则用户活跃：
  1. 最后一次启动时间晚于最后一次停止时间（说明当前处于启动状态）
  2. 最后一次停止时间恰好是目标日期（停止当天仍算活跃）

方案二：基于窗口函数的区间判断 #

利用LEAD窗口函数找到每条start记录对应的下一个状态变更时间，判断目标日期是否落在活跃区间内：

-- 替换下面的 '2020-01-19' 为你要查询的目标日期
WITH user_status_sequence AS (
    SELECT 
        status_user_id,
        status_activity,
        status_date,
        -- 获取当前记录的下一条状态变更日期（按用户分组、日期排序）
        LEAD(status_date) OVER (PARTITION BY status_user_id ORDER BY status_date) AS next_status_date
    FROM registration_statuses
)
SELECT DISTINCT status_user_id
FROM user_status_sequence
WHERE 
    status_activity = 'start'
    AND status_date <= '2020-01-19'
    -- 目标日期在当前start的活跃区间内：要么没有后续stop，要么下一个stop日期≥目标日期
    AND (next_status_date IS NULL OR next_status_date >= '2020-01-19');

逻辑说明： #

• 窗口函数LEAD为每条start记录关联其后续的第一个状态变更日期（通常是stop日期）
• 只要目标日期在start_date到next_status_date的区间内（包含两端），或者没有后续stop（用户一直处于启动状态），则用户视为活跃

为什么这两个方案更高效？ #

相比UNION合并两次查询的方式，这两个方案都只需要对表进行一次扫描（聚合或窗口函数计算），减少了IO开销，在数据量较大的场景下性能提升会更明显。

内容的提问来源于stack exchange，提问作者Philip