Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Сравнение строк по заданным условиям  [new]
ferzmikk
Member

Откуда:
Сообщений: 2029
Здравствуйте!

Есть такая таблица.
+Исходная таблица
Клиент Код ТТ SKU Дата Отгрузки шт Возвраты шт Реализация шт Цена
Клиент1 ТТ1 SKU1 01.01.2014 1 0 1 100.00
Клиент1 ТТ1 SKU1 03.01.2014 0 1 -1 100.00
Клиент1 ТТ1 SKU1 05.01.2014 6 2 4 100.00
Клиент1 ТТ1 SKU1 07.01.2014 5 1 4 150.00
Клиент1 ТТ1 SKU1 09.01.2014 2 1 1 150.00
Клиент1 ТТ1 SKU1 11.01.2014 1 0 1 100.00
Клиент1 ТТ1 SKU2 01.01.2014 3 1 2 100.00
Клиент1 ТТ1 SKU2 03.01.2014 2 0 2 50.00
Клиент1 ТТ1 SKU2 05.01.2014 3 1 2 100.00
Клиент1 ТТ1 SKU2 17.01.2014 2 0 2 100.00
Клиент1 ТТ2 SKU1 01.01.2014 3 1 2 100.00
Клиент1 ТТ2 SKU1 03.01.2014 1 0 1 100.00

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

Для решения данной задачи ориентировался по цене. Сравнивал текущую строку и предыдущую с помощью оконной функции LAG. Если в предыдущей строке другая ТТ+SKU, то не сравнивает. Написал такой SQL-запрос, но он не законченный и работает не так как надо.
+SQL-Запрос
Declare @ДельтаПредел decimal(5,2) = 0.15

DECLARE @Таблица table(
	[Клиент] varchar(20),
	[Код ТТ] varchar(8),	
	[SKU] varchar(8),
	[Дата] date,
	[Отгрузки шт] numeric(8,0),
	[Возвраты шт] numeric(8,0),
	[Реализация шт] numeric(8,0),
	[Реализация сумма без НДС] numeric(8,0),
	[Цена] decimal(5,2))
;

INSERT INTO
  @Таблица
VALUES 
('Клиент1','ТТ1', 'SKU1', '01.01.2014', 1,0,1,100,100),
('Клиент1','ТТ1', 'SKU1', '03.01.2014', 0,1,-1,-100,100),
('Клиент1','ТТ1', 'SKU1', '05.01.2014', 6,2,4,400,100),
('Клиент1','ТТ1', 'SKU1', '07.01.2014', 5,1,4,600,150),
('Клиент1','ТТ1', 'SKU1', '09.01.2014', 2,1,1,150,150),
('Клиент1','ТТ1', 'SKU1', '11.01.2014', 1,0,1,100,100),
('Клиент1','ТТ1', 'SKU2', '01.01.2014', 3,1,2,200,100),
('Клиент1','ТТ1', 'SKU2', '03.01.2014', 2,0,2,100,50),
('Клиент1','ТТ1', 'SKU2', '05.01.2014', 3,1,2,200,100),
('Клиент1','ТТ1', 'SKU2', '17.01.2014', 2,0,2,200,100),
('Клиент1','ТТ2', 'SKU1', '01.01.2014', 3,1,2,200,100),
('Клиент1','ТТ2', 'SKU1', '03.01.2014', 1,0,1,100,100)
;

