如何用SQL获取月度库存快照并计算账龄，迁移至PowerBI？

我需要将Oracle BI中已实现的库存账龄分桶逻辑迁移至SQL数据集，再导入PowerBI，具体需求如下：

• 账龄计算逻辑：快照日期 - 物料收货日期（原逻辑为当前日期减收货日期，因需月度期末快照，需替换为对应月份的期末日期）
• 账龄分桶包含多区间（如<30天、30-60天…直至超过365天）
• 需获取各物料的月度期末库存快照，每个月份对应独立的账龄区间，库存数量存储在单独的库存表中
• 期望结果与库存账龄分桶月度快照示例一致

1. SQL端实现月度期末库存账龄分桶 #

假设核心涉及两张表：

• receiving_table（物料收货表）：含item_id（物料ID）、receiving_date（收货日期）、receive_qty（收货数量）
• inventory_snapshot（库存快照表）：含item_id（物料ID）、snapshot_date（月度期末快照日期）、end_qty（期末库存数量）

核心SQL代码 #

WITH aging_calc AS (
    -- 关联库存快照与收货记录，计算对应快照日期的账龄
    SELECT
        s.item_id,
        s.snapshot_date,
        s.end_qty,
        -- 计算快照日期到收货日期的天数差
        s.snapshot_date - r.receiving_date AS aging_days
    FROM inventory_snapshot s
    JOIN receiving_table r 
        ON s.item_id = r.item_id
        AND r.receiving_date <= s.snapshot_date
),
bucketed_data AS (
    -- 划分账龄分桶
    SELECT
        item_id,
        TO_CHAR(snapshot_date, 'YYYY-MM') AS report_month,
        end_qty,
        CASE
            WHEN aging_days < 30 THEN '<30天'
            WHEN aging_days BETWEEN 30 AND 59 THEN '30-60天'
            WHEN aging_days BETWEEN 60 AND 89 THEN '60-90天'
            WHEN aging_days BETWEEN 90 AND 179 THEN '90-180天'
            WHEN aging_days BETWEEN 180 AND 364 THEN '180-365天'
            WHEN aging_days >= 365 THEN '≥365天'
            ELSE '未定义区间'
        END AS aging_bucket
    FROM aging_calc
)
-- 按物料、月份、账龄分桶聚合库存数量
SELECT
    item_id,
    report_month,
    aging_bucket,
    SUM(end_qty) AS total_inventory
FROM bucketed_data
GROUP BY item_id, report_month, aging_bucket
ORDER BY item_id, report_month, aging_bucket;

关键说明 #

• 用月度期末快照日期替代原逻辑的当前日期，确保账龄计算对应每个月的期末节点，符合快照需求
• 若库存快照表未预先按月度聚合，需先对库存表按item_id和每月最后一天聚合出期末库存
• 可根据实际需求调整CASE语句中的账龄区间划分

2. PowerBI导入与可视化优化 #

• 将上述SQL查询结果作为数据集导入PowerBI
• 若需基于报表查看日期动态计算账龄，可在PowerBI中添加DAX计算列：

  账龄天数 = DATEDIFF(MAX('receiving_table'[receiving_date]), TODAY(), DAY)
  账龄分桶 = SWITCH(
      TRUE(),
      [账龄天数] < 30, "<30天",
      [账龄天数] >=30 && [账龄天数]<60, "30-60天",
      [账龄天数] >=60 && [账龄天数]<90, "60-90天",
      [账龄天数] >=90 && [账龄天数]<180, "90-180天",
      [账龄天数] >=180 && [账龄天数]<365, "180-365天",
      [账龄天数] >=365, "≥365天",
      "未定义区间"
  )
  

• 使用矩阵可视化组件：行放置物料ID，列放置report_month和aging_bucket，值放置total_inventory，即可还原示例报表样式

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