如何用SQL按间隔天数统计购买用户数及按天数区间分组统计

1. 按间隔天数统计购买用户数 #

直接通过分组统计间隔天数对应的去重用户数，为了匹配示例中0天计数为0的要求，我们先定义需要展示的天数列表，再通过左关联统计：

WITH user_diff AS (
    SELECT DISTINCT 
        c.EMAIL, 
        DATEDIFF(day,c.action_date,c.ORDER_CREATED_DATE) as diff
    FROM Customers c
),
-- 此处可根据实际需要调整要展示的天数列表
target_days AS (
    SELECT 0 AS day
    UNION ALL SELECT 2
    UNION ALL SELECT 18
    UNION ALL SELECT 60
)
SELECT 
    t.day,
    COUNT(DISTINCT u.EMAIL) AS email_count
FROM target_days t
LEFT JOIN user_diff u ON t.day = u.diff
GROUP BY t.day
ORDER BY t.day

如果不需要固定展示没有数据的天数，直接分组统计即可：

SELECT 
    diff AS day,
    COUNT(DISTINCT EMAIL) AS email_count
FROM (
    SELECT DISTINCT 
        c.EMAIL, 
        DATEDIFF(day,c.action_date,c.ORDER_CREATED_DATE) as diff
    FROM Customers c
) t
GROUP BY diff
ORDER BY diff

2. 按间隔天数区间统计购买用户数 #

先定义需要统计的区间规则，再通过左关联实现无数据区间计数为0，完全匹配示例输出：

WITH user_diff AS (
    SELECT DISTINCT 
        c.EMAIL, 
        DATEDIFF(day,c.action_date,c.ORDER_CREATED_DATE) as diff
    FROM Customers c
),
-- 此处可根据实际需要调整统计区间规则
stat_ranges AS (
    SELECT '0-7' AS day, 0 AS min_diff, 7 AS max_diff
    UNION ALL SELECT '7-14', 7, 14
    UNION ALL SELECT '14-28', 14, 28
)
SELECT 
    s.day,
    COUNT(DISTINCT u.EMAIL) AS email_count
FROM stat_ranges s
LEFT JOIN user_diff u ON u.diff >= s.min_diff AND u.diff < s.max_diff
GROUP BY s.day
ORDER BY s.min_diff

以上SQL兼容大部分支持CTE语法的数据库（MySQL 8.0+、PostgreSQL、SQL Server等），如果使用不支持CTE的低版本数据库，把CTE部分改写为子查询即可正常运行。

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