GSQL實作啟動投資圖譜查詢分析

SetAccum<VERTEX> @parent_vertex_set;
SetAccum<EDGE> @parent_edge_set;
SetAccum<VERTEX> @@result_vertex_set;
SetAccum<EDGE> @@result_edge_set;

Start (ANY) = {};
CASE lower(trim(investor_type))
WHEN "person" THEN Start = {Person.*};
WHEN "company" THEN Start = {Company.*};
WHEN "financialorg" THEN Start = {Financial_Org.*};
END;

Investor (ANY) = SELECT inv
FROM Start:inv
WHERE ( inv.type == "Person"
AND lower(trim(inv.fullname)) == lower(trim(investor_name))
) OR lower(trim(inv.name)) == lower(trim(investor_name));

Funding_rounds = SELECT tgt
FROM Investor:s - ((investment_from_company | investment_from_person |
investment_from_financialORG):e) - Funding_Rounds:tgt
ACCUM
tgt.@parent_vertex_set += s,
tgt.@parent_edge_set += e;

Invested_companies = SELECT tgt
FROM Funding_rounds:s - ((company_funding_rounds):e) - Company:tgt
ACCUM
tgt.@parent_vertex_set += s,
tgt.@parent_edge_set += e,
tgt.@min_invested_time += s.funded_at;

IPO_acquired_companies = SELECT tgt
FROM Invested_companies:s - ((company_ipo | acquired_by):e) - :tgt
ACCUM
IF (e.type == "company_ipo"
AND datetime_diff(tgt.public_at, s.@min_invested_time) > 0
AND datetime_diff(tgt.public_at, s.@min_invested_time) <= years * SECS_PER_YR)
OR (e.type == "acquired_by"
AND datetime_diff(e.acquired_at, s.@min_invested_time) > 0
AND datetime_diff(e.acquired_at, s.@min_invested_time) <= years * SECS_PER_YR)
THEN @@result_vertex_set += tgt
END;

Children = {@@result_vertex_set};
PRINT Children.size() as Num_Successful_Exits;
WHILE (Children.size() > 0) DO
Start = SELECT s
FROM Children:s
ACCUM
@@parents += s.@parent_vertex_set,
@@result_edge_set += s.@parent_edge_set;
@@result_vertex_set += @@parents;
Children = {@@parents};
@@parents.clear();

// Step 1: Compute investment amount
Financial_Org InvestAmount = SELECT f
FROM Financial_Org:f - (investment_from_financialORG:e) - Funding_Rounds:fr
ACCUM
f.@total_investment += fr.amount;

// Step 2: Rank Financial_Org
TopFinancialOrgs = SELECT f
FROM Financial_Org:f
ORDER BY f.@total_investment DESC
LIMIT k_orgs;

// Step 3: Find Persons working for TopFinancialOrgs
TopPersons = SELECT p
FROM TopFinancialOrgs:f - (employs:e) - Person:p;

// Step 4 & 5: Find Companies where TopPersons were board members and rank them
TopCompanies = SELECT c
FROM TopPersons:p - (board_member:e) - Company:c
ACCUM
c.@board_member_count += 1
ORDER BY c.@board_member_count DESC
LIMIT num_persons;

# 步驟1：將公司的離開日期（IPO或被收購）儲存在公司節點上
Comp = SELECT c
FROM (Company):c - ((company_ipo|acquired_by>):e) - (IPO|Company):x
ACCUM
  CASE WHEN e.type == "company_ipo" AND datetime_diff(x.public_at, T0) != 0 THEN
    c.@t_exit += x.public_at
  END,
  CASE WHEN e.type == "acquired_by" AND datetime_diff(e.acquired_at, T0) != 0 THEN
    c.@t_exit += e.acquired_at
  END;

# 步驟2：找出過去n年內投資金額最高的k個金融組織
Top_orgs = SELECT org
FROM (Financial_Org):org - (financial_funds:e) - Funds:f
WHERE datetime_diff(END_2013, f.funded_at) <= past_n_years * SECS_PER_YR
ACCUM org.@amount += (f.raised_amount / @@currency2USD.get(f.raised_currency_code)),
       f.@visited = TRUE
ORDER BY org.@amount DESC
LIMIT k_orgs;

# 步驟3：找出這些頂級金融組織的員工
Persons_at_top_orgs = SELECT p
FROM Top_orgs:o - (work_for_fOrg:e) - Person:p;

# 步驟4：找出曾經在成功離開公司擔任董事會成員的員工
Top_board_members = SELECT p
FROM Persons_at_top_orgs:p - (work_for_company:w) - Company:c
WHERE (w.title LIKE "%Board%" OR w.title LIKE "%board%")
  AND c.@t_exit.size() != 0
  AND datetime_diff(w.start_at, T0) != 0
  AND datetime_diff(c.@t_exit.get(0), w.start_at) > 0;

# 步驟5：找出這些成功董事會成員參與的新創企業
Top_startups = SELECT c
FROM Top_board_members:s - (work_for_company:w) - Company:c
WHERE (w.title LIKE "%Board%" OR w.title LIKE "%board%")
  AND w.start_at != T0
  AND c.status == "operating"
  AND c.@t_exit.size() == 0;

# 步驟6：根據投資輪次進行篩選
Top_early_startups = SELECT r
FROM Top_startups:s - (company_funding_rounds:e) - Funding_Rounds:r
ACCUM
  s.@visited += TRUE,
  IF @allowed_funding_rounds.contains(r.funding_round_code) THEN
    r.@visited = TRUE
  ELSE
    s.@early += FALSE
  END;