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

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

Этот пост является продолжением поста Предыдущее значение вычисляемого поля. Но акцент делает на двойное снижение цен.

Есть исходные данные цен по отгрузочным дням.

В некоторые днях по каждому [Код ТТ] + [Код продукции] бывают снижения цены, вот их и нужно промаркировать.

Написан SQL-запрос.
+SQL-запрос
DECLARE @Таблица table(		
	[Код ТТ] varchar(8),	
	[Код продукции] varchar(8),
	[Дата] date,
	[Отгрузки шт] integer,	
	[Цена] decimal(8,0)
	)	
;

INSERT INTO
  @Таблица
VALUES 
('Код ТТ1','SKU1', '01.01.2014', 1, 100),
('Код ТТ1','SKU1', '02.01.2014', 2, 100),
('Код ТТ1','SKU1', '03.01.2014', 1, 90),
('Код ТТ1','SKU1', '04.01.2014', 1, 100),
('Код ТТ1','SKU1', '05.01.2014', 1, 100),
('Код ТТ1','SKU1', '08.01.2014', 1, 100),
('Код ТТ1','SKU2', '01.01.2014', 1, 100),
('Код ТТ1','SKU2', '02.01.2014', 2, 100),
('Код ТТ1','SKU2', '03.01.2014', 1, 90),
('Код ТТ1','SKU2', '04.01.2014', 1, 80),
('Код ТТ1','SKU2', '05.01.2014', 1, 90),
('Код ТТ2','SKU1', '01.01.2014', 1, 100),
('Код ТТ2','SKU1', '02.01.2014', 2, 100),
('Код ТТ2','SKU1', '03.02.2014', 1, 90),
('Код ТТ2','SKU1', '04.02.2014', 1, 80),
('Код ТТ2','SKU1', '05.02.2014', 1, 90),
('Код ТТ2','SKU1', '08.03.2014', 1, 100);

Declare @ДельтаПредел decimal(5,2) = 0.07;

WITH Т AS (
	SELECT		
		[Код ТТ],
		[Код продукции],	
		[Дата],
		[Отгрузки шт],		
		[Цена],		
		ROW_NUMBER()over(Partition by [Код ТТ], [Код продукции] order by  Convert(datetime,[Дата],104))	AS [ID]
	FROM
		@Таблица
)
,

cte AS (
		SELECT
			*,
			т.[Цена] AS [Цена пред],
			0 AS [Маркировка по пред цене],
			[Цена пред которая используется в расчете]=cast(null as decimal(8,0))
		FROM
			Т т 
		WHERE
			id=1
	UNION ALL	
	SELECT
		т.*,
		т.[Цена], 
		CASE
			WHEN cte.[Цена пред] IS NOT NULL AND cte.[Цена пред] <> 0 THEN
				CASE
					WHEN ((т.[Цена] / cte.[Цена пред])-1) <= -@ДельтаПредел THEN
						1
					ELSE
						CASE
							WHEN  ((т.[Цена] / cte.[Цена пред])-1) <= @ДельтаПредел AND cte.[Маркировка по пред цене] =1 THEN
								1
							ELSE
								0
							END
				END
		END,
		cte.[Цена пред]
	FROM
		Т т 
	inner join
		cte
	ON
		т.id=cte.id+1
		AND
		т.[Код ТТ]=cte.[Код ТТ]
		AND 
		т.[Код продукции]=cte.[Код продукции]
)

SELECT
	[Код ТТ],
	[Код продукции],	
	[Дата],
	[Отгрузки шт],		
	[Цена],
	[ID],
	[Маркировка по пред цене],
	[Цена пред которая используется в расчете] AS [Цена пред]
FROM
	cte c
ORDER BY	
	[Код ТТ],
	[Код продукции],
	[ID]
