Question

我有两个节点类型“个人”和“公司”。还有一种称为“股东”的关系，可以是个人与公司之间的关系，也可以是公司与公司之间（因为公司也可以持有其他公司的股份）。股东关系有三个属性：“持股开始日期”、“持股结束日期”和“股份数量”。

我有一个查询，它接受公司名称（称之为ABC），并提供日期（例如，2021-07-23），该查询返回在2021-07-22日期（即，持股_开始日期<；2021-07-23<；持股_结束日期）作为c股东的所有个人/公司节点。它还根据number_of_shares计算百分比。

工作查询如下：

MATCH (c:Company {name:”ABC”})<-[r:Shareholder]-(s)
WHERE R.shareholding_start_date <= Date(“2021-07-23”) 
   AND r.shareholding_end_date >= DATE(“2021-07-23”)
WITH c, 
collect(s) as shareholders, 
collect(r) as rels, 
sum(r.number_of_shares) AS totalShares
UNWIND range(0, size(rels) - 1) AS I
RETURN shareholders[i].name AS shareholder, 
100.0 * rels[i].number_of_shares/totalShares as percentTotalShares

我希望对其进行扩展，以便它也显示股东的股东，最高可达查询中定义的某个层限制（例如3）。所以在上面的例子中，查询会返回ABC公司的所有股东，可能是“个人”或“公司”节点类型。在ABC的股东是“公司”节点的情况下，我基本上想在他们身上重复相同的代码，并列出他们的所有股东，以及在该公司的持股%。这种情况应该持续到我们达到极限（即3）。

Answer 1

Assumptions

The date constraint is applied to all relationships in every path to the specified company ("ABC").
The end date property does not exist if shares are still currently held.
If an entity sells only a portion of its shares from a given purchase, the end date property is added, the number of shares is reduced to the number sold, and the remaining shares are used to create a new relationship with the same start date. (This is not implemented in my answer, since the use case does not sell any shares).
The date constraint is also enforced when calculating totalShares for a company.
A company can hold some of its own shares.
You pass the target date and company name in the parameters $targetDate and $company.

Data model

为了便于阅读，我简化了数据模型中的名称：

(:Person|Company {name})-[:HOLDS {start, end, shares}]->(:Company {name})

Cypher query

以下是实现您的用例的一种方法：

WITH DATE($targetDate) AS targetDate

// Find all paths up to length 3 involving the specified company, on the target date.
MATCH ()-[rs:HOLDS*..3]->(:Company {name: $company})
WHERE ALL(r IN rs WHERE r.start <= targetDate AND (r.end IS NULL OR targetDate <= r.end))

// Collect the distinct HOLD relationships in the paths for each company.
WITH targetDate, ENDNODE(rs[0]) AS c, COLLECT(DISTINCT rs[0]) AS rels

// Calculate total number of shares owned by each company in the paths, on the target date
UNWIND rels AS rel
WITH c, rels, TOFLOAT(SUM(rel.shares)) AS totalShares

// Return a company, a shareholder, and the percentage of that company owned by the shareholder on the target date.
UNWIND rels AS rel
WITH totalShares, c.name AS company, STARTNODE(rel).name AS holder, SUM(rel.shares) AS shares
RETURN company, holder, 100*shares/totalShares AS pct

Test data

CREATE (p1:Person {name:  John }),
       (p2:Person {name:  Dave }),
       (p3:Person {name:  Alice })

CREATE (c1:Company {name:  Comp1 }),
       (c2:Company {name:  Comp2 }),
       (c3:Company {name:  Comp3 }),
       (c4:Company {name:  ABC })

CREATE (p1)-[:HOLDS {start: date( 2018-01-01 ), end: date( 2022-12-31 ), shares: 1000}]->(c1)
CREATE (c2)-[:HOLDS {start: date( 2020-01-01 ), end: date( 2025-12-31 ), shares: 750}]->(c1)
CREATE (p3)-[:HOLDS {start: date( 2019-01-01 ), end: date( 2022-12-31 ), shares: 800}]->(c3)
CREATE (c1)-[:HOLDS {start: date( 2016-07-01 ), end: date( 2023-12-31 ), shares: 800}]->(c4)

// Dave bought 500 shares of Comp1, and later bought 250 more.
// Then he sold 125 shares from the second batch on  2023-06-30  and still has the remaining 125 shares.
CREATE (p2)-[:HOLDS {start: date( 2020-01-01 ), end: date( 2023-12-31 ), shares: 500}]->(c1)
CREATE (p2)-[:HOLDS {start: date( 2023-01-01 ), end: date( 2023-06-30 ), shares: 125}]->(c1)
CREATE (p2)-[:HOLDS {start: date( 2023-01-01 ), shares: 125}]->(c1)

// ABC and Comp2 own shares of each other
CREATE (c2)-[:HOLDS {start: date( 2017-01-01 ), end: date( 2023-12-31 ), shares: 700}]->(c4)
CREATE (c4)-[:HOLDS {start: date( 2016-07-01 ), shares: 500}]->(c2)

// Comp1 holds (and continues to hold) some of its own shares
CREATE (c1)-[:HOLDS {start: date( 2021-01-01 ), shares: 500}]->(c1)

Results

╒═══════╤═══════╤══════════════════╕
│company│holder │pct               │
╞═══════╪═══════╪══════════════════╡
│"ABC"  │"Comp2"│46.666666666666664│
├───────┼───────┼──────────────────┤
│"ABC"  │"Comp1"│53.333333333333336│
├───────┼───────┼──────────────────┤
│"Comp2"│"ABC"  │100.0             │
├───────┼───────┼──────────────────┤
│"Comp1"│"Comp2"│40.0              │
├───────┼───────┼──────────────────┤
│"Comp1"│"Dave" │33.333333333333336│
├───────┼───────┼──────────────────┤
│"Comp1"│"Comp1"│26.666666666666668│
└───────┴───────┴──────────────────┘

Matching graph