sql - 所有数据集的每月平均成本。数据库

Question

我有一个如下所示的数据库表，我想找出表中所有记录的每月平均费用。

CREATE TABLE repairs( 
repair_id INT NOT NULL PRIMARY KEY, 
cost NUMERIC(10,2) , 
start_date DATE NOT NULL ,   
);

我试过

SELECT AVG(cost) AS avg 
FROM ( SELECT count(*) AS count, MONTH(start_date) as mnth
       FROM repairs
       GROUP BY mnth) AS a

但是有一些错误，有解决办法吗？

Answer 1

SELECT count(*) AS count, 
DATE_TRUNC('month',start_date) as mnth, 
AVG(cost) AS avg 
FROM repairs
GROUP BY DATE_TRUNC('month', start_date);

这将按月分组。您可以将时间戳/日期截断到您喜欢的任何级别:所以日/周/季度等