MySQL：基于日期数量的时间序列动态分组实现问询

需求说明：

• 数据中唯一日期总数≤30时，按日分组求和
• 31≤唯一日期总数≤90时，按周分组求和
• 唯一日期总数>90时，按月分组求和

已实现的单独分组查询 #

1. 按日分组 #

SELECT
    dtCreated,
    FROM_DAYS(TO_DAYS(dtCreated) - MOD(TO_DAYS(dtCreated) - 1, 7)) AS week_beginning,
    DATE(DATE_FORMAT(dtCreated, '%Y-%m-01')) AS month_beginning,
    COALESCE(SUM(valToSum), 0) AS soma
FROM (   
    SELECT 
        DATE(dt_created) AS dtCreated,
        valToSum
    FROM MQV_PDV_TICKET
) AS derived_table
GROUP BY dtCreated

2. 按周分组 #

SELECT
    dtCreated,
    FROM_DAYS(TO_DAYS(dtCreated) - MOD(TO_DAYS(dtCreated) - 1, 7)) AS week_beginning,
    DATE(DATE_FORMAT(dtCreated, '%Y-%m-01')) AS month_beginning,
    COALESCE(SUM(valToSum), 0) AS soma
FROM (   
    SELECT 
        DATE(dt_created) AS dtCreated,
        valToSum
    FROM MQV_PDV_TICKET
) AS derived_table
GROUP BY week_beginning

3. 按月分组 #

SELECT
    dtCreated,
    FROM_DAYS(TO_DAYS(dtCreated) - MOD(TO_DAYS(dtCreated) - 1, 7)) AS week_beginning,
    DATE(DATE_FORMAT(dtCreated, '%Y-%m-01')) AS month_beginning,
    COALESCE(SUM(valToSum), 0) AS soma
FROM (   
    SELECT 
        DATE(dt_created) AS dtCreated,
        valToSum
    FROM MQV_PDV_TICKET
) AS derived_table
GROUP BY month_beginning

错误尝试分析 #

直接在GROUP BY的CASE中使用COUNT(*)会触发#1111错误，因为聚合函数的执行顺序晚于分组逻辑计算，无法在分组阶段使用聚合结果：

GROUP BY (CASE 
             WHEN COUNT(*) <= 30 THEN dtCreated
             WHEN COUNT(*) BETWEEN 31 AND 90 THEN week_beginning
             WHEN COUNT(*) > 90 THEN month_beginning
         END)

正确实现方法 #

方法1：预计算日期总数后动态分组 #

通过子查询先统计唯一日期总数，再基于该值选择分组字段：

SELECT
    -- 统一输出分组后的日期维度
    CASE 
        WHEN date_stats.date_count <= 30 THEN derived_table.dtCreated
        WHEN date_stats.date_count BETWEEN 31 AND 90 THEN derived_table.week_beginning
        ELSE derived_table.month_beginning
    END AS group_date,
    COALESCE(SUM(derived_table.valToSum), 0) AS soma
FROM (
    SELECT 
        DATE(dt_created) AS dtCreated,
        FROM_DAYS(TO_DAYS(DATE(dt_created)) - MOD(TO_DAYS(DATE(dt_created)) - 1, 7)) AS week_beginning,
        DATE(DATE_FORMAT(dt_created, '%Y-%m-01')) AS month_beginning,
        valToSum
    FROM MQV_PDV_TICKET
) AS derived_table
-- 交叉连接获取日期总数
CROSS JOIN (
    SELECT COUNT(DISTINCT DATE(dt_created)) AS date_count
    FROM MQV_PDV_TICKET
) AS date_stats
GROUP BY 
    CASE 
        WHEN date_stats.date_count <= 30 THEN derived_table.dtCreated
        WHEN date_stats.date_count BETWEEN 31 AND 90 THEN derived_table.week_beginning
        ELSE derived_table.month_beginning
    END

方法2：使用存储过程实现分支逻辑 #

如果需要更清晰的分支控制，可通过存储过程根据日期数执行对应分组查询：

DELIMITER //
CREATE PROCEDURE dynamic_group_sum()
BEGIN
    DECLARE total_dates INT;
    -- 统计唯一日期总数
    SELECT COUNT(DISTINCT DATE(dt_created)) INTO total_dates FROM MQV_PDV_TICKET;

    IF total_dates <= 30 THEN
        -- 按日分组查询
        SELECT
            DATE(dt_created) AS group_date,
            COALESCE(SUM(valToSum), 0) AS soma
        FROM MQV_PDV_TICKET
        GROUP BY DATE(dt_created);
    ELSEIF total_dates BETWEEN 31 AND 90 THEN
        -- 按周分组查询
        SELECT
            FROM_DAYS(TO_DAYS(DATE(dt_created)) - MOD(TO_DAYS(DATE(dt_created)) - 1, 7)) AS group_date,
            COALESCE(SUM(valToSum), 0) AS soma
        FROM MQV_PDV_TICKET
        GROUP BY group_date;
    ELSE
        -- 按月分组查询
        SELECT
            DATE(DATE_FORMAT(dt_created, '%Y-%m-01')) AS group_date,
            COALESCE(SUM(valToSum), 0) AS soma
        FROM MQV_PDV_TICKET
        GROUP BY group_date;
    END IF;
END //
DELIMITER ;

-- 调用存储过程执行动态分组
CALL dynamic_group_sum();

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