WITH
Таблица1 AS (
	SELECT
		[Клиент],
		[Код ТТ],	
		[SKU],
		[Дата],
		[Отгрузки шт],
		[Возвраты шт],
		[Реализация шт],
		[Реализация сумма без НДС],
		[Цена],
		IIF([Реализация шт] <>0,[Реализация сумма без НДС]/[Реализация шт] , Null) [Проверка],
		LAG([Код ТТ]) OVER(ORDER BY [Код ТТ]) [Код ТТ пред],
		LAG([SKU]) OVER(ORDER BY [SKU]) [SKU пред],
		LAG([Цена]) OVER(ORDER BY [Клиент],[Код ТТ],[SKU], [Дата]) [Цена пред]
	FROM
		@Таблица
),
Таблица2 AS (
	SELECT
		[Клиент],
		[Код ТТ],	
		[SKU],
		[Дата],
		[Отгрузки шт],
		[Возвраты шт],
		[Реализация шт],
		[Реализация сумма без НДС],
		[Цена],
		[Проверка],
		[Код ТТ пред],
		[SKU пред],
		[Цена пред],
		(CASE WHEN [Код ТТ] = [Код ТТ пред] AND [SKU] = [SKU пред] THEN 1 ELSE 0 END) [Предыдущее соответствие],
		(CASE
			WHEN [Код ТТ] = [Код ТТ пред] AND [SKU] = [SKU пред] THEN
				(CASE
                                        --Если цена одинаковая, но не мероприятие
					WHEN [Цена] = [Цена пред] THEN
						NUll
                                        --Если цена одинаковая, но мероприятие
					WHEN [Цена] = [Цена пред] THEN
						NUll
                                        --Если цена отличается
					ELSE
						(CASE
							WHEN [Цена] >= [Цена пред] THEN
								([Цена] - [Цена пред] )  /  [Цена пред]
							ELSE
								([Цена пред] - [Цена]) / [Цена]
						END)
				END)
			ELSE
			NULL
		END)
			[Дельта]
FROM
	Таблица1
),
Таблица3 AS (
	SELECT
		[Клиент],
		[Код ТТ],	
		[SKU],
		CONVERT(nvarchar(10), [Дата] , 104) AS [Дата],
		[Отгрузки шт],
		[Возвраты шт],
		[Реализация шт],	
		[Цена],	
		CAST([Дельта]AS decimal(10,2)) AS [Дельта],		
		IIF([Дельта] > @ДельтаПредел,1,0) [Мероприятие]
	FROM
		Таблица2
)
		
SELECT * FROM Таблица3

В общем, выдает такой результат
+Результирующая таблица
Клиент Код ТТ SKU Дата Отгрузки шт Возвраты шт Реализация шт Цена Дельта Мероприятие
Клиент1 ТТ1 SKU1 01.01.2014 1 0 1 100.00 NULL 0
Клиент1 ТТ1 SKU1 03.01.2014 0 1 -1 100.00 NULL 0
Клиент1 ТТ1 SKU1 05.01.2014 6 2 4 100.00 NULL 0
Клиент1 ТТ1 SKU1 07.01.2014 5 1 4 150.00 0.50 1
Клиент1 ТТ1 SKU1 09.01.2014 2 1 1 150.00 NULL 0
Клиент1 ТТ1 SKU1 11.01.2014 1 0 1 100.00 0.50 1
Клиент1 ТТ1 SKU2 01.01.2014 3 1 2 100.00 NULL 0
Клиент1 ТТ1 SKU2 03.01.2014 2 0 2 50.00 1.00 1
Клиент1 ТТ1 SKU2 05.01.2014 3 1 2 100.00 1.00 1
Клиент1 ТТ1 SKU2 17.01.2014 2 0 2 100.00 NULL 0
Клиент1 ТТ2 SKU1 01.01.2014 3 1 2 100.00 NULL 0
Клиент1 ТТ2 SKU1 03.01.2014 1 0 1 100.00 NULL 0

+Должно быть так
Клиент Код ТТ SKU Дата Отгрузки шт Возвраты шт Реализация шт Цена Дельта Мероприятие
Клиент1 ТТ1 SKU1 01.01.2014 1 0 1 100.00 NULL 0
Клиент1 ТТ1 SKU1 03.01.2014 0 1 -1 100.00 NULL 0
Клиент1 ТТ1 SKU1 05.01.2014 6 2 4 100.00 NULL 0
Клиент1 ТТ1 SKU1 07.01.2014 5 1 4 150.00 0.50 1
Клиент1 ТТ1 SKU1 09.01.2014 2 1 1 150.00 NULL 1
Клиент1 ТТ1 SKU1 11.01.2014 1 0 1 100.00 0.500
Клиент1 ТТ1 SKU2 01.01.2014 3 1 2 100.00 NULL 0
Клиент1 ТТ1 SKU2 03.01.2014 2 0 2 50.00 1.00 1
Клиент1 ТТ1 SKU2 05.01.2014 3 1 2 100.00 1.000
Клиент1 ТТ1 SKU2 17.01.2014 2 0 2 100.00 NULL 0
Клиент1 ТТ2 SKU1 01.01.2014 3 1 2 100.00 NULL 0
Клиент1 ТТ2 SKU1 03.01.2014 1 0 1 100.00 NULL 0

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

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

