查找 2 个日期之间的所有事件 [英] Find all events between 2 dates

问题描述

试图找出以下场景.

设置:

• 使用本文中的建议创建时间树 http://www.markhneedham.com/blog/2014/04/19/neo4j-cypher-creating-a-time-tree-down-to-the-天/
• 将其扩展到小时和分钟(为简单起见，使用 0,15,30,45
• 将分钟与 :NEXT 联系起来(类似于几天所做的，但是以分钟为单位的)
• 使用STARTS_AT"和ENDS_AT"创建了多个附加到会议记录的事件

目标

• 查找两个日期之间发生的所有事件
• 查找冲突事件(重叠的事件)

尝试以下查询:

// Get all events between 2 times
MATCH (m1:Minute { minute: 15 })<--(h:Hour { hour: 8 })<--(d:Day { day: 24 })<--(month:Month { month: 10 })
MATCH (m2:Minute { minute: 45 })<--(h2:Hour { hour: 10 })<--(d2:Day { day: 24 })<--(month2:Month { month: 10 })
MATCH p=((m1)-[:NEXT*]->(m2))
WITH NODES(p) as pnodes UNWIND pnodes AS pwind
MATCH (e:Event)-[:STARTS_AT]->(pwind)
RETURN pwind,e

确实正在检索结果，但注意到:

The results are indeed being retrieved, but noticed that:

1. 如果我尝试使用shortestpath"而不是常规路径，则查询仅适用于某个阈值(奇怪，无法解释这一点).例如，如果时间跨度超过 2-3 小时，则它不起作用；同样适用于多天等.
2. 如果不使用shortestpath"，性能会很糟糕.25 秒只返回 2-3 个项目.

使用 where 的另一种变体(仅在未来日期尝试过):

Another variation using where (tried it only for future dates):

// Get all events between 2 times
MATCH (e:Event)
WHERE (:Month { month: 10 })-->(:Day { day: 24 })-->(:Hour { hour: 9 })-->(:Minute { minute: 00})-[:NEXT*]->(:Minute)<--(e)
RETURN e

结果:性能更差.100 秒检索 1 个项目.

Results: the performance is even WORSE. 100 seconds to retrieve 1 item.

我理解并希望这样做的方式是使用某种允许路径返回相关节点的函数.这是:路径函数仅返回被查询的特定节点(在本例中为分钟)，但我想为所有这些分钟带来与:STARTS_AT"关联的事件.

The way I understand and would like to do this, is by using some sort of function that allows the path to return related nodes. This is: path function returns only the specific node being queried (in this case Minutes), but I would like to bring for ALL THOSE MINUTES, the Events associated by ":STARTS_AT".

最后，问题:

• 执行此查询的推荐方法是什么?
• 这是 Cypher 和 neo4j 支持的场景吗?
• 回退"到基于属性的时间查询而不是尝试尝试基于图形的时间查询是否更可取?

提前致谢.

推荐答案

所以 shortestPath 有一个奇怪的地方，如果你不指定最大长度，它会任意设置最大值为 15.请看这里:

So there's this weird thing with shortestPath where if you don't specify a maximum length, it arbitrarily sets the max to 15. See here:

ShortestPath 没有找到任何没有最大跳数限制的路径

我实际上将其称为错误，因为它不在文档中，并且会导致意外行为，正如您所发现的那样.

I would actually call this a bug, because it's not in the documentation and it leads to unexpected behavior, as you've found.

因此，您的问题的解决方案是使用 shortestPath 但选择最大长度.我会选择非常高的东西；让我们做 10 亿并收工:

So the solution to your problem is to use shortestPath but pick a maximum length. I'd choose something really high; let's do a billion and call it a day:

MATCH (:Year {year:2015})-[:HAS_MONTH]->(:Month {month:10})-[:HAS_DAY]->(:Day {day:23})-[:HAS_HOUR]->(:Hour {hour:8})-[:HAS_MINUTE]->(startMinute:Minute {minute:15})
MATCH (:Year {year:2015})-[:HAS_MONTH]->(:Month {month:10})-[:HAS_DAY]->(:Day {day:24})-[:HAS_HOUR]->(:Hour {hour:10})-[:HAS_MINUTE]->(endMinute:Minute {minute:45})
MATCH p = shortestPath((startMinute)-[:NEXT*..1000000000]->(endMinute))
UNWIND NODES(p) AS minute
MATCH (event:Event)-[:STARTS_AT]->(minute)
RETURN event, minute;

您应该始终使用 shortestPath 来查找分钟节点的跨度；在 (startMinute)-[:NEXT*]->(endMinute) 上匹配而不将其包装在 shortestPath 试图找到两个节点之间任意长度的所有路径，所以它是详尽的并且需要更长的时间，而 shortestPath 可以在找到路径后立即停止.

You should always use shortestPath for finding the span of minute nodes; matching on (startMinute)-[:NEXT*]->(endMinute) without wrapping it in shortestPath is trying to find all paths of any length between the two nodes, so it's exhaustive and takes much longer, whereas shortestPath can stop as soon as it's found the path.

就查找是否有任何其他事件与某个事件重叠而言:

As far as finding if any other events overlap with a certain event:

MATCH (startMinute:Minute)<-[:STARTS_AT]-(event:Event)-[:ENDS_AT]->(endMinute:Minute)
WHERE event.id = {event_id}
MATCH p = shortestPath((startMinute)-[:NEXT*..1000000000]->(endMinute))
UNWIND NODES(p) AS span
MATCH (overlap:Event)-[:STARTS_AT|ENDS_AT]->(span)
WHERE overlap <> event
RETURN overlap;

以下是如何为概念验证目的创建数据的附录.假设所有月份都有 31 天.

Below is an appendix of how the data was created for proof-of-concept purposes. Assume all months have 31 days.

约束和索引.

CREATE CONSTRAINT ON (year:Year) ASSERT year.year IS UNIQUE;
CREATE INDEX ON :Month(month);
CREATE INDEX ON :Day(day);
CREATE INDEX ON :Hour(hour);
CREATE INDEX ON :Minute(minute);

创建时间树.

WITH RANGE(2014, 2015) AS years, RANGE(1, 12) AS months, RANGE(1, 31) AS days, RANGE(0,23) AS hours, RANGE(0, 45, 15) AS minutes
FOREACH(year IN years | 
  MERGE (y:Year {year: year})
  FOREACH(month IN months | 
    CREATE (m:Month {month: month})
    MERGE (y)-[:HAS_MONTH]->(m)
    FOREACH(day IN days |      
      CREATE (d:Day {day: day})
      MERGE (m)-[:HAS_DAY]->(d)
      FOREACH(hour IN hours |
        CREATE (h:Hour {hour: hour})
        MERGE (d)-[:HAS_HOUR]->(h)
        FOREACH(minute IN minutes |
          CREATE (min:Minute {minute: minute})
          MERGE (h)-[:HAS_MINUTE]->(min)
        )
      )
    )
  )
);

在所有 Minute 节点之间创建 [:NEXT] 关系.

Create [:NEXT] relationships between all the Minute nodes.

MATCH (year:Year)-[:HAS_MONTH]->(month:Month)-[:HAS_DAY]->(day:Day)-[:HAS_HOUR]->(hour:Hour)-[:HAS_MINUTE]->(minute:Minute)
WITH year, month, day, hour, minute
ORDER BY year.year, month.month, day.day, hour.hour, minute.minute
WITH COLLECT(minute) AS minutes
FOREACH(i IN RANGE(0, LENGTH(minutes) - 2) | 
    FOREACH(min1 IN [minutes[i]] | 
        FOREACH(min2 IN [minutes[i + 1]] | 
            CREATE UNIQUE (min1)-[:NEXT]->(min2)
        )
    )
);

随机模拟事件及其开始时间.

Randomly simulate events and their start times.

MATCH (minute:Minute)
WHERE RAND() < 0.3
CREATE (event:Event)-[:STARTS_AT]->(minute);

使所有事件的长度为 5 分钟.

Make all events 5 minute blocks long.

MATCH (event:Event)-[:STARTS_AT]->(startMinute:Minute)-[:NEXT*5]->(endMinute:Minute)
CREATE (event)-[:ENDS_AT]->(endMinute);

这篇关于查找 2 个日期之间的所有事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！