如何对loginData表按小时维度去重统计独立登录用户数

核心逻辑 #

统计目标的本质是对「用户名 + 登录所属的自然小时区间」的二元组合做去重计数：同一用户在同一个连续1小时的时间窗口内，无论产生多少条登录记录，都仅计1次有效登录。

实现步骤 #

• 先将表中存储的带AM/PM标识的字符串格式登录时间，解析为数据库可识别的标准时间类型
• 将解析后的时间截断到整小时粒度（忽略分钟、秒的差异，同一小时内的所有时间映射为同一个唯一标识）
• 对「用户名 + 截断后的小时标识」的组合做去重，统计去重后的总条数即为目标值。

可落地SQL示例 #

根据不同数据库的函数差异，可直接使用以下写法：

• MySQL 适配写法（兼容带AM/PM的字符串时间格式）：

SELECT COUNT(DISTINCT 
    userName, 
    DATE_FORMAT(STR_TO_DATE(timestamp, '%Y-%m-%d %h:%i:%s %p'), '%Y-%m-%d %H:00:00')
) AS userLoggedIN
FROM loginData

• PostgreSQL 适配写法：

SELECT COUNT(DISTINCT 
    ROW(userName, DATE_TRUNC('hour', TO_TIMESTAMP(timestamp, 'YYYY-MM-DD HH:MI:SS AM')))
) AS userLoggedIN
FROM loginData

• SQL Server 适配写法：

SELECT COUNT(DISTINCT 
    CONCAT(userName, '|', 
        DATEADD(HOUR, DATEDIFF(HOUR, 0, TRY_CONVERT(datetime, timestamp, 109)), 0)
    )
) AS userLoggedIN
FROM loginData

注：上述直接对多字段做DISTINCT计数的写法，和“先通过子查询按「用户名+截断后小时」维度完成去重、再统计子查询返回行数”的写法逻辑完全等价，可根据对应数据库的执行计划选择性能更优的实现方式。

结果匹配验证 #

对照示例的统计规则：

1. 同一小时内重复登录的2条abc记录，去重后仅计1次
2. def的独立登录记录计1次
3. abcde的独立登录记录计1次
   最终统计结果为3，与预期输出userLoggedIN - 3完全一致。

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