MySQL实现指定时间范围按日期分组的产品状态统计查询方案

原有单日查询语句 #

with X as (
select l.*,
(select status_from from logs where logs.refno = l.refno and logs.logtime >= '2021-10-01' order by logs.logtime limit 1) logstat
from listings l
where l.added_date <= '2021-10-01'  
)
, Y as (select X.*, ifnull(X.logstat, X.status) stat from X)
SELECT
sum(case when status.text= 'Action' and Y.id is not null then 1 else 0 end) as `Action`,
sum(case when status.text= 'Draft' and Y.id is not null then 1 else 0 end) as `Draft`,
sum(case when status.text= 'Let' and Y.id is not null then 1 else 0 end) as `Let`,
sum(case when status.text= 'Sold' and Y.id is not null then 1 else 0 end) as `Sold`,
sum(case when status.text= 'Publish' and Y.id is not null then 1 else 0 end) as `Publish`
from status
left join Y on Y.stat = status.code

原有逻辑说明：

• logs表记录产品所有状态变更记录，不包含初始录入数据
• listings表存储产品当前最新状态和入库时间
• 原有语句仅支持单日统计，多日查询需要逐天执行，效率较低

优化后支持日期范围查询语句 #

-- 自定义统计起止日期
SET @start_date = '2021-10-01';
SET @end_date = '2021-10-03';

WITH RECURSIVE dates AS (
    -- 生成统计范围内所有日期
    SELECT @start_date AS stat_date
    UNION ALL
    SELECT DATE_ADD(stat_date, INTERVAL 1 DAY) FROM dates WHERE stat_date < @end_date
),
daily_product_stat AS (
    -- 计算每个日期下所有产品的对应状态
    SELECT 
        d.stat_date,
        l.id,
        IFNULL((
            SELECT status_from FROM logs 
            WHERE logs.refno = l.refno AND logs.logtime >= d.stat_date 
            ORDER BY logs.logtime LIMIT 1
        ), l.status) AS stat
    FROM dates d
    INNER JOIN listings l ON l.added_date <= d.stat_date
)
-- 按日期聚合统计各状态数量
SELECT 
    dps.stat_date AS `日期`,
    SUM(CASE WHEN s.text = 'Publish' AND dps.id IS NOT NULL THEN 1 ELSE 0 END) AS `Publish`,
    SUM(CASE WHEN s.text = 'Action' AND dps.id IS NOT NULL THEN 1 ELSE 0 END) AS `Action`,
    SUM(CASE WHEN s.text = 'Let' AND dps.id IS NOT NULL THEN 1 ELSE 0 END) AS `Let`,
    SUM(CASE WHEN s.text = 'Sold' AND dps.id IS NOT NULL THEN 1 ELSE 0 END) AS `Sold`,
    SUM(CASE WHEN s.text = 'Draft' AND dps.id IS NOT NULL THEN 1 ELSE 0 END) AS `Draft`
FROM status s
LEFT JOIN daily_product_stat dps ON dps.stat = s.code
GROUP BY dps.stat_date
ORDER BY dps.stat_date;

预期输出结果 #

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