使用Oracle Model Clause实现按年新增年末记录到结果集

假设你的源表名为t，包含d（DATE类型）、v（NUMBER类型）两个字段，实现SQL如下：

SELECT d, v
FROM t
MODEL
  PARTITION BY (EXTRACT(YEAR FROM d) AS yr) -- 按年份拆分数据独立处理
  DIMENSION BY (ROW_NUMBER() OVER(PARTITION BY EXTRACT(YEAR FROM d) ORDER BY d) AS rn) -- 同一年内按日期升序生成序号
  MEASURES (d, v, 0 AS is_new) -- 定义度量：原日期、原V值、新增行标记
  RULES UPSERT (
    -- 为每个年份插入新行，序号为当年最大序号+1
    d[rn = MAX(rn)[ANY] + 1] = TO_DATE(yr || '-12-31', 'yyyy-mm-dd'),
    v[rn = MAX(rn)[ANY] + 1] = v[MAX(rn)[ANY]], -- 取当年最后一条已有记录的V值
    is_new[rn = MAX(rn)[ANY] + 1] = 1
  )
ORDER BY d;

如果需要完全匹配你给出的带<<标记的输出格式，可以调整查询字段的拼接逻辑：

SELECT TO_CHAR(d, 'yyyy-mm-dd') || ', ' || v || CASE WHEN is_new = 1 THEN ' <<' ELSE '' END AS output_str
FROM t
MODEL
  PARTITION BY (EXTRACT(YEAR FROM d) AS yr)
  DIMENSION BY (ROW_NUMBER() OVER(PARTITION BY EXTRACT(YEAR FROM d) ORDER BY d) AS rn)
  MEASURES (d, v, 0 AS is_new)
  RULES UPSERT (
    d[rn = MAX(rn)[ANY] + 1] = TO_DATE(yr || '-12-31', 'yyyy-mm-dd'),
    v[rn = MAX(rn)[ANY] + 1] = v[MAX(rn)[ANY]],
    is_new[rn = MAX(rn)[ANY] + 1] = 1
  )
ORDER BY d;

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