Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / IBM DB2, WebSphere, IMS, U2, etc Новый топик    Ответить
 Запросик 2....  [new]
TORT
Member

Откуда:
Сообщений: 1095
Мыслители! Есть большая необходимость написать следующее.
1. Есть таблица с товарами product(product_id).
2. Есть таблица со сводным движением товаров, предположим
move (product_id, date, product_move).
3. Есть таблица с текущими остатками товаров stock(product_id, stock_count).
Задача: нужно посчитать количество дней с положительным остатком, например, за последний месяц... Как это лучше реализовать, или может кто-нибудь с подобным сталкивался?... Нужен один select.
18 май 05, 11:58    [1550829]     Ответить | Цитировать Сообщить модератору
 Re: Запросик 2....  [new]
ggv
Member

Откуда:
Сообщений: 1810
я бы предпочёл (в добавок к написаному) увидеть и SQL statements создания табличек с primary/foreign keys definition - как-то привычнее, что ли....
18 май 05, 12:07    [1550873]     Ответить | Цитировать Сообщить модератору
 Re: Запросик 2....  [new]
TORT
Member

Откуда:
Сообщений: 1095
create table product
(product_id bigint not null generated always ......,
.......
PRIMARY KEY (product_id)
) ................

create table move
(product_id bigint not null,
date date not null,
move double not null
PRIMARY KEY (product_id, date),
FOREIGN KEY (product_id) REFERENCES PRODUCT (PRODUCT_ID)
) ................

create table stock
(product_id bigint not null,
stock_count double not null
PRIMARY KEY (product_id),
FOREIGN KEY (product_id) REFERENCES PRODUCT (PRODUCT_ID)
) ................

что-то вроде этого....
18 май 05, 12:15    [1550905]     Ответить | Цитировать Сообщить модератору
 Re: Запросик 2....  [new]
ggv
Member

Откуда:
Сообщений: 1810
а теперь, если можно, логику. Бизнесс логику. Ну то есть как товары двигаються.
Ну то есть, как связаны между собой такие действия, как внесение записи в table move and table stok.
Я так понимаю, что при внесении записи в move, надо делать изменение записи в stok.

sorry for disturbing
18 май 05, 12:48    [1551069]     Ответить | Цитировать Сообщить модератору
 Re: Запросик 2....  [new]
TORT
Member

Откуда:
Сообщений: 1095
Таблица move показывает общее операционное движение товара за день, ее на самом деле нет. Есть несколько таблиц, в которых хранятся все приходы и расходы товаров. При изменении этих таблиц изменяется таблица с остатками.
То есть физически есть таблица, которая содержит текущие(последние) значения остатков товаров и есть таблицы, в которых содержится все движение товаров. Идеи, которые приходят в голову... Остатки на даты насчитывать либо с первоначальной даты (остаток = все приходы - все расходы по товару до определенной даты), либо идти от текущей даты (остаток = все расходы - все приходы).
18 май 05, 12:57    [1551119]     Ответить | Цитировать Сообщить модератору
 Re: Запросик 2....  [new]
gardenman
Member

Откуда: С-Петербург
Сообщений: 2347
-- получаем остатки на каждый день
-- только из оборотов
select
	product_id,
	date,
	cast(move as decimal(17,2)) move,
	cast(sum(move) over(partition by product_id order by product_id,date) as decimal(17,2)) saldo
from 
	move
order by
	product_id,
	date
@

-- там где остаток < 0 там ставим 1
with  tmp(product_id,date,move,saldo) as (
	select
		product_id,
		date,
		cast(move as decimal(17,2)) move,
		case 
			when cast(sum(move) over(partition by product_id order by product_id,date) as decimal(17,2))<0 then 1
			else 0
		end
	from 
		move	
) select 
		product_id,count(*) from tmp 
	where date between ... and ...
	group by product_id
@

типа того...
18 май 05, 13:36    [1551316]     Ответить | Цитировать Сообщить модератору
 Re: Запросик 2....  [new]
TORT
Member

Откуда:
Сообщений: 1095
Чего такое:
sum(move) over(partition by product_id order by product_id,date) as decimal(17,2))
????
18 май 05, 13:42    [1551346]     Ответить | Цитировать Сообщить модератору
 Re: Запросик 2....  [new]
TORT
Member

Откуда:
Сообщений: 1095
И еще одно маленькое уточнение, которое возможно все и портит. В таблице move если движения по товару не было - нет записи....
18 май 05, 13:57    [1551433]     Ответить | Цитировать Сообщить модератору
 Re: Запросик 2....  [new]
ggv
Member

Откуда:
Сообщений: 1810
это OLAP functions
18 май 05, 14:10    [1551545]     Ответить | Цитировать Сообщить модератору
 Re: Запросик 2....  [new]
gardenman
Member

Откуда: С-Петербург
Сообщений: 2347
TORT
И еще одно маленькое уточнение, которое возможно все и портит. В таблице move если движения по товару не было - нет записи....

