在查询中使用平均值 (PostgreSQL)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15486078/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using average in query (PostgreSQL)
提问by PangolinKing
I'm having trouble with coming up with a query to find the average age of the buses used for each bus company from the following schema:
我在提出查询以从以下模式中查找每个巴士公司使用的巴士的平均年龄时遇到问题：
buscompany
巴士公司
company_id | name
1 NED
2 JIM
bustype
总线类型
type_id | date_made
1 2006-01-26
2 1999-05-05
3 2000-09-01
route
路线
route_id | bustype | bus_company
1 2 1
2 1 1
3 1 1
4 3 2
5 3 2
6 1 2
7 2 2
In this example NED's average bus age is 4246.666 = 4247 days assuming today is 2013-03-18.
在这个例子中，假设今天是 2013-03-18，NED 的平均公交车年龄是 4246.666 = 4247 天。
What would the whole query look like?
整个查询会是什么样子？
回答by pilcrow
I can't test right now, but something like:
我现在无法测试，但类似于：
-- assuming "date_made" is a pg DATE column
--
SELECT buscompany.name, ROUND(AVG(CURRENT_DATE - date_made))
FROM buscompany
LEFT JOIN route
ON route.bus_company = buscompany.company_id
LEFT JOIN bustype
ON route.bustype = bustype.type_id
GROUP BY 1
ought to Do What You Want (tm). Date subtraction in pg gives the difference in days.
应该做你想做的（tm）。pg 中的日期减法给出了天数的差异。
回答by digitaljoel
The following (based on a sample table I made) gave me the average in days.
以下（基于我制作的示例表）给了我平均天数。
select extract(day from avg(current_date-birthday)) from stuff;
avg gives you an interval and not the number of days, so I did the extract.
avg 给你一个间隔而不是天数，所以我做了提取。
If you wantto deal with the interval instead of a number of days then you can do this instead
如果你想处理间隔而不是天数，那么你可以这样做
select avg(age(birthday)) from stuff;
