match 语句改写

提问参考模版:

  • nebula 版本:v 2.6.1
  • 部署方式:分布式
  • 安装方式: RPM
  • 是否为线上版本:Y / N
  • 硬件信息
    • 磁盘( 推荐使用 SSD):SSD
    • CPU、内存信息
  • 问题的具体描述
MATCH p = (self:Company)<-[sr:ACTUAL|CONTROL]-(target) WHERE self.keyNo = "6b58ef554c24961281b58f01cd676511" RETURN target

如何将上述语句改写为go 语句
注:target 有很多类型,如果用$$.Tag1.property1, $$.Tag2.property2, $$.Tag3.property3 这种方式 要写很多很多列

借助 properties() 函数

LOOKUP ON Company WHERE Company.keyNo == "6b58ef554c24961281b58f01cd676511" \
  YIELD id(vertex) AS VertexID \
  | GO FROM $-.VertexID OVER ACTUAL,CONTROL REVERSELY YIELD properties($^)

例子

(root@nebula) [basketballplayer]> LOOKUP ON player WHERE player.name == "Tim Duncan" YIELD id(vertex) AS VertexID| GO FROM  $-.VertexID OVER follow,serve YIELD properties($$)
+----------------------------------+
| properties($$)                   |
+----------------------------------+
| {age: 36, name: "Tony Parker"}   |
| {age: 41, name: "Manu Ginobili"} |
| {name: "Spurs"}                  |
+----------------------------------+
Got 3 rows (time spent 3834/29784 us)

ref:属性引用符 - Nebula Graph Database 手册

这个地方LOOKUP 需要加索引了,目前线上服务没有加索引,可以理解vid 是对keyno 的hash,该怎么改写呢?

match/lookup 这里如果都是把 keyNo 作为属性都是需要索引的哈。
如果实际上 keyNo 是唯一的话,可以设计成 VID,就不需要索引,这样可以直接一个 GO from 就行了哈。

才看到是hash 的关系,假设您这个 hash 是nebula的这个的话,如果不是的话,就您在 应用层自己算 hash 替换这里。

GO FROM hash("6b58ef554c24961281b58f01cd676511") OVER ACTUAL,CONTROL REVERSELY YIELD properties($^)

感谢,非常有用,如果这种呢?能不能改写,collect(p),max(sr.percent) 我都没有想到较好的写法

MATCH
   p = (self:Company)-[sr:Hold]->()<-[tr:Invest]-()
WHERE
   self.keyNo = "6b58ef554c24961281b58f01cd676511"
   AND tr.stockPercent >= 10
   AND target.keyNo <> "6b58ef554c24961281b58f01cd676511"
RETURN
   CASE target.status WHEN '存续' THEN 0 WHEN '在业' THEN 0 WHEN '' THEN 0 WHEN '迁入' THEN 1 WHEN '迁出' THEN 1 WHEN '停业' THEN 10 WHEN '清算' THEN 10 WHEN '吊销' THEN 11 WHEN '注销' THEN 11 ELSE 8 END AS status,
   target,
   collect(p) AS path,
   'HE-BE10' AS nodeType,
   max(sr.percent) AS p1,
   max(tr.stockPercent) AS p2
ORDER BY
   status, p1 desc, p2 desc
SKIP 0 LIMIT 10

这是我想到的改写方式,就很繁琐

go from -6732559126034379613 OVER Hold yield $^.Company.name as srcName, $^.Company.keyno as srcKeyno, $$.Company.name as mName, $$.Company.keyno as mKeyno, Hold._dst as id, Hold.stockpercent as sr, Hold.type as type| go from $-.id OVER Invest Reversely where Invest.stockpercent >= 10 and Invest._dst != -6732559126034379613 yield CASE $$.Company.shortstatus WHEN '存续' THEN 0 WHEN '在业' THEN 0 WHEN '' THEN 0 WHEN '迁入' THEN 1 WHEN '迁出' THEN 1 WHEN '停业' THEN 10 WHEN '清算' THEN 10 WHEN '吊销' THEN 11 WHEN '注销' THEN 11 ELSE 8 END AS status, $-.srcName as srcName, $-.srcKeyno as srcKeyno, $-.sr as relPercent, $-.type as relType, $-.mName as middleName, $-.mKeyno as middleKeyno, $$.Company.name as targetName, $$.Company.keyno as targetKeyno, Invest.role as rel2Role, Invest.type as rel2Type, Invest.stockpercent as rel2Percent, $-.sr as p1, Invest.stockpercent as p2 | ORDER BY status ASC, p1 desc, p2 desc | LIMIT 10

