问题：
现在有一张每个年份的每个部门的收入表。现在需要算每个部门的收入占同类型部门的收入的占比和当年整个公司的收入占比。要求一条SQL计算出来。比如研发部和产品部属于同类型的，都是产研；财务部和人事部都属于职能。
year dept income
2023 研发部 5000
2023 产品部 6000
2023 财务部 7000
2023 人事部 8000
2022 研发部 10000
2022 产品部 8000
2022 财务部 9000
2022 人事部 8000
SQL解答：
考察sum() over 开窗函数的使用。
with temp as
(
select '2023' as year,"研发部" as dept,5000 as income
union all
select '2023' as year,"产品部" as dept,6000 as income
union all
select '2023' as year,"财务部" as dept,7000 as income
union all
select '2023' as year,"人事部" as dept,8000 as income
union all
select '2022' as year,"研发部" as dept,10000 as income
union all
select '2022' as year,"产品部" as dept,8000 as income
union all
select '2022' as year,"财务部" as dept,9000 as income
union all
select '2022' as year,"人事部" as dept,8000 as income
)
select
year
,dept
,income
,round(income/similar_dept_income,2) as similar_dept_income_rate
,round(income/year_income,2) as year_income_rate
from
(
select
year
,dept
,income
,sum(income) over(partition by year,case
when dept in("研发部","产品部") then "产研"
when dept in("财务部","人事部") then "职能"
end
) as similar_dept_income --同类型部门收入
,sum(income) over(partition by year) as year_income
from temp
) t1
;
----结果为：
序号 year dept income similar_dept_income_rate year_income_rate
1 2022 研发部 10000 0.56 0.29
2 2022 产品部 8000 0.44 0.23
3 2022 财务部 9000 0.53 0.26
4 2022 人事部 8000 0.47 0.23
5 2023 研发部 5000 0.45 0.19
6 2023 产品部 6000 0.55 0.23
7 2023 财务部 7000 0.47 0.27
8 2023 人事部 8000 0.53 0.31
