Hive 刷题——累计占比问题

问题描述

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

year    dept    income
2024    研发部    5000
2024    产品部    6000
2024    财务部    7000
2024    人事部    8000

参考实现

with temp as
         (select 2024 as year, "研发部" as dept, 5000 as income
          union all
          select 2024 as year, "产品部" as dept, 6000 as income
          union all
          select 2024 as year, "财务部" as dept, 7000 as income
          union all
          select 2024 as year, "人事部" as dept, 8000 as income)

select year,
       dept,
       round(income / dept_type_income, 2) dept_type_income_ratio,
       round(income / year_income, 2)      year_income_ratio
from (select year
           , dept
           , income
           , sum(income) over (partition by year,dept_type) dept_type_income
           , sum(income) over (partition by year)           year_income
      from (select year,
                   dept,
                   case
                       when dept in ('人事部', '财务部') then '职能'
                       when dept in ('产品部', '研发部') then '产研'
                       else 'other' end dept_type,
                   sum(income)          income
            from temp
            group by year, dept,
                     case
                         when dept in ('人事部', '财务部') then '职能'
                         when dept in ('产品部', '研发部') then '产研'
                         else 'other' end) t) t;