百分比 SQL Oracle [英] Percentage SQL Oracle

问题描述

有什么方法可以计算前80%的百分比

there is any way to calculate the first 80% percentage

select
testoo.ttamount,
egct.Category_name,
SUM(pola.LIST_PRICE * nvl(pola.QUANTITY,1)) * NVL(poh.RATE,1)
    Line_amount,
ROUND ( SUM((pola.LIST_PRICE * nvl(pola.QUANTITY,1)) * NVL(poh.RATE,1)*100) / (testoo.ttamount) , 2 )  PERCENTAGE,
poh.CURRENCY_CODE


FROM


(SELECT 
SUM(test.line_amount) TTAmount

FROM
( select 
egct.Category_name,
SUM(pola.LIST_PRICE * nvl(pola.QUANTITY,1)) * NVL(poh.RATE,1)
    Line_amount,
poh.CURRENCY_CODE




 from EGP_CATEGORIES_TL  egct,
PO_LINES_ALL pola,
PO_HEADERS_ALL poh
where
egct.category_ID=pola.category_ID
AND pola.po_header_id = poh.po_header_id
AND LANGUAGE='US'
AND TYPE_LOOKUP_CODE='STANDARD'
AND poh.APPROVED_FLAG='Y'
 group by 
egct.Category_name,
poh.CURRENCY_CODE,
poh.RATE ) Test ) Testoo,
EGP_CATEGORIES_TL  egct,
PO_LINES_ALL pola,
PO_HEADERS_ALL poh

where
egct.category_ID=pola.category_ID
AND pola.po_header_id = poh.po_header_id
AND LANGUAGE='US'
AND TYPE_LOOKUP_CODE='STANDARD'
AND poh.APPROVED_FLAG='Y'



 group by 
egct.Category_name,
poh.RATE,
testoo.ttamount,
poh.CURRENCY_CODE


order by
Line_amount desc

例如输出类别百分比

1     32%

2     20%

3     20%

4     10%

5     18%

我想获得大约 80% 的高百分比所以输出将是

I want to get the high percentage which the percentage of it about 80 % so the output will be

类别百分比

1     32%

2     20%

3     20%

4     10%

我想要第一个类别的输出等于 80%
所以输出将检索第一个类别的总和等于 80%ROUND (SUM((pola.LIST_PRICE * nvl(pola.QUANTITY,1)) * NVL(poh.RATE,1)*100)/(testoo.ttamount) , 2 ) PERCENTAGE,谢谢.

I want the output for the first categories which equal 80%
so the output will retrieve the sum of the first categories equal 80% ROUND ( SUM((pola.LIST_PRICE * nvl(pola.QUANTITY,1)) * NVL(poh.RATE,1)*100) / (testoo.ttamount) , 2 ) PERCENTAGE, thanks.

推荐答案

我建议使用窗口函数计算运行百分比，然后将结果与 80 进行比较.

I'd suggest calculating a running percentage using a window function and then compare the result to 80.

在这个小代码示例中，我展示了如何根据放置在名为 your_data 的 CTE 块中的查询结果执行此操作.它只是向您展示了这个想法.

In this small code sample I've shown how to do it based on the result of your query placed into a CTE block called your_data. It just shows you the idea.

with 
    your_data (category, percentage) as(
        -- sample data based on your example
        select 1, 32 from dual union
        select 2, 20 from dual union
        select 3, 20 from dual union
        select 4, 10 from dual union
        select 5, 18 from dual
    ),
    t as (
        select  your_data.*,
                -- running sum calculation
                sum(percentage) over (order by category) pctg_running
          from  your_data 
    )
select * 
  from t
 where pctg_running <= 80

实际上它返回 3 行，而您说您希望显示 4 行.为了添加运行百分比首先超过您的边界(80)的行，您可以从运行值中提取当前类别百分比，即将sum(percentage) over (order by category)替换为sum(percentage) over (order by category) - 百分比.

Actually it returns 3 rows and you say you expect 4 rows to be shown. In order to add the row where running percentage first exceeds your boundary (80), you may extract the currenct category percentage from running value, i.e. replace sum(percentage) over (order by category) with sum(percentage) over (order by category) - percentage.

HTH

这篇关于百分比 SQL Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！