OPTION (MAXRECURSION 0)
Следует учесть, что тут не маркируется первая строка.
+Но тут не учитывается такой фактор как двойное снижение цены.
Код ТТ Код продукции Дата Отгрузки шт Цена ID Маркировка по пред цене Цена пред
Код ТТ1 SKU1 01.01.2014 1 100 1 0 NULL
Код ТТ1 SKU1 02.01.2014 2 100 2 0 100
Код ТТ1 SKU1 03.01.2014 1 90 3 1 100
Код ТТ1 SKU1 04.01.2014 1 100 4 0 90
Код ТТ1 SKU1 05.01.2014 1 100 5 0 100
Код ТТ1 SKU1 08.01.2014 1 100 6 0 100
Код ТТ1 SKU2 01.01.2014 1 100 1 0 NULL
Код ТТ1 SKU2 02.01.2014 2 100 2 0 100
Код ТТ1 SKU2 03.01.2014 1 90 3 1 100
Код ТТ1 SKU2 04.01.2014 1 80 4 1 90
Код ТТ1 SKU2 05.01.2014 1 90 5 0 80
Код ТТ2 SKU1 2014-01-01 1 100 1 0 NULL
Код ТТ2 SKU1 2014-01-02 2 100 2 0 100
Код ТТ2 SKU1 2014-02-03 1 90 3 1 100
Код ТТ2 SKU1 2014-02-04 1 80 4 1 90
Код ТТ2 SKU1 2014-02-05 1 90 5 0 80
Код ТТ2 SKU1 2014-03-08 1 100 6 0 90
Для решения такой проблемы делаю еще одну маркировку - по максимальной цене за текущий месяц, чтобы потом можно привязать к первой маркировке. Максимальная цена предполагает что это базовая цена.
+SQL-запрос
...
WITH Т AS (
	SELECT		
		[Код ТТ],
		[Код продукции],	
		[Дата],
		[Отгрузки шт],		
		[Цена],		
		ROW_NUMBER()over(Partition by [Код ТТ], [Код продукции] order by  Convert(datetime,[Дата],104))	AS [ID]
	FROM
		@Таблица
)
,

cte AS (
		SELECT
			*,
			т.[Цена] AS [Цена пред],
			0 AS [Маркировка по пред цене],
			[Цена пред которая используется в расчете]=cast(null as decimal(8,0))
		FROM
			Т т 
		WHERE
			id=1
	UNION ALL	
	SELECT
		т.*,
		т.[Цена], 
		CASE
			WHEN cte.[Цена пред] IS NOT NULL AND cte.[Цена пред] <> 0 THEN
				CASE
					WHEN ((т.[Цена] / cte.[Цена пред])-1) <= -@ДельтаПредел THEN
						1
					ELSE
						CASE
							WHEN  ((т.[Цена] / cte.[Цена пред])-1) <= @ДельтаПредел AND cte.[Маркировка по пред цене] =1 THEN
								1
							ELSE
								0
							END
				END
		END,
		cte.[Цена пред]
	FROM
		Т т 
	inner join
		cte
	ON
		т.id=cte.id+1
		AND
		т.[Код ТТ]=cte.[Код ТТ]
		AND 
		т.[Код продукции]=cte.[Код продукции]
),

cte2 AS (
	SELECT
		[Код ТТ],
		[Код продукции],	
		[Дата],
		[Отгрузки шт],		
		[Цена],
		[ID],
		[Маркировка по пред цене],
		[Цена пред которая используется в расчете] AS [Цена пред],
		cte1.[Макс Цена за тек месяц],
		(CASE WHEN cte1.[Макс Цена за тек месяц] <> 0 THEN
			(CASE WHEN [Цена] <= cte1.[Макс Цена за тек месяц] AND [Цена] / cte1.[Макс Цена за тек месяц] - 1 <= -@ДельтаПредел THEN 1 ELSE 0 END) 
		END) AS [Маркировка по макс цене за тек месяц],
		LAG([Маркировка по пред цене]) OVER(Partition by [Код ТТ],[Код продукции] ORDER BY  [ID]) AS [Предыдущая Маркировка по пред цене]
	FROM
		cte c
	CROSS APPLY 
		(
			SELECT MAX(a.[Цена]) as [Макс Цена за тек месяц]
			FROM cte a
			WHERE			
				a.[Код ТТ]	= c.[Код ТТ]
				AND
				a.[Код продукции]= c.[Код продукции]
				AND
				a.[Дата] BETWEEN DATEADD(day,1,EOMONTH(DATEADD(month,-1,c.[Дата]))) AND EOMONTH (c.[Дата])
		) AS cte1
)

SELECT
	[Код ТТ],
	[Код продукции],	
	[Дата],
	[Отгрузки шт],		
	[Цена],
	[ID],
	[Маркировка по пред цене],
	[Цена пред],
	[Макс Цена за тек месяц],
	[Маркировка по макс цене за тек месяц],
	[Предыдущая Маркировка по пред цене],
	(CASE
		WHEN [Маркировка по пред цене] = 1 THEN
			1
		WHEN [Маркировка по пред цене] <> 1 AND [Предыдущая Маркировка по пред цене] = 1 AND [Маркировка по макс цене за тек месяц] = 1 THEN
			1
		ELSE
			0
	END) AS [КОРРЕКТ Маркировка по пред цене]