@wey 老师,麻烦帮忙看一下…

这种输出 path 的结果,用 GO 确实不太能表达,我假设你的query类似于这个

MATCH
   p=(self:player)-[sr:serve]->()<-[tr:serve]-(target)
WHERE
   id(self) == "player100"
   AND tr.start_year >= 1995
RETURN
   target.player.age AS status,
   target,
   collect(p) AS `path`,
   'HE-BE10' AS nodeType,
   max(sr.start_year) AS p1,
   max(tr.start_year) AS p2
ORDER BY
   status, p1 desc, p2 desc
SKIP 0 LIMIT 10


+--------+-----------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+------+
| status | target                                                    | path                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | nodeType  | p1   | p2   |
+--------+-----------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+------+
| 25     | ("player106" :player{age: 25, name: "Kyle Anderson"})     | [<("player100" :player{age: 42, name: "Tim Duncan"})-[:serve@0 {end_year: 2016, start_year: 1997}]->("team204" :team{name: "Spurs"})<-[:serve@0 {end_year: 2018, start_year: 2014}]-("player106" :player{age: 25, name: "Kyle Anderson"})>]                                                                                                                                                                                                                                                              | "HE-BE10" | 1997 | 2014 |
| 27     | ("player110" :player{age: 27, name: "Cory Joseph"})       | [<("player100" :player{age: 42, name: "Tim Duncan"})-[:serve@0 {end_year: 2016, start_year: 1997}]->("team204" :team{name: "Spurs"})<-[:serve@0 {end_year: 2015, start_year: 2011}]-("player110" :player{age: 27, name: "Cory Joseph"})>]                                                                                                                                                                                                                                                                | "HE-BE10" | 1997 | 2011 |
| 29     | ("player113" :player{age: 29, name: "Dejounte Murray"})   | [<("player100" :player{age: 42, name: "Tim Duncan"})-[:serve@0 {end_year: 2016, start_year: 1997}]->("team204" :team{name: "Spurs"})<-[:serve@0 {end_year: 2019, start_year: 2016}]-("player113" :player{age: 29, name: "Dejounte Murray"})>]                                                                                                                                                                                                                                                            | "HE-BE10" | 1997 | 2016 |
| 29     | ("player112" :player{age: 29, name: "Jonathon Simmons"})  | [<("player100" :player{age: 42, name: "Tim Duncan"})-[:serve@0 {end_year: 2016, start_year: 1997}]->("team204" :team{name: "Spurs"})<-[:serve@0 {end_year: 2017, start_year: 2015}]-("player112" :player{age: 29, name: "Jonathon Simmons"})>]                                                                                                                                                                                                                                                           | "HE-BE10" | 1997 | 2015 |
| 31     | ("player105" :player{age: 31, name: "Danny Green"})       | [<("player100" :player{age: 42, name: "Tim Duncan"})-[:serve@0 {end_year: 2016, start_year: 1997}]->("team204" :team{name: "Spurs"})<-[:serve@0 {end_year: 2018, start_year: 2010}]-("player105" :player{age: 31, name: "Danny Green"})>]                                                                                                                                                                                                                                                                | "HE-BE10" | 1997 | 2010 |
| 32     | ("player104" :player{age: 32, name: "Marco Belinelli"})   | [<("player100" :player{age: 42, name: "Tim Duncan"})-[:serve@0 {end_year: 2016, start_year: 1997}]->("team204" :team{name: "Spurs"})<-[:serve@20132015 {end_year: 2015, start_year: 2013}]-("player104" :player{age: 32, name: "Marco Belinelli"})>, <("player100" :player{age: 42, name: "Tim Duncan"})-[:serve@0 {end_year: 2016, start_year: 1997}]->("team204" :team{name: "Spurs"})<-[:serve@20182019 {end_year: 2019, start_year: 2018}]-("player104" :player{age: 32, name: "Marco Belinelli"})>] | "HE-BE10" | 1997 | 2018 |
| 32     | ("player103" :player{age: 32, name: "Rudy Gay"})          | [<("player100" :player{age: 42, name: "Tim Duncan"})-[:serve@0 {end_year: 2016, start_year: 1997}]->("team204" :team{name: "Spurs"})<-[:serve@0 {end_year: 2019, start_year: 2017}]-("player103" :player{age: 32, name: "Rudy Gay"})>]                                                                                                                                                                                                                                                                   | "HE-BE10" | 1997 | 2017 |
| 32     | ("player107" :player{age: 32, name: "Aron Baynes"})       | [<("player100" :player{age: 42, name: "Tim Duncan"})-[:serve@0 {end_year: 2016, start_year: 1997}]->("team204" :team{name: "Spurs"})<-[:serve@0 {end_year: 2015, start_year: 2013}]-("player107" :player{age: 32, name: "Aron Baynes"})>]                                                                                                                                                                                                                                                                | "HE-BE10" | 1997 | 2013 |
| 33     | ("player102" :player{age: 33, name: "LaMarcus Aldridge"}) | [<("player100" :player{age: 42, name: "Tim Duncan"})-[:serve@0 {end_year: 2016, start_year: 1997}]->("team204" :team{name: "Spurs"})<-[:serve@0 {end_year: 2019, start_year: 2015}]-("player102" :player{age: 33, name: "LaMarcus Aldridge"})>]                                                                                                                                                                                                                                                          | "HE-BE10" | 1997 | 2015 |
| 34     | ("player109" :player{age: 34, name: "Tiago Splitter"})    | [<("player100" :player{age: 42, name: "Tim Duncan"})-[:serve@0 {end_year: 2016, start_year: 1997}]->("team204" :team{name: "Spurs"})<-[:serve@0 {end_year: 2015, start_year: 2010}]-("player109" :player{age: 34, name: "Tiago Splitter"})>]                                                                                                                                                                                                                                                             | "HE-BE10" | 1997 | 2010 |
+--------+-----------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+------+

