在 Cypher 中透视数据 [英] Pivoting data in Cypher

问题描述

我刚刚开始使用 Neo4J 数据库，但我很难找到一种很好的方法来旋转我正在使用的一些数据.

I've just gotten into working with a Neo4J database, and I'm having a hard time figuring out a good way to pivot some data that I'm working with.

我有一个如下所示的基本查询:MATCH (n:DATA) WHERE n.status =~ "SUCCESS";return n.group as Group, n.label as Label, avg(toFloat(n.durationMillis)/60000) as Minutes，这会产生像这样的高窄数据:

I have a basic query that looks like this: MATCH (n:DATA) WHERE n.status =~ "SUCCESS" return n.group as Group, n.label as Label, avg(toFloat(n.durationMillis)/60000) as Minutes, which produces tall narrow data like this:

|Group  |Label  |Minutes|
|-------|-------|-------|
|group1 |label1 |1.0    |
|group1 |label2 |2.0    |
|group1 |label3 |5.0    |
|group2 |label1 |3.0    |
|group2 |label3 |2.0    |
...

我想要做的是透视这些数据以提供作为汇总表的短视图:

What I would like to do is pivot this data to provide a short wide view as a summary table:

| Group | label1 | label2 | label3 |
| ----- | ------ | ------ | ------ |
|group1 | 1.0    | 2.0    | 5.0    |
|group2 | 3.0    | -      | 2.0    |
...

Cypher 有没有一种简单的方法可以做到这一点?

Is there a simple way to do this with Cypher?

推荐答案

为了让 Neo4j 工具(如 Neo4j 浏览器)从 Cypher 查询生成看起来像数据透视表的可视化，查询必须硬编码每个列"的标题；-- 因为 Cypher 查询不能动态生成它返回的值的名称.也就是说，您的 RETURN 子句必须类似于 RETURN Group, label1, label2, label3.

In order for Neo4j tools (like the Neo4j Browser) to generate a visualization that looks like a pivot table from a Cypher query, the query would have to hardcode the headings for each "column" -- since a Cypher query cannot dynamically generate the names of the values it returns. That is, your RETURN clause would have to look something like RETURN Group, label1, label2, label3.

现在，如果您碰巧事先知道所有可能的标签，那么您确实可以执行一个简单的查询来返回您的数据透视表.例如:

Now, if you do happen to know all the possible labels beforehand, then you can indeed perform a simple query that returns your pivot table. For example:

MATCH (n:DATA)
WHERE n.status =~ "SUCCESS"
WITH n.group as Group, n.label AS l, AVG(n.durationMillis/60000.0) AS m
WITH Group, apoc.map.fromLists(COLLECT(l), COLLECT(m)) AS lmMap
RETURN Group,
  lmMap['label1'] AS label1,
  lmMap['label2'] AS label2,
  lmMap['label3'] AS label3

APOC 函数 apoc.map.fromLists 返回从键和值列表生成的映射.如果 Group 没有特定的标签，它的单元格值为 null.

The APOC function apoc.map.fromLists returns a map generated from lists of keys and values. If a Group does not have a particular label, its cell value will be null.

这篇关于在 Cypher 中透视数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！