Início / dba / Perguntas / 257272
Accepted
Andrei
Asked: 2020-01-14 06:45:05 +0800 CST

Modificar tabela comprimindo linhas por hora truncada

  • 772

Eu tenho um livro- razão de tabela que representa o livro-razão.

            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

Esta tabela é muito grande, então quero reduzir a contagem de linhas.

Estou tentando determinar a melhor maneira de realizar a tarefa: como posso modificar a tabela de origem (excluindo e atualizando linhas) para obter as seguintes linhas :

            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

Esta tabela é resultado da consulta:

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

1 respostas

  1. Best Answer

    Se você não tiver nenhuma chave estrangeira referenciando essa tabela (ou seja, chaves estrangeiras "incoming"), você pode fazer algo assim:

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

    Se você quiser fazer isso por apenas um ano (ou qualquer outro período de tempo arbitrário), precisará substituir a truncateparte por uma deleteinstrução apropriada e alterar a consulta que cria a tabela temporária apenas para o ano (ou período de tempo) desejado.

    por exemplo, para esmagar as linhas de ontem:

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

relate perguntas