PostgreSQL月度数据库增长统计：生成指定格式月度容量汇总表

看起来你已经有了每日数据库容量的基础数据，现在需要解决两个核心问题：计算每月的准确增长值，以及生成每月初始容量的汇总表。咱们一步步来解决：

一、先明确数据现状 #

你的表testtable记录了每日的time_stmp（日期）、datname（数据库名）和datsize（容量），注意到5月30日的数据缺失，后续的SQL会自动处理这种“当月某一天无记录”的情况。

二、生成你需要的每月初始容量汇总表 #

你想要的是每月1号（或当月最早记录）的容量数据，这里可以用窗口函数ROW_NUMBER()按月份分组，取出每个月的第一条记录：

SELECT time_stmp, datname, datsize
FROM (
    SELECT 
        time_stmp,
        datname,
        datsize,
        ROW_NUMBER() OVER (PARTITION BY DATE_TRUNC('month', time_stmp), datname ORDER BY time_stmp) AS rn
    FROM testtable
) t
WHERE rn = 1
ORDER BY time_stmp;

说明： #

• PARTITION BY DATE_TRUNC('month', time_stmp), datname：按“月份+数据库名”分组，确保每个数据库每个月只取一条记录
• ORDER BY time_stmp：按日期排序，rn=1就会取出当月最早的那条记录（如果1号有数据就是1号的，没有的话取当月第一天存在的记录）
• 最终结果就是类似你想要的每月初始容量汇总表，会按日期排序输出。

三、计算每月的准确总增长数据 #

要计算每月增长，我们需要拿到当月最后一天的容量和上月最后一天的容量，两者的差值就是当月的增长值。这里用窗口函数LAG()来获取上月的月末容量：

WITH monthly_end AS (
    -- 先获取每个月最后一天的容量（如果月末无记录，取当月最晚的记录）
    SELECT 
        DATE_TRUNC('month', time_stmp) AS month,
        datname,
        time_stmp AS end_date,
        datsize AS end_size
    FROM (
        SELECT 
            time_stmp,
            datname,
            datsize,
            ROW_NUMBER() OVER (PARTITION BY DATE_TRUNC('month', time_stmp), datname ORDER BY time_stmp DESC) AS rn
        FROM testtable
    ) t
    WHERE rn = 1
)
SELECT 
    month,
    datname,
    end_date,
    end_size,
    LAG(end_size) OVER (PARTITION BY datname ORDER BY month) AS prev_month_end_size,
    -- 计算增长值，注意要先把MB转成数值再计算，避免字符串无法直接相减
    (REPLACE(end_size, ' MB', '')::NUMERIC - REPLACE(LAG(end_size) OVER (PARTITION BY datname ORDER BY month), ' MB', '')::NUMERIC) || ' MB' AS monthly_growth
FROM monthly_end
ORDER BY month;

关键说明： #

1. 处理容量的数值计算：因为你的datsize是带MB的字符串，所以需要用REPLACE去掉单位，转成数值类型才能计算差值，最后再拼接回MB。
2. 处理月末无记录的情况：同样用ROW_NUMBER()按月份倒序取第一条，确保拿到当月最晚的容量记录。
3. LAG()函数的作用：它会获取同一数据库上一个月的月末容量，这样就能直接计算出当月的增长值。

四、如果需要精确匹配每月1号和月末最后一天的数据 #

如果你的业务要求必须严格取每月1号和当月最后一天（哪怕当天无记录就显示空），可以用生成日期序列的方式来关联：

-- 生成全年的每月1号和月末日期
WITH date_range AS (
    SELECT 
        generate_series(
            '2018-01-01'::DATE,
            '2018-12-31'::DATE,
            '1 month'::INTERVAL
        ) AS month_start,
        (generate_series(
            '2018-01-01'::DATE,
            '2018-12-31'::DATE,
            '1 month'::INTERVAL
        ) + INTERVAL '1 month - 1 day')::DATE AS month_end
),
-- 关联原表数据
monthly_data AS (
    SELECT
        dr.month_start,
        dr.month_end,
        t1.datname,
        t1.datsize AS start_size,
        t2.datsize AS end_size
    FROM date_range dr
    LEFT JOIN testtable t1 ON t1.time_stmp = dr.month_start
    LEFT JOIN testtable t2 ON t2.time_stmp = dr.month_end
    WHERE t1.datname = 'test' -- 可以根据需要去掉过滤，统计所有数据库
)
SELECT
    month_start AS time_stmp,
    datname,
    start_size AS datsize,
    end_size,
    CASE 
        WHEN start_size IS NOT NULL AND end_size IS NOT NULL THEN 
            (REPLACE(end_size, ' MB', '')::NUMERIC - REPLACE(start_size, ' MB', '')::NUMERIC) || ' MB' 
        ELSE '数据缺失' 
    END AS monthly_growth
FROM monthly_data
ORDER BY month_start;

说明： #

• 这个方法会先生成2018年每个月的1号和月末日期，然后左关联你的表，这样即使当天无记录，也会显示对应的日期，方便你排查数据缺失的情况。

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