SQL Server中基于UNION的两列占比计算及除零错误解决

核心解决方案思路 #

要计算当期销量与去年同期的占比，重点需解决两个问题：避免除零错误，以及符合GROUP BY聚合规则。以下提供两种可行方案，优先推荐条件聚合（比UNION更高效），也支持基于现有UNION查询的改造。

方案一：用条件聚合替代UNION（推荐） #

直接通过条件聚合将当期、去年同期销量转为列，再计算占比，全程符合GROUP BY规则，同时处理除零：

SELECT 
    product_id,
    product_name,
    -- 计算当期销量
    SUM(CASE WHEN sale_date BETWEEN '2023-01-01' AND '2023-12-31' THEN sales_qty ELSE 0 END) AS current_period_sales,
    -- 计算去年同期销量
    SUM(CASE WHEN sale_date BETWEEN '2022-01-01' AND '2022-12-31' THEN sales_qty ELSE 0 END) AS last_year_period_sales,
    -- 计算占比，处理除零情况
    CASE 
        WHEN SUM(CASE WHEN sale_date BETWEEN '2022-01-01' AND '2022-12-31' THEN sales_qty ELSE 0 END) = 0 
        THEN NULL -- 去年无销量时返回NULL，也可改为0或'无数据'等自定义值
        ELSE ROUND(
            (SUM(CASE WHEN sale_date BETWEEN '2023-01-01' AND '2023-12-31' THEN sales_qty ELSE 0 END) * 100.0) 
            / SUM(CASE WHEN sale_date BETWEEN '2022-01-01' AND '2022-12-31' THEN sales_qty ELSE 0 END), 
            1 -- 保留1位小数，可按需调整
        ) 
    END AS sales_ratio_percent
FROM sales
WHERE sale_date BETWEEN '2022-01-01' AND '2023-12-31' -- 仅过滤两年数据，提升效率
GROUP BY product_id, product_name
ORDER BY product_id;

方案二：基于现有UNION查询改造 #

如果必须保留UNION结构，可通过CTE先聚合再转列，最后计算占比：

WITH sales_union AS (
    -- 当期销量聚合
    SELECT product_id, product_name, SUM(sales_qty) AS sales_qty, 'current' AS period
    FROM sales
    WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31'
    GROUP BY product_id, product_name
    UNION ALL
    -- 去年同期销量聚合
    SELECT product_id, product_name, SUM(sales_qty) AS sales_qty, 'last_year' AS period
    FROM sales
    WHERE sale_date BETWEEN '2022-01-01' AND '2022-12-31'
    GROUP BY product_id, product_name
),
sales_pivot AS (
    -- 将行转列，得到每个产品的当期/去年销量
    SELECT 
        product_id,
        product_name,
        MAX(CASE WHEN period = 'current' THEN sales_qty ELSE 0 END) AS current_period_sales,
        MAX(CASE WHEN period = 'last_year' THEN sales_qty ELSE 0 END) AS last_year_period_sales
    FROM sales_union
    GROUP BY product_id, product_name
)
-- 计算占比
SELECT 
    product_id,
    product_name,
    current_period_sales,
    last_year_period_sales,
    CASE 
        WHEN last_year_period_sales = 0 THEN NULL
        ELSE ROUND((current_period_sales * 100.0) / last_year_period_sales, 1)
    END AS sales_ratio_percent
FROM sales_pivot
ORDER BY product_id;

关键注意事项 #

1. 除零处理：通过CASE WHEN判断去年同期销量是否为0，或者用NULLIF(last_year_period_sales, 0)替代分母，避免触发除零错误。
2. 数据类型：计算时用100.0而非100，确保是浮点除法，避免整数除法导致结果失真（比如5/10整数除法得0，5/10.0得0.5）。
3. GROUP BY规则：所有非聚合列必须出现在GROUP BY子句中，占比计算基于聚合后的销量结果，无需加入GROUP BY。

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