You need to enable JavaScript to run this app. PostgreSQL 统计近30天各用户每日仅计1次的登录天数
优惠活动
大模型
产品
解决方案
定价
更多

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.13 11:36:04