MySQL按90天间隔统计唯一device_id的查询修正需求

需要在MySQL中统计指定日期间隔内的唯一device_id数量：

• 仅统计该时间段内有传输记录的设备，同一设备多次传输仅计1次
• 时间区间结束日期为当月月末，起始日期为结束日期往前推90天
• 无法使用存储过程，不能用declare/set定义日期
• 当前查询得到的是不符合预期的30天周期结果，且未正确统计唯一设备数

原查询错误结果：

期望的正确统计结果（Excel手动验证）：

原SQL代码：

SELECT 
    date_sub(LAST_DAY(t.create_date), interval 90 day ) as first_day,
    LAST_DAY(t.create_date) as last_day
from transmission t
left outer join device d
on d.device_id=t.device_id
left outer join clinic_billing cb 
on d.clinic_id = cb.clinic_id 
WHERE t.create_date>=('2021-01-01') and
(cast(t.create_date as date) <=LAST_DAY(t.create_date) and 
cast(t.create_date as 
 date)>=date_sub(LAST_DAY(t.create_date), 
 interval 90 day ))
 and t.type like ('%Remote%')
 and ((cb.do_not_start_billing_before_date is not null 
 and t.dos >= cb.do_not_start_billing_before_date)
 or cb.do_not_start_billing_before_date is null)  

原SQL存在核心问题：

1. 未使用COUNT(DISTINCT d.device_id)统计唯一设备数，仅输出了日期区间
2. 未按日期区间分组，导致每条传输记录都生成重复的区间行
3. 日期过滤条件冗余：cast(t.create_date as date) <= LAST_DAY(t.create_date)恒成立，无需额外判断

方案1：MySQL 8.0+ 递归CTE生成月末日期区间 #

利用递归CTE生成需要统计的所有月末日期，再基于每个月末日期计算90天前的起始日期，最后关联传输表统计符合条件的唯一设备数：

WITH monthly_end_dates AS (
    -- 起始月末日期，可按需调整
    SELECT LAST_DAY('2021-11-01') AS end_date
    UNION ALL
    SELECT LAST_DAY(end_date + INTERVAL 1 MONTH)
    FROM monthly_end_dates
    WHERE end_date < LAST_DAY('2022-06-01') -- 结束月末日期，可按需调整
)
SELECT
    DATE_SUB(m.end_date, INTERVAL 90 DAY) AS 起始日期,
    m.end_date AS 结束日期,
    COUNT(DISTINCT t.device_id) AS 设备数量
FROM monthly_end_dates m
LEFT JOIN transmission t
    ON CAST(t.create_date AS DATE) BETWEEN DATE_SUB(m.end_date, INTERVAL 90 DAY) AND m.end_date
    AND t.type LIKE '%Remote%'
LEFT JOIN device d ON t.device_id = d.device_id
LEFT JOIN clinic_billing cb ON d.clinic_id = cb.clinic_id
WHERE
    t.create_date >= '2021-01-01'
    AND (
        cb.do_not_start_billing_before_date IS NULL
        OR t.dos >= cb.do_not_start_billing_before_date
    )
GROUP BY m.end_date
ORDER BY m.end_date;

方案2：低版本MySQL（无CTE）手动生成月末日期 #

如果你的MySQL版本不支持递归CTE，直接用UNION ALL手动列出需要统计的月末日期：

SELECT
    DATE_SUB(m.end_date, INTERVAL 90 DAY) AS 起始日期,
    m.end_date AS 结束日期,
    COUNT(DISTINCT t.device_id) AS 设备数量
FROM (
    SELECT '2021-11-30' AS end_date UNION ALL
    SELECT '2021-12-31' UNION ALL
    SELECT '2022-01-31' UNION ALL
    SELECT '2022-02-28' UNION ALL
    SELECT '2022-03-31' UNION ALL
    SELECT '2022-04-30' UNION ALL
    SELECT '2022-05-31' UNION ALL
    SELECT '2022-06-30'
) m
LEFT JOIN transmission t
    ON CAST(t.create_date AS DATE) BETWEEN DATE_SUB(m.end_date, INTERVAL 90 DAY) AND m.end_date
    AND t.type LIKE '%Remote%'
LEFT JOIN device d ON t.device_id = d.device_id
LEFT JOIN clinic_billing cb ON d.clinic_id = cb.clinic_id
WHERE
    t.create_date >= '2021-01-01'
    AND (
        cb.do_not_start_billing_before_date IS NULL
        OR t.dos >= cb.do_not_start_billing_before_date
    )
GROUP BY m.end_date
ORDER BY m.end_date;

1. 日期区间生成：通过固定的月末日期列表，确保每个统计周期的结束日期为当月月末，起始日期为月末往前推90天
2. 唯一设备统计：使用COUNT(DISTINCT t.device_id)实现同一设备多次传输仅计1次
3. 过滤条件优化：保留原业务过滤逻辑（t.type、clinic_billing日期限制），同时用CAST(t.create_date AS DATE)避免时间部分干扰日期匹配

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