如何在PostgreSQL中用窗口函数计算各行占总和百分比
使用sum() over ()窗口函数计算总和,再除以该值即可得到百分比。具体做法是用当前行的数值除以sum(列名) over ()的结果,通常乘以100.0并保留两位小数。若需按组计算,则在over子句中加入partition by。

如何按部门分组计算员工薪资占比
在OVER子句中使用PARTITION BY department_id可以按部门分别计算总和。这样每个部门内的员工薪资占比之和为100%,而跨部门的比较则基于各自部门的总额。示例中假设表名为employees,包含salary和department_id字段。

SELECT
employee_name,
department_id,
salary,
SUM(salary) OVER (PARTITION BY department_id) AS dept_total_salary,
ROUND((salary * 100.0 / SUM(salary) OVER (PARTITION BY department_id)), 2) AS salary_percentage
FROM
employees;
如何处理空值或零值导致的除零错误
当某组总和为零时,直接除法会引发错误。可以使用NULLIF函数将零值转换为NULL,从而避免除零错误,结果自然显示为NULL。例如,使用NULLIF(SUM(salary) OVER (), 0)作为分母。

SELECT
employee_name,
department_id,
salary,
CASE
WHEN NULLIF(SUM(salary) OVER (PARTITION BY department_id), 0) IS NULL THEN NULL
ELSE ROUND((salary * 100.0 / NULLIF(SUM(salary) OVER (PARTITION BY department_id), 0)), 2)
END AS safe_salary_percentage
FROM
employees;











