PostgreSQL如何基于初始用户与年度变化率计算各年度用户数

你的问题核心是需要基于初始用户数，结合逐年的百分比变化来计算累积的用户数——这本质是一个复利式的累积计算，而你之前的尝试因为窗口函数无法引用同SELECT中的别名，且lag()只能取上一行数据而非累积结果，所以没法实现。下面提供两种高效的解决方案：

方法1：递归CTE（直观易懂，适合复杂逻辑） #

递归CTE非常适合这种需要依赖前一步计算结果的场景，它可以逐步迭代计算每一年的用户数：

WITH RECURSIVE language_user_history AS (
    -- 锚点：获取每个语言的初始用户，作为计算基准
    SELECT
        pl.id AS language_id,
        NULL::integer AS survey_year,
        pl.initial_users AS current_users
    FROM programming_language pl

    UNION ALL

    -- 递归部分：按年份顺序计算每一年的用户数
    SELECT
        ud.language_id,
        ud.survey_year,
        -- 根据增长/减少类型计算当前用户数
        CASE
            WHEN ud.increase_or_decrease THEN luh.current_users * (1 + ud.percent_users_change / 100)
            ELSE luh.current_users * (1 - ud.percent_users_change / 100)
        END AS current_users
    FROM language_user_history luh
    JOIN (
        -- 给每个语言的年份记录添加序号，确保按顺序处理
        SELECT
            *,
            ROW_NUMBER() OVER (PARTITION BY language_id ORDER BY survey_year) AS rn
        FROM usage_data
    ) ud ON ud.language_id = luh.language_id
        -- 每次递归处理下一个序号的记录
        AND ud.rn = COALESCE((SELECT MAX(rn) FROM language_user_history luh2 WHERE luh2.language_id = luh.language_id), 0) + 1
)
-- 过滤掉初始基准行，只显示各年份的计算结果
SELECT 
    language_id, 
    survey_year, 
    ROUND(current_users, 3) AS current_users
FROM language_user_history
WHERE survey_year IS NOT NULL
ORDER BY language_id, survey_year;

逻辑说明： #

1. 锚点成员：先获取所有编程语言的初始用户数，作为计算的起点（survey_year设为NULL区分基准行）。
2. 递归成员：给usage_data的每条记录按语言分组、年份排序添加序号，然后每次递归时，基于上一步的用户数计算当前年份的结果。
3. 最后过滤掉基准行，得到各年份的用户数，用ROUND()保留小数位数和示例一致。

方法2：窗口函数累积乘积（简洁高效，适合纯乘积场景） #

如果你的计算逻辑只是简单的百分比累积变化，可以用自然对数+累积和+指数的技巧来实现累积乘积，避免递归：

SELECT
    ud.language_id,
    ud.survey_year,
    ROUND(
        pl.initial_users * 
        EXP(SUM(LN(
            CASE 
                WHEN ud.increase_or_decrease THEN 1 + ud.percent_users_change/100 
                ELSE 1 - ud.percent_users_change/100 
            END
        )) OVER (PARTITION BY ud.language_id ORDER BY ud.survey_year)),
        3
    ) AS current_users
FROM usage_data ud
JOIN programming_language pl ON pl.id = ud.language_id
ORDER BY ud.language_id, ud.survey_year;

逻辑说明： #

1. 先将百分比变化转换为乘数：增长时是1 + 百分比/100，减少时是1 - 百分比/100。
2. 利用对数的性质：ln(a*b) = ln(a) + ln(b)，所以对所有乘数取自然对数后做累积和，再取指数就能得到累积乘积。
3. 最后乘以初始用户数，得到当前年份的用户数。

验证示例数据 #

对于语言ID=1（初始用户10）：

• 1991年：10 * (1 + 10/100) = 11
• 1993年：11 * (1 + 7.5/100) = 11.825
  两种方法都能得到和你预期一致的结果。

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