如何对class列中COMPLETE区间的from_value和to_value求和？

• 对class列中每两次COMPLETE记录之间的from_value和to_value求和，不能仅按location分组（同一location可能重复出现）。
• 示例：ID为7的COMPLETE记录与ID为16的COMPLETE记录之间的多条MATCH记录，需将其from_value求和为9、to_value求和为7，并将该结果对应到ID为7的COMPLETE记录。

with obj1 as 
(
    select 
        class, user_id, location, from_value, to_value, id, 
        id1 = lag (id, 1) over (order by id)
    from 
        table_0 
    where 
        class = 'COMPLETE' 
)
select 
    loc.user_id, loc.location, 
    sum(cast(loc.from_value as int)) as F_value, 
    sum(cast(loc.to_value as int)) as T_value, 
    loc.ID
from
    table_0 loc
left join 
    obj1 on loc.user_id = obj1.user_id 
         and loc.location = obj1.location
         and isnumeric(loc.from_value) = 1 
         and isnumeric(loc.to_value) = 1
group by 
    loc.user_id, loc.location, loc.ID

核心思路是为每条非COMPLETE记录标记其所属的上一条COMPLETE记录的ID，以此为分组依据求和，最后关联COMPLETE记录的基础信息。

WITH complete_markers AS (
    -- 为每条记录标记最近的上一条COMPLETE记录ID，作为分组标识
    SELECT 
        *,
        MAX(CASE WHEN class = 'COMPLETE' THEN id END) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM table_0
),
interval_sums AS (
    -- 按group_id分组，对中间非COMPLETE的有效记录求和
    SELECT 
        group_id,
        SUM(CAST(from_value AS INT)) AS total_from,
        SUM(CAST(to_value AS INT)) AS total_to
    FROM complete_markers
    WHERE class != 'COMPLETE'
      AND ISNUMERIC(from_value) = 1
      AND ISNUMERIC(to_value) = 1
    GROUP BY group_id
)
-- 关联COMPLETE记录，输出最终结果
SELECT 
    c.id AS ID,
    c.class AS Class,
    c.user_id AS [User ID],
    c.location AS Location,
    COALESCE(s.total_from, 0) AS From_value,
    COALESCE(s.total_to, 0) AS To_value
FROM table_0 c
LEFT JOIN interval_sums s ON c.id = s.group_id
WHERE c.class = 'COMPLETE'
ORDER BY c.id;

逻辑说明 #

1. complete_markers：通过MAX() OVER()窗口函数，向前遍历记录，为每条记录绑定最近的COMPLETE记录ID作为group_id，确保两次COMPLETE之间的所有记录都归属到前一个COMPLETE的分组中。
2. interval_sums：按group_id分组，计算该区间内所有有效非COMPLETE记录的from_value和to_value总和。
3. 最终关联：将求和结果与COMPLETE记录关联，输出符合预期结构的结果。

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