PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to bring one result from a table searching for multiples columns from another tables

Sup. Noob dev here.

I have one table that stores ITEMS and another with PRICES.

Prices one is multiple rows per item, if someone updates the price it appends the table with another row with the date that the price will be valid. So if I need to know the current price I have to look for the max(date) where date<=now().

The problem is, I need to build a view to integrate an API with the last price, and can’t figure out a way to do it efficiently because every way I try the query takes 1~2 min to result (we are talking about 250k items and a lot of history prices)

  • I’ve already tried a lateral join:
SELECT i.item,
    i.unidade,
    l.empresa,
    l.valor,
    l.dtvalida
 FROM itens i
JOIN LATERAL ( 
     SELECT t.empresa,
            t.tabela,
            t.tipo,
            t.codigo,
            t.unidade,
            t.dtvalida,
            t.valor,
            t.valormin
      FROM tpreco t
 LEFT JOIN paemp pa ON t.empresa = pa.empresa
     WHERE t.codigo = i.item AND t.tabela::text = pa.fattabavis::text
       AND t.empresa = 1265 AND t.unidade::text = i.unidade::text
       AND t.dtvalida <=now()
          ORDER BY t.dtvalida DESC
     LIMIT 1) l ON true

This does a good job showing results when I have low number of items, once I scale it takes very long.

  • Tried a very junky way
select i.item, 
       i.descricao, 
       i.unidade,  
       ei.empresa,  
       t1.valor,  
       t1.dtvalida  
  From itens i
left join empitens ei on ei.item = i.item and (ei.precocodificado<>'S')
left join paemp p on p.empresa = ei.empresa
join tpreco t1 on t1.codigo = i.item 
              and t1.empresa = ei.empresa 
              and t1.tabela = p.fattabavis
              and t1.unidade = i.unidade
              and t1.dtvalida = (
                     select max(t2.dtvalida) 
                       from tpreco t2 
                      where t2.codigo = t1.codigo 
                        and t2.empresa = t1.empresa
                        and t2.tabela = t1.tabela
                            and t2.unidade = t1.unidade
                                            )

Same problem, surprisingly this works very well on low items count, but high not.

And tried a function to calculate this price, but when I call it in the other view (where I originally use the current price) it also took too long

create function ult_preco_f(_emp bigint, _tab varchar, _item bigint, _un varchar)
returns float
language plpgsql
as
$$
declare
   last_valor float;
begin
   select valor
   into max_valor
   from tpreco
   where empresa = _emp
     and tabela = _tab
     and codigo = _item
     and unidade = _un
     and dtvalida<=now()
order by dtvalida desc
limit 1;

   return last_valor;
end;
$$;

Ty to anyone willing to help.

Tags:

2

Answers


  1. 0

    You should use group by clause.

    Assuming that tpreco.dtvalida is a date of processing order and tpreco.codigo is the item code, so the last order would be the following query

    select codigo, max(dtvalida) dtvalida from tpreco group by codigo;

    Then you can join these rows with original table to get the other values of the same rows using INNER JOIN.

    SELECT t.empresa
     , t.tabela
     , t.tipo
     , t.codigo
     , t.dtvalida
     , t.valor
     , t.valormin
  FROM tpreco t
INNER JOIN (select codigo, max(dtvalida) dtvalida from tpreco group by codigo) m 
   ON t.codigo = m.codigo AND t.dtvalida = m.dtvalida;

    And finally you can join this with your table itens.

    SELECT i.item
     , i.unidade
     , l.empresa
     , l.valor
     , l.dtvalida
  FROM itens i
INNER JOIN (
SELECT t.empresa
     , t.tabela
     , t.tipo
     , t.codigo
     , t.dtvalida
     , t.valor
     , t.valormin
  FROM tpreco t
INNER JOIN (select codigo, max(dtvalida) dtvalida from tpreco group by codigo) m 
   ON t.codigo = m.codigo AND t.dtvalida = m.dtvalida
LEFT JOIN paemp pa ON t.empresa = pa.empresa
 WHERE t.tabela::text = pa.fattabavis::text
   AND t.empresa = 1265 AND t.unidade::text = i.unidade::text
   AND t.dtvalida <= now()
) l ON i.item = l.codigo;

    I think you can also use another aggregate function (https://www.postgresql.org/docs/current/tutorial-agg.html) with order by, but that was the best solution I could think.

    Login or Signup to reply.
  2. 0

    As idea, find latest price with row_number().

    Try

    SELECT i.item,
    i.unidade,
    l.empresa,
    l.valor,
    l.dtvalida
 FROM itens i
 inner JOIN ( 
     SELECT t.empresa,
            t.tabela,
            t.tipo,
            t.codigo,
            t.unidade,
            t.dtvalida,
            t.valor,
            t.valormin,
          row_numbser()over(partition by t.codigo,t.unidade
                             order by t.dtvalida DESC)rn
     FROM tpreco t
     LEFT JOIN paemp pa ON t.empresa = pa.empresa
     WHERE  t.tabela::text = pa.fattabavis::text
       AND t.empresa = 1265 
       AND t.dtvalida <=now()
     ) l ON l.codigo = i.item AND t.unidade::text = i.unidade::text
      and l.rn=1

    OR
    Possible join paemp after calculation of last price. Then search for last price simplified and may be, faster with proper index on tpreco table.

    SELECT i.item,
    i.unidade,
    l.empresa,
    l.valor,
    l.dtvalida
 FROM itens i
 inner JOIN ( 
     SELECT t.empresa,
            t.tabela,
            t.tipo,
            t.codigo,
            t.unidade,
            t.dtvalida,
            t.valor,
            t.valormin,
          row_numbser()over(partition by t.codigo,t.unidade
                             order by t.dtvalida DESC)rn
     FROM tpreco t
       AND t.empresa = 1265 
       AND t.dtvalida <=now()
     ) l ON l.codigo = i.item AND t.unidade::text = i.unidade::text
      and l.rn=1
   LEFT JOIN paemp pa ON l.empresa = pa.empresa
     and  l.tabela::text = pa.fattabavis::text

    I don’t know table structure and links. Thats an idea only.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search