SQL查询计算百分比变化结果为0.00的故障排查请求

核心原因：整数除法截断导致结果为0 #

大多数SQL数据库（如PostgreSQL、MySQL、SQL Server）中，整数之间的除法会自动取整为整数。例如当计算(120 - 100)/100时，会先得到20/100=0，再乘以100后仍为0，最终ROUND后显示为0.00，这是变化率全为0的直接原因。

此外你的查询还存在两个潜在问题：

• UNION ALL将「采购量」和「变化率」混在同一列，语义混淆，不利于结果解读
• LEFT JOIN可能引入NULL值，若两年前（from_current=-2）无数据，会导致计算结果为NULL而非0（但你的结果是0，说明该场景暂未发生）

修正后的SQL #

WITH YearlyCounts AS (
    SELECT
        from_current,
        COUNT(CASE WHEN accessories > 0 THEN 1 ELSE NULL END) as accessories,
        COUNT(CASE WHEN camping > 0 THEN 1 ELSE NULL END) as camping,
        COUNT(CASE WHEN footwear > 0 THEN 1 ELSE NULL END) as footwear,
        COUNT(CASE WHEN apparel > 0 THEN 1 ELSE NULL END) as apparel,
        COUNT(CASE WHEN kids_clothes > 0 THEN 1 ELSE NULL END) as kids_clothes,
        COUNT(CASE WHEN biking > 0 THEN 1 ELSE NULL END) as biking,
        COUNT(CASE WHEN ski_snow_gear > 0 THEN 1 ELSE NULL END) as ski_snow_gear,
        COUNT(CASE WHEN fishing > 0 THEN 1 ELSE NULL END) as fishing
    FROM tenpeaks_sales
    WHERE loyalty = 't'
    GROUP BY from_current
)
SELECT 
    from_current,
    accessories,
    camping,
    footwear,
    apparel,
    kids_clothes,
    biking,
    ski_snow_gear,
    fishing,
    '采购量' AS data_type
FROM YearlyCounts

UNION ALL

SELECT
    -1 AS from_current,
    ROUND(((S1.accessories - S2.accessories)::NUMERIC / S2.accessories)*100, 2),
    ROUND(((S1.camping - S2.camping)::NUMERIC / S2.camping)*100, 2),
    ROUND(((S1.footwear - S2.footwear)::NUMERIC / S2.footwear)*100, 2),
    ROUND(((S1.apparel - S2.apparel)::NUMERIC / S2.apparel)*100, 2),
    ROUND(((S1.kids_clothes - S2.kids_clothes)::NUMERIC / S2.kids_clothes)*100, 2),
    ROUND(((S1.biking - S2.biking)::NUMERIC / S2.biking)*100, 2),
    ROUND(((S1.ski_snow_gear - S2.ski_snow_gear)::NUMERIC / S2.ski_snow_gear)*100, 2),
    ROUND(((S1.fishing - S2.fishing)::NUMERIC / S2.fishing)*100, 2),
    '同比变化率(%)' AS data_type
FROM YearlyCounts S1
INNER JOIN YearlyCounts S2 ON S1.from_current = S2.from_current + 1
WHERE S1.from_current = -1
ORDER BY from_current ASC;

关键修改说明 #

1. 强制浮点除法：将除法中的操作数转换为数值型（如PostgreSQL用::NUMERIC，MySQL用CAST(xxx AS DECIMAL)），避免整数截断，确保计算出精确的小数变化率。
2. 明确数据语义：新增data_type列，区分每一行是「采购量」还是「变化率」，解决UNION ALL后的列语义混淆问题。
3. 优化JOIN类型：若确认两年前（from_current=-2）一定有数据，改用INNER JOIN避免NULL值干扰；若可能无数据，可添加COALESCE(S2.accessories, 1)处理除以0的情况（需根据业务逻辑调整）。

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