如何在单个查询中计算百分比的百分比

Question

使用databricks SQL创建了一个表，如下所示:

<表类> 表名 RunDate 质量 qaflag 状态 tbody><<tr>等等2022-06-02青铜1通过了blah12022-06-02银1失败

切换到distinct的窗口函数以消除group by的需求，然后使用case表达式:

SELECT distinct Quality
, count(Status) over (partition by Quality) * 100 / count(status) over (partition by null) PercentageTotal
, count(case when Status = 'Passed' then 1 end) over (partition by Quality) * 100 / count(status) over (partition by Quality) PercentagePassed
, count(case when Status = 'Failed' then 1 end) over (partition by Quality) * 100 / count(status) over (partition by Quality) PercentageFailed
FROM test

您可能想要考虑切换到另一种支持小数的数据类型，但我在您的示例中保留了它。