CODEWITHSUNDEEP
sqlpostgresql
Crack_David
Crack_David

Reputation: 2843

Get the sum of values at the end of a day/month/week

I store the balance of different user wallets hourly on a database:

Table: snapshots

id      wall_id   balance          created_at
--------------------------------------------------
...

1300    30        1461.18721408    2019-07-05 18:00:05
1301    30        2922.37442816    2019-07-05 19:00:05
1302    30        228.31050220     2019-07-05 20:00:05
1303    30        2283.10502200    2019-07-05 21:00:05
1304    30        285.38812775     2019-07-05 22:00:05
1305    30        57.07762555      2019-07-05 23:00:05 <--
1306    25        2511.41552420    2019-07-05 18:00:05
1307    25        251.14155242     2019-07-05 19:00:05
1308    25        21735.61642968   2019-07-05 20:00:05
1309    25        22523.28766227   2019-07-05 21:00:05
1310    25        79.90867577      2019-07-05 22:00:05
1311    25        285.38812775     2019-07-05 23:00:05 <--
1312    100       2511.41552420    2019-07-05 18:00:05
1313    100       251.14155242     2019-07-05 19:00:05
1314    100       21735.61642968   2019-07-05 20:00:05
1315    100       22523.28766227   2019-07-05 21:00:05
1316    100       79.90867577      2019-07-05 22:00:05
1317    100       285.38812775     2019-07-05 23:00:05
1318    30        1461.18721408    2019-07-06 18:00:05
1319    30        2922.37442816    2019-07-06 19:00:05
1320    30        228.31050220     2019-07-06 20:00:05
1321    30        2283.10502200    2019-07-06 21:00:05
1322    30        285.38812775     2019-07-06 22:00:05
1323    30        79.90867577      2019-07-06 23:00:05 <--
1324    25        2511.41552420    2019-07-06 18:00:05
1325    25        251.14155242     2019-07-06 19:00:05
1326    25        21735.61642968   2019-07-06 20:00:05
1327    25        22523.28766227   2019-07-06 21:00:05
1328    25        79.90867577      2019-07-06 22:00:05
1329    25        21735.61642968   2019-07-06 23:00:05 <--
1330    100       2511.41552420    2019-07-06 18:00:05
1331    100       251.14155242     2019-07-06 19:00:05
1332    100       21735.61642968   2019-07-06 20:00:05
1333    100       22523.28766227   2019-07-06 21:00:05
1334    100       79.90867577      2019-07-06 22:00:05
1335    100       285.38812775     2019-07-06 23:00:05

...

Now I want to get the SUM of the last snapshots made on every day where wall.id = 30 && 25.

To understand the algorithm I marked the values that should be added.

F.e.:

Example result for wall_id 30 & 25 on EOD

day                           sumEOD 
--------------------------------------------------
2019-07-05 00:00:00           ‭342.4657533‬
2019-07-06 00:00:00           21815.52510545

Also it should be possible to modify it and get the SUM of the last snapshots made on every week where wall.id = 30 && 25.

What is the easiest way to achive this? I'm running postgresql.

Upvotes: 0

Views: 157

Answers (2)

forpas
forpas

Reputation: 164089

First find the last snapshots made on every day with group by wall_id, date(created_at) and then join to the table and get the sum by day: 

select 
  date(s.created_at) "day",
  sum(s.balance) sumEOD
from snapshots s inner join (
  select wall_id, max(created_at) maxdate
  from snapshots
  where wall_id in ('25', '30')
  group by wall_id, date(created_at)
) g on g.wall_id = s.wall_id and g.maxdate = s.created_at  
group by "day"
order by "day"

See the demo.
Results:

| day                      | sumeod         |
| ------------------------ | -------------- |
| 2019-07-05T00:00:00.000Z | 342.4657533    |
| 2019-07-06T00:00:00.000Z | 21815.52510545 |

If you want to get the sums per week you can change:

group by wall_id, date(created_at)

to:

group by wall_id, date_trunc('week', created_at)

Upvotes: 1

S-Man
S-Man

Reputation: 23676

demo:db<>fiddle

SELECT 
    created_date,
    SUM(balance) AS sum_balance
FROM (
    SELECT DISTINCT ON (1, 2)
        wall_id,
        created_at::date AS created_date,
        balance
    FROM
        snapshots
    WHERE wall_id IN (25, 30)
    ORDER BY 1, 2, created_at DESC
) s
GROUP BY created_date

DISTINCT ON gives you the first record of an ordered group. In this case the groups are (wall_id, created_at::date). created_at::date gives the part of the timestamp. The order of course is by the group and within it, the records are ordered by their timestamp DESC, which orders the most recent record per day to the top of the group. This one is taken by DISTINCT ON.

Afterwards you can simply group your result.

Upvotes: 1

Related Questions