Как правильно написать SQL-запрос для решения данной задачи?
23 мар 18, 10:12    [21279709]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение строк по заданным условиям  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20174
ferzmikk
Если в предыдущей строке другая ТТ+SKU, то не сравнивает.
А ORDER BY за тебя кто задавать будет?
23 мар 18, 10:28    [21279777]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение строк по заданным условиям  [new]
ferzmikk
Member

Откуда:
Сообщений: 2029
Akina
ferzmikk
Если в предыдущей строке другая ТТ+SKU, то не сравнивает.
А ORDER BY за тебя кто задавать будет?
А строки отсортированы и поэтому не использую ORDER BY. Или в любом случае надо использовать?
23 мар 18, 11:36    [21280099]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение строк по заданным условиям  [new]
waszkiewicz
Member

Откуда:
Сообщений: 1067
ferzmikk,
это кто тебе сказал, что строки отсортированы?
23 мар 18, 11:39    [21280112]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение строк по заданным условиям  [new]
ferzmikk
Member

Откуда:
Сообщений: 2029
waszkiewicz
ferzmikk,
это кто тебе сказал, что строки отсортированы?

Таблица получается отдельной выгрузкой с помощью MDX-запроса. А MDX-запрос сортирует
23 мар 18, 11:42    [21280128]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение строк по заданным условиям  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
ferzmikk,

Во-первых, чтобы определить предыдущую цену надо писать так:
LAG([Цена]) OVER(Partition by [Клиент],[Код ТТ],[SKU] ORDER BY  [Дата]) [Цена пред]


Во-вторых, исходя из желаемого результата тебя интересуют изменения цены относительно "базовой", но как определить эту базовую цену?
23 мар 18, 11:48    [21280157]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение строк по заданным условиям  [new]
waszkiewicz
Member

Откуда:
Сообщений: 1067
ferzmikk
waszkiewicz
ferzmikk,
это кто тебе сказал, что строки отсортированы?

Таблица получается отдельной выгрузкой с помощью MDX-запроса. А MDX-запрос сортирует

серверу это расскажешь
23 мар 18, 11:49    [21280164]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение строк по заданным условиям  [new]
ferzmikk
Member

Откуда:
Сообщений: 2029
Kopelly
ferzmikk,

Во-первых, чтобы определить предыдущую цену надо писать так:
LAG([Цена]) OVER(Partition by [Клиент],[Код ТТ],[SKU] ORDER BY  [Дата]) [Цена пред]

Тогда для полей [Код ТТ пред] и [SKU пред] получается тоже надо писать так
LAG([Код ТТ]) OVER(Partition by [Клиент] ORDER BY [Код ТТ]) [Код ТТ пред],
LAG([SKU]) OVER(Partition by [Клиент],[Код ТТ] ORDER BY [SKU]) [SKU пред],

Верно или оставить как было?
Во-вторых, исходя из желаемого результата тебя интересуют изменения цены относительно "базовой", но как определить эту базовую цену?

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

Если Вы имеете ввиду строку до мероприятия и последующие строки с мероприятием, то думаю будет нужна переменная, которая хранит базовую цену до мероприятия. Но не обязательно, что цена после мероприятия будет такая как до мероприятия. Важно учесть, что может быть просто изменение (повышение или понижение) цены без участия мероприятия, например, повышение цен из за инфляции.

