数据湖里的关系问题，一条 Cypher 就能搞定——在 Postgres 里用 Apache AGE 做图查询

-- 总账单金额按地区
SELECT r.name AS region, SUM(c.billed_amount)
FROM claims c
JOIN regions r ON c.region_id = r.id
GROUP BY r.name;

-- 平均索赔按专科
SELECT p.specialty, AVG(c.billed_amount)
FROM claims c
JOIN providers p ON c.provider_id = p.id
GROUP BY p.specialty;

WITH RECURSIVE chain AS (
  -- 起点：网络内医生发出的转介
  SELECT
    r.from_provider_id AS src,
    r.to_provider_id   AS dst,
    ARRAY[r.from_provider_id, r.to_provider_id] AS path,
    FALSE AS has_out_of_network
  FROM referrals r
  JOIN providers p ON p.id = r.from_provider_id
  WHERE p.in_network = TRUE

  UNION ALL

  -- 递归：沿链路继续走
  SELECT
    c.src,
    r.to_provider_id AS dst,
    c.path || r.to_provider_id,
    c.has_out_of_network
      OR (NOT po.in_network) AS has_out_of_network
  FROM chain c
  JOIN referrals r ON r.from_provider_id = c.dst
  JOIN providers po ON po.id = r.to_provider_id
  WHERE r.to_provider_id <> ALL(c.path)  -- 环检测
),
filtered AS (
  SELECT DISTINCT src, dst
  FROM chain
  WHERE has_out_of_network
)
SELECT
  f.dst AS out_of_network_provider_id,
  SUM(c.billed_amount) AS total_billed
FROM filtered f
JOIN claims c ON c.provider_id = f.dst
GROUP BY f.dst;

-- 加载扩展（pg_lake 读写 Iceberg，AGE 提供图查询）
CREATE EXTENSION IF NOT EXISTS age;
LOAD 'age';

-- 创建图空间
SELECT create_graph('healthcare_network');

-- 从 providers Iceberg 表创建医生节点
SELECT *
FROM cypher('healthcare_network', $$
  MATCH (n)
  WITH n LIMIT 0
  RETURN n
$$) AS (n agtype);

-- 实际建节点：逐行遍历 Iceberg 表
SELECT create_vlabel('healthcare_network', 'Provider');

INSERT INTO healthcare_network.Provider
SELECT cypher('healthcare_network', $$
  CREATE (p:Provider {id: $id, name: $name, specialty: $specialty, in_network: $in_network})
  RETURN p
$$,
  -- 把 Iceberg 表的行映射到 Cypher 参数
  row_to_json(providers)::agtype
) AS (p agtype)
FROM providers;  -- providers 是 Iceberg 表，pg_lake 自动读取 S3 上的 Parquet 文件

SELECT create_elabel('healthcare_network', 'REFERRED_TO');

SELECT *
FROM cypher('healthcare_network', $$
  MATCH (from:Provider {id: $from_id}), (to:Provider {id: $to_id})
  CREATE (from)-[r:REFERRED_TO {referral_date: $date, reason: $reason}]->(to)
  RETURN r
$$,
  row_to_json(referrals)::agtype
) AS (r agtype)
FROM referrals;

WITH out_of_network_chains AS (
  -- Cypher 子查询：遍历变长路径，找从网络内出发、终点在网络外的链路
  SELECT *
  FROM cypher('healthcare_network', $$
    MATCH (src:Provider {in_network: true})
          -[:REFERRED_TO*1..]->(dst:Provider {in_network: false})
    RETURN src.id AS src_id, dst.id AS dst_id
  $$) AS (src_id agtype, dst_id agtype)
)
-- 拿 Cypher 结果当普通 CTE，和 Iceberg claims 表做聚合
SELECT
  (dst_id)::text AS out_of_network_provider_id,
  SUM(c.billed_amount) AS total_billed
FROM out_of_network_chains oc
JOIN claims c ON c.provider_id = (oc.dst_id)::text
GROUP BY dst_id;