通過按截斷小時壓縮行來修改表

我有一個代表分類帳簿的表格分類帳。

           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

這張表很大，所以我想減少行數。

我正在嘗試確定完成任務的最佳方法：如何修改原點（通過刪除和更新行）表以獲取以下行：

           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

該表是查詢的結果：

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;

如果您沒有任何引用該表的外鍵（即“傳入”外鍵），您可以執行以下操作：

-- 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;

---

如果您只想在一年（或任何其他任意時間段）內執行此操作，則需要truncate用適當的delete語句替換該部分，並將創建臨時表的查詢更改為僅所需的年份（或時間段）。

例如擠壓昨天的行：

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;

引用自：https://dba.stackexchange.com/questions/257272