如何在PostgreSQL 16.2中计算指定周期内日均库存状态平均值

现有表结构 #

库存状态表 stockstatus #

create table stockstatus (
    stockdate date not null,  -- 库存状态日期
    product  character(60) not null,  -- 产品ID
    status  int not null, -- 当日结束时的库存状态
    constraint primary key ( stockdate, product )
);

结果表 AverageStockState #

create table AverageStockState (
    product character(60) primary key,
    status  int -- 指定周期内的日均库存状态值
);

需求说明 #

计算指定周期内每个产品的日均库存状态平均值，规则为：

• 某一天的库存状态沿用最近一次更新的状态值（即一条记录的状态会持续到下一条记录的前一天，或周期结束日）
• 按状态持续天数加权平均后，取整存入结果表

示例：某产品12月库存数据如下：

stockdate   status
2023-12-01  10
2023-12-05  20

计算逻辑：12月1日-4日状态为10（共4天），12月5日-31日状态为20（共27天），日均库存为 (4*10 + 27*20)/31 ≈19，结果表需存储该产品ID和数值19。

解决方案 #

使用PostgreSQL窗口函数和CTE实现，以下SQL以2023年12月为例（可自行修改周期起止日期）：

WITH stock_period AS (
    -- 处理周期内的库存记录，计算每个状态的有效起止日期
    SELECT
        product,
        status,
        GREATEST(stockdate, '2023-12-01'::date) AS start_date,
        COALESCE(LEAD(stockdate) OVER (PARTITION BY product ORDER BY stockdate) - INTERVAL '1 day', '2023-12-31'::date) AS end_date
    FROM stockstatus
    WHERE stockdate BETWEEN '2023-12-01' AND '2023-12-31'
    
    UNION ALL
    
    -- 处理周期开始前最后一条有效记录，覆盖周期起始到第一条记录之间的天数
    SELECT
        product,
        status,
        '2023-12-01'::date AS start_date,
        (MIN(stockdate) OVER (PARTITION BY product) - INTERVAL '1 day')::date AS end_date
    FROM stockstatus
    WHERE stockdate < '2023-12-01'
    AND EXISTS (SELECT 1 FROM stockstatus s2 WHERE s2.product = stockstatus.product AND s2.stockdate >= '2023-12-01')
),
stock_weighted AS (
    -- 计算每个状态的持续天数和加权值
    SELECT
        product,
        status,
        GREATEST(0, (end_date - start_date + INTERVAL '1 day')::int) AS days,
        status * GREATEST(0, (end_date - start_date + INTERVAL '1 day')::int) AS weighted_value
    FROM stock_period
    WHERE start_date <= '2023-12-31'
)
-- 计算加权平均值，插入/更新结果表
INSERT INTO AverageStockState (product, status)
SELECT
    product,
    ROUND(SUM(weighted_value) / SUM(days))::int AS avg_status
FROM stock_weighted
GROUP BY product
ON CONFLICT (product) DO UPDATE SET status = EXCLUDED.status;

逻辑说明 #

1. stock_period CTE：
   • 对周期内的每条记录，用LEAD窗口函数获取下一条记录的日期，从而确定当前状态的结束日（下一条记录的前一天）；若无后续记录，结束日设为周期最后一天
   • 补充周期开始前的最后一条有效记录，确保周期起始日到第一条记录之间的天数也被计算
2. stock_weighted CTE：计算每个状态的持续天数及对应的加权值（状态值×天数）
3. 最后分组计算加权平均值，用ROUND取整后插入结果表；若产品已存在，则更新状态值

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