单COUNT列周度增长率SQL计算结果错误，如何修正？

原SQL错误原因 #

1. 括号配对逻辑错误：将环比计算的分母部分写进了LAG函数的OVER子句内部，窗口函数的排序逻辑被打乱，完全偏离了增长率的计算规则
2. 计算顺序错误：周度环比增长率的标准公式为(当期值-上期值)/上期值*100，原SQL的括号位置错误导致运算顺序完全错乱
3. 聚合函数误用：如果原始表已经是按周汇总好的结构，不需要调用COUNT()函数对COUNT列二次聚合；如果是日度原始数据需要按周汇总，应该使用SUM(COUNT)而非COUNT(COUNT)统计周度数值
4. 类型运算逻辑错误：将数值计算和%字符串拼接的逻辑混合在乘法运算中，会触发类型转换错误，同时要注意避免整数除法导致小数位丢失

修正后的SQL #

场景1：原始表已经是按周汇总的结构（和示例给出的原始数据结构一致）

SELECT 
    DATE,
    COUNT,
    CASE 
        -- 处理上期值为0的除零异常，第一行无上期值自动返回空
        WHEN LAG(COUNT, 1) OVER (ORDER BY DATE) = 0 THEN NULL 
        ELSE ROUND((COUNT - LAG(COUNT, 1) OVER (ORDER BY DATE)) * 100.0 / LAG(COUNT, 1) OVER (ORDER BY DATE), 2) || '%' 
    END AS "Perc Growth"
FROM data
ORDER BY DATE ASC;

场景2：原始数据为日度数据，需要先按周聚合再计算环比

WITH weekly_agg AS (
    SELECT 
        DATE_TRUNC('WEEK', DATE_CREATED) AS week_date,
        SUM(COUNT) AS weekly_count -- 如需统计周度行数可替换为COUNT(*)
    FROM data
    GROUP BY 1
)
SELECT 
    week_date AS DATE,
    weekly_count AS COUNT,
    CASE 
        WHEN LAG(weekly_count, 1) OVER (ORDER BY week_date) = 0 THEN NULL
        ELSE ROUND((weekly_count - LAG(weekly_count, 1) OVER (ORDER BY week_date)) * 100.0 / LAG(weekly_count, 1) OVER (ORDER BY week_date), 2) || '%'
    END AS "Perc Growth"
FROM weekly_agg
ORDER BY week_date ASC;

注意事项 #

计算时乘以100.0而非100，是为了强制触发浮点运算，避免部分数据库的整数除法规则自动舍去小数位。

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