# Sum all unique values from previous dates

Let's say, for simplicity sake, I have the following table:

``````id      amount     p_id     date
------------------------------------------------
1       5          1        2020-01-01T01:00:00
2       10         1        2020-01-01T01:10:00
3       15         2        2020-01-01T01:20:00
4       10         3        2020-01-01T03:30:00
5       10         4        2020-01-01T03:50:00
6       20         1        2020-01-01T03:40:00
``````

Here's a sample response I want:

``````{
"2020-01-01T01:00:00": 25, -- this is from adding records with ids: 2 and 3
"2020-01-01T03:00:00": 55  -- this is from adding records with ids: 3,4,5 and 6
}
``````

I want to get the total (`sum(amount)`) of all unique `p_id`'s grouped by the hour.

The row chosen per `p_id` is the one with the latest `date`. So for example, the first value in the response above doesn't include `id 1` because the record with `id 2` has the same `p_id` and the `date` on that row is later.

The one tricky thing is I want to include the summation of all the `amount` per `p_id` if their `date` is before the hour presented. So for example, in the second value of the response (with key "2020-01-01T03:00:00"), even though `id 3` has a timestamp in a different hour, it's the latest for that `p_id 2` and therefore gets included in the sum for "2020-01-01T03:00:00". But the row with `id 6` overrides `id 2` with the same `p_id 1`.

In other words: always take the latest `amount` for each `p_id` so far, and compute the sum for every distinct hour found in the table.

• "2020-01-01T03:00:00": 55 #this is from adding records with ids: 3,4,5 and 6 Why row #3 is taken (its datetime is earlier then specified in your responce)? Jul 7, 2020 at 5:08

### Simple case

Getting the sum for a single, given hour is relatively simple:

``````SELECT timestamp '2020-01-01 03:00:00', sum(amount)
FROM  (
SELECT DISTINCT ON (p_id) amount
FROM   tbl
WHERE  date < timestamp '2020-01-01 03:00:00' + interval '1h'
ORDER  BY p_id, date DESC
) sub;
``````

`DISTINCT ON` is typically substantially faster than subquery / CTE with `row_number()`. See:

For many rows per `p_id`, there is potential to optimize some more with smart indexing and a corresponding query. See:

### Special running sum for every hour

You seem to want the sum for every distinct hour in the table.

You can simply distill distinct hours, and apply the simple solution to ever one of them in a `LATERAL` subquery:

``````WITH grid AS (  -- or some other source?
SELECT DISTINCT date_trunc('hour', date) AS hour
FROM   tbl
)
SELECT *
FROM   grid
CROSS  JOIN LATERAL (
SELECT sum(amount)
FROM  (
SELECT DISTINCT ON (p_id) amount
FROM   tbl
WHERE  date < grid.hour + interval '1h'
ORDER  BY p_id, date DESC
) sub1
) sub2
ORDER  BY grid.hour;
``````

Should be fine while there are not many distinct hours. But the query does not scale very well. The latest `amount` for every `p_id` is computed at full (steadily increasing) cost for every additional hour.

### Optimize running sum for many hours

I expect this recursive CTE to scale better (while having considerable overhead initially), as it only needs to join to one row per `p_id` for every next hour:

``````WITH RECURSIVE
values AS (
SELECT DISTINCT ON (1, 2)
date_trunc('hour', date) AS hour, p_id, amount
FROM   tbl
ORDER  BY 2, 1, date DESC  -- !
)
, hours AS (
SELECT hour, row_number() OVER (ORDER BY hour) AS hnr
FROM  (SELECT DISTINCT hour FROM values) sub
)
, cte AS (
SELECT p_id, h.hour, v.amount, 2 AS next_hnr
FROM  (SELECT DISTINCT p_id FROM values) i  -- all IDs
JOIN   hours       h ON hnr = 1             -- first hour
LEFT   JOIN values v USING (p_id, hour)

UNION ALL
SELECT c.p_id, h.hour, COALESCE(v.amount, c.amount), c.next_hnr + 1
FROM   cte         c
JOIN   hours       h ON h.hnr  = c.next_hnr
LEFT   JOIN values v ON v.p_id = c.p_id
AND v.hour = h.hour
)
SELECT hour, sum(amount)
FROM   cte
GROUP  BY 1
ORDER  BY 1;
``````

Plus, there may be more potential to optimize, depending on value distribution, like indicated for the simple case at the top.

db<>fiddle here

Aside: calling your `timestamp` column "date" is rather misleading as that is a different basic data type.

• Thank you so much for your clear answer. Your last query seemed to work. I know this wasn't included in the OP, but what if instead of adding the amount from the latest `p_id` I want to add from the latest `p_id`, `loc_id`(not included in original table) pair. I tried changing `(SELECT DISTINCT p_id FROM values)` to `(SELECT DISTINCT p_id, loc_id FROM values)` but it made no difference to the answer.
– tony
Jul 8, 2020 at 1:35
• @tony: To extend the logic with `loc_id`, you need to adapt the code in several positions. I suggest you ask a new question. You can always link to this one for reference (and drop a comment here to link back). Jul 8, 2020 at 1:43
• will do, thanks!
– tony
Jul 8, 2020 at 1:44
• Basically, this: db<>fiddle here. BTW, did you compare performance of the 2nd and 3rd query? Jul 8, 2020 at 1:57
• I'm not currently in a position to work with large datasets but when I do (hopefully soon), I will test and report my results... Thanks for the new query in fiddle, I appreciate it a lot
– tony
Jul 8, 2020 at 2:40

Look for

``````WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY p_id ORDER BY date DESC) rn
FROM source_table
WHERE date_trunc('hour', date) = @needed_datetime_truncated_to_hour )
SELECT @needed_datetime_truncated_to_hour, SUM(amount)
FROM cte
WHERE rn = 1;
``````