How to calculate column (integer) changes in hourly buckets? [closed]

I have following table:

``````id serial pk
created_at timestamp with time zone not null
user_id integer not null
points integer not null
``````

For a given time period (min_date and max_date, both timestamp with time zone) it should return list of total (without partitioning by user_id) points changes within this period, partitioned by hour. Point change is a difference of points for a user since previous record for the same user.

For example:

``````created_at  | user_id | points
10:00       | 1       | 100
10:30       | 2       | 50
11:00       | 2       | 75
12:00       | 1       | 130
12:30       | 2       | 80
13:00       | 3       | 20
``````

Without min/max_date it should return:

``````hour | points |
10   | 100 + 50 = 150
11   | 75 - 50 (from previous points for user 2) = 25
12   | 130 - 100 (from previous points for user 1) + (80 - 75 (previous for user 2)) = 30 + 5 = 35
13   | 20
``````

With min/max_date it should return:

``````min_date=11:00
max_date=12:00

hour | points |
11   | 75 - 50 (from previous points for user 2) = 25
12   | 130 - 100 (from previous points for user 1) + (80 - 75 (previous for user 2)) = 30 + 5 = 35
``````

So basically it should return sum of user point changes for each hour bucket since previous (outside of current bucket) row entry.

Note if there are more than one rows for same user in same hour bucket, then the latest should be used in calculation. Also if there will be more than one rows for same user in same bucket hour with same created_at then maximum value of "points" for given user in given bucket should be used in calculation.

Actually "points" for given user will ALWAYS grow (or will be the same as previous) so it is possible to just use max(points) for user in given bucket for calculation.

In examples above I simplified "created_at" field to just hour, but in reality it's a timestamp with time zone and it should be grouped by `extract(year from created_at), extract(day from created_at), ...`.

So far I have:

``````with stats_buckets as (
select
extract(year from stats.created_at) as year,
extract(month from stats.created_at) as month,
extract(day from stats.created_at) as day,
extract(hour from stats.created_at) as hour,
max(stats.points), -- use max points value inside time-user bucket since points always grow (dont have to use latest entry for given user)
stats.user_id
from points as stats
group by year, month, day, stats.user_id
)
select * from stats_buckets
``````

It just groups records by year, month, day, hour, user_id and selects max(points) from each bucket. Now for each bucket I have to calculate difference between selected max(points) and previous points for given user outside of the bucket. And then group results by year, month, day, hour.

I tried to do this with windowing function but without any luck so far.

Also one more important thing: there might be a LOT of rows in points table so calculating the differences for ALL of the buckets might be too slow. However min_date, max_date will ALWAYS be used to prevent calculating ALL buckets. The problem is that it's not possible to filter out point records initially by min/max_date, since for each row, the previous one (from which we calculate the difference) might be outside of min/max_date range.

closed as unclear what you're asking by Evan Carroll, Marco, Colin 't Hart, McNets, joanoloMay 31 '17 at 19:27

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

• `10 | 100 + 50 = 150` how did you get that? – Evan Carroll May 30 '17 at 16:16
• `without partitioning by user_id` why is that? it's seems likely that this is how it should be done. (though I don't understand exactly what you want yet) – Evan Carroll May 30 '17 at 16:18
• 1) because in 10h bucket there were two point entries (10 and 10.30) for two separate users. Because there are no previous entries for those users then point delta = 100 + 50 = 150 2) I mean "at the end it should not be partitioned by user" - the final result should be total points change in given bucket across all users. – user606521 May 31 '17 at 9:11

This query is for all the buckets.

``````with points_enhanced as( -- Add date_id for grouping and ordering, hour for displaying
select to_char(created_at, 'YYYYMMDDHH24') date_id, extract (hour from created_at) as hour, * from public.points
)
, points_with_lag as ( -- Calcuate lag point by user_id
select date_id, user_id, hour, points,
lag(points, 1, 0 ) over w points_before
from points_enhanced pe
window w as (partition by user_id order by date_id)
)
, cte_buckets as ( -- What if we have more than one record by bucket and user_id ? Use only max(points) and min(points_before)
select date_id, hour, user_id, max(points)  - min(points_before) points_bucket
from points_with_lag
group by date_id, hour, user_id
)

select date_id, hour, sum (points_bucket) points
from cte_buckets
group by date_id, hour
order by date_id
``````

This query is for min_date / max_date filter.

``````with points_enhanced as( -- Add date_id for grouping and ordering, hour for displaying
select to_char(created_at, 'YYYYMMDDHH24') date_id, extract (hour from created_at) as hour, * from public.points
where created_at >= '2017-05-30 12:00:00' and created_at < '2017-05-30 14:00:00' -- filter by min_date/max_date
)
, points_with_lag as ( -- Calcuate lag point by user_id
select date_id, user_id, hour, points,
lag(points, 1,
( select coalesce(max(points), 0) from public.points p -- This correlated subquery gets the lag point value, or 0, for that user_id before the min_date limit
where created_at < '2017-05-30 12:00:00'
and p.user_id = pe.user_id
)
) over w points_before
from points_enhanced pe
window w as (partition by user_id order by date_id)
)
, cte_buckets as ( -- What if we have more than one record by bucket and user_id ? Use only max(points) and min(points_before)
select date_id, hour, user_id, max(points)  - min(points_before) points_bucket
from points_with_lag
group by date_id, hour, user_id
)

select date_id, hour, sum (points_bucket) points
from cte_buckets
group by date_id, hour
order by date_id
``````

Regards.

I'd try something like this:

CTE 1: locate all users and max points within each valid period within min/max dates.

CTE 2: For each row in CTE 1, get the max(`points`) for the user prior to that row's hour (can't just get the maximum prior to min-time, because we may have multiple hours we're looking at; from your example, user 2's prior max for hour 11 is 50, and for hour 12 is 75.

CTE 3 and 4: total points (CTE 3, from CTE 1) and prior points (CTE 4, from CTE2 2) by year, month, day, and hour.

Main query: for each hour, subtract total points from prior points.