如何在SQLite视图中实现Cb、Cc列的动态递归计算？

问题说明 #

现有SQLite表Test，包含日期Date、列Ca、待计算列Cb和Cc。初始数据中仅第一行有Cc值，其余行Cb和Cc为NULL，需按照以下递归规则动态计算：

• Cb = 上一行的Cc值 * 当前行的Ca值
• Cc = 上一行的Cc值 + 当前行的Cb值

输入初始数据结构：

需要生成适配任意行数的动态视图，输出符合规则的计算结果。

核心解决方案：递归CTE创建视图 #

SQLite支持在视图中使用递归CTE，这是实现动态递推计算的最优方案——视图会实时根据源表数据更新，无需提前存储计算结果。

创建计算视图的SQL代码 #

CREATE VIEW TestCalculated AS
WITH RECURSIVE rec_calc AS (
    -- 递归基例：取初始行（Cc非空的行）
    SELECT 
        Date,
        Ca,
        Cb,
        Cc,
        ROW_NUMBER() OVER (ORDER BY Date) AS rn
    FROM Test
    WHERE Cc IS NOT NULL

    UNION ALL

    -- 递归步骤：关联下一行，计算Cb和Cc
    SELECT
        t.Date,
        t.Ca,
        ROUND(r.Cc * t.Ca, 1) AS Cb, -- 按示例保留1位小数，可按需调整
        ROUND(r.Cc + (r.Cc * t.Ca), 1) AS Cc,
        r.rn + 1 AS rn
    FROM rec_calc r
    JOIN (
        SELECT 
            Date,
            Ca,
            ROW_NUMBER() OVER (ORDER BY Date) AS rn
        FROM Test
    ) t ON r.rn + 1 = t.rn
)
SELECT Date, Ca, Cb, Cc FROM rec_calc ORDER BY Date;

代码逻辑说明 #

1. 递归基例：筛选出初始数据行，同时用ROW_NUMBER()按日期生成行号，保证递推顺序与日期一致。
2. 递归步骤：将递归结果与源表的行号关联，每次用上一行的Cc值计算当前行的Cb和Cc，用ROUND()匹配示例的小数精度。
3. 视图最终输出按日期排序的完整计算结果，查询时会自动适配源表的行数变化。

验证结果 #

查询视图TestCalculated即可得到符合预期的结果：

替代方案：触发器维护计算列（针对大表性能优化） #

如果源表数据量极大，递归CTE视图的查询性能无法满足需求，可通过触发器在数据插入/更新时实时计算并存储Cb和Cc值，以写入开销换取查询速度。

触发器示例代码 #

-- 初始化初始行（若未设置）
UPDATE Test SET Cb = NULL, Cc = 100.0 WHERE Date = '2020-01-01';

-- 插入新行时自动计算
CREATE TRIGGER trg_test_insert AFTER INSERT ON Test
BEGIN
    WITH prev_row AS (
        SELECT Cc FROM Test WHERE Date < NEW.Date ORDER BY Date DESC LIMIT 1
    )
    UPDATE Test
    SET 
        Cb = (SELECT Cc FROM prev_row) * NEW.Ca,
        Cc = (SELECT Cc FROM prev_row) + ((SELECT Cc FROM prev_row) * NEW.Ca)
    WHERE Date = NEW.Date;
END;

-- 更新Ca值时，重新计算当前行及后续所有行
CREATE TRIGGER trg_test_update AFTER UPDATE OF Ca ON Test
BEGIN
    WITH RECURSIVE rec_update AS (
        SELECT 
            Date,
            Ca,
            (SELECT Cc FROM Test WHERE Date < NEW.Date ORDER BY Date DESC LIMIT 1) * NEW.Ca AS Cb,
            (SELECT Cc FROM Test WHERE Date < NEW.Date ORDER BY Date DESC LIMIT 1) + ((SELECT Cc FROM Test WHERE Date < NEW.Date ORDER BY Date DESC LIMIT 1) * NEW.Ca) AS Cc,
            ROW_NUMBER() OVER (ORDER BY Date) AS rn
        FROM Test WHERE Date = NEW.Date

        UNION ALL

        SELECT
            t.Date,
            t.Ca,
            ROUND(r.Cc * t.Ca, 1) AS Cb,
            ROUND(r.Cc + (r.Cc * t.Ca), 1) AS Cc,
            r.rn + 1 AS rn
        FROM rec_update r
        JOIN (
            SELECT 
                Date,
                Ca,
                ROW_NUMBER() OVER (ORDER BY Date) AS rn
            FROM Test
        ) t ON r.rn + 1 = t.rn
    )
    UPDATE Test
    SET Cb = rec_update.Cb, Cc = rec_update.Cc
    FROM rec_update WHERE Test.Date = rec_update.Date;
END;

此方案适合数据写入频率低、查询频率高的场景，计算结果直接存储在表中，无需视图实时计算。

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