Соединение таблицы с последними значениями другой таблицы

[Amarok]

Соединение таблицы с последними значениями другой таблицы

Ситуация следующая:
Есть две таблицы

movements(движения товаров):
--------------------------------------------------
| period | shop_id | good_id | quantity |
--------------------------------------------------

prices(цены товаров):
---------------------------------------------
| period | shop_id | good_id | price |
---------------------------------------------

Связь таблиц соответственно по полям shop_id и good_id.
Поле period - это дата либо движения, либо установки цены.

Необходимо получить запрос дающий следующий результат:

| period | shop_id | good_id | quantity | price |

где соответственно цена - это цена товара, ближайшая к дате движения.

Проблема в том что у меня не получатся получить цену товара на дату движения, во всяком случае чтобы это было быстро.

Пробовал сделать так:
[sql]
select
m.period,
m.good_id,
m.quantity,
p.price
from
movements as m
left join prices as p on
(p.shops_id=m.shops_id
and
p.goods_id=m.goods_id
AND
p.period=(
select max(pr.period) from prices as pr where (p.shops_id=pr.shops_id)and(p.goods_id=pr.goods_id)and(pr.period<=m.period))
)
[/sql]

Нашел тему Срез последних, но тут немножечко другая ситуация...

Может кто помочь?

 

[alpine]

Amarok
1) Зачем нужны shop_id | good_id в одной таблице и зачем объединять и по shop_id и по good_id эти таблицы одновременно?
2) Чего ты хотел добиться этим подзапросом:

( SELECT max( pr.period ) FROM prices AS pr WHERE (
p.shops_id = pr.shops_id
) AND (
p.goods_id = pr.goods_id
) AND (
pr.period <= m.period
) )

да еще и в условии объединения таблиц?

 

[Amarok]

Автор оригинала: alpine
1) Зачем нужны shop_id | good_id в одной таблице и зачем объединять и по shop_id и по good_id эти таблицы одновременно?
2) Чего ты хотел добиться этим подзапросом:

( SELECT max( pr.period ) FROM prices AS pr WHERE (
p.shops_id = pr.shops_id
) AND (
p.goods_id = pr.goods_id
) AND (
pr.period <= m.period
) )

1. shop_id в обоих таблицах - id мгазина в котором все просиходит, т.е. движение товара и назначение цены.
Соответственно good_id это id товара с которым все это поисходит.
2. с помощью него я получаю максимальное значение даты назначения цены по данному товару, т.е. последнюю дату, соответственно ее значение я потом и выбираю.

 

[chira]

Amarok

индексы есть для таблицы prices?

 

[Amarok]

Автор оригинала: chira
индексы есть для таблицы prices?

да, три штуки по полям shops_id, goods_id и period

Может можно запрос как-нибудь по другому написать?..

 

[alpine]

Amarok
Попробуй вынести вложенный селект из условия объединения в WHERE. Дальше нужно смотреть и анализовать Explaine запроса.

 

[Amarok]

Автор оригинала: alpine
Попробуй вынести вложенный селект из условия объединения в WHERE. Дальше нужно смотреть и анализовать Explaine запроса.

Попробовал вынести так:
[sql]select
m.period,
p.period,
m.shop_id,
m.good_id,
m.quantity,
p.price
from
movements as m
left join prices as p USING(shop_id,good_id)
WHERE
p.period=(select max(pr.period) from prices as pr where (p.shop_id=pr.shop_id)and(p.good_id=pr.good_id)and(pr.period<=m.period))[/sql]

При таком раскладе запрос выполняется еще дольше... А точнее дождаться почти не возможно.. Explain говорит, что все хорошо.. Если так можно сказать..

 

[alpine]

Amarok
Пости сюда что говорит Explaine.

-~{}~ 18.01.06 16:28:

Если можно сделай небольшой дампик в пределех 1-2 метров для этих таблиц и выкини в нет, хочу поиграться с запросом.

 

[Amarok]

Автор оригинала: alpine
Пости сюда что говорит Explaine.
Если можно сделай небольшой дампик в пределех 1-2 метров для этих таблиц и выкини в нет, хочу поиграться с запросом.

Explain:
id | select_type |table | type | key | ref
1|PRIMARY |m |ref |docs_id |const
1|PRIMARY |p |ref |good_id |m.good_id
2|DEPENDENT SUBQUE |pr |ref |good_id |p.good_id

А с дампом все плохо, поскольку этот вопрос был немного упрощен. Да и сама база порядка 22М записей, поэтому выбрать небольшой кусочек в котором эти две таблички пересекаются немножко сложно..

 

[chira]

Amarok
попробуй создать составной индекс (good_id,shop_id,period)
вместо good_id

 

[alpine]

Кстати, а есть возможность наложить дополнительные ограничивающие условия например по дате/периоду?

-~{}~ 18.01.06 22:08:

Может стоит перемещать неактуальные записи в архив, тоесть в другую таблицу?

 

[Amarok]

