zsjz-ai/sql/testSql/test_sql.sql

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;