如何用PostgreSQL生成资产每周状态的堆叠柱状图数据源？

问题描述 #

我有一张资产表（示例含3个资产），包含assetID、status、updated_date字段。每周仅插入状态发生变更的资产记录——比如2个资产状态变更就只新增2条记录。经过数周后，我需要制作堆叠柱状图展示每周所有资产的状态变化：X轴为各周日期，Y轴为对应状态的资产数量。但当前表仅包含变更记录，无法直接生成所需的完整数据源，需要用PostgreSQL实现补全数据的需求。

当前表数据 #

理想数据源（每周所有资产的状态记录） #

解决方案 #

核心思路是：先生成所有需要统计的周日期与所有资产ID的组合，再为每个组合匹配该资产在对应周及之前的最新状态。

以下是具体的PostgreSQL SQL语句：

WITH 
-- 1. 提取所有需要统计的周日期（原表中出现的所有更新日期）
weekly_dates AS (
    SELECT DISTINCT updated_date
    FROM assets
    ORDER BY updated_date
),
-- 2. 提取所有唯一的资产ID
all_assets AS (
    SELECT DISTINCT assetID
    FROM assets
),
-- 3. 生成每个资产对应每个周日期的组合
asset_week_combinations AS (
    SELECT a.assetID, w.updated_date
    FROM all_assets a
    CROSS JOIN weekly_dates w
)
-- 4. 为每个组合匹配最新的状态
SELECT 
    ac.assetID,
    (
        SELECT status
        FROM assets
        WHERE assetID = ac.assetID
          AND updated_date <= ac.updated_date
        ORDER BY updated_date DESC
        LIMIT 1
    ) AS status,
    ac.updated_date
FROM asset_week_combinations ac
ORDER BY ac.updated_date, ac.assetID;

代码解释 #

• weekly_dates：从原表中提取所有唯一的updated_date，这些就是我们需要统计的周节点。如果你的周是按自然周划分（比如每周一），可以用generate_series生成连续的周起始日期，替换这部分逻辑。
• all_assets：提取所有资产ID，确保每个资产都能出现在每一周的记录中。
• asset_week_combinations：通过笛卡尔积，得到每个资产和每个周日期的组合，这一步是补全缺失记录的基础。
• 最后一步的子查询：对每个资产-周组合，找到该资产在该周日期及之前最后一次更新的状态，也就是当前周该资产的实际状态。

如果需要按自然周统计（比如不管原表的更新日期，取每周的固定日期），可以修改weekly_dates部分为：

weekly_dates AS (
    SELECT generate_series(
        (SELECT DATE_TRUNC('week', MIN(updated_date)) FROM assets),
        (SELECT DATE_TRUNC('week', MAX(updated_date)) FROM assets),
        '1 week'::interval
    )::date AS updated_date
)

这样会生成从最早记录的周一开始，到最晚记录的周一的连续周日期。

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