Início / dba / Perguntas / 190815
Accepted
Alin
Asked: 2017-11-14 13:27:52 +0800 CST

Obter linhas com a data mais recente para cada item diferente

  • 772

Digamos que esta seja a data de amostra proveniente de uma junção de 2 tabelas. O banco de dados é Postgres 9.6

id  product_id  invoice_id  amount       date
1    PROD1       INV01       2          01-01-2018
2    PROD2       INV02       3          01-01-2018
3    PROD1       INV01       2          05-01-2018
4    PROD1       INV03       1          05-01-2018
5    PROD2       INV02       3          08-01-2018
6    PROD2       INV04       4          08-01-2018

Quero saber se é possível de forma otimizada:

  1. Obtenha todos os PRODx com seus respectivos INVx que possuem a data mais recente, mas por product_id. Observe que os registros não utilizados de um dia podem ser relatados para um novo. Isso significa:
id  product_id  invoice_id  amount       date
3    PROD1       INV01       2          05-01-2018
4    PROD1       INV03       1          05-01-2018
5    PROD2       INV02       3          08-01-2018
6    PROD2       INV04       4          08-01-2018
  1. Obtenha valores somados diários para cada PRODx, mas preencha as lacunas com os anteriores se o dia não existir.

Isso significa:

 product_id    amount       date
   PROD1         2          01-01-2018
   PROD2         3          01-01-2018
   PROD1         2          02-01-2018
   PROD2         3          02-01-2018
   PROD1         2          03-01-2018
   PROD2         3          03-01-2018
   PROD1         2          04-01-2018
   PROD2         3          04-01-2018
   PROD1         3          05-01-2018
   PROD2         3          05-01-2018
   PROD1         3          06-01-2018
   PROD2         3          06-01-2018
   PROD1         3          07-01-2018
   PROD2         3          07-01-2018
   PROD1         3          08-01-2018
   PROD2         7          08-01-2018

Alguns pensamentos:

  1. Para a primeira pergunta eu poderia obter o max(date)para cada PRODx e escolher para cada PRODx as linhas que possuem o date=with max(date)mas eu queria saber se existe uma maneira mais rápida de obter isso dado um grande número de registros no banco de dados

  2. Para a segunda pergunta, eu poderia gerar uma série de datas para o intervalo necessário e depois usar WITH rows Ase fazer o agrupamento de consultas por product_ide sumpor quantidade e depois selecionar para cada data os valores anteriores rowscom a limit 1mas isso também não soa tão otimizado.

Aguardo qualquer entrada. Obrigada.

Edição posterior: Tentando experimentar DISTINCT ON ().

  • Se tiver distinct on(product_id, invoice_id), não recebo apenas os mais recentes para a data mais recente. Se houver fatura_ids no passado, além da data mais recente, eles serão devolvidos
  • Se eu tiver distinct on (product_id)então ele retorna da data mais recente, mas como normal, apenas as últimas linhas mesmo que no último dia eu tenha duas posições para PROD1.

Basicamente, preciso de algo como 'Preciso para a data mais recente, todos os product_ids e seus invoice_ids, tendo em mente que um product_id pode ter vários invoice_ids'

Edição posterior 2:

Executar uma consulta como para a primeira pergunta parece ser razoavelmente rápido:

select product_id, invoice_id, amount
from mytable inner join myOtherTable on...
             inner join (select max(date) as last_date, product_id 
                         from mytable 
                         group by product_id) sub on mytable.date = 
                         sub.last_date
postgresql greatest-n-per-group

3 respostas

  1. Best Answer

    Esfolando o Q#1 de forma independente e um pouco diferente do @ypercube

    with cte as (select row_number() over (partition by product_id,
                                       invoice_id 
                                 order by dt desc) as rn,
                    product_id,
                    invoice_id,
                    amount,dt
               from product ) 
select product_id, invoice_id,amount,dt
  from cte
 where rn=1
 order by product_id,invoice_id;

 product_id | invoice_id | amount |     dt     
------------+------------+--------+------------
 PROD1      | INV01      |      2 | 2018-01-05
 PROD1      | INV03      |      1 | 2018-01-05
 PROD2      | INV02      |      3 | 2018-01-08
 PROD2      | INV04      |      4 | 2018-01-08
(4 rows)

    Para Q#2, você está no caminho certo, mas o SQL terá uma junção cruzada (suspiro!)

    Eu acho que uma função com um loop/cursor seria mais otimizada (vou tentar isso no meu próximo bloco de tempo livre)

    --the cte will give us the real values
with cte as (select product_id, 
                    sum(amount) as amount, 
                    dt
               from product
              group by product_id,dt)
select p.product_id,  
       (select cte.amount --choose the amount
          from cte
         where cte.product_id = p.product_id
           and cte.dt <= d.gdt -- for same day or earlier
         order by cte.dt desc
         limit 1) as finamt,
       d.gdt
from (select generate_series( (select min(dt)
                                 from product), --where clause if some products 
                                                --don't have an amount
                              (select max(dt)
                                 from product),
                              '1 day' 
                            )::date as gdt)  d
cross join --assuming each listed product has an amount on the min date
     (select distinct product_id
        from product) p
left join --since we need to fill the gaps
     cte on ( d.gdt = cte.dt 
             and p.product_id = cte.product_id)
order by d.gdt, p.product_id
;
    • 12

  2. Entendo que você deseja todas as linhas com a data mais recente para cada produto (vinculações incluídas, ou seja, todas as linhas com a última data). Isso pode ser feito com a rank()função:

    select id, product_id, invoice_id, amount, date
from
  ( select id, product_id, invoice_id, amount, date,
           rank() over (partition by product_id
                        order by date desc) as rnk
    from 
        -- your joins
  ) as t 
where rnk = 1 ;
    • 7

  3. Eu concordo com sua forma de edição posterior, deve ser:

    select product_id, invoice_id, amount 
    from mytable inner join 
    (select max(date) as last_date, product_id, invoice_id 
        from mytable 
        group by product_id) sub 
    on mytable.date = sub.last_date 
    and mytable.product_id = sub.product_id 
    and mytable.invoice_id = sub.invoice_id;

    A "chave" deve ser o date, product_ide invoice_id.

    • 1

relate perguntas