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

Question

目前我正面临在Vertica中生成特定开始日期的日期列表的问题。基于这篇文章https://forum.vertica.com/discussion/240532/use-time-series-analytics-to-generate-a-list-of-dates-at-a-specific-start-date，我得到了我想要的数据。但是最近，不知怎么的，我的查询生成了重复的日期(月)，最后一个月(三月)完全丢失了。

我查询:

SELECT
LEFT(ADD_MONTHS((EXTRACT (YEAR FROM ts::DATE) || '-' || EXTRACT(MONTH FROM ts::DATE) ||
'-01')::DATE, 1)::varchar, 7) as validity_month
FROM (
SELECT ADD_MONTHS(CURRENT_DATE(), -36)::TIMESTAMP as tm -- take the last 36 months
UNION ALL 
SELECT CURRENT_DATE()::TIMESTAMP
) as t TIMESERIES ts as '1 MONTH' OVER (ORDER BY t.tm) -- build a timeseries for every month

截至2023年1月的最后36个月工作良好，并提供了我需要的数据系列。从2023年1月开始，我得到了以下数据:

tbody> <<tr>

validity_month
2023 - 01年的
2023 - 01年的
2023 - 02年的
2023 - 04的
2023 - 04的

要解决月间隔的问题，请尝试使用TIMESERIES来获取一系列整数，然后使用CROSS JOIN来获取该整型列表，并使用ADD_MONTHS():

WITH
monthcount(monthcount) AS (SELECT 6) -- take the last 6 months
, lim(dt) AS (
SELECT TRUNC(ADD_MONTHS(CURRENT_DATE(), -monthcount),'MONTH') FROM monthcount
UNION ALL 
SELECT TRUNC(CURRENT_DATE(),'MONTH')
)
, ts(ts) AS (
SELECT ts FROM lim
TIMESERIES ts as '1 MONTH' OVER (ORDER BY dt::TIMESTAMP) 
) 
, i(i) AS ( SELECT ROW_NUMBER() OVER(ORDER BY ts) FROM ts) 
, minmonth(minmonth) AS (SELECT MIN(dt) FROM lim)
SELECT
ADD_MONTHS(minmonth,i) AS validity_month
, i
FROM i CROSS JOIN minmonth CROSS JOIN monthcount
WHERE i <= monthcount
ORDER BY 1
;

我3

这是因为，在TIMESERIES子句中，'1 MONTH'等同于'30 DAYS'。查看以下查询(我将表达式固定为返回输入日期之后的第一个月-不要使用字符串操作!)-使用validity_month的表达式和输入日期值-对于过去6个月:

SELECT
TO_CHAR(ADD_MONTHS(TRUNC(ts,'MONTH'),1),'YYYY-MM') AS validity_month
, ts::DATE AS real_date
FROM (
SELECT ADD_MONTHS(CURRENT_DATE(), -6)::TIMESTAMP as tm -- take the last 6 months
UNION ALL 
SELECT CURRENT_DATE()::TIMESTAMP
) as t 
-- build a timeseries for every month
TIMESERIES ts as '1 MONTH' OVER (ORDER BY t.tm); 

结果:

tbody> <<tr>2022-10-022022-11-012023 - 012022-12-012023 - 012022-12-312023-01-302023-03-012023-03-31

validity_month		real_date
2022 - 11
2022 - 12
2023 - 02年
2023 - 04
2023 - 04