Правильный поиск в истории цен - DAX: Программирование

Borsugg · 11.11.2011, 09:11

Доброе утро!

Возникла такая проблема.
Есть таблица История цен (RetailItemPriceHistory), в которой записывается розничная цена при каждом ее изменении. То есть история ведется не на каждый день, а только на те дни, когда произошло изменение цены. И вот возникает проблема поиска вот этой цены по определенной выборке.
Пример: допустим надо найти минимальную цену за период с 05.01.2011 по 20.02.2011 по конкретному товару.
В таблице есть только 3 строки с разными датами по этому артикулу (есть еще склад, но его опустим):
itemid transdate price
000001 01.01.2011 100
000001 07.01.2011 110
000001 15.02.2011 105

Делаем выборку таким образом:

X++:

select minOf(NewPrice) from RetailItemPriceHistory
                                where  RetailItemPriceHistory.ItemId            ==  itemId
                                    && RetailItemPriceHistory.InventLocationId  ==  InventLocationId
                                    && RetailItemPriceHistory.TransDate         >=  "05.01.2011"
                                    && RetailItemPriceHistory.TransDate         <=  "20.02.2011"
                                    && RetailItemPriceHistory.NewPrice;

Этот запрос вернет нам цену 105.
А правильная цена за этот период является 100, т.к. период указан начиная с 05.01.2011 и цена действовала в тот момент получается с 01.01.2011 по 07.01.2011 равной 100.

Подскажите как учесть эту цену?
У меня возникли 2 идеи по поводу решения этой задачи:
1. Написать метод, кот. будет возвращать мин. цену следующим образом:

Первый запрос будет таким, который представлен выше (Находим 1-ю мин.цену).

Второй запрос:

X++:

select firstonly RetailItemPriceHistory
                               order by RecId
                                where  RetailItemPriceHistory.ItemId            ==  itemId
                                    && RetailItemPriceHistory.InventLocationId  ==  InventLocationId
                                    && RetailItemPriceHistory.TransDate         >=  "05.01.2011"
                                    && RetailItemPriceHistory.TransDate         <=  "20.02.2011"
                                    && RetailItemPriceHistory.NewPrice;

Находим первую строку в истории по заданным условиям. То есть вот эту строку:
itemid transdate price
000001 07.01.2011 110

Третий запрос:
Находим 2-ю минимальную цену след.образом:

X++:

select firstonly RetailItemPriceHistory2
                                order by RecId
                                where  RetailItemPriceHistory2.ItemId            == RetailItemPriceHistory itemId
                                    && RetailItemPriceHistory2.InventLocationId  ==  RetailItemPriceHistory.InventLocationId
                                    && RetailItemPriceHistory2.RecId <  RetailItemPriceHistory.RecId
                                    && RetailItemPriceHistory2.NewPrice;

И потом уже сравниваем эти 2 получившиеся цены на мин.
Выглядит уже очень коряво, посему и пишу вам =)
(Знаю что с датами запросы не отработают, поставил для наглядности)

2-й способ:
Прикрутить таблицу с датами к истории и после найти мин.цену (В SQL вижу как сделать, а в аскапте пока думаю..)

Спасибо!

ice · 11.11.2011, 09:21

может лучше найти цену на начало периода и сравнить с ценой из первого запроса?

S.Kuskov · 11.11.2011, 09:27

Я бы второй запрос переписал вот так

X++:

select firstonly RetailItemPriceHistory2
                                order by TransDate desc
                                where  RetailItemPriceHistory2.ItemId            == itemId
                                    && RetailItemPriceHistory2.InventLocationId  == InventLocationId
                                    && RetailItemPriceHistory2.TransDate < "05.01.2011"
                                    && RetailItemPriceHistory2.NewPrice;

Borsugg · 11.11.2011, 09:27

Цитата:

Сообщение от ice

может лучше найти цену на начало периода и сравнить с ценой из первого запроса?

Мм..
Я вот это и пытаюсь сделать =))
Только как указать правильно условие, чтобы он подхватил вот эту строку:
itemid transdate price
000001 01.01.2011 100