其实 collect 、max、min 的表达在 GO 里边是 GROUP BY … YIELD collect/max/min

我直接写出来是这样的

GO FROM "player100" OVER serve YIELD serve._dst AS id, serve.start_year AS sr_start_year, properties($$) AS middle_vertex, properties(edge) AS edge0, properties($^) AS start_vertex | GO from $-.id OVER serve REVERSELY WHERE serve._dst != "player100" \
YIELD properties($$).age AS status, $-.sr_start_year AS sr_start_year, serve.start_year AS tr_start_year, [$-.start_vertex, $-.edge0, $-.middle_vertex, properties(edge), properties($$)] AS p, properties($$) AS target | \
GROUP BY $-.status YIELD $-.status AS status, $-.target AS target, collect(p) AS `path`, 'HE-BE10' AS nodeType, max($-.sr_start_year) AS p1, max($-.tr_start_year) AS p2

可以看到聚合之前的query是ok的,我也很蹩脚地窜出来了 path

GO FROM "player100" OVER serve YIELD serve._dst AS id, serve.start_year AS sr_start_year, properties($$) AS middle_vertex, properties(edge) AS edge0, properties($^) AS start_vertex | GO from $-.id OVER serve REVERSELY WHERE serve._dst != "player100" \
YIELD properties($$).age AS status, $-.sr_start_year AS sr_start_year, serve.start_year AS tr_start_year, [$-.start_vertex, $-.edge0, $-.middle_vertex, properties(edge), properties($$)] AS p, properties($$) AS target

