需求：获取各变量指定月份的最后有效状态

问题背景 #

我们需要为每个变量生成指定月份（示例中为2019年1-3月）的月末最后状态，核心要求是：即使变量在某个月份没有发生状态变更，也要沿用其最近一次的有效状态值。

示例输入 #

期望输出 #

关键规则 #

• 当变量在某月份没有状态变更记录时，月末状态继承最近一次（之前月份）的状态值（例如变量A在2019年3月无操作，状态沿用2月的70）
• Operation ID为递增序列，但不影响状态选择，我们只关注每个变量按时间顺序的最后状态

实现方案（以SQL为例） #

这里我们可以通过生成月份维度表 + 窗口函数的方式来实现：

1. 首先生成目标月份的维度表（示例中为2019年1-3月）：

WITH months AS (
    SELECT 1 AS month, 2019 AS year UNION ALL
    SELECT 2 AS month, 2019 AS year UNION ALL
    SELECT 3 AS month, 2019 AS year
),

2. 处理原始数据，提取每个记录的月份和年份，并为每个变量按时间排序，保留到每个月份的最新状态：

variable_states AS (
    SELECT
        Variable,
        EXTRACT(MONTH FROM TO_DATE(Date, 'DDMonYYYY')) AS month,
        EXTRACT(YEAR FROM TO_DATE(Date, 'DDMonYYYY')) AS year,
        State,
        ROW_NUMBER() OVER (PARTITION BY Variable, EXTRACT(YEAR FROM TO_DATE(Date, 'DDMonYYYY')), EXTRACT(MONTH FROM TO_DATE(Date, 'DDMonYYYY')) ORDER BY TO_DATE(Date, 'DDMonYYYY') DESC) AS rn
    FROM your_table_name
)

3. 关联维度表，使用LAST_VALUE窗口函数填充缺失月份的状态值：

SELECT
    m.Variable,
    m.month,
    m.year,
    LAST_VALUE(vs.State IGNORE NULLS) OVER (PARTITION BY m.Variable ORDER BY m.year, m.month) AS Last_State_Until_End_of_Month
FROM (
    SELECT DISTINCT t.Variable, mo.month, mo.year
    FROM your_table_name t
    CROSS JOIN months mo
) m
LEFT JOIN variable_states vs
    ON m.Variable = vs.Variable
    AND m.month = vs.month
    AND m.year = vs.year
    AND vs.rn = 1
ORDER BY m.Variable, m.year, m.month;

实现思路说明 #

• 先构建需要覆盖的月份维度，确保每个变量都能对应到所有目标月份
• 对原始数据按变量+月份分组，筛选出每个月的最后一条状态记录
• 通过LEFT JOIN将变量-月份维度和每月最后状态关联，再用LAST_VALUE函数向前填充缺失的状态值，实现无变更月份沿用最近状态的需求

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