Oracle SQL统计指定日期活跃用户数：取分组后最接近日期的记录

原来的代码逻辑错误出在KEEP子句的排序规则写反了：要取每个用户不晚于指定日期的最新MODIFIED记录，应该按MODIFIED降序排序后取第一条，而不是升序。

方案1：基于KEEP子句的优化实现 #

SELECT SUM(CASE WHEN STATUS = 'active' THEN 1 ELSE 0 END) AS active_users
FROM MY_TABLE t1
INNER JOIN (
    SELECT MAX(ID) KEEP (DENSE_RANK FIRST ORDER BY MODIFIED DESC) AS record_id
    FROM MY_TABLE
    WHERE MODIFIED <= TO_DATE('01.07.2020', 'DD.MM.YYYY') -- 显式指定日期格式避免隐式转换错误
    GROUP BY USERID
) t2 ON t1.ID = t2.record_id;

注意点： #

• 一定要显式用TO_DATE指定日期格式，避免Oracle会话默认日期格式和输入格式不匹配导致的查询错误
• ORDER BY MODIFIED DESC是核心修正点：降序排列后，FIRST就对应每个用户不晚于指定日期的最新修改记录
• 如果同一个用户同一天有多条修改记录，MAX(ID)会取当天最后生成的那条，符合业务逻辑

方案2：基于窗口函数的更易读实现 #

如果你觉得KEEP语法可读性不高，也可以用ROW_NUMBER窗口函数实现，逻辑更直观：

SELECT SUM(CASE WHEN STATUS = 'active' THEN 1 ELSE 0 END) AS active_users
FROM (
    SELECT 
        USERID, 
        STATUS,
        ROW_NUMBER() OVER (PARTITION BY USERID ORDER BY MODIFIED DESC) AS rn
    FROM MY_TABLE
    WHERE MODIFIED <= TO_DATE('01.07.2020', 'DD.MM.YYYY')
) t
WHERE rn = 1;

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