ну и что? ... можешь разницу в днях между записями посчитать - типа сколько дней продержался остаток без движения... Захочешь - выкрутишься
18 май 05, 14:26    [1551693]     Ответить | Цитировать Сообщить модератору
 Re: Запросик 2....  [new]
TORT
Member

Откуда:
Сообщений: 1095
млин... Я уже написал запрос... Без OLAP-функций....Он даже правильно считает.:).. Только не устраивает скорость запроса и стоимость (~2.5 млн. в моем варианте). Нужно ускорить...Поэтому ищу технологии, может кто сталкивался с подобным.....
18 май 05, 14:30    [1551732]     Ответить | Цитировать Сообщить модератору
 Re: Запросик 2....  [new]
gardenman
Member

Откуда: С-Петербург
Сообщений: 2347
обычно остатки для этого уже хранят в посчитанном виде, а не считают... или по крайней мере обороты.
18 май 05, 14:36    [1551776]     Ответить | Цитировать Сообщить модератору
 Re: Запросик 2....  [new]
TORT
Member

Откуда:
Сообщений: 1095
Если держать таблицу с остатками/оборотами, то накладно получается.....
Количество товаров * количество дат * количество предприятий = Слишком много, и причем растет быстро... Разве это выход?
18 май 05, 14:41    [1551804]     Ответить | Цитировать Сообщить модератору
 Re: Запросик 2....  [new]
TORT
Member

Откуда:
Сообщений: 1095
Подсчет оборотов дает стоимость (~8,5 тыс).... Ну можно посторить MQT для них, наверняка это увеличит скорость...
У меня основной затуп идет, когда пытаюсь собрать в подзапросе таблицу (product_id, date) для всех дат и товаров....
18 май 05, 14:43    [1551823]     Ответить | Цитировать Сообщить модератору
 Re: Запросик 2....  [new]
ggv
Member

Откуда:
Сообщений: 1810
у Николая есть бесподобная презентация Unleashed SQL. Это второй must read после CookBook для каждого, кто должен писать что-нибудь сложнее select * from table
в DB2

Ну а по поводу "накладно получается" - кто строит дизайн, тому и решать. Сравнивай. Скорость выполнения запроса, стоимость дискового пространства.
18 май 05, 14:55    [1551901]     Ответить | Цитировать Сообщить модератору
 Re: Запросик 2....  [new]
TORT
Member

Откуда:
Сообщений: 1095
НИКОЛАЙ!!!!!
18 май 05, 14:57    [1551919]     Ответить | Цитировать Сообщить модератору
 Re: Запросик 2....  [new]
gardenman
Member

Откуда: С-Петербург
Сообщений: 2347
Действительно, сейчас посмотрел-подумал... да... ОЛАП функции не катят для того, что тебе нужно. Но на С/С++ такую фишку действительно можно написать. Конечно - интересная, нетривиальная задача.

Пусть у нас есть упорядоченные записи:

1 01.01.2005 0
2 05.01.2005 4
3 15.01.2005 10
4 17.01.2005 2
5 28.01.2005 11

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

5 28.01.2005 0
4 17.01.2005 11
3 15.01.2005 2
2 05.01.2005 10
1 01.01.2005 4

Офигительно!... по-моему ОЛАП ф-ции этого не умеют.... но на С++ такое поведение реализовать (такую ф-цию) - как два пальца! ...
18 май 05, 16:14    [1552312]     Ответить | Цитировать Сообщить модератору
 Re: Запросик 2....  [new]
TORT
Member

Откуда:
Сообщений: 1095
Идея хорошая...... А дальше можно получить 1 или 0 в смысле был остаток на эту дату или нет, а потом перемножить 1 или 0 на 3 параметр.... Просуммировав получим число дней с остатком???? Правильно мыслю?
18 май 05, 16:23    [1552346]     Ответить | Цитировать Сообщить модератору
 Re: Запросик 2....  [new]
TORT
Member

Откуда:
Сообщений: 1095
А самое главное не надо для всех товаров держать все даты, а это уже сокращает стоимость на несколько порядков......
18 май 05, 16:27    [1552364]     Ответить | Цитировать Сообщить модератору
 Re: Запросик 2....  [new]
gardenman
Member

Откуда: С-Петербург
Сообщений: 2347
ну дык, действуй!)
18 май 05, 17:05    [1552556]     Ответить | Цитировать Сообщить модератору
 Re: Запросик 2....  [new]
TORT
Member

Откуда:
Сообщений: 1095
Выводы:
1. Стоимость запроса снизилась в 2500000/8600 ~300 раз.....
2. SCRATCHPAD forever!!!
Всем спасибо.....Громадное gardenman'у!
18 май 05, 18:18    [1552873]     Ответить | Цитировать Сообщить модератору
Все форумы / IBM DB2, WebSphere, IMS, U2, etc Ответить