如何编写通用SQL统计指定周期内record表的每日新建记录数

你当前编写的单日统计SQL存在两处核心逻辑偏差，完全没必要针对每一天单独执行一次查询：

1. extract(isodow from created_at) = 1 是匹配所有标记为周一的日期记录，并非锁定某一个固定自然日
2. 整段SQL实际统计的是「过去7天内所有周一的新增记录总数」，既不能输出单天计数，也无法直接扩展为按天批量统计
   如果采用逐天单查的方案，不仅执行效率极低，还会直接跳过没有新增记录的日期，出现结果缺漏。

核心实现思路 #

通用查询只需要拆解为两个固定步骤，支持任意时长的周期统计（3天、7天、30天都可直接切换）：

• 按照自定义的统计时长，生成覆盖整个周期的连续自然日序列，哪怕当天没有任何新增记录，也会保留对应的日期节点，不会出现统计结果缺行的问题
• 将record表按「创建时间截断到自然日」的粒度分组统计数量，和上面生成的连续日期序列做左关联，给没有记录的日期自动补计0值

可直接复用的通用查询语法 #

基于你当前使用的PostgreSQL环境（和原SQL使用的current_date、extract函数匹配），只需要修改自定义统计天数的参数值，就能适配不同的统计时长：

WITH date_range AS (
    -- 生成覆盖完整统计周期的连续日期序列
    SELECT 
        generate_series(
            current_date - INTERVAL '1 day' * 自定义统计天数,
            current_date - INTERVAL '1 day',
            INTERVAL '1 day'
        ) AS stat_date
),
daily_record_stat AS (
    -- 按天粒度统计周期内的新增记录数
    SELECT
        DATE(created_at) AS record_date,
        COUNT(id) AS issue_count
    FROM record
    -- 提前过滤统计周期外的数据，避免全表扫描
    WHERE created_at >= current_date - INTERVAL '1 day' * 自定义统计天数
    GROUP BY DATE(created_at)
)
SELECT
    -- 按照需要的格式，把日期序号转成day1/day2的展示格式
    'day' || ROW_NUMBER() OVER (ORDER BY dr.stat_date ASC) AS "Number of day",
    -- 没有记录的天自动补0
    COALESCE(st.issue_count, 0) AS "issue count"
FROM date_range dr
LEFT JOIN daily_record_stat st
ON dr.stat_date = st.record_date
ORDER BY dr.stat_date ASC;

上述查询为PostgreSQL原生语法，无需额外扩展即可直接运行。

常见场景适配说明 #

• 统计最近3天的每日记录数：把SQL里所有自定义统计天数的位置替换成3，运行后就会输出3行按天计数的结果，和预期格式完全一致
• 统计最近1周的记录数：把参数值替换为7即可
• 统计最近30天的记录数：把参数值替换为30即可
• 如果需要过滤特定状态的记录，只需要在daily_record_stat的WHERE条件里加上对应规则，比如status = 有效状态值，不会影响整体查询结构

输出示例（以统计3天的结果为例） #

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