查找给定时间序列之间最近的重叠

7lrncoxx 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(308)

我正在构建一个调度系统，在其中存储初始约会以及它重复的频率。我的table看起来像这样：

CREATE TABLE (
    id serial primary key,
    initial_timestamp timestamp not null,
    recurring interval
);

id        initial_timestamp          recurring
27        2020-06-02                 3 weeks
24        2020-06-03                 10 days

假设我可以处理时间部分，并且我们将要经过的时间间隔只有几天和几周，那么我如何找到这两个约会重叠的时间？例如，上一个示例将在6月23日重叠，从6月2日起3周，从6月3日起20天，因此第一个约会将在当天重复一次，第二个约会将在13日重复，然后是23日。
在我的程序中，我有另一个日期，比如6月7日，重复间隔12天。我可以使用什么查询来查找从6月7日开始的定期约会与每个现有定期约会重叠所需的时间？例如，这个约会将在6月19日、7月1日和7月13日重复。如果我的计算正确的话，上表中的24号约会将在6月13日、6月23日、7月3日和7月13日重复。我希望我的查询将此约会与约会#24进行比较，首先返回7月13日，然后返回再次重复需要多长时间，我假设这就像找到两个间隔中最不常见的倍数，在本例中为60天（lcm为12和10）。所以我可以预期它会在7月13日+60天=9月11日再次重复。
我试着用generateïu级数，但是因为我不知道区间的大小，所以级数必须无限延续，对吗？这可能不是最好的选择。我想答案更多的是关于相乘区间的数学。
请注意 recurring 可以为空，所以我假设 WHERE recurring IS NOT NULL 在那里的某个地方。另一件需要注意的事情是：最初的约会没有重叠。我已经防范过了。搜索词也不会与约会的任何初始时间重叠。
如果有帮助的话，我将使用PHP5.3向Postgres9.4发送查询（我知道，这是一个古老的设置）。我更喜欢在sql中完成大部分操作，因为其他大部分逻辑现在都在sql中，所以我可以运行查询并开始用php处理结果。
总之，如果我的数学是正确的，我应该用什么样的postgres查询来比较给定的日期和间隔与表中的每个日期和间隔对，以找到下一个日期这两个重叠，以及每个重叠示例相距多远？

sql postgresql Intervals date-math

来源：https://stackoverflow.com/questions/62183436/find-the-nearest-overlap-between-given-time-series

1条答案

按热度按时间

kulphzqa1#

这很难。

WITH RECURSIVE moving_target(initial_timestamp, recurring) AS (
   VALUES (timestamp '2020-06-07', interval '12 days')  -- search term
   )
,  x AS (         -- advance to the closest day before or at moving target
   SELECT t.id
        , t_date + ((m_date - t_date) / t_step) * t_step AS t_date
        , t_step
        , m.*
   FROM  (        -- normalize table data
      SELECT id
           , initial_timestamp::date AS t_date
           , EXTRACT ('days' FROM recurring)::int AS t_step
      FROM   tbl
      WHERE  recurring IS NOT NULL  -- exclude!
      ) t
   CROSS  JOIN (  -- normalize input
      SELECT initial_timestamp::date AS m_date
           , EXTRACT ('days' FROM recurring)::int AS m_step
      FROM   moving_target
      ) m
   )
, rcte AS (       -- recursive CTE
   SELECT id, t_date, t_step, m_date, m_step
        , ARRAY[m_date - t_date] AS gaps        -- keep track of gaps
        , CASE
            WHEN t_date = m_date     THEN true  -- found match
            WHEN t_step % m_step = 0 THEN false -- can never match
            WHEN (m_date - t_date) % 2 = 1      -- odd gap ...
             AND t_step % 2 = 0                 -- ... but even steps
             AND m_step % 2 = 0      THEN false -- can never match
         -- WHEN <stop conditions?>  THEN false -- hard to determine!
         -- ELSE                          null  -- keep searching
          END AS match
   FROM   x

   UNION ALL
   SELECT id, t_date, t_step, m_date, m_step
        , gaps || m_date - t_date
        , CASE
            WHEN t_date = m_date                THEN true
            WHEN (m_date - t_date) = ANY (gaps) THEN false  -- gap repeated!
         -- ELSE                                     null   -- keep searching
          END AS match
   FROM  (
      SELECT id
           , t_date + (((m_date + m_step) - t_date) / t_step) * t_step AS t_date
           , t_step
           , m_date + m_step AS m_date -- + 1 step
           , m_step
           , gaps
      FROM   rcte
      WHERE  match IS NULL
      ) sub
   )
SELECT id, t.initial_timestamp, t.recurring
     , CASE WHEN r.match THEN r.t_date END AS match_date
FROM   rcte r
JOIN   tbl  t USING (id)
WHERE  r.match IS NOT NULL;

db<>在这里摆弄-有更多的测试行
可能还有进一步改善的潜力。核心问题在于
素因子分解。因为期望间隔很小似乎是合理的，所以我通过测试周期来解决这个问题：如果在逐步前进的过程中，检测到我们以前看到的日期之间的间隔，并且日期还没有重叠，那么它们将永远不会重叠，我们可以停止。这个循环最多 GREATEST(m_step, t_step) 时间（较大间隔内的天数），所以它的伸缩性不会太大。
我确定了一些基本的数学停止条件，以避免在无望的情况下循环先验。可能还有更多。。。
解释这里发生的一切比设计查询要费劲。我添加了一些应该解释基本情况的评论。。。
同样，虽然间隔很小，但基于 generate_series() 可能还要快。

赞(0）回复(0）举报 2021-07-26

我来回答

查找给定时间序列之间最近的重叠

1条答案

相关问题

热门标签

最新问答