一组中案件的百分比(按年份) [英] Percentage of cases within a group by year

问题描述

我有这个数据集:

Year   Grade  count 
2010    B2     1
2010    B      478
2010    B1     46
2010    B1     12 
2010    B1     2
2010    B2     612
2010    A1     160
2010    A1     8
...

我想确定每一年(在该示例中只有2010年)A1和B1在该年的总数中所占的百分比，即

I would like to determine for each year (in the example there is only 2010) the percentage of A1 and B1 over the total in that year, I.e.

478 + 46 + 12 + 2 = 538 在B的总数(B1，B，B2)中:1202

478+46+12+2=538 Over the total of B (B1, B, B2) :1202

为了获得45％的百分比. 对于A同样如此. 我想我也应该使用like来区分B和A值. 我该怎么办?

In order to get the percentage 45%. Similarly for A. I think I should also use like to distinguish between B and A values. How can I do?

推荐答案

SEL years,
Score,
,count(0)
,100.0*count(*) / 
Sum(Count(CASE WHEN grade LIKE '_1' THEN 1 end ))   -- A1 & B1
Over (PARTITION BY years, Substr(grade,1,1)) AS pct -- per A/B
From table1
Group by 1,2

这篇关于一组中案件的百分比(按年份)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！