Início / dba / Perguntas / 270521
Accepted
tony
Asked: 2020-07-07 19:23:47 +0800 CST

Soma todos os valores exclusivos de datas anteriores

  • 772

Digamos que, para simplificar, eu tenha a seguinte tabela:

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

Aqui está um exemplo de resposta que eu quero:

{
 "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
}

Eu quero obter o total ( sum(amount)) de todos os exclusivos p_idagrupados por hora.

A linha escolhida por p_idé aquela com o último date. Por exemplo, o primeiro valor na resposta acima não inclui id 1porque o registro com id 2tem o mesmo p_ide o datedessa linha é posterior.

A única coisa complicada é que eu quero incluir a soma de todos os amountpor p_idse eles dateforem antes da hora apresentada. Por exemplo, no segundo valor da resposta (com a chave "2020-01-01T03:00:00"), embora id 3tenha um carimbo de data/hora em uma hora diferente, é o mais recente para isso p_id 2e, portanto, é incluído na soma de "2020-01-01T03:00:00". Mas a linha com id 6substituições id 2com o mesmo p_id 1.

Em outras palavras: sempre pegue o último amountpara cada p_idaté o momento e calcule a soma para cada hora distinta encontrada na tabela.

postgresql greatest-n-per-group

2 respostas

  1. Olhe para

    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;
    • 1
  2. Best Answer

    Caso simples

    Obter a soma para uma única hora é relativamente simples:

    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 ONnormalmente é substancialmente mais rápido que a subconsulta/CTE com row_number(). Ver:

    Para muitas linhas por p_id, há potencial para otimizar um pouco mais com a indexação inteligente e uma consulta correspondente. Ver:

    Soma corrida especial para cada hora

    Você parece querer a soma para cada hora distinta na tabela .

    Você pode simplesmente destilar horas distintas e aplicar a solução simples a cada uma delas em uma LATERALsubconsulta:

    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;

    Deve ficar bem enquanto não há muitas horas distintas. Mas a consulta não escala muito bem . O último amountpara cada p_idé calculado com o custo total (aumentando continuamente) para cada hora adicional.

    Otimize a soma acumulada por muitas horas

    Espero que esse CTE recursivo escale melhor (com uma sobrecarga considerável inicialmente), pois ele só precisa se unir a uma linha p_ida cada próxima hora:

    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;

    Além disso, pode haver mais potencial para otimizar, dependendo da distribuição de valor, como indicado para o caso simples no topo.

    db<>fique aqui

    Além disso: chamar sua timestampcoluna de "data" é bastante enganoso, pois é um tipo de dados básico diferente.

    • 0

relate perguntas