如何创建查询以查找数据百分比(基于每个字段的值)?

Question

我有一张表毒性，显示了药物对几个患者的副作用，如下图所示：

"恶心、呕吐和皮肤"列中的值是指药物毒性。严重性有 4 个值：0,1,2,3

|-----|---------------|--------|-------|------| 
| ID  | Regime Course | Nausea | Vomit | Skin | 
| 1   | Regime A      | 1      | 2     | 1    | 
| 2   | Regime A      | 1      | 1     | 1    | 
| 3   | Regime A      | 2      | 3     | 2    | 
| 4   | Regime A      | 0      | 0     | 2    | 
| 5   | Regime B      | 3      | 0     | 3    | 
| 6   | Regime B      | 3      | 3     | 3    | 
| 7   | Regime B      | 1      | 3     | 1    | 
| 8   | Regime B      | 2      | 2     | 0    | 
| 9   | Regime A      | 3      | 2     | 0    | 
| 10  | Regime A      | 3      | 2     | 0    | 
| 11  | Regime A      | 0      | 1     | 1    | 
| 12  | Regime A      | 0      | 1     | 1    | 
| 13  | Regime B      | 1      | 1     | 2    | 
| 14  | Regime B      | 2      | 0     | 0    | 
| 15  | Regime B      | 1      | 0     | 2    | 
| 16  | Regime B      | 0      | 0     | 0    | 
|-----|---------------|--------|-------|------|

我应该使用什么查询才能获得两种制度的所有副作用的百分比(例如恶心=1，恶心=2，皮肤=0，皮肤=2(？

结果示例：

Regime A |  Nausea:0  | 0.375
Regime A |  Nausea:1  | 0.25
Regime A |  Nausea:2  | 0.125
Regime A |  Nausea:3  | 0.25
Regime B |  Nausea:0  | 0.125
Regime B |  Nausea:1  | 0.375
Regime B |  Nausea:2  | 0.25
Regime B |  Nausea:3  | 0.25
...
...

谢谢！

Answer 1

以下是获取恶心比率的解决方案：

select
regime,
nausea,
regime_nausea_count / (1.0*regime_count) as nausea_ratio
from 
(
select
regime,
nausea,
count(*) as regime_nausea_count
from
Toxicity
group by regime, nausea
),
(
select
regime,
count(*) as regime_count
from
Toxicity
group by regime
) using (regime);

显然，您可以用另一列替换恶心以获得相应的统计数据。