PostgreSQL按月末动态调整区间的近2个月数据查询实现

实现方案 #

调整后的SQL语句 #

核心修改是WHERE子句中的时间区间逻辑，替换原有14天区间即可：

SELECT 
 datex, 
 hourx, 
'AX' AS apps, 
 hostname, 
 MAX (max_cpu) AS maxofmax_cpu, 
 MAX (avg_cpu) AS maxofavg_cpu, 
 AVG (max_cpu) AS avgofmax_cpu, 
 AVG (avg_cpu) AS avgofavg_cpu 
FROM 
  tb_example 
WHERE 
  TYPE = 'Active' 
  AND apps = 'AX' 
  -- 时间区间逻辑：起始为「结束日期所属月份往前推2个月的1日」，结束为查询前一天
  AND datex >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 day') - INTERVAL '2 month'
  AND datex < CURRENT_DATE
GROUP BY 
  1, 
  2, 
  3, 
  4 
ORDER BY 
  1, 
  2

逻辑验证说明 #

和你给出的示例完全匹配：

• 当查询前一天是2021-09-28时，DATE_TRUNC('month', '2021-09-28'::date)得到2021-09-01，减2个月得到起始日期2021-07-01，符合要求
• 当查询前一天是2021-10-01时，DATE_TRUNC('month', '2021-10-01'::date)得到2021-10-01，减2个月得到起始日期2021-08-01，符合要求

每日自动运行实现思路 #

1. 数据库层定时：可以安装PostgreSQL官方扩展pg_cron，配置每天固定时间执行上述查询，可直接将结果写入归档表或生成导出文件
2. 系统层定时：使用Linux crontab或Windows任务计划，每天固定时间调用psql命令执行SQL脚本，按需处理查询结果

内容的提问来源于stack exchange，提问作者jack sumatra