sql - 在Oracle查询中显示总计、小计、百分比

Question

问题，

我有这样的表格:

PID Category Year
1    AAA     2011
2    AAA     2012
3    BBB     2011
4    CCC     2010
5    CCC     2011
6    CCC     2012

我需要将输出显示为:

Subtotal Total Category  Year   Percentage
1         1      CCC      2010    100%
1         2      AAA      2011    50%
1         2      BBB      2011    50%
1         2      AAA      2012    50%
1         2      CCC      2012    50%

其中小计是特定年份该类别的计数。总计是特定年份的计数，包括所有类别。百分比为小计/总计 *100

Answer 1

select subtotal, 
       total,
       category, 
       year,
       subtotal / total * 100 as percentage
from (
  select count(*) over (partition by category, year) as subtotal,
         count(*) over (partition by year) as total,
         category,
         year
  from the_unknown_table
) t
order by year;