CODEWITHSUNDEEP
sqlpostgresql
saman.shahmohamadi
saman.shahmohamadi

Reputation: 485

SQL: Select average value of column for last hour and last day

I have a table like below image. What I need is to get average value of Volume column, grouped by User both for 1 hour and 24 hours ago. How can I use avg with two different date range in single query?
enter image description here

Upvotes: 0

Views: 1685

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Use conditional aggregation. Postgres offers very convenient syntax using the FILTER clause:

SELECT user,
       AVG(Volume) FILTER (WHERE created >= NOW() - interval '1 hour' AND created <= NOW()) as avg_1hour,
       AVG(Volume) FILTER (WHERE created >= NOW() - interval '1 day' AND created <= NOW()) as avg_1day
FROM mytable
WHERE created >= NOW() - interval '1 DAY' AND
      created <= NOW()
GROUP BY user;

This will filter out users who have had no activity in the past day. If you want all users -- even those with no recent activity -- remove the WHERE clause.

The more traditional method uses CASE:

SELECT user,
       AVG(CASE WHEN created >= NOW() - interval '1 hour' AND created <= NOW() THEN Volume END) as avg_1hour,
       AVG(CASE WHEN created >= NOW() - interval '1 day' AND created <= NOW() THEN Volume END) as avg_1day
. . .

Upvotes: 2

Ahmad Alkaraki
Ahmad Alkaraki

Reputation: 106

SELECT User, AVG(Volume) , ( IIF(created < DATE_SUB(NOW(), INTERVAL 1 HOUR) , 1 , 0) )IntervalType
WHERE created < DATE_SUB(NOW(), INTERVAL 1 HOUR)
      AND created < DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY User, (IIF(created < DATE_SUB(NOW(), INTERVAL 1 HOUR))

Please Tell me about it's result :)

Upvotes: 0

SMA
SMA

Reputation: 37093

You can do it like:

SELECT user, AVG(Volume)
FROM mytable
WHERE created >= NOW() - interval '1 hour'
AND   created <= NOW()
GROUP BY user

Few things to remember, you are executing the query on same server with same time zone. You need to group by the user to group all the values in volume column and then apply the aggregation function like avg to find average. Similarly if you need both together then you could do the following:

SELECT u1.user, u1.average, u2.average
FROM
    (SELECT user, AVG(Volume) as average
    FROM mytable
    WHERE created >= NOW() - interval '1 hour'
    AND   created <= NOW()
    GROUP BY user) AS u1
INNER JOIN
    (SELECT user, AVG(Volume) as average
    FROM mytable
    WHERE created >= NOW() - interval '1 day'
    AND   created <= NOW()
    GROUP BY user) AS u2
ON u1.user = u2.user

Upvotes: 2

Related Questions