Только вот пока затрудняюсь понять как это все сделать эскуэльно.
23 мар 18, 13:09    [21280501]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение строк по заданным условиям  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
ferzmikk
Тогда для полей [Код ТТ пред] и [SKU пред] получается тоже надо писать так
LAG([Код ТТ]) OVER(Partition by [Клиент] ORDER BY [Код ТТ]) [Код ТТ пред],
LAG([SKU]) OVER(Partition by [Клиент],[Код ТТ] ORDER BY [SKU]) [SKU пред],

Верно или оставить как было?

Добавляя выражение "Partition by [Клиент],[Код ТТ],[SKU]" ограничиваем работу окна именно значениями из текущей строки.
Т.е. "LAG([Цена]) OVER(Partition by [Клиент],[Код ТТ],[SKU] ORDER BY [Дата])" можно перевести как:
Для текущих значений [Клиент],[Код ТТ],[SKU] найти предыдущее по [Дата] значение [Цена].
Так что проверка предыдущей строки на соответствие полей [Клиент],[Код ТТ],[SKU] не требуется.

Как определять "базовую" цену уточняй у постановщика задания.
Если все-таки нужно будет брать цену на начало года, то запросом собираешь таблицу базовых цен и все последующие даты/цены сравниваешь с ней...
24 мар 18, 09:45    [21282608]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение строк по заданным условиям  [new]
ferzmikk
Member

Откуда:
Сообщений: 2029
Kopelly
ferzmikk
Тогда для полей [Код ТТ пред] и [SKU пред] получается тоже надо писать так
LAG([Код ТТ]) OVER(Partition by [Клиент] ORDER BY [Код ТТ]) [Код ТТ пред],
LAG([SKU]) OVER(Partition by [Клиент],[Код ТТ] ORDER BY [SKU]) [SKU пред],

Верно или оставить как было?

Добавляя выражение "Partition by [Клиент],[Код ТТ],[SKU]" ограничиваем работу окна именно значениями из текущей строки.
Т.е. "LAG([Цена]) OVER(Partition by [Клиент],[Код ТТ],[SKU] ORDER BY [Дата])" можно перевести как:
Для текущих значений [Клиент],[Код ТТ],[SKU] найти предыдущее по [Дата] значение [Цена].
Так что проверка предыдущей строки на соответствие полей [Клиент],[Код ТТ],[SKU] не требуется.

Проверка на соответствие я сделал для того, чтобы SQL понимал, что это другой TT+SKU и сравнивал цены только для соответствующих TT+SKU.
+
...
                (CASE
                        --Если предыдущая строка это соответствующая ТТ+SKU
			WHEN [Код ТТ] = [Код ТТ пред] AND [SKU] = [SKU пред] THEN
				(CASE
                                        --Если цена одинаковая, но не мероприятие
					WHEN [Цена] = [Цена пред] THEN
						NUll
                                        --Если цена одинаковая, но мероприятие
					WHEN [Цена] = [Цена пред] THEN
						NUll
                                        --Если цена отличается
					ELSE
						(CASE
							WHEN [Цена] >= [Цена пред] THEN
								([Цена] - [Цена пред] )  /  [Цена пред]
							ELSE
								([Цена пред] - [Цена]) / [Цена]
						END)
				END)
			ELSE
			NULL
		END)
			[Дельта]
...

Я правильно понимаю, что достаточно так написать?
+
...
SELECT
	[Клиент],
	[Код ТТ],	
	[SKU],
	[Дата],
	[Отгрузки шт],
	[Возвраты шт],
	[Реализация шт],
	[Реализация сумма без НДС],
	[Цена],
	LAG([Цена]) OVER(Partition by [Клиент],[Код ТТ],[SKU] ORDER BY  [Дата]) [Цена пред]
FROM
	@Таблица
...
24 мар 18, 11:45    [21282723]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение строк по заданным условиям  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5112
ferzmikk
Я правильно понимаю, что достаточно так написать?
для решения исходной задачи ("нужно отметить те строки-дни, где было ценовое мероприятие") - нЕт.
вам уже несколько раз написали, уточните постановку, а именно что такое "ценовое мероприятие".
от ответа на это вопрос будет зависеть конкретный вариант решения.
24 мар 18, 13:03    [21282833]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение строк по заданным условиям  [new]
ferzmikk
Member

