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

Question

我有一个包含 6 列的表:故障日期、IP 地址、 Assets 标签、sid(主键)、rdl 和错误类型。我需要一个表，其中列为第一次失败、最近(最后一次)失败、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 日)。希望这能解决我的问题。

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 天的行。