Vertica时间序列分析查询中的重复与缺失值问题

1. 日期边界处理导致序列错位：你的查询中，TIMESERIES基于ADD_MONTHS(CURRENT_DATE(), -36)和CURRENT_DATE()生成每月时间点。如果这两个日期是月末（比如31日），当遇到没有对应日期的月份（如2023年2月没有31日），Vertica会自动将ts调整为该月最后一天（2023-02-28）。后续计算ADD_MONTHS时，会从2023-02-28再加1个月得到2023-03-28，而非预期的2023-03-01，最终导致月份计算错位，出现重复或缺失。
2. 冗余的日期转换逻辑：你手动拼接年月生成当月1号再做ADD_MONTHS的操作不仅冗余，还容易在边界场景下出错，比如日期格式转换时的隐式类型转换问题。

方法1：简化月份计算逻辑 #

直接使用TO_CHAR函数提取ts对应的月份，避免手动拼接日期：

SELECT
    TO_CHAR(ts, 'YYYY-MM') as validity_month
FROM (
    SELECT ADD_MONTHS(CURRENT_DATE(), -36)::TIMESTAMP as tm
    UNION ALL 
    SELECT CURRENT_DATE()::TIMESTAMP
) as t 
TIMESERIES ts as '1 MONTH' OVER (ORDER BY t.tm)

如果需求是生成每个ts月份的下一个月，则修改为：

SELECT
    TO_CHAR(ADD_MONTHS(ts, 1), 'YYYY-MM') as validity_month
FROM (
    SELECT ADD_MONTHS(CURRENT_DATE(), -36)::TIMESTAMP as tm
    UNION ALL 
    SELECT CURRENT_DATE()::TIMESTAMP
) as t 
TIMESERIES ts as '1 MONTH' OVER (ORDER BY t.tm)

方法2：固定生成每月第一天的时间序列 #

通过DATE_TRUNC将起始和结束日期统一为当月第一天，彻底避免月末日期的边界问题：

SELECT
    TO_CHAR(ts, 'YYYY-MM') as validity_month
FROM (
    SELECT DATE_TRUNC('month', ADD_MONTHS(CURRENT_DATE(), -36))::TIMESTAMP as tm
    UNION ALL 
    SELECT DATE_TRUNC('month', CURRENT_DATE())::TIMESTAMP
) as t 
TIMESERIES ts as '1 MONTH' OVER (ORDER BY t.tm)

这个方法生成的ts序列都是每月第一天，后续的月份计算完全不会出现错位，能保证序列无重复、无缺失。

验证结果 #

使用方法2后，从36个月前到当前的月份序列会是连续的，例如：

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