+--------+---------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------+
| status | sr_start_year | tr_start_year | p                                                                                                                                                              | target                               |
+--------+---------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------+
| 32     | 1997          | 2018          | [{age: 42, name: "Tim Duncan"}, {end_year: 2016, start_year: 1997}, {name: "Spurs"}, {end_year: 2019, start_year: 2018}, {age: 32, name: "Marco Belinelli"}]   | {age: 32, name: "Marco Belinelli"}   |
| 36     | 1997          | 1999          | [{age: 42, name: "Tim Duncan"}, {end_year: 2016, start_year: 1997}, {name: "Spurs"}, {end_year: 2018, start_year: 1999}, {age: 36, name: "Tony Parker"}]       | {age: 36, name: "Tony Parker"}       |
| 33     | 1997          | 2015          | [{age: 42, name: "Tim Duncan"}, {end_year: 2016, start_year: 1997}, {name: "Spurs"}, {end_year: 2019, start_year: 2015}, {age: 33, name: "LaMarcus Aldridge"}] | {age: 33, name: "LaMarcus Aldridge"} |
| 32     | 1997          | 2017          | [{age: 42, name: "Tim Duncan"}, {end_year: 2016, start_year: 1997}, {name: "Spurs"}, {end_year: 2019, start_year: 2017}, {age: 32, name: "Rudy Gay"}]          | {age: 32, name: "Rudy Gay"}          |
| 31     | 1997          | 2010          | [{age: 42, name: "Tim Duncan"}, {end_year: 2016, start_year: 1997}, {name: "Spurs"}, {end_year: 2018, start_year: 2010}, {age: 31, name: "Danny Green"}]       | {age: 31, name: "Danny Green"}       |
| 25     | 1997          | 2014          | [{age: 42, name: "Tim Duncan"}, {end_year: 2016, start_year: 1997}, {name: "Spurs"}, {end_year: 2018, start_year: 2014}, {age: 25, name: "Kyle Anderson"}]     | {age: 25, name: "Kyle Anderson"}     |
| 32     | 1997          | 2013          | [{age: 42, name: "Tim Duncan"}, {end_year: 2016, start_year: 1997}, {name: "Spurs"}, {end_year: 2015, start_year: 2013}, {age: 32, name: "Aron Baynes"}]       | {age: 32, name: "Aron Baynes"}       |
| 36     | 1997          | 2012          | [{age: 42, name: "Tim Duncan"}, {end_year: 2016, start_year: 1997}, {name: "Spurs"}, {end_year: 2016, start_year: 2012}, {age: 36, name: "Boris Diaw"}]        | {age: 36, name: "Boris Diaw"}        |
| 34     | 1997          | 2010          | [{age: 42, name: "Tim Duncan"}, {end_year: 2016, start_year: 1997}, {name: "Spurs"}, {end_year: 2015, start_year: 2010}, {age: 34, name: "Tiago Splitter"}]    | {age: 34, name: "Tiago Splitter"}    |
| 27     | 1997          | 2011          | [{age: 42, name: "Tim Duncan"}, {end_year: 2016, start_year: 1997}, {name: "Spurs"}, {end_year: 2015, start_year: 2011}, {age: 27, name: "Cory Joseph"}]       | {age: 27, name: "Cory Joseph"}       |
| 38     | 1997          | 2015          | [{age: 42, name: "Tim Duncan"}, {end_year: 2016, start_year: 1997}, {name: "Spurs"}, {end_year: 2016, start_year: 2015}, {age: 38, name: "David West"}]        | {age: 38, name: "David West"}        |
| 29     | 1997          | 2015          | [{age: 42, name: "Tim Duncan"}, {end_year: 2016, start_year: 1997}, {name: "Spurs"}, {end_year: 2017, start_year: 2015}, {age: 29, name: "Jonathon Simmons"}]  | {age: 29, name: "Jonathon Simmons"}  |
| 29     | 1997          | 2016          | [{age: 42, name: "Tim Duncan"}, {end_year: 2016, start_year: 1997}, {name: "Spurs"}, {end_year: 2019, start_year: 2016}, {age: 29, name: "Dejounte Murray"}]   | {age: 29, name: "Dejounte Murray"}   |
| 39     | 1997          | 2013          | [{age: 42, name: "Tim Duncan"}, {end_year: 2016, start_year: 1997}, {name: "Spurs"}, {end_year: 2013, start_year: 2013}, {age: 39, name: "Tracy McGrady"}]     | {age: 39, name: "Tracy McGrady"}     |
| 41     | 1997          | 2002          | [{age: 42, name: "Tim Duncan"}, {end_year: 2016, start_year: 1997}, {name: "Spurs"}, {end_year: 2018, start_year: 2002}, {age: 41, name: "Manu Ginobili"}]     | {age: 41, name: "Manu Ginobili"}     |
| 38     | 1997          | 2016          | [{age: 42, name: "Tim Duncan"}, {end_year: 2016, start_year: 1997}, {name: "Spurs"}, {end_year: 2019, start_year: 2016}, {age: 38, name: "Paul Gasol"}]        | {age: 38, name: "Paul Gasol"}        |
| 32     | 1997          | 2013          | [{age: 42, name: "Tim Duncan"}, {end_year: 2016, start_year: 1997}, {name: "Spurs"}, {end_year: 2015, start_year: 2013}, {age: 32, name: "Marco Belinelli"}]   | {age: 32, name: "Marco Belinelli"}   |
+--------+---------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------+
Got 17 rows (time spent 5882/30980 us)