Найти получается как-то таким образом:

X++:

 select firstonly RetailItemPriceHistory
                                order by TransDate desc
                                where  RetailItemPriceHistory.ItemId            ==  itemId
                                    && RetailItemPriceHistory.InventLocationId  ==  InventLocationId
                                    && RetailItemPriceHistory.TransDate         <=  "05.01.2011"
                                    && RetailItemPriceHistory.NewPrice;

ice · 11.11.2011, 09:35

Цитата:

Сообщение от S.Kuskov

Я бы второй запрос переписал вот так

X++:

select firstonly RetailItemPriceHistory2
                                order by TransDate desc
                                where  RetailItemPriceHistory2.ItemId            == itemId
                                    && RetailItemPriceHistory2.InventLocationId  == InventLocationId
                                    && RetailItemPriceHistory2.TransDate < "05.01.2011"
                                    && RetailItemPriceHistory2.NewPrice;

только вместо "<" надо "<="

wef · 11.11.2011, 09:38

Цитата:

Сообщение от Borsugg

Мм..
Только как указать правильно условие, чтобы он подхватил вот эту строку:
itemid transdate price
000001 01.01.2011 100

как то так:
select firstonly price from... order by transdate desc where transdate <= startperiod

Borsugg · 11.11.2011, 09:48

Цитата:

Сообщение от wef

как то так:
select firstonly price from... order by transdate desc where transdate <= startperiod

Ага, так выше и написал =)
Спасибо.

А может есть какие-либо другие интересные методы нахождения средствами аксапты?
Или представленный выше оптимальный для поставленной задачи?

lev · 11.11.2011, 09:58

можно сделать одним запросом

X++:

select firstonly NewPrice from RetailItemPriceHistory
    order by NewPrice
    where  RetailItemPriceHistory.ItemId            ==  itemId
          && RetailItemPriceHistory.InventLocationId  ==  InventLocationId
          && RetailItemPriceHistory.TransDate         >=  "05.01.2011"
          && RetailItemPriceHistory.TransDate         <=  "20.02.2011"
          && RetailItemPriceHistory.NewPrice
join firstonly NewPrice from RetailItemPriceHistory2
    order by TransDate desc
    where  RetailItemPriceHistory2.ItemId                ==  RetailItemPriceHistory.ItemId
          && RetailItemPriceHistory2.InventLocationId  ==  RetailItemPriceHistory.InventLocationId
          && RetailItemPriceHistory2.TransDate           <    "05.01.2011";

return min(RetailItemPriceHistory.NewPrice, RetailItemPriceHistory2.NewPrice);

Borsugg · 11.11.2011, 09:59

Цитата:

Сообщение от lev

можно сделать одним запросом

Ммм, а в таком случае RetailItemPriceHistory вернет первую цену, а не минимальную за период =)

lev · 11.11.2011, 10:03

Цитата:

Сообщение от Borsugg

Ммм, а в таком случае RetailItemPriceHistory вернет первую цену, а не минимальную за период =)

Там же сделана сортировка по цене

по умолчанию она "ASC" - от сюда следут, что будет возвращена минимальная цена в периоде

(если это не так и тест это подтверждает, напишите плиз)

S.Kuskov · 11.11.2011, 10:07

Цитата:

Сообщение от lev

можно сделать одним запросом

Теоретически может так оказаться, что одна из частей запроса не найдёт не одной записи (например период в которм ищем минимальную цену начинается раньше чем первая запись о цене). И тогда джойн не вернёт ничего.

В случае с двумя запросами, перед тем как искать минимум среди двух результатов нужно принудительно проверить что результаты не пусты.

lev · 11.11.2011, 10:16

Цитата:

Сообщение от S.Kuskov

Теоретически может так оказаться, что вторая часть запроса не найдёт не одной записи (например период в которм ищем минимальную цену насчинается раньше чем первая запись о цене). И тогда джойн не вернёт ничего.

да, такое возможно... я думаю что можно это будет учесть. в начале выбрать с помощью джойна, если там ничего не нашлось, тогда получается что достаточно сделать один запрос с указанием периода. он и найдет минимальную цену. Т.е. в итоге метод получится что то типа:

