累计求和与整数值分配：如何计算每行剩余积分分配量

我来帮你搞定这个每行剩余积分分配量的计算问题！先捋清楚咱们的核心需求：要基于audits视图里的每条积分记录，结合已分配积分@allocated和待分配积分@to_allocate，精准算出每行还能分配多少积分，解决你之前只有特定情况才出正确结果的问题。

先明确已知条件 #

• 变量定义：
  • @allocated int：已经分配出去的积分总量
  • @to_allocate int：当前需要完成分配的积分总量
• 视图audits结构：
  • ts：积分收集的日期（默认按时间先后顺序分配积分，这个排序很关键）
  • points：每条记录收集到的积分数量

核心问题分析 #

你之前的代码只在特定情况生效，大概率是没处理好累计积分和分配阈值的边界情况——比如当待分配积分超过剩余可分配的总积分，或者某条记录的积分不足以覆盖剩余待分配量的时候。下面是修正后的完整解决方案：

完整实现代码 #

-- 先清理并重建示例视图（如果你的视图已经存在，可以跳过这部分）
IF OBJECT_ID('audits') IS NOT NULL DROP VIEW audits;
GO
CREATE VIEW audits AS
SELECT ts = CAST('2024-01-01' AS DATE), points = 100
UNION ALL SELECT '2024-01-02', 200
UNION ALL SELECT '2024-01-03', 150;
GO

-- 核心计算逻辑
DECLARE @allocated int = 120; -- 示例：已分配120积分
DECLARE @to_allocate int = 250; -- 示例：当前待分配250积分

WITH audit_with_cumulative AS (
    -- 计算每条记录的累计积分、以及当前记录之前的累计积分
    SELECT 
        ts,
        points,
        -- 从第一条到当前记录的累计积分
        cumulative_points = SUM(points) OVER (ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
        -- 当前记录之前所有记录的累计积分（用来定位已分配积分的覆盖范围）
        prev_cumulative = SUM(points) OVER (ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
    FROM audits
)
SELECT
    ts,
    points,
    -- 计算当前记录剩余的可分配积分（扣除已分配的部分）
    remaining_allocation = CASE
        WHEN @allocated > ISNULL(prev_cumulative, 0) THEN
            -- 已分配积分覆盖到了当前记录，算出剩余未分配的部分
            points - (@allocated - ISNULL(prev_cumulative, 0))
        ELSE
            -- 已分配积分还没到这条记录，全部积分都可分配
            points
        END,
    -- 实际分配给当前记录的积分（不超过待分配总量）
    actual_allocation = CASE
        WHEN @to_allocate <= 0 THEN 0
        WHEN @allocated > ISNULL(prev_cumulative, 0) THEN
            LEAST(points - (@allocated - ISNULL(prev_cumulative, 0)), @to_allocate)
        ELSE
            LEAST(points, @to_allocate)
        END,
    -- 分配完当前记录后，剩余的待分配积分
    remaining_to_allocate = CASE
        WHEN @to_allocate <= 0 THEN 0
        WHEN @allocated > ISNULL(prev_cumulative, 0) THEN
            @to_allocate - LEAST(points - (@allocated - ISNULL(prev_cumulative, 0)), @to_allocate)
        ELSE
            @to_allocate - LEAST(points, @to_allocate)
        END
FROM audit_with_cumulative
ORDER BY ts;

代码逻辑说明 #

1. CTE部分：用窗口函数SUM() OVER()计算累计积分，帮咱们快速定位已分配积分到底覆盖到了哪条记录，这是处理边界情况的关键。
2. 剩余可分配量计算：通过CASE分支判断已分配积分是否覆盖当前记录，分别计算剩余可分配的积分值。
3. 实际分配量计算：用LEAST()函数确保分配的积分不会超过待分配总量，避免出现超分配的情况。
4. 剩余待分配量：实时更新分配后的剩余待分配积分，方便你验证结果是否符合预期。

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