sqloracle-databasepercentagepartition

Oracle SQL - Get rows where it meets a percent allocation


I am working on selecting participants for a program based on certain criteria and after meeting the criteria, get x% (say 60%) from a specific department A, and the remaining 20% from another department B and the last 20% from department C. I am able to flag rows meeting the criteria in a column named flag_criteria and I do have the department names in the department column, however I don't know how I can get the rows based on the percentage allocation from the department column. As an example:

enter image description here

Now, based on the allocation %, I created a new column "Allocation", and of the 10 rows meeting the criteria, I assigned 60% from Dept A, 20% from B and 20% from C.

enter image description here

How would I handle this in SQL? Also, if there are no rows meeting the allocation %, I would want to give the "extra" allocations to the next dept. Please advise.


Solution

  • By calculating the position in the partition by (dept, flag) and comparing with the % for the dept:

    with data(empid, dept, flag) as (
        select 12345, 'A', 1 union all
        select 23456, 'A', 1 union all
        select 34567, 'B', 1 union all
        select 45678, 'A', 0 union all
        select 56789, 'A', 1 union all
        select 67900, 'A', 1 union all
        select 79011, 'B', 1 union all
        select 90122, 'C', 0 union all
        select 101233, 'A', 1 union all
        select 112344, 'A', 1 union all
        select 123455, 'C', 1 union all
        select 134566, 'B', 1 union all
        select 145677, 'C', 0 -- union all
    ),
    percentages(dept, pc) as (
        select 'A', 60 union all
        select 'B', 20 union all
        select 'C', 20 -- union all
    )
    select d.empid, dept, d.flag,
        case when flag  = 1 and rn <= n*pc/100  
            then 'y'
            else 'n'
            end
        as allocation
    from (
        select empid, dept, flag,
            row_number() over(partition by dept, flag order by empid) as rn,
            count(empid) over(partition by flag) as n
        from data 
    ) d
    join percentages p using(dept)
    order by d.empid
    ;