计算项目工时百分比，保留两位小数后百分比合计不等于1，将多或少的比例加减到百分比最高的那个项目

一、应用场景

 后台有2023年A公司 制剂 部门的张三的项目工时明细表，现需要统计项目占个人总工时的比例，保留两位小数，统计后发现比例合计不等于1，需要将多或少的比例加减到比例最高的项目。

 

 二、后台数据集准备

1、基础数据

Smonth    com  dept   manager   project    worktime
202301    A    制剂    张三      Pro1          77.1
202301    A    制剂    张三      Pro2          28
202301    A    制剂    张三      Pro2          28
202301    A    制剂    张三      Pro3          42.4
202301    A    制剂    张三      Pro4          8
202301    A    制剂    张三      Pro4          8
202301    A    制剂    张三      Pro5          16
202301    A    制剂    张三      Pro6          16
202302    A    制剂    张三      Pro7          8
202302    A    制剂    张三      Pro7          8
202302    A    制剂    张三      Pro8          8
202302    A    制剂    张三      Pro9          8

2、建表

create table worktime(
Smonth varchar2(20),
com varchar2(10),
dept varchar2(100),
manager varchar2(100),
project varchar2(20),
worktime decimal(6,2)
);

3、PL/SQL导入数据

Tools->文本导入器->来自文本文件的数据->选择文件->编码->到Oracle的数据->所有者->表->导入，会显示12条记录已被导入

 4、查看数据

SELECT * FROM WORKTIME

 

三、调整比例

因为需要有日期维度，所以不能聚合再处理，这里使用的是窗口函数，求项目维度、人员维度的工时合计

1、求项目总工时pro_time、人员总工时human_time

create table t0 as
select  t.smonth,t.com,t.dept,t.manager,t.project,t.worktime,
        sum(t.worktime) over (partition by t.com,t.dept,t.manager,t.project) pro_time,
        sum(t.worktime) over (partition by t.com,t.dept,t.manager) human_time
from worktime t
order by t.project

2、求项目工时比例prop、项目条数pronum(为了求合计比例的时候去掉重复计算)、个人项目个数human_pronum(为了找到比例最高的项目，根据比例排序，序号和项目个数相等就是比例最高的项目)、工时比例排序ranknum。如果是在hive使用且计算个人项目个数human_pronum并不是select阶段，不能使用count(distinct project)，可以使用size(collect_set(project) over (partition by t.com,t.dept,t.manager))

create table t1 as 
select t.smonth,t.com,t.dept,t.manager,t.project,t.worktime,t.pro_time,
       round(pro_time/human_time,2) prop,
       count(*) over (partition by t.com,t.dept,t.manager,t.project) pronum,
       count(distinct t.project) over (partition by t.com,t.dept,t.manager) human_pronum,
       dense_rank() over(partition by t.com,t.dept,t.manager order by t.pro_time,t.project) ranknum
from t0 t
order by t.project

在这里排序使用dense_rank，比如pro2，最后显示的工时是56，而不是按照两条28来显示，如果使用row_number会给pro2排两次；可以看到如果合计工时比例prop的话总计是1.33，pro2、pro4、pro7合并两次，所以要将比例/项目条数，即prop/pronum，后面相加再相加。

3、求合计工时比例prop_sum

with t3 as
(select t.smonth,t.com,t.dept,t.manager,t.project,t.worktime,t.pro_time,prop,pronum,
        sum(prop/pronum) over (partition by t.com,t.dept,t.manager) prop_sum,
        human_pronum,ranknum 
  from t1 t
  order by t.project
)
select * from t3

 

最后合计是0.99，在比例最高的项目上加上0.01，目测应该是pro1，可以通过human_pronum与ranknum比较得到pro1

4、调整比例

select distinct t.com,t.dept,t.manager,t.project,t.pro_time,
       (case when human_pronum=ranknum then prop+1-prop_sum else prop end) adjust_pro
from t3 t
order by t.project
--因为t3是临时表，所以需要将这个语句和第三步一起执行

 

得到最后结果