但是只要下一步开始聚合的时候这里边有两个问题:

  1. 攒出来的 list(T ) p 作为 path 不允许被 deduction
  2. target (properties map) 也不允许被输出

所以也只能输出去掉了 path和target 的:

GO FROM "player100" OVER serve YIELD serve._dst AS id, serve.start_year AS sr_start_year, properties($$) AS middle_vertex, properties(edge) AS edge0, properties($^) AS start_vertex | GO from $-.id OVER serve REVERSELY WHERE serve._dst != "player100" \
YIELD properties($$).age AS status, $-.sr_start_year AS sr_start_year, serve.start_year AS tr_start_year, [$-.start_vertex, $-.edge0, $-.middle_vertex, properties(edge), properties($$)] AS p, properties($$) AS target | \
GROUP BY $-.status YIELD $-.status AS status, 'HE-BE10' AS nodeType, max($-.sr_start_year) AS p1, max($-.tr_start_year) AS p2

这个情况下看起来还是用 cypher match 去做。

(root@nebula) [basketballplayer]> GO FROM "player100" OVER serve YIELD serve._dst AS id, serve.start_year AS sr_start_year, properties($$) AS middle_vertex, properties(edge) AS edge0, properties($^) AS start_vertex | GO from $-.id OVER serve REVERSELY WHERE serve._dst != "player100" \
                               -> YIELD properties($$).age AS status, $-.sr_start_year AS sr_start_year, serve.start_year AS tr_start_year, [$-.start_vertex, $-.edge0, $-.middle_vertex, properties(edge), properties($$)] AS p, properties($$) AS target | \
                               -> GROUP BY $-.status YIELD $-.status AS status, $-.target AS target, collect(p) AS `path`, 'HE-BE10' AS nodeType, max($-.sr_start_year) AS p1, max($-.tr_start_year) AS p2
[ERROR (-1009)]: SemanticError: Not supported expression `p' for props deduction.
(root@nebula) [basketballplayer]> GO FROM "player100" OVER serve YIELD serve._dst AS id, serve.start_year AS sr_start_year, properties($$) AS middle_vertex, properties(edge) AS edge0, properties($^) AS start_vertex | GO from $-.id OVER serve REVERSELY WHERE serve._dst != "player100" \
                               -> YIELD properties($$).age AS status, $-.sr_start_year AS sr_start_year, serve.start_year AS tr_start_year, [$-.start_vertex, $-.edge0, $-.middle_vertex, properties(edge), properties($$)] AS p, properties($$) AS target | \
                               -> GROUP BY $-.status YIELD $-.status AS status, $-.target AS target
[ERROR (-1009)]: SemanticError: Yield non-agg expression `$-.target' must be functionally dependent on items in GROUP BY clause

Thu, 06 Jan 2022 11:32:22 CST

收到,十分感谢。我之前想的把聚合效果拆开试试,类似我上面写的效果,但是发现,需求还有三跳的查询,每一跳的结果都要通过管道传递给下一层,这个很繁琐。
我先用match 试试性能吧,再次感谢。

2 个赞

此话题已在最后回复的 30 天后被自动关闭。不再允许新回复。