X++:

....
Price    returnPrice;
;

select firstonly NewPrice from RetailItemPriceHistory
    order by NewPrice
    where  RetailItemPriceHistory.ItemId            ==  itemId
          && RetailItemPriceHistory.InventLocationId  ==  InventLocationId
          && RetailItemPriceHistory.TransDate         >=  "05.01.2011"
          && RetailItemPriceHistory.TransDate         <=  "20.02.2011"
          && RetailItemPriceHistory.NewPrice
join firstonly NewPrice from RetailItemPriceHistory2
    order by TransDate desc
    where  RetailItemPriceHistory2.ItemId                ==  RetailItemPriceHistory.ItemId
          && RetailItemPriceHistory2.InventLocationId  ==  RetailItemPriceHistory.InventLocationId
          && RetailItemPriceHistory2.TransDate           <    "05.01.2011";

if (RetailItemPriceHistory.RecId != 0)
    returnPrice = min(RetailItemPriceHistory.NewPrice, RetailItemPriceHistory2.NewPrice);
else
{
    select minOf(NewPrice) from RetailItemPriceHistory
                                where  RetailItemPriceHistory.ItemId            ==  itemId
                                    && RetailItemPriceHistory.InventLocationId  ==  InventLocationId
                                    && RetailItemPriceHistory.TransDate         >=  "05.01.2011"
                                    && RetailItemPriceHistory.TransDate         <=  "20.02.2011"
                                    && RetailItemPriceHistory.NewPrice;   

    returnPrice = RetailItemPriceHistory.NewPrice;
}

return returnPrice;

P.S. в общем конечно этот вариант надо обдумывать с тестированием (гонять на разных вариантах), тогда в итоге можно получить наиболее сбалансированный результат

Borsugg · 11.11.2011, 10:26

Цитата:

Сообщение от lev

да, такое возможно... я думаю что можно это будет учесть. в начале выбрать с помощью джойна, если там ничего не нашлось, тогда получается что достаточно сделать один запрос с указанием периода. он и найдет минимальную цену. Т.е. в итоге метод получится что то типа:

X++:

....
....
if (RetailItemPriceHistory.RecId != 0)
    returnPrice = min(RetailItemPriceHistory.NewPrice, RetailItemPriceHistory2.NewPrice);
else
{
    select minOf(NewPrice) from RetailItemPriceHistory
                                where  RetailItemPriceHistory.ItemId            ==  itemId
                                    && RetailItemPriceHistory.InventLocationId  ==  InventLocationId
                                    && RetailItemPriceHistory.TransDate         >=  "05.01.2011"
                                    && RetailItemPriceHistory.TransDate         <=  "20.02.2011"
                                    && RetailItemPriceHistory.NewPrice;   

    returnPrice = RetailItemPriceHistory.NewPrice;
}

return returnPrice;

P.S. в общем конечно этот вариант надо обдумывать с тестированием (гонять на разных вариантах), тогда в итоге можно получить наиболее сбалансированный результат

А если RetailItemPriceHistory.RecId и так ничего не вернул в первом запросе с join'ом, то скорее же всего и второй запрос тоже ничего не вернет? Условия выборки то одинаковые...

S.Kuskov · 11.11.2011, 10:29

Цитата:

Сообщение от lev

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

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

ice · 11.11.2011, 10:32

может лучше в качестве оптимизации сделать нужные индексы, чем делать громоздкий код?

lev · 11.11.2011, 10:46

Цитата:

Сообщение от S.Kuskov

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

смотри мое P.S.

я же говорю, нужно все варианты обдумать и протестить. с джойном я предложил как вариант, что бы не писать по 10 запросов

если этот вариант не подходит, то его нужно выкинуть в корзину

Цитата:

Сообщение от Borsugg

А если RetailItemPriceHistory.RecId и так ничего не вернул в первом запросе с join'ом, то скорее же всего и второй запрос тоже ничего не вернет? Условия выборки то одинаковые...

