时间戳

首页 > 解决方案 > 如何计算百分比条件聚合

sql - 如何计算百分比条件聚合

问题描述

我有一个数据表，请参阅http://sqlfiddle.com/#!4/cf72da/5 使用数据透视语句，我可以按标题细分计数

select division, status, 
       sum(case when title = 'worker' then 1 else 0 end) as workerCount,
       (sum(case when title = 'worker' then 1 else 0 end) /
        sum(sum(case when title = 'worker' then 1 else 0 end)) over ()
       )*100 as workerPercent
from ta
group by status, division
order by division, status

所以结果看起来像这样：

DIVISION  STATUS    WORKERCOUNT  WORKERPERCENT
11        ready     3            30
11        started   3            30
12        finished  4            40
12        ready     0            0
12        started   0            0

我实际上需要除以除以百分比，例如

DIVISION  STATUS    WORKERCOUNT  WORKERPERCENT
11        ready     3            50
11        started   3            50
12        finished  4            100
12        ready     0            0
12        started   0            0

知道如何使用 SQL 完成此任务吗？

标签： sqloracle

解决方案

正如其他海报所指出的那样，您可以（并且应该）使用分析函数来解决这类问题。

专门针对您的用例，有一个RATIO_TO_REPORT功能 - 使用它比在您的尝试和几个答案中“手动”计算事物要好得多。

请注意我所做的另一项更改 - 当“工人”和 0 “否则”时的总和与计算“工人”相同。COUNT比SUM在这种情况下要清楚得多。

select division, status, 
       count(case when title = 'worker' then 1 end) as workerCount,
       ratio_to_report(count(case when title = 'worker' then 1 end))
           over (partition by division)*100 as workerPercent
from  ta
group by status, division
order by division, status;

感谢您发布测试数据和您的尝试 - 这非常有帮助！

推荐阅读