PostgreSQL 统计近30天各用户每日仅计1次的登录天数
单日去重的近30天登录次数统计实现
核心实现逻辑:将登录时间截断到自然日粒度后,对同一用户的不同自然日做去重计数,单用户的统计值最大为30,完全匹配需求
最简洁的单用户统计写法
不需要引入复杂窗口函数,直接用COUNT(DISTINCT)聚合即可,对应你之前的查询逻辑修改如下:
SELECT COUNT(DISTINCT DATE(datetime)) AS valid_login_days FROM dating.logins WHERE userid = 1000000000 AND datetime >= NOW() - INTERVAL '30 DAY';
逻辑说明:
DATE(datetime)会把带时分秒的登录时间截断为纯日期值(如2020-12-08),COUNT(DISTINCT ...)会对同一个用户的重复日期去重,单日哪怕产生上百条登录记录,也只会被统计1次。
- 注意:时间过滤建议把原来的
datetime > NOW() - INTERVAL '30 DAY'调整为datetime >= NOW() - INTERVAL '30 DAY',避免漏掉刚好卡在30天整的边界登录记录,保证统计范围覆盖完整的30天。
批量统计所有用户的写法
之前认为GROUP BY无法满足需求,本质是没有把统计粒度对齐到“用户+自然日”。如果需要一次查询拿到所有用户的近30天有效登录天数,直接加分组逻辑即可:
SELECT userid, COUNT(DISTINCT DATE(datetime)) AS valid_login_days FROM dating.logins WHERE datetime >= NOW() - INTERVAL '30 DAY' GROUP BY userid;
需要保留原始记录的窗口函数实现场景
如果查询需要保留全量登录明细,同时附带每个用户的累计有效登录天数,就可以配合窗口函数实现单日去重,示例代码:
WITH distinct_login_records AS ( -- 按用户+自然日分区打行号,同一用户同一天的多条记录仅保留1条标记 SELECT pid, userid, datetime, ROW_NUMBER() OVER ( PARTITION BY userid, DATE(datetime) ) AS daily_rn FROM dating.logins WHERE datetime >= NOW() - INTERVAL '30 DAY' ) SELECT pid, userid, datetime, -- 计算当前用户在近30天内的总有效登录天数 COUNT(CASE WHEN daily_rn = 1 THEN 1 END) OVER ( PARTITION BY userid ) AS user_valid_login_days FROM distinct_login_records;
结果验证
按照你提供的样本数据,用户1000000000对应的不同自然日登录记录共有8天(2020-12-08、12-09、12-10、12-11、12-12、12-13、12-14、12-15),即使12月15日当天有3条重复登录记录,最终统计的有效登录天数就是8,完全符合需求。
内容的提问来源于stack exchange,提问作者Adam
相关产品推荐
相关产品推荐

