PostgreSQL：按用户分区计算迭代累积乘积的RESULTADO字段查询

需求说明 #

现有包含ESTADO、USUARIO_ID、EJERCICIO_MES、TIEMPO字段的数据集，需要新增RESULTADO字段，计算规则如下：

1. 当ESTADO为ABIERTA时，RESULTADO等于TIEMPO的值；
2. 按USUARIO_ID分区后首次出现ESTADO为CERRADA时，RESULTADO为该用户所有ABIERTA行TIEMPO的累积和乘以10；
3. 后续出现ESTADO为CERRADA时，RESULTADO为（该用户ABIERTA行TIEMPO累积和 + 此前所有CERRADA行RESULTADO之和）乘以10。

实现查询语句 #

由于后续CERRADA行的计算依赖之前的结果，这里使用递归CTE处理累积逻辑，结合窗口函数预计算用户的ABIERTA总时长：

WITH usuario_abierta_total AS (
    -- 预计算每个用户所有ABIERTA行的TIEMPO总和
    SELECT 
        USUARIO_ID,
        SUM(CASE WHEN ESTADO = 'ABIERTA' THEN TIEMPO ELSE 0 END) AS total_abierta
    FROM tu_tabla
    GROUP BY USUARIO_ID
),
cerrada_rows AS (
    -- 给每个用户的CERRADA行按顺序编号
    SELECT 
        t.*,
        uat.total_abierta,
        ROW_NUMBER() OVER (PARTITION BY t.USUARIO_ID ORDER BY t.EJERCICIO_MES) AS cerrada_seq
    FROM tu_tabla t
    JOIN usuario_abierta_total uat ON t.USUARIO_ID = uat.USUARIO_ID
    WHERE t.ESTADO = 'CERRADA'
),
recursive_cerrada AS (
    -- 递归起始：首次CERRADA行
    SELECT 
        USUARIO_ID,
        EJERCICIO_MES,
        ESTADO,
        TIEMPO,
        total_abierta * 10 AS RESULTADO,
        cerrada_seq,
        total_abierta * 10 AS cumulative_cerrada
    FROM cerrada_rows
    WHERE cerrada_seq = 1

    UNION ALL

    -- 递归后续CERRADA行：基于之前的累积结果计算
    SELECT 
        cr.USUARIO_ID,
        cr.EJERCICIO_MES,
        cr.ESTADO,
        cr.TIEMPO,
        (cr.total_abierta + rc.cumulative_cerrada) * 10 AS RESULTADO,
        cr.cerrada_seq,
        rc.cumulative_cerrada + (cr.total_abierta + rc.cumulative_cerrada) * 10 AS cumulative_cerrada
    FROM cerrada_rows cr
    JOIN recursive_cerrada rc ON cr.USUARIO_ID = rc.USUARIO_ID AND cr.cerrada_seq = rc.cerrada_seq + 1
)
-- 合并ABIERTA行和计算后的CERRADA行
SELECT 
    t.USUARIO_ID,
    t.EJERCICIO_MES,
    t.ESTADO,
    t.TIEMPO,
    CASE 
        WHEN t.ESTADO = 'ABIERTA' THEN t.TIEMPO
        ELSE rc.RESULTADO
    END AS RESULTADO
FROM tu_tabla t
LEFT JOIN recursive_cerrada rc ON t.USUARIO_ID = rc.USUARIO_ID AND t.EJERCICIO_MES = rc.EJERCICIO_MES AND t.ESTADO = rc.ESTADO
ORDER BY t.USUARIO_ID, t.EJERCICIO_MES;

关键逻辑解释 #

• usuario_abierta_total：预计算每个用户所有ABIERTA行的TIEMPO总和，避免重复计算；
• cerrada_rows：筛选出所有CERRADA行，并用ROW_NUMBER()按EJERCICIO_MES排序，标记每个用户的CERRADA行顺序；
• recursive_cerrada：递归CTE，先计算首次CERRADA行的结果，再依次计算后续行——每次基于用户的ABIERTA总时长加上之前所有CERRADA行的结果总和，再乘以10；
• 最后一步合并原始表的ABIERTA行和递归计算后的CERRADA行，得到完整结果集。

注意：请将语句中的tu_tabla替换为你的实际表名；如果EJERCICIO_MES不是合适的排序字段，请替换为能确定行顺序的字段（比如时间戳）。

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