сложный запрос

← →
alexa (2002-05-15 10:56) [0]

Вот такая вот проблемка.
Имеется база на IB в базе таблица. (Карточка материалов)
У таблицы примерно такая структура
1. код материала ()
2. дата операции
3. Вид операции (А - остатки, B - Приход, C - расход)
4. Остаток
5. Уникальное поле

Задача найти остатки на какую-либо дату.

До недавнего времени работал по следуещему алгоритму (В настоящее время делается очень долго)
- была создана временая таблица: код, остаток.

1.Записываю во временную таблицу кода материалов, дата которых меньше необходимой даты.
(большая часть остаков из них будет равна 0, но я об этом еще не знаю - остатки нахожу позже)
2. Перебираю все коды во временной таблице по каждому коду вызываю хранимую процедуру.
У процедуры два входных параметра - код и дата; один выходной - Остаток.
Остаток записываю во временую таблицу.
3. конецю Во временой таблице остатки. Только очень долго :-((

Хранимая процедура работает следующим образом
1. Находим мах () дату по указаному материалу, меншую чем в параметре
2. Находим мах () вид операции по указаному материалу за дату найденую в 1.
3. Находим мах () уникальное поле по указаному материалу за дату найденую в 1 и виду операции найденом в 2
4. Находим остаток по уникальному полю найденому в 3.

Интуиция подсказывает что нужно либо делать запрос посложнее - не знаю как :-(
либо хранимую процедуру - не знаю как перепирать записи в процедуре.

Подскажите пжлст
Спасибо.

← →
fnatali (2002-05-15 12:03) [1]

Например:
1)Переписать во временную таблицу все нужные данные меньше требуемой даты и если код=С, то сумму*(-1) - расход.
2) сделать по этой таблице один запрос:
если нужны остатки на КАЖДУЮ дату меньше указанной
select kod,data,sum(pole_summ) from table group by kod,data
если нужны остатки на дату
select kod,sum(pole_summ) from table group by kod

← →
Mambet (2002-05-15 14:38) [2]

У меня есть похожая выборка.Решаю при помощи вложенных подзапросов:

select * from planjd t1
where Nnn=
(select max(Nnn) from planjd t2 where t2.kodgruz=t1.kodgruz
and t2.data=
(select max(data) from planjd t3 where t3.kodgruz=t1.kodgruz
and t3.data<"01.08.2002")
)
order by kodgruz

Не забудь про индексы.

← →
alexA (2002-05-17 16:46) [3]

Спасибо за внимание
fnatali
Что-то я не понял что такое "если код=С, то сумму*(-1) - расход."

Mambet
Похижие запросы я пытался создавать - результата нужного не получалось.
Пишу по твоему примеру. Тоже не выходит. Опыту маловато.:-((
В твоем запросе kodgruz - это код материала. Так ? Тогда при
таком запросе время возрастает еще более. в таблице материалов с одинаковыми кодами - много. Пока их со всеми соодинишь.
Или я чего не понимаю. Подскажи.

Спасибо

← →
kaif (2002-05-17 18:29) [4]

Что значит A-остатки?
Для каждого материала начальные остатки введены 1 раз?
То есть можно ли исходить из того, что в рамках материала
Текущие остатки на дату = Начальные остатки + Приход - Расход?
A+B-C
Тогда добавь поле AAA в таблицу.
Сделай
UPDATE <table>
SET AAA = 1 WHERE <Vid_operacii> IN ("A","B");
UPDATE <table>
SET AAA = -1 WHERE <Vid_operacii> = "C";

Затем получаешь все одним запросом:
SELECT SUM(amount * AAA)
WHERE <operation_date> <= :DATE
GROUP BY <material>

← →
Николай В (2002-05-17 18:40) [5]

Предлагаю решать задачу именно так, как она была поставлена
изначально-остаток на дату.У меня очень похожая проблема-операции по счету вкладчика.Для проверки сравниваю обороты с остаками.Остатки все равно нужно находить. Так что решение проблемы остается некрасивым, к сожалению :-(

← →
Fay (2002-05-17 19:22) [6]

2Mambet Сговорились все что-ли да T-SQL подсказывать ?!

2alexa Не парься, дело не в опыте. IB не тянет таких запросов.
Делай с хорошими процедурами

← →
Fay (2002-05-17 19:35) [7]

set term ^;
create procedure sp1
returns (p1 integer)
as
begin
for select t1_id from t1 into :p1 do suspend;
end
^
set term ;^

← →
Mambet (2002-05-18 05:24) [8]

2Fay © О чем ты?
И что значит "IB не тянет таких запросов"? Прекрасно тянет.

2alexa © Я прислал свой запрос,не стал под твою таблицу переделывать.В твоем случае будет примерно так:

select Kod,Ostastok from MyTable t1
where Uin=
(select max(Uin) from MyTable t2 where t2.Kod=t1.Kod
and t2.Data=
(select max(Data) from MyTable t3 where t3.Kod=t1.Kod
and t3.data<myData)
)
order by kod

Нужны индексы по Uin,Kod,Data

← →
Fay (2002-05-18 05:44) [9]

2Mambet SORRY! Хреново рассмотрел запрос. Просто похоже на
select
w.sss
qqq = (select max(f.eee) from fff f where f.sss = w.sss)
from www w

← →
AlexA (2002-05-19 18:37) [10]

2Mambet

Я понял с первого раза структуру твоего запроса.
Хотя имелиь вопросы.
У меня он выполняется около 3,5 - 5 минут .
Индексы по полям имеются, но для уникального поля
почему-то не используется. :-( при указании плана
запроса IB сообщает что исползовать указаный индекс нельзя.
Но это не главное. Дело в том, что указаный запрос не выясняет код операпии,
а это важно. Последняя счетается (условно) опреция "С" - расход.
Написать такой запрос у меня не получается.
(Если считать последнию по уникальному полю то придется переделывать работающию программу. :-)(
хотя - вариант)
Может заодно подскажешь что за беда с индексом.

kaif

Вообще планировал, что остатки могут быть введены не 1 раз. А еше при инвертаризации
Может пересмотрю логику. Если не найду другого варианта.

Fay

Не понял запроса в теле процедуры - напрочь
Входных параметров в процедуре нет. Где же дата?

← →
kaif (2002-05-20 02:05) [11]

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

CREATE PROCEDURE get_remaindes
RETURNS(ostatok DECIMAL(15,3))
AS
DECLARE VARIABLE last_the_tovar INTEGER;
BEGIN
FOR SELECT <...>
FROM <...>
GROUP BY the_tovar, the_date
ORDER BY the_tovar, the_date DESC
INTO :ostatok, :the_tovar, :the_date
DO
BEGIN
IF (NOT (last_the_tovar = the_tovar)) THEN
BEGIN
last_the_tovar = the_tovar;
SUSPEND;
END
END
END

Учти, что, чтобы последнее значение захватить, если оно одно нужно еще немного усовершенствовать эту процедуру.
Я такие вещи писал - сработает за долю секунды.(30тыс строк в секунду разовьет как пить дать).
Однопроходная процедура - милая вещь.

← →
kaif (2002-05-20 02:08) [12]

Извиняюсь, с учетом даты:

CREATE PROCEDURE get_remaindes(on_date DATE)
RETURNS(ostatok DECIMAL(15,3))
AS
DECLARE VARIABLE last_the_tovar INTEGER;
BEGIN
FOR SELECT <...>
FROM <...>
WHERE the_date <= :on_date
GROUP BY the_tovar, the_date
ORDER BY the_tovar ASC, the_date DESC /*обратный порядок дат */
DO
BEGIN
IF (NOT (last_the_tovar = the_tovar)) THEN
BEGIN
last_the_tovar = the_tovar;
SUSPEND;
END
END
END

← →
AlexA (2002-05-21 23:04) [13]

kaif
Однопроходная процедура - милая вещь!!! :-)))
Спору нет . Летает дай дорогу. Спасибо!!!
У меня только проблемы с терминологией.

Может подкинешь пример Двупроходную процедуры.
Если такая есть. Задом наперед это обратный порядок?

All
Спасибо.

← →
kaif (2002-05-22 10:35) [14]

Пардон. Я просто имел в виду под однопроходной процедурой ту процедуру, которая обходит таблицу 1 раз (не содержит вложенных запросов и подциклов). Наверно, так называть не стоит.
Да, задом наперед это обратный порядок или убывающий порядок индекса даты (DESCENDING ORDER).

сложный запрос Найти похожие ветки