如何在PostgreSQL中获取所有表过去一年的每日新增行数？

以下是适配你需求的PostgreSQL查询，它会统计public schema下所有非系统表（非pg_开头）过去一年的每日新增行数：

方案一：包含所有日期（无新增时显示0） #

这个方案会生成过去一年的完整日期序列，即使某天没有新增数据也会显示0，更适合做趋势分析：

WITH date_range AS (
    -- 生成从一年前今天到昨天的所有日期
    SELECT generate_series(
        current_date - INTERVAL '1 year',
        current_date - INTERVAL '1 day',
        INTERVAL '1 day'
    )::DATE AS record_date
),
table_list AS (
    -- 筛选目标表，复用你原查询的表范围逻辑
    SELECT table_schema, table_name
    FROM information_schema.tables
    WHERE table_name NOT LIKE 'pg_%'
      AND table_schema = 'public'
),
dynamic_queries AS (
    -- 为每个表生成按日期统计的查询语句
    SELECT format(
        $$
            SELECT
                '%I' AS table_schema,
                '%I' AS table_name,
                dr.record_date,
                COUNT(t.*) AS daily_new_rows
            FROM date_range dr
            LEFT JOIN %I.%I t
                ON t.created_at::DATE = dr.record_date
            GROUP BY dr.record_date
            ORDER BY dr.record_date
        $$,
        table_schema, table_name, table_schema, table_name
    ) AS query
    FROM table_list
)
-- 执行所有动态查询并合并结果
SELECT * FROM dynamic_queries, LATERAL(query_to_xml(query, false, true, '')) AS q;

方案二：仅包含有新增数据的日期 #

如果只需要显示实际有新增的日期，这个方案更简洁，生成的SQL可以直接执行：

WITH table_list AS (
    SELECT table_schema, table_name
    FROM information_schema.tables
    WHERE table_name NOT LIKE 'pg_%'
      AND table_schema = 'public'
)
SELECT string_agg(
    format(
        $$
            SELECT
                '%I' AS table_schema,
                '%I' AS table_name,
                DATE(created_at) AS record_date,
                COUNT(*) AS daily_new_rows
            FROM %I.%I
            WHERE created_at >= current_date - INTERVAL '1 year'
            GROUP BY DATE(created_at)
        $$,
        table_schema, table_name, table_schema, table_name
    ),
    ' UNION ALL '
) AS combined_query
FROM table_list;

运行上述查询后，复制输出的combined_query字段内容，直接执行就能得到结果。

关键说明 #

• 两个方案都依赖created_at字段是可转换为日期的时间类型（如timestamp、timestamptz）
• 方案一用LEFT JOIN保证日期完整性，适合报表或趋势分析；方案二只返回有数据的日期，查询效率更高
• 如果你需要调整时间范围，修改current_date - INTERVAL '1 year'为目标起始时间即可

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