zsjz-ai/sql/testSql/aa.sql

WITH base_call AS (SELECT personName,
                          personPhone,
                          COALESCE(otherName, '未知对手') AS otherName,
                          otherPhone,
                          DATE_TRUNC('month', callDate)   AS call_month
                   FROM call_record
                   WHERE personName IN ('王某某')),
     monthly_stats AS (SELECT personName, personPhone, otherName, otherPhone, call_month, COUNT(*) AS month_call_count
                       FROM base_call
                       GROUP BY personName, personPhone, otherName, otherPhone, call_month),
     continuous_group AS (SELECT personName,
                                 personPhone,
                                 otherName,
                                 otherPhone,
                                 month_call_count,
                                 call_month,
                                 DATE_DIFF('month', DATE '2000-01-01', call_month) -
                                 ROW_NUMBER() OVER ( PARTITION BY personName, personPhone, otherName, otherPhone
                                     ORDER BY call_month ) AS cont_group_id
                          FROM monthly_stats),
     qualified_groups AS (SELECT personName,
                                 personPhone,
                                 otherName,
                                 otherPhone,
                                 cont_group_id,
                                 SUM(month_call_count) AS pair_total_calls,
                                 COUNT(*)              AS pair_cont_month_count,
                                 MIN(call_month)       AS continuous_start_month,
                                 MAX(call_month)       AS continuous_end_month
                          FROM continuous_group
                          GROUP BY personName, personPhone, otherName, otherPhone, cont_group_id
                          HAVING COUNT(*) >= 2)
SELECT personName,
       otherName,
       otherPhone,
       SUM(pair_total_calls)      AS pairTotalCalls,
       SUM(pair_cont_month_count) AS pairContMonthCount
FROM qualified_groups
GROUP BY personName, otherName, otherPhone
LIMIT 20