MySQL查询防火墙策略统计信息

Question

对不起，对mysql来说真的很陌生，并且在创建查询的起点上有点空白。

你想实现什么创建一个带有3个仪表的简单仪表板，以显示防火墙策略统计数据。基本上，我想要一个指标来显示最近使用不到3个月、3到6个月以及超过12个月的保单数量。

你是如何实现的我有包含列的MySQL表

policyid intlast_used int-epochtimestamp-时间戳。

我每5分钟放一次政策统计数据，所以最后得到了这个表

时间戳

策略id	last_used
511	1662808082	2022-09-10 12:08:08
511	1662808562	2022-09-10 12:16:44
511	1662809702	2022-09-10 12:35:04
511	1662809942	2022-09-10 12:40:02
511	1662810302	2022-09-10 12:45:02
511	1662810602	2022-09-10 12:50:02
511	1662810842	2022-09-10 12:55:02
512	1661442932	2022-08-25 16:55:32
512	1662808054	2022-09-10 12:08:08
512	1662808599	2022-09-10 12:16:44
512	1662809702	2022-09-10 12:35:04
512	1662809987	2022-09-10 12:40:02
512	1662810298	2022-09-10 12:45:02
512	1662810592	2022-09-10 12:50:02
512	1662810860	2022-09-10 12:55:02
512	1662811198	2022-09-10 13:00:02
512	1662811449	2022-09-10 13:05:02

不完全清楚您想要实现什么，但基于

显示最后使用的策略数少于3个月，3至6个月和12个月以上。

您可以使用条件聚合来计算基于last_used列的记录数。

尝试：

select  sum(case when FROM_UNIXTIME(last_used)  <= now() and FROM_UNIXTIME(last_used) >=(now() -INTERVAL 3 MONTH) then 1 end )  as last_3_months,
sum(case when FROM_UNIXTIME(last_used)  <= (now() -INTERVAL 3 MONTH) and FROM_UNIXTIME(last_used) >=(now() -INTERVAL 6 MONTH) then 1 end ) as between_3_6_months,
sum(case when FROM_UNIXTIME(last_used)  <= (now() -INTERVAL 12 MONTH) then 1 end )  as over_12_months
from firewall_policy_stats; 

https://dbfiddle.uk/Y-h60QD2

注意。我添加了一些更多的数据示例，只是为了测试目的。如果你想显示0而不是null，你可以使用coalesce

select  coalesce(sum(case when FROM_UNIXTIME(last_used)  <= now() and FROM_UNIXTIME(last_used) >=(now() -INTERVAL 3 MONTH) then 1 end ),0)  as last_3_months,
coalesce(sum(case when FROM_UNIXTIME(last_used)  <= (now() -INTERVAL 3 MONTH) and FROM_UNIXTIME(last_used) >=(now() -INTERVAL 6 MONTH) then 1 end ),0)  as between_3_6_months,
coalesce(sum(case when FROM_UNIXTIME(last_used)  <= (now() -INTERVAL 12 MONTH) then 1 end ),0)   as over_12_months
from firewall_policy_stats;

https://dbfiddle.uk/R5JuoulF

编辑

这与我需要的输出非常接近。然而我只想对策略id计数一次。因此，所有包含相同策略id 的行

select  coalesce(sum(case when FROM_UNIXTIME(last_used)  <= now() and FROM_UNIXTIME(last_used) >=(now() -INTERVAL 3 MONTH) then 1 end ),0)  as last_3_months,
coalesce(sum(case when FROM_UNIXTIME(last_used)  <= (now() -INTERVAL 3 MONTH) and FROM_UNIXTIME(last_used) >=(now() -INTERVAL 6 MONTH) then 1 end ),0)  as between_3_6_months,
coalesce(sum(case when FROM_UNIXTIME(last_used)  <= (now() -INTERVAL 12 MONTH) then 1 end ),0)   as over_12_months
from ( select policyid,
max(last_used) as last_used
from firewall_policy_stats 
group by policyid
) as tbl;

https://dbfiddle.uk/rJLB3svf