MySQL计算月度总价：排除每月最后一条记录

需求说明 #

给定包含Date（datetime类型）和Price（数值类型）的数据集，需要按月份统计Price的总和，同时排除每个月的最后一条记录，并输出如下格式的结果：

• 时间范围：该月第一条记录到排除最后一条后的最后一条记录的时间
• 月份标识：缩写月份+两位年份（如Oct-19）
• 总Price：求和后取整

最终MySQL查询语句 #

SELECT
    CONCAT(MIN(ob.Date), ' to ', MAX(ob.Date)) AS time_range,
    DATE_FORMAT(ob.Date, '%b-%y') AS month_label,
    ROUND(SUM(ob.Price)) AS total_price
FROM order_book ob
LEFT JOIN (
    -- 子查询：找出每个月的最后一条记录时间
    SELECT DATE_FORMAT(Date, '%Y-%m') AS month, MAX(Date) AS last_record_time
    FROM order_book
    GROUP BY month
) lr ON DATE_FORMAT(ob.Date, '%Y-%m') = lr.month AND ob.Date = lr.last_record_time
-- 筛选掉每个月的最后一条记录
WHERE lr.last_record_time IS NULL
GROUP BY DATE_FORMAT(ob.Date, '%Y-%m')
ORDER BY month_label;

语句解释 #

1. 子查询lr：按YYYY-MM格式的月份分组，提取每个月的最后一条记录的时间，用于后续排除操作。
2. 左连接筛选：将原表与子查询左连接，通过lr.last_record_time IS NULL过滤掉每个月的最后一条记录。
3. 分组统计：按月份分组后，计算该组的时间范围（最小到最大日期）、格式化月份标签，对Price求和并取整匹配示例结果。
4. 排序：按月份标识排序，保证结果时序正确。

结果验证 #

针对提供的示例数据，执行该语句后将得到与示例一致的结果：

2019-10-09 12:18:00 to 2019-10-29 09:24:00  | Oct-19  | 79727
2019-10-31 15:00:00 to 2019-11-28 15:33:00  | Nov-19  | 203661
2019-11-29 11:00:00 to 2019-12-30 15:00:00  | Dec-19  | 121714

内容的提问来源于stack exchange，提问作者ML-DL-Learner