нет, первый запрос с джойном ничего не вернет, если нет записей в истории с датой ранее начальной датой периода поиска.
второй запрос вернет если в заданном периоде есть записи.
это разные выборки.

Borsugg · 11.11.2011, 10:46

Цитата:

Сообщение от ice

может лучше в качестве оптимизации сделать нужные индексы, чем делать громоздкий код?

А если не затруднит, можно поподробнее, что под нужными индексами подразумеваете?

Borsugg · 11.11.2011, 10:48

Цитата:

Сообщение от lev

нет, первый запрос с джойном ничего не вернет, если нет записей в истории с датой ранее начальной датой периода поиска.
второй запрос вернет если в заданном периоде есть записи.
это разные выборки.

Тобишь джойн отработает как INNER JOIN в SQL. Понятно)

ice · 11.11.2011, 10:55

Цитата:

Сообщение от Borsugg

А если не затруднит, можно поподробнее, что под нужными индексами подразумеваете?

индекс по полям участвующим в запросе

Владимир Максимов · 11.11.2011, 11:50

Цитата:

Сообщение от Borsugg

А если не затруднит, можно поподробнее, что под нужными индексами подразумеваете?

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

X++:

FromDate fromDate = 05\01\2011;
ToDate toDate = 20\02\2011;
newPrice minNewPrice = maxInt();
;

while select NewPrice, TransDate
	from RetailItemPriceHistory
	order by TransDate desc
	where RetailItemPriceHistory.ItemId            ==  itemId
		&& RetailItemPriceHistory.InventLocationId  ==  InventLocationId
		&& RetailItemPriceHistory.TransDate         <=  toDate
		&& RetailItemPriceHistory.NewPrice
{
	minNewPrice = min(minNewPrice, RetailItemPriceHistory.NewPrice);
	// Сравнение с начальной датой ПОСЛЕ вычисления значения
	// чтобы учесть цену, указанную ДО начальной даты
	if (RetailItemPriceHistory.TransDate <= fromDate)
	{
		break;
	}
}

if (minNewPrice == maxInt())
{
	// На дату окончания периода нет цены
	minNewPrice = 0;
}

Если же все-таки хочется все в одном запросе, то проблема заключается в том, что необходимо уточнить дату начала периода. Т.е. изменить начальную дату, если в истории есть запись с датой меньше указанной, но нет записи с датой равной указанной.

X++:

FromDate fromDate = 05\01\2011;
ToDate toDate = 20\02\2011;
;

// Уточняем "начальную дату"
select firstonly TransDate
from RetailItemPriceHistory
order by TransDate desc
where RetailItemPriceHistory.ItemId            ==  itemId
	&& RetailItemPriceHistory.InventLocationId  ==  InventLocationId
	&& RetailItemPriceHistory.TransDate         <=  fromDate 
	&& RetailItemPriceHistory.NewPrice;

// Если есть запись с датой меньше начальной и нет записи с датой
// равной начальной, то начальной датой считаем найденное значение
if (RetailItemPriceHistory.TransDate < fromDate)
{
	fromDate = RetailItemPriceHistory.TransDate;
}

// Теперь собственно запрос
select minOf(NewPrice) 
from RetailItemPriceHistory
where  RetailItemPriceHistory.ItemId            ==  itemId
	&& RetailItemPriceHistory.InventLocationId  ==  InventLocationId
	&& RetailItemPriceHistory.TransDate         >=  fromDate
	&& RetailItemPriceHistory.TransDate         <=  toDate
	&& RetailItemPriceHistory.NewPrice;

Похожие темы
Тема	Автор	Раздел	Ответов	Посл. сообщение
Поиск набором в выпадающем списке..	propeller	DAX: Программирование	0	04.04.2011 17:31
Поиск сотрудников по наименованию (ФИО) в AX 2009	propeller	DAX: Программирование	12	15.11.2010 12:42
"поиск"	braathe	DAX: Программирование	6	24.03.2006 13:07
Поиск по подстроке	JohNick	DAX: Программирование	8	06.03.2004 22:24
Поиск по полю временной таблицы	Swetik	DAX: Программирование	2	10.12.2003 11:35