基于权重阈值的连续Segment行分组优化方案咨询

Great question! Cursors get the job done but can be slow and cumbersome with large datasets—here's a clean, set-based solution using recursive CTEs that avoids looping entirely, and it’ll perform much better for bigger workloads.

How It Works #

The core idea is to use a recursive CTE to iterate through each segment in order, dynamically building groups as we go:

• Anchor Member: Start with the first segment, initializing the first group.
• Recursive Member: For each subsequent segment, check if adding its weight to the current group’s total exceeds your threshold. If it does, start a new group; otherwise, extend the current group.
• Final Aggregation: Group the results by the generated group ID to get the min/max segment and total weight for each group.

Full Code Implementation #

SET NOCOUNT ON;
DROP TABLE IF EXISTS #tmpIncoming;
CREATE TABLE #tmpIncoming (
 [Segment] int NOT NULL,
 [Weight] int NOT NULL
);
INSERT INTO #tmpIncoming VALUES ( 1, 25), ( 2, 45), ( 3, 20), ( 4, 30), ( 5, 50), ( 6, 21), ( 7, 110);
DECLARE @nMaxChunkSize int = 100;

-- CTE-based solution
WITH SegmentsOrdered AS (
    -- Assign row numbers to process segments in order
    SELECT 
        Segment, 
        Weight,
        ROW_NUMBER() OVER (ORDER BY Segment) AS RowNum
    FROM #tmpIncoming
),
RecursiveGroups AS (
    -- Anchor: initialize first group with the first segment
    SELECT 
        RowNum,
        Segment AS MinSegment,
        Segment AS MaxSegment,
        Weight AS TotalWeight,
        1 AS GroupID
    FROM SegmentsOrdered
    WHERE RowNum = 1

    UNION ALL

    -- Recursive step: build groups incrementally
    SELECT 
        so.RowNum,
        -- Start new group if adding current weight exceeds threshold
        CASE WHEN rg.TotalWeight + so.Weight > @nMaxChunkSize THEN so.Segment ELSE rg.MinSegment END,
        so.Segment AS MaxSegment,
        -- Update total weight or reset for new group
        CASE WHEN rg.TotalWeight + so.Weight > @nMaxChunkSize THEN so.Weight ELSE rg.TotalWeight + so.Weight END,
        -- Increment group ID if starting new group
        CASE WHEN rg.TotalWeight + so.Weight > @nMaxChunkSize THEN rg.GroupID + 1 ELSE rg.GroupID END
    FROM RecursiveGroups rg
    JOIN SegmentsOrdered so ON so.RowNum = rg.RowNum + 1
)
-- Aggregate to get final group results
SELECT 
    MIN(MinSegment) AS MinSegment,
    MAX(MaxSegment) AS MaxSegment,
    MAX(TotalWeight) AS Weight
FROM RecursiveGroups
GROUP BY GroupID
ORDER BY GroupID;

DROP TABLE IF EXISTS #tmpIncoming;

Key Advantages Over Cursor #

• Set-Based Logic: SQL is optimized for set operations, so this will run faster than a cursor, especially with large datasets.
• Readability: The logic is more declarative and easier to follow than looping code.
• Maintainability: Less code to debug and modify if your grouping rules change.

This solution will produce exactly the same result as your cursor-based approach, but with better performance and cleaner code.

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