如何编写SQL查询获取多组10天区间内登录的唯一用户？

针对你需要从user_login表中提取多个日期区间（每个目标日期D及其前10天）内的唯一用户的需求，原有的UNION ALL写法在日期数量大时会非常冗余且性能低下，这里提供两种更高效的优化方案：

方案1：处理连续日期区间 #

如果你的目标日期是一个连续的范围（比如从2024-01-01到2024-01-30的所有日期），可以用递归公共表表达式（CTE）生成日期序列，再关联用户表：

WITH target_dates AS (
    -- 起始日期
    SELECT DATE '2024-01-01' AS target_day
    UNION ALL
    -- 递归生成后续日期，直到结束日期
    SELECT target_day + INTERVAL 1 DAY
    FROM target_dates
    WHERE target_day < DATE '2024-01-30'
)
-- 关联用户表，筛选符合区间的记录并去重
SELECT DISTINCT 
    td.target_day,  -- 如果需要按目标日期分组展示用户，保留此字段
    ul.user_id
FROM target_dates td
JOIN user_login ul
ON ul.login_date BETWEEN td.target_day - INTERVAL 10 DAY AND td.target_day;

如果只需要所有区间合并后的唯一用户（不区分属于哪个日期区间），可以去掉td.target_day，仅保留ul.user_id。

方案2：处理离散指定日期 #

如果你的目标日期是分散的特定日期（比如2024-01-01、2024-01-15、2024-02-01等），直接在CTE中列出所有目标日期即可：

WITH target_dates AS (
    SELECT DATE '2024-01-01' AS target_day UNION ALL
    SELECT DATE '2024-01-15' UNION ALL
    SELECT DATE '2024-02-01' UNION ALL
    SELECT DATE '2024-03-10'  -- 继续添加所有需要的目标日期
)
SELECT DISTINCT 
    td.target_day,
    ul.user_id
FROM target_dates td
JOIN user_login ul
ON ul.login_date BETWEEN td.target_day - INTERVAL 10 DAY AND td.target_day;

关键注意事项 #

• 区间顺序修正：原查询中BETWEEN 'day1' AND ('day1' - INTERVAL 10 DAY)的参数顺序错误，因为day1比day1-10天大，多数数据库要求BETWEEN的左值≤右值，正确的写法是将更早的日期（目标日期减10天）放在前面。
• 性能提升：这种写法只需扫描一次user_login表，相比多个UNION ALL子查询重复扫描表的方式，性能提升明显，尤其是数据量较大时。
• 数据库兼容：递归CTE支持MySQL 8.0+、PostgreSQL、SQL Server等主流数据库；如果是PostgreSQL，还可以用generate_series函数更简洁地生成日期序列：

SELECT DISTINCT ul.user_id
FROM generate_series(
    DATE '2024-01-01',
    DATE '2024-01-30',
    INTERVAL '1 day'
) AS td(target_day)
JOIN user_login ul
ON ul.login_date BETWEEN td.target_day - INTERVAL '10 days' AND td.target_day;

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