如何通过SQL创建月度快照式时间序列表？

核心思路 #

不需要修改原表，而是通过生成固定的月末快照日期列表，关联原表筛选出截至对应月末的数据，再用递归CTE+关联的方式（本质是用递归替代重复写UNION）合并所有月份的快照结果，最终得到带12个月快照日期的时间序列表。自连接在这里不适用，因为我们要的是不同时间点的独立快照，不是同表数据的关联匹配。

具体实现步骤 #

1. 生成12个月末的快照日期

用递归CTE可以高效生成最近12个月的月末日期，避免手动写12条UNION语句：

WITH RECURSIVE snapshot_dates AS (
    -- 初始值：当前月的最后一天
    SELECT 
        DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 day' AS snapshot_date,
        1 AS month_counter
    UNION ALL
    -- 递归生成前11个月的月末日期
    SELECT 
        DATE_TRUNC('month', snapshot_date - INTERVAL '1 month') - INTERVAL '1 day',
        month_counter + 1
    FROM snapshot_dates
    WHERE month_counter < 12
)

2. 关联原表生成快照数据

将生成的快照日期与原表关联，筛选出截至该月末的所有数据，并为每条数据打上对应的固定快照日期：

SELECT 
    sd.snapshot_date, -- 固定的月末快照日期，不会随原表每日更新变化
    t.* -- 原表的所有业务字段
FROM snapshot_dates sd
LEFT JOIN your_daily_table t
    -- 假设原表有`data_date`字段记录数据对应的日期；如果是逐月新增，也可以用创建时间`created_at`
    ON t.data_date <= sd.snapshot_date
-- 按快照日期倒序，方便查看最新快照
ORDER BY sd.snapshot_date DESC;

如果原表没有日期字段，而是用year_month（比如2024-05）标识数据所属月份，可以调整关联条件：

ON t.year_month <= TO_CHAR(sd.snapshot_date, 'YYYY-MM')

3. 保存为持久化快照表（可选）

如果需要将结果保存为固定表（避免每次查询都重新生成），可以用CREATE TABLE AS：

CREATE TABLE monthly_snapshot_table AS
WITH RECURSIVE snapshot_dates AS (
    SELECT DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 day' AS snapshot_date, 1 AS month_counter
    UNION ALL
    SELECT DATE_TRUNC('month', snapshot_date - INTERVAL '1 month') - INTERVAL '1 day', month_counter + 1
    FROM snapshot_dates
    WHERE month_counter < 12
)
SELECT 
    sd.snapshot_date,
    t.*
FROM snapshot_dates sd
LEFT JOIN your_daily_table t
    ON t.data_date <= sd.snapshot_date
ORDER BY sd.snapshot_date DESC;

方案优势 #

• 快照日期是固定的月末值，彻底解决你之前用ADD COLUMN导致日期随原表更新变化的问题；
• 通过LEFT JOIN确保每个月末都有对应的快照记录（即使当月无新增数据，也会保留快照日期）；
• 最终的时间序列表可直接导入Tableau，通过snapshot_date维度筛选，直观查看各月份的数据状态变化。

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