Neo4j Cypher 返回最连续的“passes"； [英] Neo4j Cypher return most consecutive "passes"

问题描述

我试图从图形数据库中返回连续通过一系列考试的学生.

I am trying to return from a graph database the students with the most consecutive passes to a series of exams.

以下是我当前的代码，但不确定我可以从当前状态中取出它来解决我的问题.

Below is my current code but not sure where I can take it from its current state to solve my issue.

MATCH (s:Student)-[r:TAKEN]->(e:Exam)
RETURN s.name, e.date,
  CASE 
    WHEN r.score >= e.pass_mark THEN 1
  END as pass
ORDER BY e.date

我想要一个基本表格，它可以显示学生以及连续最多的连续传球次数.

I would like a basic table which would show the student along with there most consecutive passes in a row.

例如:

| student   | pass/fail |
| joe       | pass      |
| matt      | pass      |
| joe       | fail      |
| matt      | pass      |    
| joe       | pass      |
| matt      | pass      |
| joe       | pass      |
| matt      | fail      |

而且我希望我的查询结果能够连续显示每个学生和他们的最高通过率.

And I would like the result from my query to display each student and their highest pass streak in a row.

| student  | passes in a row |
| joe      | 2               |
| matt     | 3               |

我一直在玩 CASE 但还没有想出一个好的解决方案，目前，它只会列出每门考试的所有学生，如果他们通过了，就会得到 1.

I have been playing around with CASE but haven't been able to come up with a good solution, currently, it will just list all the students for each exam and have a 1 if they have passed it.

推荐答案

你可以用普通的 Cypher 来完成，但我认为它不是很实用——你本质上需要用 reduce.

You can do it with plain Cypher, but I don't think it's very practical - you essentially need to write a program with reduce.

基本上，拆分"的工作原理如下:初始化一个空的累加器列表并通过遍历通过/失败列表来计算条纹，检查当前元素是否与前一个元素相同.例如 ['pass', 'pass'] 保持连续，['pass', 'fail'] 打破它.如果它中断(例如在列表的开头)，则将一个新元素附加到累加器.如果它保持，则将一个新元素附加到累加器的最后一个元素，例如使用新的 'fail'，[['pass', 'pass'], ['fail']] 变成 [['pass', 'pass'], ['fail', 'fail]].

Basically, the "split" works as follows: initialize an empty accumulator list and calculate streaks by iterating through the list of passes/fails, check whether the current element is the same as the previous one. For example ['pass', 'pass'] keeps the streak, ['pass', 'fail'] breaks it. If it breaks (like at the start of the list), append a new element to the accumulator. If it keeps, append a new element to the last element of the accumulator, e.g. with a new 'fail', [['pass', 'pass'], ['fail']] becomes [['pass', 'pass'], ['fail', 'fail]].

UNWIND
  [
    ['joe',  'pass'],
    ['matt', 'pass'],
    ['joe',  'fail'],
    ['matt', 'pass'],
    ['joe',  'pass'],
    ['matt', 'pass'],
    ['joe',  'pass'],
    ['matt', 'fail']
  ] AS row
WITH row[0] AS s, row[1] AS passed
WITH s, collect(passed) AS p
WITH s, reduce(acc = [], i IN range(0, size(p) - 1) | 
    CASE p[i] = p[i-1]
      WHEN true THEN [j IN range(0, size(acc) - 1) |
          CASE j = size(acc) - 1
            WHEN true THEN acc[j] + [p[i]]
            ELSE acc[j]
          END
        ]
      ELSE acc + [[p[i]]]
    END
  ) AS streaks // (1)
UNWIND streaks AS streak
WITH s, streak
WHERE streak[0] <> 'fail'
RETURN s, max(size(streak)) AS consecutivePasses // (2)

在步骤 (1) 中，这会计算条纹，例如:

In step (1), this calculates streaks such as:

╒══════╤═════════════════════════════════╕
│"s"   │"streaks"                        │
╞══════╪═════════════════════════════════╡
│"matt"│[["pass","pass","pass"],["fail"]]│
├──────┼─────────────────────────────────┤
│"joe" │[["fail"],["pass","pass"]]       │
└──────┴─────────────────────────────────┘

在(2)中，它给出:

╒══════╤═══════════════════╕
│"s"   │"consecutivePasses"│
╞══════╪═══════════════════╡
│"matt"│3                  │
├──────┼───────────────────┤
│"joe" │2                  │
└──────┴───────────────────┘

当然，在这种特殊情况下，没有必要进行拆分:简单地计数就足够了.但是在99%的实际情况下，APOC是要走的路，所以我没有费心去优化这个方案.

Of course, in this particular case it's not necessary to do the splitting: simply counting would be enough. But in 99% of practical situations, APOC is the way to go, so I did not bother optimising this solution.

这篇关于Neo4j Cypher 返回最连续的“passes"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！