Откуда:
Сообщений: 2029
Дедушка
уточните постановку, а именно что такое "ценовое мероприятие".
от ответа на это вопрос будет зависеть конкретный вариант решения.

Описываю развернуто.

Имеются исторические данные с января 2014 г. о продажах. Нужно провести прогнозирование, но этот вопрос в данном посте не рассматривается. В разные периоды присутствуют различные промо. При промо продажи обычно выше, различные всплески продаж. Чтобы убрать эти всплески нужно промаркировать дни, где было промо.

Промо бывает ценовое и не ценовое. Ценовое промо это когда в TT+SKU в какие то дни цена снижена. В данную задачу пока смотрим только ценовое промо.

Проблема в том, что для начала нужно определить базовую цену. Данные начинаются с 1.01.2014. г. Всегда это праздничный день. Тут могло быть либо утвержденное приказом ценовое промо (цена занижена), либо нет (просто народ закупает по обычной (базовой) цене в праздничный день, и продажи больше).

Решение по поводу определения базовой цены:
- Вариант 1, если фактически 1.01.2014 г. было ценовое промо. Тут для каждого ТТ+SKU для первых чисел января 2014 года это ценовое промо. И если на следующий какой то день цена повысится, то это будет базовая цена. Задать константу первые 15 дней.
- Вариант 2, если фактически 1.01.2014 г. не было ценового промо, народ просто закупает по базовой цене. Не придумал решение пока.

Тут надо еще распознать по каждому ТТ+SKU по данным к какому варианту относится, а потом определять базовую цену.

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

К сообщению приложен файл. Размер - 11Kb
25 мар 18, 12:17    [21283928]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение строк по заданным условиям  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5112
ferzmikk,

для подобных задач только ценовых данных недостаточно нужно подключать другие данные учётных систем
(например 1С где фиксируются данные о закупочных ценах и промо акциях).

пример:
- 1го января привезли партию товара по цене 100 - для всего января "базовая" цена на этот товар = 100
- 1го февраля привезли новую партию этого же товара, но уже по цене 200 - для февраля базовая цена уже другая
- 10го февраля ввели акцию купи это товар на этой неделе со скидкой 50%, а на следующей уже будет только 20%
(т.е цена сначала снижена, а потом повысилась, но это всё ещё промо-цена)

НО с точки зрения исходной задачи - прогнозирования
точная "день в день" идентификация промо акций вовсе не нужна (это шум)
снижение цены на 10 рублей нивелируется в общем прогнозе
вам нужно просто удалить выбросы (существенное изменение цены)
по уму, это нужно делать с помощью медианы, квартилей и тд.
если же вы хотите прогнозировать "спрос" гуглите - "восстановление спроса".
25 мар 18, 14:17    [21284105]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение строк по заданным условиям  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
ferzmikk
Я правильно понимаю, что достаточно так написать?

Для определения предыдущей цены - да, правильно.

Я думаю что постановка которую ты описал не совсем корректна:
1. На начало года в теории может быть повышение цен для реализации товаров по каким-то временным бонусам.
2. Как определить базовую цену для товаров которые появились в течении года (они могли сразу попасть в какое-то ценовое событие).

Ну если реализовать под текущую постановку, то запрос для "базовой цены" можно реализовать так:
With Ordered as 
(Select Товар, Цена, Row_Number() Over (Partition by Товар Order by Дата desc) as RN From Цены Where Дата<=@НачалоГода)
Select Ordered.Товар, isnull(След.Цена,Ordered.Цена) as БазоваяЦена
From Ordered 
Outer apply 
(Select top 1 a.Цена
From Цены a 
Where a.Товар = Ordered.Товар and a.Дата>@НачалоГода and a.Дата <=DateAdd(dd,14,@НачалоГода)
and a.Цена>Ordered.Цена
Order by a.Data
) след
Where Ordered.RN = 1
26 мар 18, 04:48    [21284910]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить