SQL中基于GROUP BY双字段计算占比的实现方案

我现有如下查询结果，展示了不同办公室下各部门的总工时与总成本：

OfficeCode    Department     Total Hours     Total Costs
---------------------------------------------------------
OFC-UK        Buying         44              50850
OFC-UK        Design         42              30008
OFC-UK        R&D            28              70600
OFC-AZ        Buying         52              30801
OFC-AZ        Design         34              50080
OFC-BG        Buying         25              40030
OFC-BG        Design         37              10020

该结果由以下SQL查询生成：

SELECT office.office_code,
       department.department_description,       
       CAST(SUM(timesheet_entry.timesheet_entry_hour) + (SUM(timesheet_entry.timesheet_entry_minute)/60) as float) as total_hours,
       SUM((timesheet_entry.timesheet_entry_hour + ((timesheet_entry.timesheet_entry_minute)/60)) * person.person_rate) as 'total_cost'
FROM timesheet_entry
INNER JOIN user
        ON user.user_obj = timesheet_entry.user_obj          
INNER JOIN person
    ON person.person_obj = user.person_obj
INNER JOIN department
        ON department.department_obj = user.department_id
INNER JOIN office
        ON office.office_id = timesheet_entry.office_id        
GROUP BY timesheet_entry.office_code, department.department_description
ORDER BY office.office_code, department.department_description;

我需要按办公室分组，计算各部门在所属办公室内的total_hours和total_cost占比，期望结果如下：

OfficeCode    Department     Total Hours     HoursPercentage     Total Costs     CostPercentage
-----------------------------------------------------------------------------------------------
OFC-UK        Buying         44              38.59               50850           33.57
OFC-UK        Design         42              36.84               30008           19.81        
OFC-UK        R&D            28              24.56               70600           46.61
OFC-AZ        Buying         52              etc                 etc             etc
OFC-AZ        Design         34              etc                 etc             etc
OFC-BG        Buying         25              etc                 etc             etc
OFC-BG        Design         37              etc                 etc             etc

我尝试将结果存入临时表后计算占比，但该方案未按办公室分组统计，而是计算了所有办公室的全局占比，请问有没有更准确的实现方法？以下是我尝试的代码：

CREATE TEMPORARY TABLE OfficeDepartmentTotalsTbl
SELECT office.office_code,
       department.department_description,       
       CAST(SUM(timesheet_entry.timesheet_entry_hour) + (SUM(timesheet_entry.timesheet_entry_minute)/60) as float) as total_hours,
       SUM((timesheet_entry.timesheet_entry_hour + ((timesheet_entry.timesheet_entry_minute)/60)) * person.person_rate) as 'total_cost'
FROM timesheet_entry
INNER JOIN user
        ON user.user_obj = timesheet_entry.user_obj          
INNER JOIN person
    ON person.person_obj = user.person_obj
INNER JOIN department
        ON department.department_obj = user.department_id
INNER JOIN office
        ON office.office_id = timesheet_entry.office_id        
GROUP BY timesheet_entry.office_code, department.department_description
ORDER BY office.office_code, department.department_description;

SELECT 
    OfficeDepartmentTotalsTbl.office_code,
    OfficeDepartmentTotalsTbl.department_description,  
    OfficeDepartmentTotalsTbl.total_hours,
    OfficeDepartmentTotalsTbl.total_hours * 100 /  
        (SELECT SUM(OfficeDepartmentTotalsTbl.total_hours) FROM OfficeDepartmentTotalsTbl) AS 'department_percentage_of_total_hours',
    OfficeDepartmentTotalsTbl.total_cost,
    OfficeDepartmentTotalsTbl.total_cost * 100 / (SELECT 
            SUM(OfficeDepartmentTotalsTbl.total_cost)
        FROM
            OfficeDepartmentTotalsTbl) AS 'department_percentage_of_total_cost'
FROM
    OfficeDepartmentTotalsTbl
GROUP BY OfficeDepartmentTotalsTbl.office_code, OfficeDepartmentTotalsTbl.department_description;

DROP TEMPORARY TABLE OfficeDepartmentTotalsTbl;

问题根源在于你的子查询未按办公室过滤，计算的是所有办公室的全局占比，而非当前办公室内的部门占比。以下是两种准确实现需求的方法：

方法一：窗口函数实现（推荐，简洁高效） #

利用SUM() OVER (PARTITION BY office_code)直接按办公室分组计算合计值，无需临时表：

SELECT 
    office.office_code,
    department.department_description,
    CAST(SUM(timesheet_entry.timesheet_entry_hour) + (SUM(timesheet_entry.timesheet_entry_minute)/60) AS FLOAT) AS total_hours,
    ROUND(
        (CAST(SUM(timesheet_entry.timesheet_entry_hour) + (SUM(timesheet_entry.timesheet_entry_minute)/60) AS FLOAT) 
        / SUM(CAST(timesheet_entry.timesheet_entry_hour + (timesheet_entry.timesheet_entry_minute/60) AS FLOAT)) OVER (PARTITION BY office.office_code)) 
        * 100, 2
    ) AS HoursPercentage,
    SUM((timesheet_entry.timesheet_entry_hour + ((timesheet_entry.timesheet_entry_minute)/60)) * person.person_rate) AS total_cost,
    ROUND(
        (SUM((timesheet_entry.timesheet_entry_hour + ((timesheet_entry.timesheet_entry_minute)/60)) * person.person_rate) 
        / SUM((timesheet_entry.timesheet_entry_hour + ((timesheet_entry.timesheet_entry_minute)/60)) * person.person_rate) OVER (PARTITION BY office.office_code)) 
        * 100, 2
    ) AS CostPercentage
FROM timesheet_entry
INNER JOIN user
    ON user.user_obj = timesheet_entry.user_obj          
INNER JOIN person
    ON person.person_obj = user.person_obj
INNER JOIN department
    ON department.department_obj = user.department_id
INNER JOIN office
    ON office.office_id = timesheet_entry.office_id        
GROUP BY office.office_code, department.department_description
ORDER BY office.office_code, department.department_description;

方法二：临时表关联实现（兼容旧版数据库） #

若数据库不支持窗口函数，可先分别计算部门-办公室合计和办公室总合计，再关联计算占比：

-- 创建部门-办公室合计临时表
CREATE TEMPORARY TABLE OfficeDepartmentTotalsTbl
SELECT 
    office.office_code,
    department.department_description,       
    CAST(SUM(timesheet_entry.timesheet_entry_hour) + (SUM(timesheet_entry.timesheet_entry_minute)/60) AS FLOAT) AS total_hours,
    SUM((timesheet_entry.timesheet_entry_hour + ((timesheet_entry.timesheet_entry_minute)/60)) * person.person_rate) AS total_cost
FROM timesheet_entry
INNER JOIN user
    ON user.user_obj = timesheet_entry.user_obj          
INNER JOIN person
    ON person.person_obj = user.person_obj
INNER JOIN department
    ON department.department_obj = user.department_id
INNER JOIN office
    ON office.office_id = timesheet_entry.office_id        
GROUP BY office.office_code, department.department_description;

-- 创建办公室总合计临时表
CREATE TEMPORARY TABLE OfficeTotalsTbl
SELECT 
    office_code,
    SUM(total_hours) AS office_total_hours,
    SUM(total_cost) AS office_total_cost
FROM OfficeDepartmentTotalsTbl
GROUP BY office_code;

-- 关联计算占比
SELECT 
    odt.office_code,
    odt.department_description,
    odt.total_hours,
    ROUND((odt.total_hours / ot.office_total_hours) * 100, 2) AS HoursPercentage,
    odt.total_cost,
    ROUND((odt.total_cost / ot.office_total_cost) * 100, 2) AS CostPercentage
FROM OfficeDepartmentTotalsTbl odt
INNER JOIN OfficeTotalsTbl ot ON odt.office_code = ot.office_code
ORDER BY odt.office_code, odt.department_description;

-- 清理临时表
DROP TEMPORARY TABLE OfficeDepartmentTotalsTbl;
DROP TEMPORARY TABLE OfficeTotalsTbl;

核心说明 #

• 窗口函数方法中，PARTITION BY office_code会将数据集按办公室拆分，计算每个组内的总和，从而得到部门在所属办公室内的占比。
• 两种方法均通过ROUND()函数将占比保留两位小数，匹配你期望的结果格式。
• 方法一无需额外临时表，性能更优，适用于支持窗口函数的数据库（如MySQL 8.0+、PostgreSQL、SQL Server等）；方法二兼容不支持窗口函数的旧版数据库。

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