基于计算值生成累计成本总额的SQL实现问题

问题原因 #

你遇到的ERROR 3593是因为窗口函数不能直接嵌套在聚合函数（比如SUM()）内部使用。必须先通过子查询或CTE（公共表表达式）计算出每条记录的单条成本，再基于这个中间结果做聚合或累计计算。

解决方案 #

以下是适配MySQL 8.0.35和SQL Server的分步实现：

1. 先计算每条记录的单条成本（核心步骤）

首先通过CTE获取每条记录的下一条事件时间，再根据start/stop动作计算单条成本：

MySQL版本

WITH event_costs AS (
    SELECT
        *,
        -- 获取下一条事件的时间，按事件发生时间排序
        LEAD(event_time) OVER (ORDER BY event_time) AS next_event_time,
        -- 计算单条记录的成本
        CASE
            WHEN Action = 'start' THEN
                ResourceCount * HardwareUnitCost * TIMESTAMPDIFF(SECOND, event_time, LEAD(event_time) OVER (ORDER BY event_time))
            WHEN Action = 'stop' THEN
                -1 * ResourceCount * HardwareUnitCost * TIMESTAMPDIFF(SECOND, event_time, LEAD(event_time) OVER (ORDER BY event_time))
        END AS single_cost
    FROM clouddata
    -- 如果有多个集群，需要按集群分区，添加PARTITION BY cluster_id到OVER子句中
)

SQL Server版本

WITH event_costs AS (
    SELECT
        *,
        -- 获取下一条事件的时间，按事件发生时间排序
        LEAD(event_time) OVER (ORDER BY event_time) AS next_event_time,
        -- 计算单条记录的成本
        CASE
            WHEN Action = 'start' THEN
                ResourceCount * HardwareUnitCost * DATEDIFF(SECOND, event_time, LEAD(event_time) OVER (ORDER BY event_time))
            WHEN Action = 'stop' THEN
                -1 * ResourceCount * HardwareUnitCost * DATEDIFF(SECOND, event_time, LEAD(event_time) OVER (ORDER BY event_time))
        END AS single_cost
    FROM clouddata
    -- 如果有多个集群，需要按集群分区，添加PARTITION BY cluster_id到OVER子句中
)

2. 计算累计成本总额或逐行累计值

基于上面的CTE，你可以选择：

• 获取总成本总额：

SELECT SUM(single_cost) AS total_cumulative_cost
FROM event_costs
-- 过滤掉最后一条记录（因为没有下一条事件，single_cost为NULL）
WHERE single_cost IS NOT NULL;

• 获取逐行累计成本（每一条事件后的累计总额）：

SELECT
    event_time,
    Action,
    ResourceCount,
    single_cost,
    SUM(single_cost) OVER (ORDER BY event_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_total
FROM event_costs
WHERE single_cost IS NOT NULL
ORDER BY event_time;

关键注意事项 #

• 替换event_time为表中实际记录事件发生时间的字段名。
• 如果你的日志是按集群区分的，需要在LEAD()和SUM()的OVER子句中添加PARTITION BY cluster_id（替换为实际集群标识字段），确保计算是针对单个集群的。
• 最后一条记录因为没有下一条事件，next_event_time会是NULL，对应的single_cost也会是NULL，所以需要用WHERE single_cost IS NOT NULL过滤，避免影响计算结果。如果最后一条事件需要特殊处理（比如计算到当前时间），可以用COALESCE(LEAD(event_time) OVER(...), NOW())（MySQL）或COALESCE(LEAD(event_time) OVER(...), GETDATE())（SQL Server）替换LEAD()部分。

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