计算得到生成的前n个百分比

Question

我正在努力理解这个查询语句。

write a query to show % of total profit that the top 10% of businesses generated.

1st：从所有业务中获得总利润

2nd：获得单个业务产生的所有利润的%

3rd：按下降排序利润%

4th：获得前10名

这就是我所理解的。

但我的某些想法并不正确。

有人能提出这个问题的实际要求吗？

Note: cant ask back from where I got, because this was in interview :)

感谢

Answer 1

1. 按利润额对业务进行排序
2. 以企业总数的前10%为例(有或没有关系(
3. 计算这10%的企业的总利润
4. 将其与100%企业的总利润进行比较

您可以使用PERCENT_RANK分析函数按利润顺序对业务进行百分比排名，然后使用条件聚合将利润前10%的业务的利润相加，并将其与总利润进行比较，以获得利润前10%业务的百分比：

SELECT SUM(CASE WHEN pct_rnk <= 0.1 THEN profit END)
/ SUM(profit) * 100 AS percent_profit_for_top_10_percent
FROM   (
SELECT profit,
PERCENT_RANK() OVER (ORDER BY profit DESC) AS pct_rnk
FROM   businesses
)

您也可以使用：

SELECT (SELECT SUM(profit)
FROM   (
SELECT profit
FROM   businesses
ORDER BY profit DESC
FETCH FIRST 10 PERCENT ROWS WITH TIES
)
)
/
(SELECT SUM(profit)
FROM   businesses)
* 100 AS percent_profit_for_top_10_percent
FROM   DUAL;

对于样本数据：

CREATE TABLE businesses (profit) AS
SELECT  1000 FROM DUAL CONNECT BY LEVEL <= 10 UNION ALL
SELECT  2000 FROM DUAL CONNECT BY LEVEL <= 8 UNION ALL
SELECT  3000 FROM DUAL CONNECT BY LEVEL <= 6 UNION ALL
SELECT  4000 FROM DUAL CONNECT BY LEVEL <= 4 UNION ALL
SELECT  5000 FROM DUAL CONNECT BY LEVEL <= 2 UNION ALL
SELECT 10000 FROM DUAL CONNECT BY LEVEL <= 1;

两种输出：

PERCENT_PROFIT_FOR_TOP_10_PERCENT
45