n

问题

nnnn

现在有一张每个年份的每个部门的收入表。现在需要算每个部门的收入占同类型部门的收入的占比和当年整个公司的收入占比。要求一条SQL计算出来。比如研发部和产品部属于同类型的，都是产研；财务部和人事部都属于职能。

nnnn

year    dept    incomen2023    研发部    5000n2023    产品部    6000n2023    财务部    7000n2023    人事部    8000n2022    研发部    10000n2022    产品部    8000n2022    财务部    9000n2022   人事部    8000

nnnn

解答

nnnn

考察对开窗sum()函数的使用，其中窗口内支持case when语法来对部门进行分类

nnnn

with temp asn(n    select '2023' as year,"研发部" as dept,5000 as incomen    union alln    select '2023' as year,"产品部" as dept,6000 as incomen    union alln    select '2023' as year,"财务部" as dept,7000 as incomen    union alln    select '2023' as year,"人事部" as dept,8000 as incomen    union all n    select '2022' as year,"研发部" as dept,10000 as incomen    union alln    select '2022' as year,"产品部" as dept,8000 as incomen    union alln    select '2022' as year,"财务部" as dept,9000 as incomen    union alln    select '2022' as year,"人事部" as dept,8000 as incomen)nselectnyearn,deptn,incomen,round(income/similar_dept_income,2) as similar_dept_income_raten,round(income/year_income,2) as year_income_ratenfromn(n    selectn    yearn    ,deptn    ,incomen    ,sum(income) over(partition by year,case n                        when dept in("研发部","产品部") then "产研"n                        when dept in("财务部","人事部") then "职能"n                        endn    ) as similar_dept_income  --同类型部门收入n    ,sum(income) over(partition by year) as year_incomen    from tempn) t1n;n----结果为：n序号       year      dept       income     similar_dept_income_rate     year_income_raten1	       2022     研发部      10000              0.56                      0.29n2	       2022     产品部      8000               0.44                      0.23n3	       2022     财务部      9000               0.53                      0.26n4	       2022     人事部      8000               0.47                      0.23n5	       2023     研发部      5000               0.45                      0.19n6	       2023     产品部      6000               0.55                      0.23n7	       2023     财务部      7000               0.47                      0.27n8          2023     人事部      8000               0.53                      0.31

n