SQL查询（MAX-MIN）结果异常，求含refills的每日消费计算方案

看起来你遇到了两个常见的计量数据计算问题：MAX-MIN的局限性，以及如何处理补录（refill）场景下的每日消耗量计算。让我一步步帮你解决：

为什么MAX(Content)-MIN(Content)会返回异常结果？ #

这个问题的核心是MAX-MIN只适用于Content全天持续下降、无补录的场景：

当数据中存在补录（Content突然上升）时，MAX会取到补录后的高值，MIN取到当天某个低点，两者的差值会包含补录的量，而非实际消耗量。比如某天Content从100跳到200，之后降到198，MAX-MIN会算出101，但实际消耗只有(100-之前的低值)+(200-198)，结果完全偏离预期。

只有像你给出的示例那样，Content全程下降时，MAX-MIN才等于「当天初始值-最终值」的正确消耗量。

处理补录场景的每日消耗量查询 #

我们可以用窗口函数LAG()来获取每条记录的上一条Content值，判断是否属于消耗（当前值<上一条值），再累加当天所有的消耗差值。

假设你的表名为consumption_data，以下是适配MySQL 8+/PostgreSQL/SQL Server等主流数据库的查询：

WITH daily_with_prev AS (
    SELECT
        Name,
        -- 根据你的数据库调整日期转换函数：
        -- MySQL: DATE(DateTime)
        -- SQL Server: CAST(DateTime AS DATE)
        -- PostgreSQL: DATE_TRUNC('day', DateTime)::DATE
        DATE(DateTime) AS record_date,
        Content,
        -- 按名称+日期分组、时间排序，获取上一条记录的Content
        LAG(Content) OVER (PARTITION BY Name, DATE(DateTime) ORDER BY DateTime) AS prev_content
    FROM consumption_data
)
SELECT
    Name,
    record_date,
    -- 只累加实际消耗的差值：当前值小于上一条时，计算消耗
    SUM(CASE WHEN prev_content > Content THEN prev_content - Content ELSE 0 END) AS daily_consumption
FROM daily_with_prev
GROUP BY Name, record_date
ORDER BY Name, record_date;

用你的示例数据测试，结果会是：

完全符合你的预期。

如果你的数据库不支持CTE（比如MySQL 5.x），可以用子查询替代：

SELECT
    Name,
    record_date,
    SUM(CASE WHEN prev_content > Content THEN prev_content - Content ELSE 0 END) AS daily_consumption
FROM (
    SELECT
        Name,
        DATE(DateTime) AS record_date,
        Content,
        LAG(Content) OVER (PARTITION BY Name, DATE(DateTime) ORDER BY DateTime) AS prev_content
    FROM consumption_data
) AS sub_query
GROUP BY Name, record_date
ORDER BY Name, record_date;

额外说明 #

• 如果某天只有一条记录，daily_consumption会返回0，符合「无消耗变化」的逻辑；
• 这个查询只计算当天内的消耗变化，如果需要跨天关联前一天的最终值（比如当天第一条记录是补录），可以调整窗口函数的分组规则，有需要可以再细化逻辑。

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