SubQuery最大日期

Question

我已经找过这个了，运气不太好。我在OrientDb (3.0.2)中设置了一个图形数据库，并且对子查询有一些问题。我将能够轻松地在一个经典的关系数据库中编写这个文件。我玩过让和$家长/$的孩子，但这些概念仍然有点混乱。

三个顶点：实体<-检查->违规行为

我想知道每个实体的最后一次检查(日期)，并知道与检查有关的违规事件有多少次。

我知道如何提取每个检查的违规次数(边的大小())。我也知道如何提取每个实体的最大检查日期，但是将这两者结合起来是一场斗争。有什么想法吗？谢谢!

更新：--我想我越来越近了，但仍然不知道如何进行“内部”连接。不确定我是否完全理解$parent/$当前变量是如何工作的。此查询不工作

SELECT
    entityId
    ,$a.num_violations
    ,max(Date) as mDate
FROM (
    SELECT
        @rid AS entityId
        ,in('COMPLETED_AT').Date as Date
    FROM Entity
    unwind Date
)
let $a = (
  SELECT
    Date
    ,out('FOUND_DURING').size() as num_violations
  FROM Inspection
  WHERE $parent.mDate = Date
  AND $parent.entityId in out('COMPLETED_AT').@rid
)
GROUP BY entityId
    ,$a.num_violations

Answer 1

试试这个：

select expand($c)
let $a = (select date, out("link")[@class="Violations"].size() as num_violations from Inspection),
$b = (select name, max(date) as max_date from (select name, in("link").date as date from Entity unwind date) group by name),
$c = unionAll($a, $b)

你得到的是：

+----+-------------------+--------------+----------+-------------------+
|#   |date               |num_violations|name      |max_date           |
+----+-------------------+--------------+----------+-------------------+
|0   |2018-08-31 00:00:00|1             |          |                   |
|1   |2018-08-30 00:00:00|2             |          |                   |
|2   |2018-08-29 00:00:00|1             |          |                   |
|3   |                   |              |entity_001|2018-08-31 00:00:00|
|4   |                   |              |entity_002|2018-08-29 00:00:00|
+----+-------------------+--------------+----------+-------------------+

更新

select expand($c)
let $a = (select name as entity_name, max(date) as max_date from (select name, in("link").date as date from Entity unwind date) group by name),
$b = (select out("link")[@class="Violations"].size() as num_violations from Inspection),
$c = unionAll($a, $b)


+----+-----------+-------------------+--------------+
|#   |entity_name|max_date           |num_violations|
+----+-----------+-------------------+--------------+
|0   |entity_001 |2018-08-31 00:00:00|              |
|1   |entity_002 |2018-08-29 00:00:00|              |
|2   |           |                   |1             |
|3   |           |                   |2             |
|4   |           |                   |1             |
+----+-----------+-------------------+--------------+

希望它能帮上忙

问候