如何使用Reshape在查询中按时间顺序填充价格字段的空值

嘿，我完全懂你的困扰——现在生成了每个商品的每日日期序列后，关联价格历史得到的结果里有好多空价格，得按照规则把这些空值填上：早于第一个有价格日期的用该商品的第一个价格，晚于的就用最后一个价格，对吧？你提到的Reshape应该是指重塑数据来填充空值的意思吧？在PostgreSQL里咱们不用复杂的工具，用窗口函数就能轻松实现这个需求。

咱们先理清楚核心思路 #

要实现你的填充规则，得先给每个商品算出两个关键信息：

• 第一个出现的非空价格，以及它对应的日期
• 最后一个出现的非空价格
  然后针对每条空价格的记录，看它的日期是在第一个价格日期之前还是之后，选择对应的填充值就行。

修改后的完整查询 #

-- 先计算每个商品的关键价格参数：第一个价格、第一个价格日期、最后一个价格
WITH article_price_params AS (
    SELECT
        article,
        -- 获取该商品第一个非空价格
        FIRST_VALUE(price) OVER (PARTITION BY article ORDER BY date) AS first_price,
        -- 获取第一个价格对应的日期
        FIRST_VALUE(date) OVER (PARTITION BY article ORDER BY date) AS first_price_date,
        -- 获取该商品最后一个非空价格，注意要指定窗口范围才能拿到全局最后一个值
        LAST_VALUE(price) OVER (PARTITION BY article ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_price
    FROM
        market.prices_history
    WHERE
        price_type_id = 'W23'
),
-- 去重，确保每个商品只有一条参数记录
unique_article_params AS (
    SELECT DISTINCT article, first_price, first_price_date, last_price
    FROM article_price_params
),
-- 保持你原来的日期序列生成逻辑
date_series AS (
    SELECT
        generate_series(
            '2022-01-01',
            '2024-12-31',
            '1 day'::interval
        )::date AS date,
        article
    FROM
        (SELECT DISTINCT article FROM market.prices_history WHERE article NOT IN ('1', '100-3519200')) AS articles
)
-- 主查询：关联参数和价格历史，填充空值
SELECT
    ds.date,
    ds.article,
    -- 填充逻辑：优先用当日价格，没有的话根据日期选第一个或最后一个价格
    COALESCE(
        ph.price,
        CASE
            WHEN ds.date < uap.first_price_date THEN uap.first_price
            ELSE uap.last_price
        END
    ) AS filled_price
FROM
    date_series ds
-- 先关联商品的价格参数，确保每条记录都有填充用的值
LEFT JOIN unique_article_params uap 
    ON ds.article = uap.article
-- 再关联当日的价格历史
LEFT JOIN market.prices_history ph 
    ON ds.date = ph.date 
    AND ds.article = ph.article
    AND ph.price_type_id = 'W23'
ORDER BY
    ds.article,
    ds.date

为啥这么写？ #

1. article_price_params：用窗口函数一次性算出每个商品的三个关键值，LAST_VALUE必须指定窗口范围，不然默认只会取到当前行之前的最后一个值，拿不到全局的最后价格。
2. unique_article_params：因为窗口函数会给每条价格记录都带上参数，所以用DISTINCT去重，每个商品只留一套参数，避免关联后出现重复记录。
3. 主查询：用COALESCE优先取当日的真实价格，空的话就进入CASE判断——日期在第一个价格之前就用第一个价格，否则用最后一个价格，完全符合你的要求。

这样调整后，你的查询就能输出每个日期都有填充后价格的结果啦！

备注：内容来源于stack exchange，提问作者Sergey Bakaev Rettley