FROM
	cte2
ORDER BY	
	[Код ТТ],
	[Код продукции],
	[ID]
OPTION (MAXRECURSION 0)
+Не улавливается для строки Код ТТ2 + SKU1 + 2014-02-05
Код ТТ Код продукции Дата Отгрузки шт Цена ID Маркировка по пред цене Цена пред Макс Цена за тек месяц Маркировка по макс цене за тек месяц Предыдущая Маркировка по пред цене КОРРЕКТ Маркировка по пред цене
Код ТТ1 SKU1 2014-01-01 1 100 1 0 NULL 100 0 NULL0
Код ТТ1 SKU1 2014-01-02 2 100 2 0 100 100 0 0 0
Код ТТ1 SKU1 2014-01-03 1 903 1 100 100 1 0 1
Код ТТ1 SKU1 2014-01-04 1 100 4 0 90 100 0 1 0
Код ТТ1 SKU1 2014-01-05 1 100 5 0 100 100 0 0 0
Код ТТ1 SKU1 2014-01-08 1 100 6 0 100 100 0 0 0
Код ТТ1 SKU2 2014-01-01 1 100 1 0 NULL 100 0 NULL 0
Код ТТ1 SKU2 2014-01-02 2 100 2 0 100 1000 0 0
Код ТТ1 SKU2 2014-01-03 1 90 3 1 100 100 1 0 1
Код ТТ1 SKU2 2014-01-04 1 80 4 1 90 100 1 1 1
Код ТТ1 SKU2 2014-01-05 1 90 5 0 80 100 1 1 1
Код ТТ2 SKU1 2014-01-01 1 100 1 0 NULL 100 0 NULL 0
Код ТТ2 SKU1 2014-01-02 2 100 2 0 100 100 0 0 0
Код ТТ2 SKU1 2014-02-03 1 90 3 1 100 90 0 0 1
Код ТТ2 SKU1 2014-02-04 1 80 4 1 90 90 1 1 1
Код ТТ2 SKU1 2014-02-05 1 90 5 0 80 90 0 1 0
Код ТТ2 SKU1 2014-03-08 1 100 6 0 90 100 0 0 0
Маркировать по максимальной цене можно по разному:
- за текущий месяц (используется в данном SQL-запросе)
- за предыдущие n-ые месяца и текущий месяц
- за текущий месяц и следующие n-месяца
- за предыдущие n-ые месяца, за текущий месяц и за следующие -ые месяца.
Но если увеличивать период для маркировки максимальной цены, данная маркировка становится не точной.

Кажется слишком усложняю. Чтобы уловить двойное снижение цен нужно ли было использовать маркировку по максимальной цене? Достаточно ли было воспользоваться первой маркировкой? Если да, то как это сделать?
11 май 18, 14:15    [21403106]     Ответить | Цитировать Сообщить модератору
 Re: Двойное снижение цен  [new]
Владислав Колосов
Member

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

Вы, вообще, какую задачу решаете? Вам для чего эти вычисления?
13 май 18, 15:18    [21406344]     Ответить | Цитировать Сообщить модератору
 Re: Двойное снижение цен  [new]
ferzmikk
Member

Откуда:
Сообщений: 2031
Владислав Колосов
ferzmikk,

Вы, вообще, какую задачу решаете? Вам для чего эти вычисления?

Из данных строк по [Код ТТ] + [Код продукции] + [Дата] за последние несколько лет промаркировать была ценовая акция или нет. Ценовая акция это снижение цены. Для решения этой задачи использую маркировку по предыдущей отгрузке (строке). Но тут не улавливается двойное снижение цен. Поэтому использую вторую маркировку по максимальной цене за текущий месяц.

Тогда ранее не было поля была акция или нет, поэтому приходится маркировать с помощью таких расчетов.
14 май 18, 07:17    [21407006]     Ответить | Цитировать Сообщить модератору
 Re: Двойное снижение цен  [new]
ferzmikk
Member

Откуда:
Сообщений: 2031
Еще учесть такой фактор как обычное повышение (не после акции).
14 май 18, 08:42    [21407092]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить