如何在PieCloudDB中正确计算连续日期的日销售增长率？

问题根源分析 #

你的SQL查询出现两个问题的核心原因：

1. 未判断日期连续性：使用普通LAG()函数会直接取结果集中的上一条数据，不管日期是否连续，导致非连续日期的行错误计算了增长率。
2. 整数除法截断：如果sales字段是整数类型，直接进行除法运算会触发整数截断（比如(120-100)/100会得到0而非0.2），最终导致增长率显示为0.00%。

修正方案 #

以下提供两种适配PieCloudDB（兼容PostgreSQL语法）的修正方案，均能满足"仅连续日期计算增长率，非连续/无前置数据时显示NULL"的需求：

方案1：使用LAG()结合日期连续性判断 #

SELECT 
    sale_date,
    sales,
    CASE 
        -- 判断当前日期与上一条数据的日期是否连续
        WHEN sale_date = LAG(sale_date) OVER (ORDER BY sale_date) + INTERVAL '1 day'
        -- 将整数转换为NUMERIC避免截断，计算后保留2位小数并拼接百分号
        THEN ROUND((sales::NUMERIC - LAG(sales) OVER (ORDER BY sale_date)) / LAG(sales) OVER (ORDER BY sale_date) * 100, 2) || '%'
        ELSE NULL
    END AS growth_rate
FROM daily_sales
ORDER BY sale_date;

方案2：使用自连接匹配连续日期 #

这种方案逻辑更直观，适合复杂的日期匹配场景：

SELECT 
    d1.sale_date,
    d1.sales,
    CASE 
        -- 仅当前一天存在销售数据时计算增长率
        WHEN d2.sales IS NOT NULL
        THEN ROUND((d1.sales::NUMERIC - d2.sales) / d2.sales * 100, 2) || '%'
        ELSE NULL
    END AS growth_rate
FROM daily_sales d1
-- 左连接匹配当前日期的前一天数据
LEFT JOIN daily_sales d2 
    ON d1.sale_date = d2.sale_date + INTERVAL '1 day'
ORDER BY d1.sale_date;

关键修正点说明 #

• 日期连续性校验：通过INTERVAL '1 day'判断前后日期是否连续，确保只有真正连续的日期才计算增长率。
• 数据类型转换：将sales转换为NUMERIC类型，避免整数除法导致的结果截断，保证增长率计算精度。
• NULL值处理：通过CASE分支，在无前置连续数据时返回NULL，符合你的期望结果。

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