mysql - mysql：仅当日期每天至少存在一次时才需要记录，该日期应从当前日期时间开始重复 4 天

Question

我有一个包含 6 列的表：故障日期、ipaddress、assettag、sid（主键）、rdl 和错误类型。我需要一个表，其中列有 First failure、Recent(Last) failure、ipaddress、assettag、rdl 但只有当日期从当前日期时间重复 4 天时，记录才会存在。连一天都不能错过。例如：如果今天是 5 月 30 日，我需要每天都有失败日期的所有记录--30 日、29 日、28 日、27 日。如果记录日期仅存在两/三/一天 - 它必须被忽略。

我可以使用“min（date）和max（date）-group by ipaddress”获得第一次和最后一次失败，但无法根据条件获取记录——“失败（日期）从当前日期时间开始重复 4 天"

    select min(date), max(date), ipaddress, assettag, rdl
 from flashinglist.response
  where ((DATE_FORMAT((date_sub(NOW(), interval 24 hour)), '%y-%m-%d')) in 
  (select group_concat((DATE_FORMAT(date,'%y-%m-%d')) separator ', ') 
from flashinglist.response group by ipaddress) 
       and (DATE_FORMAT((date_sub(NOW(), interval 48 hour)), '%y-%m-%d')) in 
        (select group_concat((DATE_FORMAT(date,'%y-%m-%d')) separator ', ') 
from flashinglist.response group by ipaddress)   
        and (DATE_FORMAT((date_sub(NOW(), interval 72 hour)), '%y-%m-%d')) in 
        (select group_concat((DATE_FORMAT(date,'%y-%m-%d')) separator ', ') 
from flashinglist.response group by ipaddress)  
        and (DATE_FORMAT((date_sub(NOW(), interval 96 hour)), '%y-%m-%d')) in 
        (select group_concat((DATE_FORMAT(date,'%y-%m-%d')) separator ', ') 
from flashinglist.response group by ipaddress) ) 
order by max(date) desc

上面的查询应该可以工作，因为我正在按 IP 连接所有日期组并检查“IN”条件，但它不起作用，无法弄清楚原因。（使用 'date_format' 仅查找日期而不是时间戳）

以下是架构和示例数据：

CREATE TABLE `response` (
  `date` varchar(50) NOT NULL,
  `ipaddress` varchar(16) NOT NULL,
  `assettag` varchar(200) NOT NULL,
  `sid` int(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `rdl` varchar(30) NOT NULL,
  `errortype` int(2) NOT NULL)
ENGINE=InnoDB DEFAULT CHARSET=latin1;

样本数据：

INSERT INTO `response` (`date`, `ipaddress`, `assettag`, `sid`, `rdl`, `errortype`) VALUES
('2019-05-31 09:46:10.878', '123.34.45.67', 'fresh', 483, '13234', 1),
('2019-05-30 19:46:11.578', '123.34.45.67', 'fresh', 490, '13234', 1),
('2019-05-29 14:30:11.577', '123.34.45.67', 'fresh', 496, '13234', 1),
('2019-05-28 17:23:11.573', '123.34.45.67', 'fresh', 499, '13234', 1),
('2019-05-27 22:32:11.550', '123.34.45.67', 'fresh', 503, '13234', 1),
('2019-05-29 12:54:11.571', '457.673.768.24', 'store', 560, '9297', 1),
('2019-05-31 08:46:11.569', '457.673.768.24', 'store', 565, '9297', 1),
('2019-05-28 10:45:11.566', '457.673.768.24', 'store', 567, '9297', 1),
('2019-05-30 20:16:11.566', '457.673.768.24', 'store', 569, '9297', 1),
('2019-05-29 23:46:11.234', '140.232.546.74', 'sample', 580, '6076', 1),
('2019-05-31 09:26:11.562', '140.232.546.74', 'sample', 581, '6076', 1),
('2019-05-30 19:34:16.533', '140.232.546.74', 'sample', 583, '6076', 1);
COMMIT;

请根据今天和过去 4 天更改值。我的输出应该返回 First failure、Recent(Last) failure、ipaddress、assettag、rdl-- 使用上面的示例数据，它必须显示 IP 记录：123.34.45.67 和 457.673.768.24，对应的最大和最小日期在范围内仅 1 到 96 小时（4 天）。IP- 140.232.546.74 不应出现，因为它是 4 天不重复失败（缺少第 28 个日期）。希望这能解决我的问题。

标签： mysql

Answer 1

计算结果中不同日期的数量，并测试这是否是所需的数字。

SELECT  min(date) AS mindate, max(date) AS maxdate, date, ipaddress, assettag, rdl 
FROM flashinglist.response 
  WHERE  date < DATE_SUB(NOW(), interval 1 hour)
  AND date > date_sub(NOW(), interval 96 hour)
GROUP BY ipaddress
ORDER BY mindate DESC
HAVING COUNT(DISTINCT DATE(date)) = DATE_SUB(maxdate, mindate) + 1

你也不应该有这些行：

  AND (date > date_sub(NOW(), interval 24 hour) )
  AND (date > date_sub(NOW(), interval 48 hour))
  AND (date > date_sub(NOW(), interval 72 hour))

因为它们将排除超过 1 天的行。