在含DATE_TRUNC的单查询中计算两个Count值的占比

我希望在单个PostgreSQL查询中计算两个COUNT值的比值，但DATE_TRUNC带来了一些问题。当前我的查询语句如下：

SELECT DATE_TRUNC('month', "Date") as date, 
COUNT(*) as AllTransactions,
COUNT(*) filter (where "State"='ACCEPTED') as Accepted,
FROM "Acceptance_Report"
GROUP BY 1
ORDER BY 1

该查询返回结果表：

现在需要计算已接受交易数的占比，公式为 Accepted/AllTransactions*100。我知道可以新建表并使用INSERT实现，但希望找到单查询的简便方法，请问有什么思路？

方法1：直接在原查询中计算占比 #

直接在SELECT列表新增占比字段即可，注意整数除法的截断问题，需要把其中一个数值转为浮点数或NUMERIC类型：

SELECT DATE_TRUNC('month', "Date") as date, 
COUNT(*) as AllTransactions,
COUNT(*) filter (where "State"='ACCEPTED') as Accepted,
-- 转NUMERIC后计算百分比，保留2位小数示例
ROUND(COUNT(*) filter (where "State"='ACCEPTED')::NUMERIC / COUNT(*) * 100, 2) as AcceptanceRate
FROM "Acceptance_Report"
GROUP BY 1
ORDER BY 1

方法2：用AVG简化计算（性能更优） #

占比本质是符合条件的记录数占总数的比例，用AVG()结合CASE语句可以避免重复统计COUNT，只需要一次全表扫描：

SELECT DATE_TRUNC('month', "Date") as date, 
COUNT(*) as AllTransactions,
COUNT(*) filter (where "State"='ACCEPTED') as Accepted,
-- 100.0确保浮点运算，避免整数截断
ROUND(AVG(CASE WHEN "State"='ACCEPTED' THEN 100.0 ELSE 0 END), 2) as AcceptanceRate
FROM "Acceptance_Report"
GROUP BY 1
ORDER BY 1

方法3：子查询/CTE包装（适合复杂场景） #

如果后续有更多衍生计算需求，可先用CTE或子查询统计基础数据，再计算占比：

WITH MonthlyStats AS (
    SELECT DATE_TRUNC('month', "Date") as date, 
    COUNT(*) as AllTransactions,
    COUNT(*) filter (where "State"='ACCEPTED') as Accepted
    FROM "Acceptance_Report"
    GROUP BY 1
)
SELECT 
    date,
    AllTransactions,
    Accepted,
    ROUND(Accepted::NUMERIC / AllTransactions * 100, 2) as AcceptanceRate
FROM MonthlyStats
ORDER BY date

以上三种方法都无需新建表，可直接在单查询内完成占比计算。

内容的提问来源于stack exchange，提问作者dwampa