MySQL/MariaDB groups of 7 days interval-CodePudding

I have a Perl script which in the end will print 0 or 1 based on the query result.

    my $sthA = $dbhA->prepare( "select phone_number
                                from vicidial_closer_log vcl
                                where vcl.call_date between (DATE_SUB(NOW(), INTERVAL 7 DAY)) and NOW() 
                                and vcl.phone_number like ? 
                                 "); 
       $sthA->bind_param( 1, "%$phone_number%"); 
       $sthA->execute();
    my $sthArows=$sthA->rows;

        if ($sthArows < 1) 
           {
              print "0" ; 
            }
             else
                 {                          
                    print "1" ;
                   } 
        $sthA->finish();

Database version: MySQL 8 and 10.5.8-MariaDB.

Some data examples which can be found on this fiddle as well .

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');

What have I tried ?

I can fix this by creating another table and save the result(unika,doppia) per each number using a trigger, but I can't find a solution using the query only.

My problem for the above given data example on the 2023-01-27 11:06:40 the query should return 0(unika). Something like below:

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

The first entry per the number should be unika , if the same number enters again for the next 7 days it should be doppia , at the 8th day , unika again

CodePudding user response：

WITH RECURSIVE
enumerated AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY call_date) rn
  FROM vicidial_closer_log
),
marked AS (
  SELECT *, call_date AS last_date, CAST('unica' AS CHAR(6)) AS mark
  FROM enumerated 
  WHERE rn = 1
UNION ALL
  SELECT enumerated.*,
         CASE WHEN marked.last_date > enumerated.call_date - INTERVAL 7 DAY
              THEN marked.last_date
              ELSE enumerated.call_date
              END,
         CASE WHEN marked.last_date > enumerated.call_date - INTERVAL 7 DAY
              THEN 'doppia'
              ELSE 'unica'
              END  
  FROM enumerated
  JOIN marked ON enumerated.rn = marked.rn   1
)
SELECT closecallid, call_date, phone_number, uniqueid, mark
FROM marked 
ORDER BY call_date;

fiddle