You need to enable JavaScript to run this app. 如何使用PostgreSQL生成指定日期区间的月度作业完成量统计报表
优惠活动
大模型
产品
解决方案
定价
更多

如何使用PostgreSQL生成指定日期区间的月度作业完成量统计报表

月度作业完成数统计PostgreSQL实现方案

实现思路

要满足柱状图展示的时间轴连续性要求,需要覆盖查询区间内的所有自然月,即使当月没有已完成作业也返回0值,避免图表断档。核心用到generate_series生成连续月份序列,左连接作业表的聚合统计结果即可。

完整可运行SQL

-- 参数:$1为查询起始日期,$2为查询结束日期,示例值分别为'2020-01-01'、'2020-12-01'
SELECT
  TO_CHAR(month_list.month_start, 'Mon YYYY') AS "Months",
  COALESCE(month_stats.completed_count, 0) AS "Jobs Completed"
FROM generate_series(
  DATE_TRUNC('month', $1::DATE),
  DATE_TRUNC('month', $2::DATE),
  INTERVAL '1 month'
) AS month_list(month_start)
LEFT JOIN (
  SELECT
    DATE_TRUNC('month', date_completed) AS month_bucket,
    COUNT(*) AS completed_count
  FROM your_job_table -- 替换为实际的作业表名称
  WHERE
    status = '1' -- 仅统计状态为已完成的记录
    AND date_completed BETWEEN $1 AND $2
  GROUP BY DATE_TRUNC('month', date_completed)
) AS month_stats
ON month_list.month_start = month_stats.month_bucket
ORDER BY month_list.month_start;

关键规则说明

  • 过滤作业记录的条件(状态判断、日期范围)必须放在左连接的内层子查询中,如果放到最外层的WHERE子句,空值的月份节点会被过滤,导致时间序列不连续
  • COALESCE用于将无作业记录的月份的空统计值转为0,避免前端拿到null值导致图表渲染异常
  • 列名使用双引号包裹,是PostgreSQL的语法要求,用于保留带空格的自定义列名,输出结构和你需要的表格式完全一致
  • 最终排序按月份正序排列,适配柱状图的时间轴展示逻辑

pg模块适配提示

SQL采用参数化占位符写法,可直接在Node.js的pg模块中调用,无需拼接SQL字符串,避免注入风险:

const { Pool } = require('pg');
const pool = new Pool({ /* 数据库连接配置 */ });

// 调用示例
async function getMonthlyJobStats(startDate, endDate) {
  const queryRes = await pool.query(
    `上述SQL语句`,
    [startDate, endDate]
  );
  // 返回的rows数组结构直接匹配前端可视化要求,可直接使用
  return queryRes.rows;
}

若业务不需要保留无作业的月份节点,可省略generate_series部分,直接对作业表按月份分组统计即可,但生成的时间轴会有断档。

内容的提问来源于stack exchange,提问作者TechArtificer

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.06 04:15:05