SQL如何对比两个created_at列 按日统计计数并计算占比百分比

先提个注意点：你原来写的SQL里用单引号包裹字段名是不规范的，单引号是字符串常量的标识，字段名要么直接写，要么用双引号/反引号包裹，之前能跑出结果应该是你所用数据库做了语法兼容，后续写SQL建议避开这个写法，避免出现解析异常。

实现核心逻辑是先拿到所有需要统计的日期维度集合，再分别关联两个时间字段的按天计数，最后计算百分比，不需要多次全表扫描，性能和准确性都有保证，下面给两种可直接复用的写法：

通用兼容写法（全数据库支持，无需额外建表） #

不需要依赖数据库特有语法，所有支持标准SQL的引擎都能跑：

WITH date_dim AS (
    -- 提取两个时间字段中所有出现过的非空日期，去重作为统计基准维度
    SELECT DATE_TRUNC('day', CreatedAt_1) AS stat_date FROM 你的表名 WHERE CreatedAt_1 IS NOT NULL
    UNION
    SELECT DATE_TRUNC('day', CreatedAt_2) AS stat_date FROM 你的表名 WHERE CreatedAt_2 IS NOT NULL
),
cnt_c1 AS (
    -- 按天统计CreatedAt_1的非空记录数
    SELECT
        -- 如果你原来的时区偏移逻辑是业务必须的，就把这里的DATE_TRUNC替换成你原来的截断写法
        DATE_TRUNC('day', CreatedAt_1) AS stat_date,
        COUNT(ID) AS c1_count
    FROM 你的表名
    -- 替换成你原来的时间范围过滤条件
    WHERE /* 你的time_interval条件 */
    GROUP BY 1
),
cnt_c2 AS (
    -- 按天统计CreatedAt_2的非空记录数
    SELECT
        DATE_TRUNC('day', CreatedAt_2) AS stat_date,
        COUNT(ID) AS c2_count
    FROM 你的表名
    -- 和上面保持完全一致的时间过滤条件，避免统计范围不一致
    WHERE /* 你的time_interval条件 */
    GROUP BY 1
)
SELECT
    dd.stat_date AS 统计日期,
    COALESCE(cc1.c1_count, 0) AS CreatedAt_1计数,
    COALESCE(cc2.c2_count, 0) AS CreatedAt_2计数,
    -- 分母为0时返回空，避免除零报错，需要返回0的话在外层套一层COALESCE即可
    ROUND(
        CASE WHEN COALESCE(cc2.c2_count, 0) = 0 THEN NULL
             ELSE 1.0 * COALESCE(cc1.c1_count, 0) / cc2.c2_count * 100
        END, 2
    ) AS 占比百分比
FROM date_dim dd
LEFT JOIN cnt_c1 cc1 ON dd.stat_date = cc1.stat_date
LEFT JOIN cnt_c2 cc2 ON dd.stat_date = cc2.stat_date
ORDER BY dd.stat_date ASC;

几个注意点：

• 计算时乘1.0是为了把整数转成浮点型，避免整数除法丢失精度（比如部分数据库里1/2会直接返回0，转浮点后才能得到0.5的正确结果）
• 如果你原来写的DATE_TRUNC加1天再减1天的逻辑是为了修正时区偏移问题，直接把上面两个计数CTE里的日期截断部分替换成你原来的写法即可，不需要改其他逻辑

简化写法（支持UNION ALL拆分逻辑的数据库可用） #

如果你用的是PostgreSQL、ClickHouse、BigQuery、SparkSQL等主流引擎，可以把代码写得更简洁，不需要拆三个CTE：

SELECT
    stat_date AS 统计日期,
    COUNT(c1_mark) AS CreatedAt_1计数,
    COUNT(c2_mark) AS CreatedAt_2计数,
    ROUND(1.0 * COUNT(c1_mark) / NULLIF(COUNT(c2_mark), 0) * 100, 2) AS 占比百分比
FROM (
    SELECT
        DATE_TRUNC('day', CreatedAt_1) AS stat_date,
        1 AS c1_mark,
        NULL AS c2_mark
    FROM 你的表名
    WHERE CreatedAt_1 IS NOT NULL AND /* 你的time_interval条件 */
    UNION ALL
    SELECT
        DATE_TRUNC('day', CreatedAt_2) AS stat_date,
        NULL AS c1_mark,
        1 AS c2_mark
    FROM 你的表名
    WHERE CreatedAt_2 IS NOT NULL AND /* 你的time_interval条件 */
) t
GROUP BY stat_date
ORDER BY stat_date ASC;

这个写法的逻辑是把两个时间字段拆成多行，非空的字段打标记，聚合时COUNT会自动忽略NULL值，直接得到对应字段的非空计数，代码量少一半，执行效率也更高。

用你提供的样例数据跑上述SQL，得到的结果如下：

如果需要做ID去重（比如同一个ID两个时间字段都是同一天，只算一次计数），把COUNT(标记字段)改成COUNT(DISTINCT ID)即可。

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