mysql - 如何计算两个查询之间的百分比？

Question

我希望生成一个结果或创建一个基于计算一个查询/另一个查询的百分比的 View 。

这些是查询:

查询 1:

SELECT
count(distinct email), Utility
from elec_cust
where email != 'NULL' and MAILLIST = '1'
GROUP BY Utility
ORDER BY count(distinct email)

和查询 2:

SELECT
count(distinct email), Utility
from elec_cust
where email != 'NULL' and MAILLIST = '0'
GROUP BY Utility
ORDER BY count(distinct email)

我希望以百分比形式生成 Query2/Query1 的结果:

效用 |百分比

Answer 1

您可以使用条件聚合来做到这一点:

SELECT COUNT(DISTINCT CASE WHEN MAILLIST = '1' THEN email END) as Query_1_result,
       COUNT(DISTINCT CASE WHEN MAILLIST = '0' THEN email END) as Query_2_result,
       COUNT(DISTINCT CASE WHEN MAILLIST = '1' THEN email END)/COUNT(DISTINCT CASE WHEN MAILLIST = '0' THEN email END)*100 AS Perc_Col ,
       Utility
from elec_cust
where email != 'NULL' 
GROUP BY Utility

MySQL 将 bool 表达式视为 1 表示真，0 表示假。所以这将对 MAILLIST = '1' 求和并将其除以 MAILLIST = '0'

您可以删除前两列，为了您的方便，我添加了它们。