Сложный запрос. Логично ли построение этого запроса ЛИШЬ средствами MySQL?

[mus]

Сложный запрос. Логично ли построение этого запроса ЛИШЬ средствами MySQL?

(РАБОТАЕМ С MySQL)

Задача следующая:

Вывести на экран результат вычисления стоимости товара после поступления новой партии на склад по средствам алгоритма вычисления средневзвешенного.

Заказчик всегда должен знать средневзвешенную стоимость товара.
Средневзвешенная стоимость считается так:
( стоимость_товара * остаток на складе + стоимость_товара_в_новой_партии * кол_во_товара_в_новой_партии) / общее_получившееся_кол-во

Например:
01.01 пришла самая первая партия товара Z по цене $10 в кол-ве 10 штук

01.02
остаток (кол-во пришедшего - кол-во проданного) = 5 шт.
цена_товара_Z_в_новой_партии = 15
кол-во_в_новой_партии = 10

Итого = ( 10 * 5 + 15 * 10) / 15 = 13.3

Средневзвешенная на момент 01.02 = 13.3

01.03
остаток = 10
цена_товара_новой_партии = 25
кол-во = 20

Итого = (10 * 13.3 + 25 * 20) / 30 = 21.1


Так вот, среднешвзвешенное хранить не представляется возможным, так как в случае удаления факта закупки (например - удалили вторую запись из пяти) или удаления факта продажи нам придется пересчитывать это значение.

Так вот, чтобы построить такой запрос логично ли использовать ЛИШЬ ресурсы MySQL? И представляется ли возможным вообще без наличия хранимых процедур решить эту задачу с помощью лишь SQL-запроса?
Как осуществить подобие рекурсии для решения этой задачи?

 

[hermit_refined]

Как осуществить подобие рекурсии для решения этой задачи?

а при чем тут рекурсия?..

И представляется ли возможным вообще без наличия хранимых процедур решить эту задачу с помощью лишь SQL-запроса?

схема бд?..

вообще же - всё как всегда.
by default - обсчитывать на выводе. если это входит в противоречие с производительностью, необходимые значения высчитываются при вставке и пересчитываются при всех изменениях. триггерами или на php - не принципиально.

важно лишь скрыть реализацию за api, чтобы можно было легко переходит от первого варианта ко второму и наоборот.

 

[mus]

а при чем тут рекурсия?..

А как иначе вычислить средневзв. цену? Ведь у нас это значение содержится в самой формуле и подставляется всякий раз новое в течении одного высчета...Если я путаю, исправьте меня.

схема бд?..

Очень формально (использую лишь нужные атрибуты):

PRODUCTION

production_id
title
weight


ORDER (Закупка)

order_id
date
trans_cost (стоимость транс. нужна при расчете цены)
supplier_id (ИД поставщика)

SUP_ORD (Характеристическая связь "многие-ко-многим" между поставщиками, заказами и продукцией)

sup_ord_id
order_id
production_id
quantity
price


SALE (Продажи)

sale_id
date
trans_cost
client_id


SALE_CLIENT (Характеристическая связь "многие-ко-многим"

sale_client_id
sale_id
production_id
quantity
price


По поводу производительности - не паримся, это статистика, там этот запрос редко используется. Вообще можно обработать все средствами php, однако хочется именно на SQL сделать, дабы прояснить возможности базы и получить незаменимый опыт.

 

[hermit_refined]

А как иначе вычислить средневзв. цену? Ведь у нас это значение содержится в самой формуле и подставляется всякий раз новое в течении одного высчета...

да, виноват, невнимательно прочитал.

схема достаточно неудобная для расчетов, к тому же - кол-во товаров на складе у вас хоть где-нибудь хранится?

Так вот, среднешвзвешенное хранить не представляется возможным, так как в случае удаления факта закупки (например - удалили вторую запись из пяти) или удаления факта продажи нам придется пересчитывать это значение.

непонятно. т.е. смотря что считается продажей и закупкой. если мы вчера рассчитывали цену, исходя их того, что закупка произошла, а потом её "удалили", и сегодня мы уже считаем вчерашнюю цену из предположения, что вчера закупки не было... возникает некрасивая путаница.

в любом случае, очевидно, что никаким одним sql-запросом нельзя. можно серией запросов, либо (если для нескольких товаров) - через временные таблицы. но поскольку считать придется "от сотворения мира" - лучше об этом забыть.

т.е. каждый день (или когда там у вас) - надо пересчитывать цену. если разрешаете объявлять бывшее небывшим - можно определить максимальный срок, в течение которого можно произвести отмену продажи/закупки, и храните историю, чтобы в таком случае можно было пересчитать стоимость конкретного товара.