-2

I have a table ledger which represents the ledger book.

            date            | user_id | usd_value | eur_value 
----------------------------+---------+-----------+-----------
 2020-01-13 19:00:10.877+03 |       1 |        10 |         0
 2020-01-13 19:10:15.4+03   |       1 |        30 |         0
 2020-01-13 19:44:40.187+03 |       1 |         0 |        40
 2020-01-13 19:45:06.935+03 |       2 |        15 |         0
 2020-01-13 19:46:22.38+03  |       1 |        40 |         0
 2020-01-13 19:50:43.176+03 |       2 |         0 |        15
 2020-01-13 20:08:58.47+03  |       1 |        55 |         0

This table is very big, so I want to reduce count of rows.

I am trying to determine the best way to accomplish task: how can I modify origin (by deleting and updating rows) table to get following rows:

            date            | user_id | usd_value | eur_value 
----------------------------+---------+-----------+-----------
 2020-01-13 19:00:10.877+03 |       1 |        80 |        40
 2020-01-13 19:45:06.935+03 |       2 |        15 |        15
 2020-01-13 20:08:58.47+03  |       1 |        55 |         0

This table is result of query:

SELECT min(date) as date, user_id, sum(usd_value) as usd_value, sum(eur_value) as eur_value
from ledger
GROUP BY date_trunc('hour', date), user_id
ORDER BY date;
  • General SQL queries are better asked on Stack Overflow; DBA.SE is more specialized, intended for server configuration, optimization, business intelligence, and advanced querying. Have you searched on Stack Overflow for UPDATE and GROUP BY? – Jon of All Trades Jan 15 at 21:39
  • How big is "very big"? – Randolph West Jan 17 at 23:17
2

If you don't have any foreign keys referencing that table (i.e. "incoming" foreign keys), you could do something like this:

-- save the squashed data into a temporary table
create temp table new_data as 
SELECT min(date) as date, user_id, sum(usd_value) as usd_value, sum(eur_value) as eur_value
from ledger
GROUP BY date_trunc('hour', date), user_id;

-- get rid of all the old rows
truncate table ledger;

-- then insert the saved data back into the table
insert into ledger (date, user_id, usd_value, eur_value)
select date, user_id, usd_value, eur_value
from new_data;

If you want to do that for just one year (or any other arbitrary time period), you need to replace the truncate part with an appropriate delete statement and change the query that creates the temp table to only the desired year (or time period).

e.g. to squash yesterday's rows:

create temp table new_data as 
SELECT min(date) as date, user_id, sum(usd_value) as usd_value, sum(eur_value) as eur_value
from ledger
-- only for yesterday
where date >= current_date - 1
  and date < current_date
GROUP BY date_trunc('hour', date), user_id;

delete from ledger
where date >= current_date - 1
  and date < current_date

insert into ledger (date, user_id, usd_value, eur_value)
select date, user_id, usd_value, eur_value
from new_data;
  • Thank's for answer! user_id is foreign key. – Andrei Jan 13 at 15:47
  • 1
    @Andrei: I assume it's referencing another table. Not being referenced from another table. – a_horse_with_no_name Jan 13 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.