zsjz-ai/sql/testSql/graph_test_1.sql

DROP TABLE IF EXISTS temp_core_nodes;

DROP TABLE IF EXISTS other_filter_all_rel_edge;
DROP TABLE IF EXISTS other_filter_rel_edge;
DROP TABLE IF EXISTS other_core_nodes;

DROP TABLE IF EXISTS call_filter_all_rel_edge;
DROP TABLE IF EXISTS call_filter_rel_edge;
DROP TABLE IF EXISTS call_core_nodes;

DROP TABLE IF EXISTS trans_filter_all_rel_edge;
DROP TABLE IF EXISTS trans_filter_rel_edge;
DROP TABLE IF EXISTS trans_core_nodes;
DROP TABLE IF EXISTS final_rel_edge;

CREATE
TEMP TABLE final_rel_edge AS
SELECT CAST(NULL AS VARCHAR) AS node1,
       CAST(NULL AS VARCHAR) AS node2,
       CAST(NULL AS VARCHAR) AS node1cn,
       CAST(NULL AS VARCHAR) AS node2cn,
       CAST(NULL AS VARCHAR) AS mc,
       CAST(NULL AS VARCHAR) AS clazz,
       CAST(NULL AS VARCHAR) AS num,
       CAST(NULL AS INTEGER) AS fx,
       CAST(NULL AS INTEGER) AS lx WHERE 1 = 0;
CREATE
TEMP TABLE temp_core_nodes AS
SELECT unnest(['513126197412174415','513126199410052413', '110101199003071233', '330382199003081853',
                                    '510183198103160051', '6222024505288690226', '1023254100000211',
                                    '6216603637176773705', '3521023012569874', '6223092984200995695',
                                    '6237683810003447983', '6237683810003442235', '6225881084437762',
                                    '6222027549499181296']) AS node_id,
       unnest(['obj','obj', 'obj', 'obj', 'obj', 'card', 'card', 'card', 'card', 'card', 'card', 'card', 'card',
                     'card'])                               AS node_type;
CREATE
TEMP TABLE trans_core_nodes AS
SELECT *
FROM temp_core_nodes;
CREATE
TEMP TABLE trans_filter_all_rel_edge AS
SELECT node1,
       node2,
       ANY_VALUE(node1cn) AS node1cn,
       ANY_VALUE(node2cn) AS node2cn,
       ANY_VALUE(mc)      AS mc,
       ANY_VALUE(clazz)   AS clazz,
       sum(num::decimal)  AS num,
       sj                 AS sj,
       ANY_VALUE(fx)      AS fx,
       ANY_VALUE(lx)      AS lx
FROM rel_edge
WHERE clazz = 'card_card'
  AND num::decimal >= 1 AND num::decimal <= 2000000
GROUP BY node1, node2, sj;
CREATE
TEMP TABLE trans_filter_rel_edge AS
SELECT *
FROM (SELECT *
      FROM trans_filter_all_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
      SELECT *
      FROM trans_filter_all_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;
insert into trans_core_nodes
select node_id, node_type
from (select node1 as node_id, 'card' as node_type
      from trans_filter_rel_edge
      union all
      select node2 as node_id, 'card' as node_type
      from trans_filter_rel_edge) as temp_node_id
GROUP BY node_id, node_type;
INSERT INTO final_rel_edge
SELECT node1,
       node2,
       node1cn,
       node2cn,
       mc,
       clazz,
       sum(num::decimal) as num,
       ANY_VALUE(fx)     as fx,
       ANY_VALUE(lx)     as lx
FROM trans_filter_rel_edge
WHERE node1 IN (SELECT node_id FROM trans_core_nodes)
  AND node2 IN (SELECT node_id FROM trans_core_nodes)
GROUP BY node1, node2, mc, clazz;
CREATE
TEMP TABLE call_core_nodes AS
SELECT *
FROM temp_core_nodes;
CREATE
TEMP TABLE call_filter_all_rel_edge AS
SELECT node1,
       node2,
       ANY_VALUE(node1cn) AS node1cn,
       ANY_VALUE(node2cn) AS node2cn,
       ANY_VALUE(mc)      AS mc,
       ANY_VALUE(clazz)   AS clazz,
       count()            AS num,
       ANY_VALUE(sj)      AS sj,
       ANY_VALUE(fx)      AS fx,
       ANY_VALUE(lx)      AS lx
FROM rel_edge
WHERE clazz = 'tel_tel'
GROUP BY node1, node2;
CREATE
TEMP TABLE call_filter_rel_edge AS
SELECT *
FROM (SELECT *
      FROM call_filter_all_rel_edge
      WHERE clazz = 'tel_tel'
        AND fx = 1
        AND node1 IN (SELECT node_id FROM call_core_nodes)
        AND node2 NOT IN (SELECT node_id FROM call_core_nodes)
      UNION ALL
      SELECT *
      FROM call_filter_all_rel_edge
      WHERE clazz = 'tel_tel'
        AND fx = 2
        AND node1 NOT IN (SELECT node_id FROM call_core_nodes)
        AND node2 IN (SELECT node_id FROM call_core_nodes)) AS combined_edges
WHERE num >= 4;
INSERT INTO call_core_nodes
select node_id, node_type
from (select node1 as node_id, 'tel' as node_type
      from call_filter_rel_edge
      union all
      select node2 as node_id, 'tel' as node_type
      from call_filter_rel_edge) as temp_node_id
GROUP BY node_id, node_type;
INSERT INTO final_rel_edge
SELECT node1,
       node2,
       node1cn,
       node2cn,
       mc,
       clazz,
       sum(num::integer) as num,
       ANY_VALUE(fx)     as fx,
       ANY_VALUE(lx)     as lx
FROM call_filter_rel_edge
WHERE node1 IN (SELECT node_id FROM call_core_nodes)
  AND node2 IN (SELECT node_id FROM call_core_nodes)
GROUP BY node1, node2, mc, clazz;
CREATE
TEMP TABLE other_core_nodes AS
SELECT *
FROM temp_core_nodes;
CREATE
TEMP TABLE other_filter_all_rel_edge AS
SELECT *
FROM rel_edge
WHERE clazz NOT IN ('card_card', 'tel_tel');
CREATE
TEMP TABLE other_filter_rel_edge AS
SELECT *
FROM other_filter_all_rel_edge
WHERE clazz NOT IN ('card_card', 'tel_tel')
  AND node1 IN (SELECT node_id FROM trans_core_nodes)
  AND node2 IN (SELECT node_id FROM trans_core_nodes);
INSERT INTO other_core_nodes
SELECT node_id, node_type
FROM (SELECT node1 as node_id, 'other' as node_type
      FROM other_filter_rel_edge
      UNION ALL
      SELECT node2 as node_id, 'other' as node_type
      FROM other_filter_rel_edge) as temp_node_id
GROUP BY node_id, node_type;
INSERT INTO final_rel_edge
SELECT node1,
       node2,
       node1cn,
       node2cn,
       mc,
       clazz,
       num,
       fx,
       lx
FROM other_filter_rel_edge
WHERE clazz not in ('card_card', 'tel_tel')
  AND node1 IN (SELECT node_id FROM other_core_nodes)
  AND node2 IN (SELECT node_id FROM other_core_nodes);
SELECT node1,
       node2,
       mc,
       node1cn,
       node2cn,
       clazz,
       SUM(COALESCE(num, '0')::DECIMAL) AS num,
       ANY_VALUE(fx)                    as fx,
       ANY_VALUE(lx)                    as lx
FROM final_rel_edge