Skip to main content
 首页 » 数据库

PostgreSQL 实战——查询上月状态没有成功的记录

2022年07月19日133emanlee

本文通过示例进行实战学习,如何有效查找上月状态没有成功的记录。

需求说明

假设有过程在后台运行并在数据库中记录状态。现在需要查询上月状态从未成功的记录。

insert into exp_table values 
(1  ,'2021-06-15' ,'FAILED'),     -- <- PID 1 was successful, but in last month  
(1  ,'2021-06-05' ,'FAILED'), 
(1  ,'2021-06-01' ,'FAILED'), 
(1  ,'2021-05-01' ,'SUCCESSFUL' ),  
(2  ,'2021-06-15' ,'SUCCESSFUL'),   -- <- PID 2 was successful failed within month 
(2  ,'2021-06-05' ,'FAILED'), 
(2  ,'2021-06-01' ,'SUCCESSFUL'), 
(3  ,'2021-06-15' ,'FAILED'  ),    -- <- PID 3 only fails 
(3  ,'2021-06-05' ,'FAILED'), 
(3  ,'2021-06-01' ,   'FAILED'), 
(4  ,'2021-06-15' ,  'SUCCESSFUL'),   -- <- PID 4 only successful 
(4  ,'2021-06-05' ,   'SUCCESSFUL'), 
(4  ,'2021-06-01' ,  'SUCCESSFUL') 

给定条件:

  • 假设当前时间为 2021-06-16
  • 查询上月只有失败状态的记录( 2021-05-16 ~ 2021-06-16 )

期望结果:

  • PID 为1 和 PID 为3 的任务符合条件

实现说明

首先尝试获得每个PID的状态统计量,然后再通过HAVING过滤失败数量为1的记录,但结果不正确:

 WITH dataset_by_status AS 
 ( 
      SELECT pid, status, Count(*) AS counter 
      FROM     exp_table 
      WHERE    (status = 'FAILED'  OR  status = 'SUCCESSFUL') 
      AND      exe_date >= ('2021-06-16'::date - interval '1 MONTH') 
      GROUP BY pid,status 
      ORDER BY pid  
) 
 SELECT   pid, 
          count(*) AS counter 
 FROM     dataset_by_status 
 WHERE    status = 'FAILED' 
 GROUP BY pid 
 HAVING   count(*) = 1; 

因为上面仅过滤失败状态,但PID 为 2 的记录也有成功的记录。下面看如何解决。

利用 fliter 子句

在count 函数中使用 filter子句实现:

select pid 
from exp_table 
group by pid 
having count(*) filter (where status = 'SUCCESSFUL' 
                          and exe_date >= '2021-06-16'::date  - interval '1 month') = 0; 

查询结果符合预期,但我们可以日期过滤放在where 条件的后面,提升查询性能:

select pid, count(*) filter (where status = 'FAILED') as failed 
from exp_table 
where exe_date >= '2021-06-16'::date - interval '1 month' 
group by pid 
having count(*) filter (where status = 'SUCCESSFUL') = 0; 

总结

本文综合利用 with、日期运算、filter表达式,查询上月未成功运行的任务。


本文参考链接:https://blog.csdn.net/neweastsun/article/details/119141185
阅读延展