sql - 按小时分组，汇总平均值

Question

我有一个包含作业历史记录的 PostgreSQL 表:

CREATE TABLE jobHistory
(
   jobid int4,
   starttime timestamptz,
   endtime timestamptz
);

我正在尝试创建一个 SQL 查询来显示每小时平均运行的作业数。应该有 24 行(24 小时为 0-23)，以包含该小时运行的平均作业数。

我的 jobHistory 表中的总行数约为 20k。这是一个示例:

jobid  startTime                 endTime
------------------------------------------------------
377    2017-11-02 15:43:43.0     2017-11-02 15:49:22.0
114    2017-11-02 15:47:05.0     2017-11-02 15:56:19.0
378    2017-11-02 15:49:22.0     2017-11-02 15:53:01.0
379    2017-11-02 15:53:01.0     2017-11-02 16:33:16.0
380    2017-11-02 16:33:16.0     2017-11-02 16:51:26.0
381    2017-11-02 16:51:26.0     2017-11-02 17:03:00.0
382    2017-11-02 17:03:00.0     2017-11-02 17:11:59.0
383    2017-11-02 17:11:59.0     2017-11-02 17:12:44.0
384    2017-11-02 17:12:43.0     2017-11-02 17:13:36.0
385    2017-11-02 17:13:36.0     2017-11-02 17:14:20.0
386    2017-11-02 17:14:20.0     2017-11-02 17:15:08.0
387    2017-11-02 17:15:08.0     2017-11-02 17:16:03.0
388    2017-11-02 17:16:03.0     2017-11-02 18:31:01.0
150    2017-11-02 17:55:00.0     2017-11-02 18:16:32.0
153    2017-11-02 17:55:00.0     2017-11-02 18:15:28.0
135    2017-11-02 17:55:00.0     2017-11-02 17:55:01.0
132    2017-11-02 17:55:00.0     2017-11-02 18:13:00.0
156    2017-11-02 17:55:00.0     2017-11-02 17:55:06.0
147    2017-11-02 17:55:00.0     2017-11-02 17:57:47.0
138    2017-11-02 17:55:00.0     2017-11-02 18:12:33.0
140    2017-11-02 17:55:00.0     2017-11-02 17:55:17.0
389    2017-11-02 18:31:01.0     2017-11-02 18:38:11.0
390    2017-11-02 18:38:11.0     2017-11-02 18:38:35.0
391    2017-11-02 18:38:35.0     2017-11-02 18:38:39.0

我正在寻找每小时的平均作业数。作业全天 24 小时不间断运行。有些时间比其他时间运行的作业更多。总共有大约 120 个职位。有些需要几分钟，有些需要几个小时。为了保持负载平衡，当我安排一个新工作时，我想看看平均哪个小时的工作数量最少。

Answer 1

你的问题不清楚。

我假设您正在根据 starttime 列查找每小时运行的作业总数，下面是查询。

SELECT count(jobid) AS TotalJobs,
       date_trunc('hour', starttime) AS Date_Hour
FROM jobHistory
GROUP BY date_trunc('hour', starttime)
order by Date_Hour;

如果你在endTime的基础上需要它，你可以改变上面的查询，将starttime替换为endTime

结果:

totaljobs   date_hour
-------------------------------
4           02.11.2017 15:00:00
2           02.11.2017 16:00:00
15          02.11.2017 17:00:00
3           02.11.2017 18:00:00

希望对你有帮助

DEMO