如何统计截至当前记录、日期早于所在周周日的累计记录数？

AI 小北

2026-7-5

解决方案

针对你的inventory表，需要统计截至对应周周日的累计记录总数，以下是两种可行的实现方式：

方法一：基于周截止日期统计累计

假设get_week_number是你用于计算周数的自定义函数（返回当年的周序号），先确定每个周的周日截止日期，再统计所有早于等于该日期的记录总数：

WITH weekly_summary AS (
    -- 提取每个周的周数及对应周日日期
    SELECT
        get_week_number(date::DATE) AS week_number,
        MAX(date_trunc('week', date::DATE)::DATE + INTERVAL '6 days') AS week_end_sunday
    FROM inventory
    GROUP BY week_number
    ORDER BY week_number
)
SELECT
    ws.week_number,
    -- 统计所有日期≤当前周周日的记录总数
    (SELECT COUNT(*) FROM inventory i WHERE i.date::DATE <= ws.week_end_sunday) AS count
FROM weekly_summary ws;

方法二：窗口函数累计求和（更高效）

先按周统计单周记录数，再用窗口函数对周数排序后做累计求和：

WITH weekly_counts AS (
    -- 统计每个周的单周记录数
    SELECT
        get_week_number(date::DATE) AS week_number,
        COUNT(*) AS weekly_count
    FROM inventory
    GROUP BY week_number
    ORDER BY week_number
)
SELECT
    week_number,
    -- 按周数顺序累计求和，得到截至当前周的总记录数
    SUM(weekly_count) OVER (ORDER BY week_number) AS count
FROM weekly_counts;