SQL如何新增列实现每行profit值与profit列平均值的对比计算

解决方案 #

你可以直接使用SQL窗口聚合函数实现需求，无需额外嵌套子查询关联，修改后的完整查询语句如下：

SELECT 
    region, 
    store_id, 
    SUM(q1.revenue) AS revenue, 
    SUM(q1.profit) AS `profit`, 
    SUM(q1.profit) / SUM(revenue) * 100 AS `percentage`,
    -- 新增index列：当前门店利润 / 所有门店利润平均值，保留2位小数
    ROUND(SUM(q1.profit) / AVG(SUM(q1.profit)) OVER(), 2) AS `index`
FROM ( 
    SELECT
        regions.region_name AS `region`, 
        s.store_id,
        s.revenue AS `revenue`, 
        (s.revenue - s.costs) AS `profit`
    FROM
        store_sales s
        JOIN stores ON s.store_id = stores.store_id
        JOIN regions ON stores.store_id = regions.store_id
        JOIN weeks ON s.week_id = weeks.week_id
    WHERE
        weeks.end_date BETWEEN '2020-01-01' AND '2020-03-31'
) AS q1
GROUP BY
    store_id, region -- 建议补上region，部分SQL模式下非聚合列必须在GROUP BY中
ORDER BY
    store_id

说明 #

• 核心逻辑是AVG(SUM(q1.profit)) OVER()：空参数的OVER()子句会将整个查询结果集作为统计范围，直接计算所有聚合后行的profit列平均值
• 用ROUND()保留2位小数，和你给出的示例格式对齐
• 注意index是SQL保留关键字，作为列名时建议用反引号包裹，也可以替换为profit_index等非关键字列名避免报错
• 补充了GROUP BY中的region字段，在开启ONLY_FULL_GROUP_BY模式的MySQL等数据库中，非聚合的查询字段必须包含在GROUP BY中，否则会触发语法错误

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