窗口函数互引用字段实现：累计积分消费SQL查询方案

方法一：递归CTE（直观递推实现） #

递归CTE适配这种行结果相互依赖的场景，能按年份顺序逐步计算各字段：

WITH RECURSIVE yearly_points AS (
    -- 基础查询：给数据按年份排序并加序号
    SELECT 
        year,
        points,
        ROW_NUMBER() OVER (ORDER BY year) AS rn
    FROM test
),
recursive_calc AS (
    -- 递归起点：第一行使用初始积分100计算
    SELECT 
        year,
        points,
        100 AS available,
        LEAST(points, 100) AS consumed,
        100 - LEAST(points, 100) AS remaining,
        rn
    FROM yearly_points
    WHERE rn = 1

    UNION ALL

    -- 递归步骤：用上一行的剩余积分作为当前行的可用积分
    SELECT 
        y.year,
        y.points,
        r.remaining AS available,
        LEAST(y.points, r.remaining) AS consumed,
        r.remaining - LEAST(y.points, r.remaining) AS remaining,
        y.rn
    FROM yearly_points y
    JOIN recursive_calc r ON y.rn = r.rn + 1
)
SELECT year, points, available, consumed, remaining
FROM recursive_calc
ORDER BY year;

方法二：用SUM() OVER()实现（无需递归） #

可以通过计算累计消费上限实现，核心逻辑是用累计值推导各字段：

WITH yearly_stats AS (
    SELECT 
        year,
        points,
        -- 截至当前年份的累计请求积分
        SUM(points) OVER (ORDER BY year) AS total_requested,
        -- 截至当前年份的实际累计消费（不超过初始100）
        LEAST(SUM(points) OVER (ORDER BY year), 100) AS total_consumed,
        -- 上一年的实际累计消费
        LAG(LEAST(SUM(points) OVER (ORDER BY year), 100), 1, 0) OVER (ORDER BY year) AS total_consumed_prev
    FROM test
)
SELECT 
    year,
    points,
    -- 当前可用积分 = 初始100 - 上一年累计消费（最低为0）
    GREATEST(100 - total_consumed_prev, 0) AS available,
    -- 当前实际消费 = 当前累计消费 - 上一年累计消费
    total_consumed - total_consumed_prev AS consumed,
    -- 剩余积分 = 初始100 - 当前累计消费（最低为0）
    GREATEST(100 - total_consumed, 0) AS remaining
FROM yearly_stats
ORDER BY year;

原SQL问题说明 #

你之前的SQL无法执行，是因为字段依赖顺序逻辑错误：available依赖remaining，但remaining又依赖available和consumed，而LATERAL子查询的执行顺序无法满足这种递推关系，且同一查询层中LAG(remaining)无法获取还未计算出的remaining值，导致报错。

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