How correctly aggregate date in PostgreSQL database?

Question

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

In other words, I need in result the hourly average (AVG) of trains speed in the city.

Can you explain how did you get 852.125?

kgzdev
– kgzdev

2018-10-25 13:51:45 +00:00
Commented Oct 25, 2018 at 13:51 — kgzdev
– kgzdev, Commented Oct 25, 2018 at 13:51
... GROUP BY date_key, city

Laurenz Albe
– Laurenz Albe

2018-10-25 13:52:34 +00:00
Commented Oct 25, 2018 at 13:52 — Laurenz Albe
– Laurenz Albe, Commented Oct 25, 2018 at 13:52
What did you try? This is a very basic SQL aggregate query.

Parfait
– Parfait

2018-10-25 14:15:57 +00:00
Commented Oct 25, 2018 at 14:15 — Parfait
– Parfait, Commented Oct 25, 2018 at 14:15

Eray Balkanli · Accepted Answer · 2018-10-25 15:35:55Z

1

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

edited Oct 25, 2018 at 15:35

answered Oct 25, 2018 at 13:50

Eray Balkanli

8,05013 gold badges60 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hambone Over a year ago

I don't think you want those single quotes around AVG_SPEED

Mahesh H Viraktamath · Accepted Answer · 2018-10-26 09:57:30Z

1

I know the dataset mentioned in the question is hourly values, if you want to compute the average across different timestamps (like "2018-10-01 02:45:08") then you can average speeds for each hour like this -

select DATE_TRUNC('day', a.DATE_KEY)+cast(DATE_PART('hour',a.DATE_KEY) as Integer)/1*INTERVAL '1 hour' as hour_key, city, avg(speed) as avg_speed 
    FROM table1 a group by 1, 2;

edited Oct 26, 2018 at 9:57

answered Oct 26, 2018 at 5:57

Mahesh H Viraktamath

8943 gold badges17 silver badges42 bronze badges

Collectives™ on Stack Overflow

How correctly aggregate date in PostgreSQL database?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related