zsjz-ai/sql/testSql/关系图.sql

--  '6222024505288690000',
SELECT unnest(array ['A', 'B', 'C'])          AS node_id,
       unnest(array ['张三', '李四', '王五']) AS node_name;

DROP TABLE IF EXISTS temp_core_nodes;
DROP TABLE IF EXISTS person_rel_edge_trans_filter;
DROP TABLE IF EXISTS person_rel_edge_trans;
CREATE TEMP TABLE temp_core_nodes AS
SELECT unnest(['6222024505288690226', '1023254100000211', '6216603637176773705',
    '3521023012569874',
    '6223092984200995695',
    '6237683810003447983',
    '6237683810003442235',
    '6225881084437762',
    '6222027549499181296',
    '513126197412174415', '513126199410052413', '110101199003071233',
    '330382199003081853', '510183198103160051'
    ]) AS node_id;
-- 如果有日期条件 则过滤 日期
CREATE TEMP TABLE person_rel_edge_trans_filter AS
SELECT *
FROM (
         -- 情况1: 核心 -> 外部
         SELECT *
         FROM person_rel_edge
         WHERE clazz = 'card_card'
           AND fx = 1
           AND node1 IN (SELECT node_id FROM temp_core_nodes)
           AND node2 NOT IN (SELECT node_id FROM temp_core_nodes)

         UNION ALL

         -- 情况2: 外部 -> 核心
         SELECT *
         FROM person_rel_edge
         WHERE clazz = 'card_card'
           AND fx = 2
           AND node1 NOT IN (SELECT node_id FROM temp_core_nodes)
           AND node2 IN (SELECT node_id FROM temp_core_nodes)) AS combined_edges;

select node_id, node_type
from (select node1 as node_id, 'card' as node_type
      from person_rel_edge_trans_filter
      GROUP BY node1
      HAVING count(node2) > 1
      union all
      select node2 as node_id, 'card' as node_type
      from person_rel_edge_trans_filter
      GROUP BY node2
      HAVING count(node1) > 1) as temp_node_id;

-- 715条
-- select count(*) from person_rel_edge_trans_filter GROUP BY node1, node1, sj;

insert into temp_core_nodes
select node_id
from (select node1 as node_id
      from person_rel_edge_trans_filter
      union all
      select node2 as node_id
      from person_rel_edge_trans_filter) as temp_node_id
group by node_id;

-- 125条
-- select count(*) from temp_core_nodes;
-- select * from temp_core_nodes;

-- 856条
create table person_rel_edge_trans as
SELECT *
FROM person_rel_edge
WHERE clazz = 'card_card'
  and node1 IN (SELECT node_id FROM temp_core_nodes)
  AND node2 IN (SELECT node_id FROM temp_core_nodes);



select count(*)
from person_rel_edge
GROUP BY node1, node2, sj;


SELECT node1, node2, sj, mc
FROM person_rel_edge
WHERE clazz not in ('card_card', 'sfz_sfz', 'sfz_obj', 'tel_tel')
  and node1 IN (SELECT node_id FROM temp_core_nodes)
  AND node2 IN (SELECT node_id FROM temp_core_nodes)
GROUP BY node1, node2, sj;

SELECT max(id)           as id,
       node1,
       node2,
       mc,
       clazz,
       sum(num::decimal) as num,
       any_value(fx)     as fx,
       any_value(lx)     as lx
FROM person_rel_edge_trans
where clazz in ('card_card')
group by node1, node2, mc, clazz;

SELECT node1, node2
FROM person_rel_edge_trans
where clazz in ('card_card')
group by node1, node2;

--119
SELECT *
FROM temp_core_nodes;