求助SQL语句

[xdhis]

表中字段：包号，公司ID，公司技术人员数，公司人员总数

我现在想按包分组依据公司技术人员占公司总人数比例计算，比例最高得3分，依次递减0.5分,减到0分为止,
SQL该如何写啊？

[qq_22790241]

能给一些测试数据么？什么叫包号？

[qq_22790241]

 create table test_1208 (
 company_id int,
 dept_no int,
 tech_cn int,
 total_cn int
 )
 
 insert into test_1208 values(1001,101,10,100);
 insert into test_1208 values(1001,102,30,100);
  insert into test_1208 values(1001,103,20,100);
    insert into test_1208 values(1001,104,40,100);
    
    
     insert into test_1208 values(2002,201,80,200);
 insert into test_1208 values(2002,202,30,100);
  insert into test_1208 values(2002,203,40,100);
    insert into test_1208 values(2002,204,50,100);
    
    select company_id,dept_no,tech_cn,total_cn,
    case cn when 1 then 3 
            when 2 then 3-0.5
            when 3 then 3-1 
            when 4 then 3-1.5
            when 5 then 3-2 
            when 6 then 3-2.5
            when 7 then 0
            else  null
            end as pt
    from (
    select company_id,dept_no,tech_cn,total_cn,
    dense_rank()over(partition by company_id order by tech_cn desc) as cn from test_1208) a;