MySQL指定时间范围按日分组统计房源状态数据方案咨询

实现方案 #

你可以通过先生成连续日期序列，再关联原有单日期状态计算逻辑，最后行转列统计的方式实现需求，参考SQL如下：

-- 替换为用户输入的起止日期即可，示例：@start_date='2021-10-01', @end_date='2021-10-07'
SET @start_date = '用户输入起始日期', @end_date = '用户输入结束日期';

WITH RECURSIVE date_range AS (
    SELECT @start_date AS stat_date
    UNION ALL
    SELECT DATE_ADD(stat_date, INTERVAL 1 DAY) 
    FROM date_range 
    WHERE stat_date < @end_date
),
daily_status 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 date_range d
    JOIN listings l ON l.added_date < d.stat_date
)
SELECT
    dr.stat_date AS `日期`,
    SUM(CASE WHEN s.text = 'Publish' AND ds.id IS NOT NULL THEN 1 ELSE 0 END) AS `Publish`,
    SUM(CASE WHEN s.text = 'Action' AND ds.id IS NOT NULL THEN 1 ELSE 0 END) AS `Action`,
    SUM(CASE WHEN s.text = 'Let' AND ds.id IS NOT NULL THEN 1 ELSE 0 END) AS `Let`,
    SUM(CASE WHEN s.text = 'Sold' AND ds.id IS NOT NULL THEN 1 ELSE 0 END) AS `Sold`,
    SUM(CASE WHEN s.text = 'Draft' AND ds.id IS NOT NULL THEN 1 ELSE 0 END) AS `Draft`
FROM date_range dr
CROSS JOIN status s
LEFT JOIN daily_status ds ON dr.stat_date = ds.stat_date AND ds.stat = s.code
GROUP BY dr.stat_date
ORDER BY dr.stat_date;

逻辑说明 #

• 递归CTE date_range 用于生成起止日期范围内的所有连续日期，确保无数据的日期也能正常展示
• daily_status 层完全复用你原有的单日期状态计算逻辑，遍历每个日期计算对应日期下所有房源的有效状态
• 最后统计层通过交叉关联状态表，确保每个日期的所有状态都有对应统计值，无数据时自动返回0

兼容说明 #

如果你使用的数据库不支持递归CTE（如MySQL 8.0以下版本），可以提前创建一张日期维度表，替换date_range的递归生成逻辑即可。

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