如何在PostgreSQL数据库中正确汇总日期? [英] How correctly aggregate date in PostgreSQL database?
本文介绍了如何在PostgreSQL数据库中正确汇总日期?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在 PostgreSQL 数据库中有一个表。
I have table in PostgreSQL database.
下表显示了城市中每个地下车站的火车每小时速度英格兰:
The table below shows you the hourly speed of trains in each underground station of the cities of England:
DATE_KEY | STATION | CITY | SPEED
-------------------------------------------------------
2018-10-01 00:00:00 | Arsenal | London | 1078.125
2018-10-01 01:00:00 | Arsenal | London | 877.222
2018-10-01 02:00:00 | Arsenal | London | 1127.752
2018-10-01 00:00:00 | Beckton | London | 2866.375
2018-10-01 01:00:00 | Beckton | London | 1524.375
2018-10-01 02:00:00 | Beckton | London | 1618.533
2018-10-01 00:00:00 | Chesham | Liverpool | 1567.588
2018-10-01 01:00:00 | Chesham | Liverpool | 792.333
2018-10-01 02:00:00 | Chesham | Liverpool | 1138.857
2018-10-01 00:00:00 | Farringdon | Liverpool | 1543.625
2018-10-01 01:00:00 | Farringdon | Liverpool | 538.666
2018-10-01 02:00:00 | Farringdon | Liverpool | 1587.583
我正在尝试获取这样的汇总数据:
I'm trying to get aggregated data like this:
DATE_KEY | CITY | AVG_SPEED
----------------------------------------------------
2018-10-01 00:00:00 | London | 852.125
2018-10-01 01:00:00 | London | 750.222
2018-10-01 02:00:00 | London | 625.752
2018-10-01 00:00:00 | Liverpool | 804.588
2018-10-01 01:00:00 | Liverpool | 792.333
2018-10-01 02:00:00 | Liverpool | 952.857
换句话说,我需要计算城市中火车平均时速(AVG)
In other words, I need in result the hourly average (AVG) of trains speed in the city.
推荐答案
我认为您所需要的只是将AVG函数与group by子句一起使用:
I think all you need is to use AVG function with group by clause like:
SELECT DATE_KEY, CITY, AVG(SPEED) as AVG_SPEED
FROM table
GROUP BY DATE_KEY, CITY
这篇关于如何在PostgreSQL数据库中正确汇总日期?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文