Jasper Reports分组及计算商品占月度销售总额百分比的问题

原始数据 #

问题说明 #

按Year→Month→Item层级分组展示时，用Month分组重置的变量v{Total_Monthly_Sales}，通过公式F{SalesCount}/v{Total_Monthly_Sales}/100计算v{Percent_of_Total}会出错：因为变量是动态递增统计的，首个商品占比会显示100%，后续商品的占比基于累计值计算，得不到正确的月度占比——月度总销量要等Month分组完全统计完才有效，动态变量的计算时机不匹配。

解决方案：SQL预计算月度总销量 #

直接在SQL层预算出每个Year+Month对应的总销量，再基于这个固定值计算百分比，就能避开动态变量的问题。

方法1：窗口函数（推荐） #

窗口函数可以直接在原数据基础上计算分组总销量，无需额外子查询：

SELECT 
    Year,
    Month,
    Item,
    SaleCount,
    -- 按Year和Month分组，计算月度总销量
    SUM(SaleCount) OVER (PARTITION BY Year, Month) AS Total_Monthly_Sales,
    -- 计算占比并保留两位小数
    ROUND((SaleCount * 100.0) / SUM(SaleCount) OVER (PARTITION BY Year, Month), 2) AS Percent_of_Total
FROM 
    your_table_name;

方法2：子查询预计算月度总销量 #

如果数据库不支持窗口函数，可以用子查询先算出每个月度的总销量，再关联回原表：

SELECT 
    t.Year,
    t.Month,
    t.Item,
    t.SaleCount,
    tm.Total_Monthly_Sales,
    ROUND((t.SaleCount * 100.0) / tm.Total_Monthly_Sales, 2) AS Percent_of_Total
FROM 
    your_table_name t
JOIN (
    SELECT 
        Year,
        Month,
        SUM(SaleCount) AS Total_Monthly_Sales
    FROM 
        your_table_name
    GROUP BY 
        Year, Month
) tm ON t.Year = tm.Year AND t.Month = tm.Month;

效果示例 #

执行后会得到正确的占比结果，比如2020年1月Apple占比为40.00%（2/(2+3)*100），Banana占比为60.00%，完全符合预期。

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