Автор оригинала: alpine
Кстати, а есть возможность наложить дополнительные ограничивающие условия например по дате/периоду?

-~{}~ 18.01.06 22:08:

Может стоит перемещать неактуальные записи в архив, тоесть в другую таблицу?

Ограничения по дате есть, но только на движения. Причем это ограничение не очень сказываются на скорости. Т.е. период месяц и два формируются почти одинаково.

А с архивом.. Фактически это и есть архив т.е. в эту БД сливаются данные и по ним строятся отчеты.

-~{}~ 19.01.06 10:27:

chira

Автор оригинала: chira
попробуй создать составной индекс (good_id,shop_id,period)
вместо good_id

В какой таблице?
Создал в movements в explain он не появился... И измененений тоже вроде как никаких....

-~{}~ 19.01.06 12:26:

Переписал запрос таким образом:
[sql]
CREATE Temporary table tmp
(
m_p char(23),p_p char(23),s CHAR(17),g CHAR(17),q decimal(19,3),cost decimal(19,2), p decimal(19,2),
INDEX p_p(p_p), INDEX m_p(m_p), INDEX s(s), INDEX g(g)
)
as select
m.period as m_p,
p.period as p_p,
m.shop_id as s,
m.good_id as g,
m.quantity as q,
m.cost as cost,
p.price as p
from
movements as m
left join prices as p USING(shop_id,good_id)
WHERE
p.period<m.period;

CREATE Temporary table tmp2
(
m_p char(23),p_p char(23),s CHAR(17),g CHAR(17),q decimal(19,3),cost decimal(19,2), p decimal(19,2),
INDEX p_p(p_p), INDEX m_p(m_p), INDEX s(s), INDEX g(g)
)
as select * from tmp;

SELECT
g.descr,sum(tmp_1.q),sum(tmp_1.cost),sum(tmp_1.q*tmp_1.p)
FROM
tmp as tmp_1, goods as g
WHERE
tmp_1.p_p=(SELECT MAX(p_p) FROM tmp2 WHERE (tmp2.s=tmp_1.s)and(tmp2.g=tmp_1.g)and(tmp2.p_p<tmp_1.m_p))
and
g.id=tmp_1.g
group by g.descr;
[/sql]

Скорость увеличилась примерно в 20 раз.
Можно еще каким-нибудь образом увеличить скорость последнего запроса? Может его можно по другому написать?

На то что поле period - char(23) внимания обращать не нужно. Это дата+позиция во времени.

 

[alpine]

Amarok
Не понял, зачем делать две одинаковые временные таблицы? А использовать алиас "tmp as tmp2" и объединять таблицу tmp с самой собой никак не получается?
>> Это дата+позиция во времени.
Хотелось бы увидить пример данных которые хранятся в этом поле.

 

[chira]

Amarok

Автор оригинала: chira
попробуй создать составной индекс (good_id,shop_id,period)
вместо good_id

В какой таблице?
Создал в movements в explain он не появился... И измененений тоже вроде как никаких....

для таблицы prices

 

[Amarok]

Автор оригинала: alpine
Amarok
Не понял, зачем делать две одинаковые временные таблицы? А использовать алиас "tmp as tmp2" и объединять таблицу tmp с самой собой никак не получается?
>> Это дата+позиция во времени.
Хотелось бы увидить пример данных которые хранятся в этом поле.

1. если делать как алиас, то мускл ругается что не может открыть таблицу повторно. Видимо это с временными таблицами такая фича. Пришлось ее копировать.
2. Вот пример - 20060118 7PS 1BO6.

 

[alpine]

Amarok
У меня складывается такое впечатление что имеется ошибка проектирования бд, если она конечно вообще проектировалась. Почему я делаю такие выводы: я вижу две не нормализованные таблицы + длинные поля типа чар (которые кастятся в инт и естественно индекс по ним не используется) по которым происходит сравнение.
Все это конечно мое imho.
В последнем запросе "AND (tmp2.p_p < tmp_1.m_p)" мне кажется лишним.

 

[Amarok]

Автор оригинала: alpine
У меня складывается такое впечатление что имеется ошибка проектирования бд, если она конечно вообще проектировалась. Почему я делаю такие выводы: я вижу две не нормализованные таблицы + длинные поля типа чар (которые кастятся в инт и естественно индекс по ним не используется) по которым происходит сравнение.
Все это конечно мое imho.

Спор тут будет не уместен
А что можно сделать чтобы поправить это дело? Совет может какой?

Автор оригинала: alpine
В последнем запросе "AND (tmp2.p_p < tmp_1.m_p)" мне кажется лишним.

Это точно, уже исправил после того как написал.

 

[alpine]

Amarok
Странно что у вас все id - чар, занимают больше места и индексы медленней работают по сравнению с интом. Не есть гуд, хотя это вы и сами знаете ...

 

[Amarok]

alpine
По другому никак, данные беруться из других баз, а там они такие.. Чтобы поддерживалась уникальность приходится типы оставлять такими какие есть....