DROP TABLE IF EXISTS filter_rel_edge; DROP TABLE IF EXISTS temp_core_nodes; DROP TABLE IF EXISTS person_rel_edge_trans_filter; DROP TABLE IF EXISTS person_rel_edge_call_filter; DROP TABLE IF EXISTS person_rel_edge_other; DROP TABLE IF EXISTS person_rel_edge_call; DROP TABLE IF EXISTS person_rel_edge_trans; 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 filter_rel_edge AS SELECT * FROM person_rel_edge; CREATE TEMP TABLE person_rel_edge_trans_filter AS SELECT * FROM (SELECT * FROM filter_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 filter_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 where 1 = 1 AND num::decimal >= 1 AND num::decimal <= 2000; insert into temp_core_nodes select node_id, node_type from (select node1 as node_id, 'card' as node_type from person_rel_edge_trans_filter union all select node2 as node_id, 'card' as node_type from person_rel_edge_trans_filter) as temp_node_id group by node_id, node_type; CREATE TEMP TABLE person_rel_edge_call_filter AS SELECT * FROM (SELECT * FROM filter_rel_edge WHERE clazz = 'tel_tel' 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 filter_rel_edge WHERE clazz = 'tel_tel' 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 where num::integer >= 4; insert into temp_core_nodes select node_id, node_type from (select node1 as node_id, 'tel' as node_type from person_rel_edge_call_filter union all select node2 as node_id, 'tel' as node_type from person_rel_edge_call_filter) as temp_node_id group by node_id, node_type; SELECT node_id, node_type FROM temp_core_nodes GROUP BY ALL; SELECT max(id) as id, node1, node2, mc, clazz, SUM(COALESCE(num, 0)::DECIMAL) AS num, any_value(fx) as fx, any_value(lx) as lx FROM filter_rel_edge WHERE node1 IN (SELECT node_id FROM temp_core_nodes) AND node2 IN (SELECT node_id FROM temp_core_nodes) group by node1, node2, mc, clazz; SELECT id, node1, node2, mc, clazz, num, fx, lx FROM filter_rel_edge WHERE clazz not in ('card_card', 'tel_tel') AND node1 IN (SELECT node_id FROM temp_core_nodes) AND node2 IN (SELECT node_id FROM temp_core_nodes); -- SELECT max(id) as id, node1, node2, mc, clazz, SUM(COALESCE(num, 0)::DECIMAL) AS num, any_value(fx) as fx, any_value(lx) as lx FROM filter_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) group by node1, node2, mc, clazz; SELECT max(id) as id, node1, node2, mc, clazz, sum(num::integer) as num, any_value(fx) as fx, any_value(lx) as lx FROM filter_rel_edge WHERE clazz = '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, mc, clazz; with filter_recipient as (select 0 as type, id, recipientName as personName, recipientCurrentAddress as location, sendDate from express_info WHERE recipientCurrentAddress = '滕王阁'), filter_sender as (select 1 as type, id, senderName as personName, senderCurrentAddress as location, sendDate from express_info WHERE senderCurrentAddress = '滕王阁'), union_data as (select * from filter_recipient union all select * from filter_sender) select * from union_data select personName, location, count(*) as expressCount, count() filter (type = 0) as recipientCount, count() filter (type = 1) as senderCount, string_agg(id, ',') as ids from union_data group by personName, location order by expressCount desc SELECT coalesce(personName, personPhone) as personName, personRegionId, personCellId, personStationName, (COUNT(*) FILTER (EXTRACT(HOUR FROM callTime::TIME) >= 6 AND EXTRACT(HOUR FROM callTime::TIME) < 11) + COUNT(*) FILTER (EXTRACT(HOUR FROM callTime::TIME) >= 20 OR EXTRACT(HOUR FROM callTime::TIME) < 20)) AS dayNightTotal FROM call_record WHERE personCellId IS NOT NULL AND personRegionId IS NOT NULL GROUP BY ALL having dayNightTotal > 1 ORDER BY dayNightTotal DESC SELECT * FROM call_record WHERE personName = '白歌' AND callTime::TIME >= '22:00:00' AND callTime::TIME <= '23:00:00' AND otherName IS NULL; WITH monthly_stats AS (SELECT personName, personCardNo, COALESCE(otherName, '未知对手') AS otherName, otherCardNo, EXTRACT(YEAR FROM transDate) * 12 + EXTRACT(MONTH FROM transDate) AS ym, DATE_TRUNC('month', transDate) AS trans_month, COUNT(*) AS monthly_times, SUM(transAmount) AS monthly_amount FROM trans_record WHERE personName IN ('白歌') and otherName = '支付宝（中国）网络技术有限公司' GROUP BY ALL), continuous_group AS (SELECT personName, personCardNo, otherName, otherCardNo, monthly_times, trans_month, monthly_amount, ym - ROW_NUMBER() OVER ( PARTITION BY personName, personCardNo,otherName,otherCardNo ORDER BY trans_month ) AS cont_group_id FROM monthly_stats) SELECT personName, personCardNo, otherName, otherCardNo, cont_group_id as contGroupId, SUM(monthly_times) AS pairTotalTrans, SUM(monthly_amount) AS pairTotalAmount, COUNT(*) AS pairContMonthCount, MIN(trans_month) AS continuousStartMonth, MAX(trans_month) AS continuousEndMonth FROM continuous_group GROUP BY ALL HAVING COUNT(*) >= 6 LIMIT 200; CREATE SEQUENCE time_series_id_seq START 1; DROP TABLE IF EXISTS time_series_info; CREATE TABLE time_series_info AS select nextval('') as rowId, personName, otherName, seriesDateTime, tableNameEn, ext1, ext2, ext3, ext4, ext5, ext6, ext7, ext8, ext9 from (SELECT personName AS personName, personStationLatitude AS ext9, personStationLongitude AS ext8, personStationName AS ext7, otherName AS otherName, personCellId AS ext6, id AS id, '通信记录' AS tableNameCn, callDateTime AS seriesDateTime, personRegionId AS ext5, callDirection AS ext4, 'call_record' AS tableNameEn, operatorId AS ext3, otherPhone AS ext2, personPhone AS ext1 FROM call_record UNION ALL SELECT personName AS personName, null AS ext9, null AS ext8, null AS ext7, otherName AS otherName, transAmount AS ext6, id AS id, '交易记录' AS tableNameCn, transDateTime AS seriesDateTime, null AS ext5, debitCreditFlag AS ext4, 'trans_record' AS tableNameEn, bankName AS ext3, otherCardNo AS ext2, personCardNo AS ext1 FROM trans_record UNION ALL SELECT personName AS personName, null AS ext9, null AS ext8, null AS ext7, null AS otherName, null AS ext6, id AS id, '住宿信息' AS tableNameCn, inDateTime AS seriesDateTime, addressDetail AS ext5, roomNo AS ext4, 'trans_record' AS tableNameEn, null AS ext3, hotelName AS ext2, personCertNo AS ext1 FROM hotel_stay_info UNION ALL SELECT personName AS personName, null AS ext9, null AS ext8, null AS ext7, null AS otherName, null AS ext6, id AS id, '铁路行程' AS tableNameCn, depDateTime AS seriesDateTime, null AS ext5, destination AS ext4, 'train_ticket_info' AS tableNameEn, origin AS ext3, trainNo AS ext2, personCertNo AS ext1 FROM train_ticket_info UNION ALL SELECT personName AS personName, null AS ext9, null AS ext8, null AS ext7, null AS otherName, null AS ext6, id AS id, '铁路行程' AS tableNameCn, depDateTime AS seriesDateTime, null AS ext5, destination AS ext4, 'together_train_info' AS tableNameEn, origin AS ext3, trainNo AS ext2, personCertNo AS ext1 FROM together_train_info UNION ALL SELECT personName AS personName, null AS ext9, null AS ext8, null AS ext7, null AS otherName, null AS ext6, id AS id, '航班行程' AS tableNameCn, depDateTime AS seriesDateTime, null AS ext5, arrAirport AS ext4, 'flight_ticket_info' AS tableNameEn, depAirport AS ext3, ticketNo AS ext2, personCertNo AS ext1 FROM flight_ticket_info UNION ALL SELECT personNameCn AS personName, null AS ext9, null AS ext8, null AS ext7, null AS otherName, null AS ext6, id AS id, '航班行程' AS tableNameCn, depDateTime AS seriesDateTime, null AS ext5, arrCode AS ext4, 'flight_departure_info' AS tableNameEn, depCode AS ext3, flightNo AS ext2, personCertNo AS ext1 FROM flight_departure_info UNION ALL SELECT companionName AS personName, null AS ext9, null AS ext8, null AS ext7, null AS otherName, null AS ext6, id AS id, '航班行程' AS tableNameCn, flightDateTime AS seriesDateTime, null AS ext5, destination AS ext4, 'together_flight_info' AS tableNameEn, origin AS ext3, flightNo AS ext2, personCertNo AS ext1 FROM together_flight_info UNION ALL SELECT personName AS personName, null AS ext9, null AS ext8, null AS ext7, null AS otherName, null AS ext6, id AS id, '汽车购票行程' AS tableNameCn, depDateTime AS seriesDateTime, null AS ext5, destination AS ext4, 'track_car_ticket_info' AS tableNameEn, origin AS ext3, bch AS ext2, personCertNo AS ext1 FROM track_car_ticket_info UNION ALL SELECT recipientName AS personName, null AS ext9, null AS ext8, null AS ext7, senderName AS otherName, null AS ext6, id AS id, '快递收发' AS tableNameCn, sendDate AS seriesDateTime, null AS ext5, senderCurrentAddress AS ext4, 'express_info' AS tableNameEn, recipientCurrentAddress AS ext3, null AS ext2, null AS ext1 FROM express_info) as t ORDER BY seriesDateTime; INSERT INTO rel_edge (node1, node2, node1cn, node2cn, mc, clazz, num, sj, fx, lx) SELECT CASE WHEN c.callDirection = '主叫' THEN c.personPhone ELSE c.otherPhone END AS node1, CASE WHEN c.callDirection = '主叫' THEN c.otherPhone ELSE c.personPhone END AS node2, CASE WHEN c.callDirection = '主叫' THEN c.personName ELSE c.otherName END AS node1cn, CASE WHEN c.callDirection = '主叫' THEN c.otherName ELSE c.personName END AS node2cn, CASE WHEN c.callDirection = '主叫' THEN 1 ELSE 2 END AS fx, '通信' AS mc, 'tel_tel' AS clazz, '1' AS num, c.callDateTime AS sj, 1 AS lx FROM call_record as c limit 500;