英文:
Getting weights as percentages from edges
问题
我有两种节点类型 "Person" 和 "Company"。还有一个名为 "Shareholder" 的关系，可以从个人到公司，也可以从公司到公司（因为公司也可以持有其他公司的股份）。"Shareholder" 关系有三个属性 "shareholding_start_date"、"shareholding_end_date"、"number_of_shares"。
我想要能够按名称查询一个公司（称为 c），同时提供一个日期（例如 '2021-07-23'），查询应该返回在日期 '2021-07-23' 持有 c 股份的所有个人/公司节点（即 shareholding_start_date < 2021-07-23 < shareholding_end_date）。我还想根据 'number_of_shares' 计算百分比。
举个例子，假设有一家名为 XYZ 的公司，我想知道在 2022-03-15 这一天它的所有股东。假设 ABC 在这一天有三个股东，分别是 ABC、DEF、GHI（这些可以是个人节点或公司节点）。ABC 持有 30 股，DEF 持有 50 股，GHI 持有 120 股。
查询应该类似于以下内容（显然这不是 Cypher，但我无法以其他方式表达！）：
Company.name = "XYZ"
and Shareholder.shareholding_start_date <= 2022-03-15
and Shareholder.shareholding_end_date >= 2022-03-15
返回结果类似于：
ABC 15%，DEF 25%，GHI 60%
英文:
I have two node types "Person" and "Company". There is also a relationship called "Shareholder", which can be from Person to Company or Company to Company (as companies can also hold shares in other companies). The Shareholder relationship has three properties "shareholding_start_date", "shareholding_end_date", "number_of_shares".
I want to be able to query a Company (call it c) by name, as well as provide a date (e.g. '2021-07-23'), and the query should return all Person/Company nodes that are shareholders of c at the date '2021-07-23' (i.e. shareholding_start_date < 2021-07-23 < shareholding_end_date). I also want to calculate the percentage based on 'number_of_shares'.
As an example, let's say there is a company called XYZ, and I want to know all it's shareholders on 2022-03-15. Suppose ABC has three shareholders ABC, DEF, GHI on this day (these can be either Person or Company nodes). ABC holds 30 shares, DEF holds 50 shares, GHI holds 120 shares.
The query should be something like this (obviously this is not cypher, but I'm unable to express it in any other way!):
Company.name = "XYZ"
and Shareholder.shareholding_start_date <= 2022-03-15
and SHareholder.shareholding_end_date >= 2022-03-15
Returns something like:
ABC 15%, DEF 25%, GHI 60%
答案1
得分: 2
匹配 (c:公司 {名称:"ABC"})<-[r:股东]-(s)
其中 r.shareholding_start_date <= Date("2022-03-15")
AND r.shareholding_end_date >= DATE("2022-03-15")
WITH c,
收集(s) as 股东,
收集(r) as 关系,
总数(r.number_of_shares) AS 总股数
展开 范围(0, 大小(关系) - 1) AS i
返回 股东[i].名称 AS 股东,
100.0 * 关系[i].numberOfShares/总股数 as 百分比总股数
对于请求中要求显示距离目标公司的股东的股东的情况，可以使用以下方法。
匹配 p=(c:公司 {名称:"ABC"})<-[r:股东*1..3]-(持有人)
WHERE 所有(持有 IN 关系(p) WHERE 持有.shareholding_start_date <= Date('2022-03-15') <= 持有.shareholding_end_date)
WITH
长度(p) - 1 AS 距离目标公司的距离,
节点(p)[-2].名称 AS 公司名称,
持有人,
关系(p)[-1].number_of_shares AS 持有股数
WITH
距离目标公司的距离,
公司名称,
收集(独特 {股东: 持有人.名称, 持有股数: 持有股数}) as 股东
WITH
距离目标公司的距离,
公司名称,
股东,
减少(acc=0, 公司 IN 股东 | acc + 公司['numberOfShares']) AS 总股数
返回
距离目标公司的距离,
公司名称,
减少(acc=[], 公司 IN 股东 | acc + 公司{.*, 百分比总股数:100.0 * 公司['numberOfShares']/总股数}) AS 股东
按距离目标公司的距离, 公司名称排序
英文:
MATCH (c:Company {name:"ABC"})<-[r:Shareholder]-(s)
WHERE r.shareholding_start_date <= Date("2022-03-15")
AND r.shareholding_end_date >= DATE("2022-03-15")
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].numberOfShares/totalShares as percentTotalShares
For more examples of percent of total in Neo4j, see this blog post. https://medium.com/neo4j/kickstart-your-transition-from-sql-analytic-and-window-functions-to-neo4j-987d67f7fdb4
To show shareholders of shareholders to an arbitrary distance from your target company as requested in the comment, you might use this approach.
MATCH p=(c:Company {name:"ABC"})<-[r:Shareholder*1..3]-(holder)
WHERE all(holding IN relationships(p) WHERE holding.shareholding_start_date <= Date('2022-03-15') <= holding.shareholding_end_date)
WITH
length(p) - 1 AS distanceToTargetCompany,
nodes(p)[-2].name AS companyName,
holder,
relationships(p)[-1].number_of_shares AS number_of_shares
WITH
distanceToTargetCompany,
companyName,
collect(DISTINCT {shareHolder: holder.name, numberOfShares: number_of_shares}) as shareHolders
WITH
distanceToTargetCompany,
companyName,
shareHolders,
reduce(acc=0, company in shareHolders | acc + company['numberOfShares']) AS totalShares
RETURN
distanceToTargetCompany,
companyName,
reduce(acc=[], company in shareHolders | acc + company{.*, percentTotal:100.0 * company['numberOfShares']/totalShares}) AS shareHolders
ORDER BY distanceToTargetCompany, companyName
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论