按年月统计布尔值数量及false/true比率的SQL查询问题

云上老周

2026-9-27

正确查询语句实现

适配支持`date_trunc`的数据库（PostgreSQL、Redshift、Spark SQL等）

WITH monthly_alive_stats AS (
    SELECT
        DATE_TRUNC('month', "timestamp") AS month_begin,
        COUNT(CASE WHEN alive = true THEN 1 END) AS true_count,
        COUNT(CASE WHEN alive = false THEN 1 END) AS false_count
    FROM 替换为你的实际表名
    GROUP BY DATE_TRUNC('month', "timestamp")
)
SELECT
    UPPER(TO_CHAR(month_begin, 'MON-YYYY')) AS "Period",
    true_count AS "True",
    false_count AS "False",
    ROUND(
        CASE WHEN true_count = 0 THEN NULL 
        ELSE false_count::NUMERIC / true_count END,
        2
    ) AS "false/true"
FROM monthly_alive_stats
ORDER BY month_begin ASC;

MySQL适配版本（不支持`date_trunc`场景）

SELECT
    UPPER(DATE_FORMAT(`timestamp`, '%b-%Y')) AS `Period`,
    SUM(alive = TRUE) AS `True`,
    SUM(alive = FALSE) AS `False`,
    ROUND(
        IF(SUM(alive = TRUE) = 0, NULL, SUM(alive = FALSE)/SUM(alive = TRUE)),
        2
    ) AS `false/true`
FROM 替换为你的实际表名
GROUP BY DATE_FORMAT(`timestamp`, '%Y-%m')
ORDER BY DATE_FORMAT(`timestamp`, '%Y-%m') ASC;