问题

I have to compare total _revenue with target and multiplied with respective percent column. So once 750 is greater than 700 then it should multiply with 0.02 and ignore 500 and 400. and if total_revenue is 470 then multiplied by 1% and ignores other.

我需要比较总收入与目标，并与相应的百分比列相乘。因此，一旦总收入大于700，就应该乘以0.02并忽略500和400。如果总收入为470，则乘以1％，忽略其他。

sample data is like this

样本数据如下

branch_id total_revenue target percent
2356 750 500 1.5%
2356 750 700 2%
2356 750 450 1%
2458 750 500 1.5%
2458 750 700 2%
2458 750 450 1%

desired output

期望的输出

branch_id    total_revenue  target    percent  test percent
2356             750         700      2%          15
2458             750         700      2%          15

I am trying

我正在尝试

with revenue_cal(select a+b as total_revenue from dummy table)
,percent_cal as (
select a, b, .....
, (case when (select total_revenue from revenue_cal) > (target)
then((percent/100)*total_revenue)
else 0 end) as test_percent)

我正在尝试以下查询

I can't use max function here because I have eighty columns and I don't want to group by on them, I can just group by on id. It work fine if total revenue is less than smallest number but then other values repeat. I don't want repetition.

我不能在这里使用max函数，因为我有八十个列，而我不想对它们进行分组，我只能按ID分组。如果总收入小于最小值，那么它可以正常工作，但其他值会重复。我不想要重复。

branch_id total_revenue target percent target percent
2356 450 500 1.5% 00
2356 450 700 2% 00
2356 450 450 1% 4.5
2458 450 500 1.5% 00
2458 450 700 2% 00
2458 450 450 1% 4.5

it should be

应该是这样的

branch_id total_revenue target percent target percent
2356 450 450 1% 4.5
2458 450 450 1% 4.5

如何获得期望的输出？

branch_id    total_revenue  target    percent   
2356             750         500       1.5%
2356             750         700       2%
2356             750         450       1%
2458             750         500       1.5%
2458             750         700       2%
2458             750         450       1%

    branch_id    total_revenue  target    percent  test percent
    2356             750         700      2%          15
    2458             750         700      2%          15

 with revenue_cal(select a+b as  total_revenue from dummy table)
 ,percent_cal as (
  select a, b, .....
, (case when (select total_revenue from revenue_cal) > (target) 
 then((percent/100)*total_revenue)
		else 0 end)  as test_percent)

branch_id    total_revenue  target    percent  target percent 
2356             450         500       1.5%        00
2356             450         700       2%          00
2356             450         450       1%          4.5  
2458             450         500       1.5%        00
2458             450         700       2%          00
2458             450         450       1%          4.5

branch_id    total_revenue  target    percent  target percent 
2356             450         450       1%          4.5 
2458             450         450       1%          4.5

答案1

得分: 1

我假设，对于每个分支，您想要总额等于目标或具有“最接近”的更大总额的行。

如果是这样，您可以像这样使用row_number：

select
from (
    select t.*, 
        row_number() over(partition by branch_id order by total_revenue - target) rn
    where total_revenue >= target
    from mytable t
) t
where rn = 1

select
from (
    select t.*, 
        row_number() over(partition by branch_id order by total_revenue - target) rn
    where total_revenue >= target
    from mytable t
) t
where rn = 1