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

n53p2ov0 于 2023-04-11 发布在 Oracle

关注(0)|答案(2)|浏览(209)

目前，我正面临一个问题，在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

过去36个月，直到2023年1月只是工作正常，并提供我需要的数据系列。从2023年1月开始，我得到以下数据：
| 有效月份|
| --------------|
| 2023年1月|
| 2023年1月|
| 2023年2月|
| 2023年4月|
| 2023年4月|
March丢失了，我得到了重复的值。有人知道问题是什么以及如何修复它吗？
非常感谢和最好的regrads！
试图改变查询，并期望我会产生一个数据系列开始，从一个给定的月份，直到今天没有失踪和重复的月份

oracle

来源：https://stackoverflow.com/questions/75947953/duplicated-and-missing-values-in-vertica-time-series-analytics-query

2条答案

按热度按时间

mgdq6dx11#

这是因为，在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);

结果：
| 有效月份|实际日期|
| - -------------|- -------------|
| 2022 - 11|2022 - 10 - 02 2022 - 10 - 02|
| 2022 - 12|2022 - 11 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01|
| 2023 - 01|2022 - 12 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01|
| 2023 - 01|2022 - 12 - 31 - 2022 - 12 - 31 - 2022 - 12 - 31 - 2022 - 12 - 31 - 2022 - 12 - 31 - 2022 - 12 - 21 - 21 - 21 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22 - 22|
| 2023 - 02年|2023 - 01 - 30 - 2023 - 01 - 30|
| 2023 - 04年|2023 - 03 - 01 2023 - 03 - 01 2023 - 03 - 01|
| 2023 - 04年|2023 - 03 - 31 - 2023 - 2023 - 03 - 31 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 2023 - 202|
1月3日+30天是3月1日;3月1日+30天是3月31日。ADD_MONTHS(TRUNC(DATE '2023-03-01','MONTH),1)和ADD_MONTHS(TRUNC(DATE '2023-03-31','MONTH),1)都返回4月1日。

赞(0）回复(0）举报 2023-04-11

wlsrxk512#

为了解决长达一个月的间隔问题，尝试使用TIMESERIES来获取一系列整数，然后使用CROSS JOIN与int列表，并使用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
;

| 有效月份|我|
| - -------------|- -------------|
| 2022 - 11 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01|1|
| 2022 - 12 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01 - 01|二|
| 2023 - 01 - 01 2023 - 01 - 01|三|
| 2023 - 02 - 01 2023 - 02 - 01 2023 - 02 - 01|四|
| 2023 - 03 - 01 2023 - 03 - 01 2023 - 03 - 01|五|
| 2023 - 04 - 01 2023 - 04 - 01|六|

赞(0）回复(0）举报 2023-04-11

我来回答

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

2条答案

相关问题

热门标签

最新问答