PostgreSQL查询指定日期段内有n个不同天提交记录的用户方案

基础信息 #

表结构 #

• users 表：id 字段类型为uuid，username 字段类型为varchar(255)
• entries 表：id 字段类型为uuid，user_id 字段类型为uuid，inserted_at 字段类型为timestamp

需求说明 #

查询两个指定日期间，累计在n个不同日期存在entries提交记录的用户列表，用户单日多次提交仅计为1天。
示例场景：查询9月1日至9月15日之间有10天存在提交记录的所有用户。

PostgreSQL 专属实现 #

核心逻辑为将时间戳转为日期格式后去重统计，适配PostgreSQL的语法特性：

-- 可替换参数说明：
-- :start_date 统计范围开始日期，示例值 '2024-09-01'
-- :end_date 统计范围结束日期，示例值 '2024-09-15'
-- :target_days 目标达标天数n，示例值 10
SELECT u.id, u.username, COUNT(DISTINCT e.inserted_at::date) AS active_days
FROM users u
INNER JOIN entries e ON u.id = e.user_id
WHERE e.inserted_at >= :start_date
  -- 避免结束日期当天带时间的记录被漏掉
  AND e.inserted_at < (:end_date + INTERVAL '1 day')
GROUP BY u.id, u.username
HAVING COUNT(DISTINCT e.inserted_at::date) = :target_days;

通用SQL实现（兼容多数关系型数据库） #

如果需要适配MySQL、SQL Server等其他数据库，调整日期函数即可，核心逻辑不变：

SELECT u.id, u.username, COUNT(DISTINCT DATE(e.inserted_at)) AS active_days
FROM users u
INNER JOIN entries e ON u.id = e.user_id
WHERE e.inserted_at >= :start_date
  AND e.inserted_at < DATE_ADD(:end_date, INTERVAL 1 DAY)
GROUP BY u.id, u.username
HAVING COUNT(DISTINCT DATE(e.inserted_at)) = :target_days;

注：部分数据库的日期函数存在差异，比如Oracle用TRUNC(e.inserted_at)取日期部分、SQL Server用CAST(e.inserted_at AS DATE)，可根据实际使用的数据库调整对应函数即可。

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