PostgreSQL实现：某月全量记录+上月最后一条记录的窗口查询

假设表名为disk_quota_requests，字段为request_time(datetime)、path(varchar)、quota(int/bigint)。需求是：针对指定路径，计算某目标月份所有请求的配额聚合（比如总和），同时获取该路径上月最后一条请求的配额值。

方案一：单扫描+窗口函数（最优性能） #

通过CTE（公共表表达式）结合窗口函数，仅扫描一次表即可完成所有计算，是性能最优的方案。

WITH request_with_month AS (
    SELECT
        path,
        quota,
        -- 提取记录所属的年月（格式如'2024-05'）
        DATE_FORMAT(request_time, '%Y-%m') AS request_month,
        -- 窗口函数：按路径+年月分组，按请求时间倒序标记当月最后一条记录
        ROW_NUMBER() OVER (PARTITION BY path, DATE_FORMAT(request_time, '%Y-%m') ORDER BY request_time DESC) AS rn_in_month,
        -- 窗口函数：计算当前路径当月的配额总和
        SUM(quota) OVER (PARTITION BY path, DATE_FORMAT(request_time, '%Y-%m')) AS monthly_quota_sum,
        -- 窗口函数：获取当前路径上月最后一条的配额
        LAG(CASE WHEN rn_in_month = 1 THEN quota END) OVER (PARTITION BY path ORDER BY request_month) AS last_month_last_quota
    FROM disk_quota_requests
)
-- 过滤指定路径和目标月份，去重取唯一结果
SELECT DISTINCT
    path,
    request_month AS target_month,
    monthly_quota_sum,
    last_month_last_quota
FROM request_with_month
WHERE
    path = '/data/app' -- 指定路径
    AND request_month = '2024-05'; -- 目标月份

关键点说明：

• 日期格式化函数需适配数据库：MySQL用DATE_FORMAT，PostgreSQL用TO_CHAR(request_time, 'YYYY-MM')，SQL Server用FORMAT(request_time, 'yyyy-MM')
• LAG函数配合CASE WHEN rn_in_month=1，精准抓取每个路径上月最后一条记录的配额
• 用DISTINCT去重，因为当月每条记录都会携带相同的聚合值和上月数据

方案二：聚合子查询关联（兼容性好） #

如果对窗口函数不熟悉，可通过两个子查询分别计算当月聚合和上月最后一条记录，再关联结果。适合老版本数据库，性能略逊于方案一，但有合适索引时差异不大。

-- 子查询1：计算指定路径目标月份的配额总和
WITH monthly_sum AS (
    SELECT
        path,
        DATE_FORMAT(request_time, '%Y-%m') AS target_month,
        SUM(quota) AS monthly_quota_sum
    FROM disk_quota_requests
    WHERE
        path = '/data/app'
        AND request_time BETWEEN '2024-05-01 00:00:00' AND '2024-05-31 23:59:59'
    GROUP BY path, DATE_FORMAT(request_time, '%Y-%m')
),
-- 子查询2：获取指定路径上月的最后一条请求配额
last_month_last AS (
    SELECT
        path,
        quota AS last_month_last_quota
    FROM disk_quota_requests
    WHERE
        path = '/data/app'
        AND DATE_FORMAT(request_time, '%Y-%m') = '2024-04' -- 目标月份的上月
    ORDER BY request_time DESC
    LIMIT 1 -- 取最后一条
)
-- 关联两个子查询结果
SELECT
    ms.path,
    ms.target_month,
    ms.monthly_quota_sum,
    lml.last_month_last_quota
FROM monthly_sum ms
LEFT JOIN last_month_last lml ON ms.path = lml.path;

性能优化核心 #

无论哪种方案，创建**复合索引(path, request_time DESC)**能大幅提速：

• 快速定位指定路径的所有记录
• 按时间倒序排列后，无需额外排序即可获取每月最后一条记录
• 聚合计算时可利用索引缩小扫描范围

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