Как получить один результат из таблицы при поиске нескольких столбцов из других таблиц
Как дела. Нуб-разработчик здесь.
У меня есть одна таблица, в которой хранятся ТОВАРЫ, а другая - ЦЕНЫ.
Цены: один — это несколько строк для каждого товара. Если кто-то обновляет цену, он добавляет в таблицу еще одну строку с датой, когда цена будет действительна. Поэтому, если мне нужно узнать текущую цену, мне нужно искать max(date), где date<=now().
Проблема в том, что мне нужно создать представление для интеграции API с последней ценой, и я не могу найти способ сделать это эффективно, потому что каждый раз, когда я пытаюсь получить результат, требуется 1–2 минуты (мы говорим о 250к предметов и много исторических цен)
- Я уже пробовал боковое соединение:
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
Это хорошо показывает результаты, когда у меня небольшое количество элементов, а после масштабирования это занимает очень много времени.
- Пробовал очень неприятный способ
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
)
Та же проблема, на удивление, это очень хорошо работает при небольшом количестве предметов, а при большом — нет.
И попробовал функцию для расчета этой цены, но когда я вызываю ее в другом представлении (где я изначально использую текущую цену), это также занимает слишком много времени.
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;
$$;
Спасибо всем, кто хочет помочь.
Ответы 3
Вы должны использовать предложение group by.
Предполагая, что tpreco.dtvalida — это дата обработки заказа, а tpreco.codigo — это код товара, то последним заказом будет следующий запрос.
select codigo, max(dtvalida) dtvalida from tpreco group by codigo;
Затем вы можете соединить эти строки с исходной таблицей, чтобы получить другие значения тех же строк, используя 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;
И, наконец, вы можете присоединиться к этому со своими таблицами.
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;
Я думаю, вы также можете использовать другую агрегатную функцию (https://www.postgresql.org/docs/current/tutorial-agg.html) с порядком, но это было лучшее решение, которое я мог придумать.
В качестве идеи найдите последнюю цену с помощью row_number().
Пытаться
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
ИЛИ
Возможно присоединиться paemp после расчета последней цены. Тогда поиск последней цены упростится и, возможно, станет быстрее с правильным индексом в таблице tpreco.
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
Я не знаю структуры таблицы и ссылок. Это всего лишь идея.
Вероятно, после некоторого тестирования вы сможете получить лучшие результаты, но вот мое мнение. Многие предыдущие попытки показали, что вы всегда должны стараться избегать подзапросов и операций с данными вне предложения SELECT, поэтому вы можете попробовать некоторые общие табличные выражения:
With tPreco_CTE AS (
SELECT
tpreco.empresa,
MAX(tpreco.valor)
FROM tpreco
WHERE tpreco.empresa = $SomeConstant
AND tpreco.codigo = $AnotherConstant
)
SELECT
itens.unidade,
tPrecos_CTE.empresa,
FROM itens
INNER JOIN tPreco_CTE ON tPreco_CTE.empresa = itens.empresa
-- And so on...
Просто включите туда другие столбцы, и все будет в порядке. Боа сорте!
Спасибо за этот ответ, первая итерация сокращает время запроса на целую минуту. Сейчас сижу на запросе 2 секунды, что очень приемлемо для данной операции.