MySQL/MariaDB按7天间隔标记unika/doppia的纯查询实现需求

需求说明 #

针对vicidial_closer_log表，按以下规则标记每条记录：

• 同一手机号的第一条记录标记为unika
• 该记录7天内的同手机号后续记录标记为doppia
• 距离最近的unika记录超过7天的新记录，重新标记为unika，其后续7天内的记录继续标记doppia

表结构与测试数据 #

CREATE TABLE `vicidial_closer_log` (
  `closecallid` int(9) unsigned NOT NULL AUTO_INCREMENT,
  `call_date` datetime DEFAULT NULL,
  `phone_number` varchar(18) COLLATE utf8_unicode_ci DEFAULT NULL,
  `uniqueid` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`closecallid`),
  KEY `call_date` (`call_date`),
  KEY `uniqueid` (`uniqueid`),
  KEY `phone_number` (`phone_number`),
  KEY `dt_phone` (`call_date`,`phone_number`),
  KEY `dt_phone_uniqueid` (`call_date`,`phone_number`,`uniqueid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

insert into `vicidial_closer_log`(call_date,phone_number,uniqueid) values
('2023-01-17 16:01:25','1000106338','1673708481.829754'),
('2023-01-17 16:02:04','1000106338','1673708519.829761'),
('2023-01-19 18:20:23','1000106338','1673889620.853236'),
('2023-01-19 18:27:04','1000106338','1673890021.853302'),
('2023-01-22 15:30:09','1000106338','1674138607.888573'),
('2023-01-22 15:53:06','1000106338','1674139983.888844'),
('2023-01-22 15:53:32','1000106338','1674140009.888856'),
('2023-01-22 15:54:15','1000106338','1674140052.888863'),
('2023-01-22 15:54:51','1000106338','1674140088.888874'),
('2023-01-22 15:55:56','1000106338','1674140153.888888'),
('2023-01-22 15:56:36','1000106338','1674140193.888895'),
('2023-01-27 11:06:40','1000106338','1674554798.944512');  

实现查询 #

使用递归CTE分组每个手机号的记录，确定每条记录所属的unika基准组：

WITH RECURSIVE call_groups AS (
    -- 初始化：取每个手机号的第一条记录作为第一个unika组
    SELECT 
        closecallid,
        call_date,
        phone_number,
        uniqueid,
        call_date AS group_start,
        'unika' AS tag
    FROM vicidial_closer_log t1
    WHERE NOT EXISTS (
        SELECT 1 FROM vicidial_closer_log t2
        WHERE t2.phone_number = t1.phone_number AND t2.call_date < t1.call_date
    )
    
    UNION ALL
    
    -- 递归处理后续记录
    SELECT 
        t.closecallid,
        t.call_date,
        t.phone_number,
        t.uniqueid,
        CASE 
            WHEN t.call_date <= DATE_ADD(cg.group_start, INTERVAL 7 DAY) THEN cg.group_start
            ELSE t.call_date
        END AS group_start,
        CASE 
            WHEN t.call_date <= DATE_ADD(cg.group_start, INTERVAL 7 DAY) THEN 'doppia'
            ELSE 'unika'
        END AS tag
    FROM vicidial_closer_log t
    JOIN call_groups cg ON t.phone_number = cg.phone_number
    WHERE t.call_date > cg.call_date
    -- 确保每条记录只被处理一次
    AND NOT EXISTS (
        SELECT 1 FROM vicidial_closer_log t2
        WHERE t2.phone_number = t.phone_number 
        AND t2.call_date > cg.call_date 
        AND t2.call_date < t.call_date
    )
)
-- 按手机号和通话时间排序输出
SELECT 
    phone_number,
    call_date,
    CONCAT(phone_number, '   ', call_date, ' ----- > ', tag) AS result
FROM call_groups
ORDER BY phone_number, call_date;

查询结果 #

1000106338   2023-01-17 16:01:25 ----- > unika
1000106338   2023-01-17 16:02:04 ----- > doppia
1000106338   2023-01-19 18:20:23 ----- > doppia
1000106338   2023-01-19 18:27:04 ----- > doppia
1000106338   2023-01-22 15:30:09 ----- > doppia
1000106338   2023-01-22 15:53:06 ----- > doppia
1000106338   2023-01-22 15:53:32 ----- > doppia
1000106338   2023-01-22 15:54:15 ----- > doppia
1000106338   2023-01-22 15:54:51 ----- > doppia
1000106338   2023-01-22 15:55:56 ----- > doppia
1000106338   2023-01-22 15:56:36 ----- > doppia
1000106338   2023-01-27 11:06:40 ----- > unika

说明 #

1. 递归CTE初始部分筛选每个手机号的最早记录，标记为unika并设置该记录时间为组起始时间
2. 递归部分依次处理后续记录：若当前记录距组起始时间不超7天，标记为doppia并沿用原组起始时间；若超过7天，标记为unika并将自身时间设为新组起始时间
3. 最后按手机号和通话时间排序，输出符合需求的标记结果

内容的提问来源于stack exchange，提问作者Ergest Basha