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

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

Решаю задачку

+Есть исходные данные
Код ТТ Код продукции Дата Цена
Код ТТ1 SKU1 2014-01-01 100
Код ТТ1 SKU1 2014-01-02 110
Код ТТ1 SKU1 2014-01-03 100
Код ТТ1 SKU1 2014-01-04 100
Код ТТ1 SKU1 2014-01-05 120
Код ТТ1 SKU1 2014-01-08 120
Код ТТ1 SKU1 2014-01-10 100
Код ТТ1 SKU2 2014-01-01 100
Код ТТ1 SKU2 2014-01-02 100
Код ТТ1 SKU2 2014-01-03 90
Код ТТ1 SKU2 2014-01-04 80
Код ТТ1 SKU2 2014-01-05 90
Код ТТ2 SKU1 2014-01-01 100
Код ТТ2 SKU1 2014-01-02 100
Код ТТ2 SKU1 2014-02-03 80
Код ТТ2 SKU1 2014-02-04 90
Код ТТ2 SKU1 2014-02-05 80
Код ТТ2 SKU1 2014-03-08 100

+Нужно получить такой результат
Код ТТ Код продукции Дата Цена Отметка
Код ТТ1 SKU1 2014-01-01 100 0
Код ТТ1 SKU1 2014-01-02 110 1
Код ТТ1 SKU1 2014-01-03 100 0
Код ТТ1 SKU1 2014-01-04 100 0
Код ТТ1 SKU1 2014-01-05 120 0
Код ТТ1 SKU1 2014-01-08 120 0
Код ТТ1 SKU1 2014-01-10 100 0
Код ТТ1 SKU2 2014-01-01 100 0
Код ТТ1 SKU2 2014-01-02 100 0
Код ТТ1 SKU2 2014-01-03 90 0
Код ТТ1 SKU2 2014-01-04 80 0
Код ТТ1 SKU2 2014-01-05 90 0
Код ТТ2 SKU1 2014-01-01 100 0
Код ТТ2 SKU1 2014-01-02 100 0
Код ТТ2 SKU1 2014-02-03 80 0
Код ТТ2 SKU1 2014-02-04 90 1
Код ТТ2 SKU1 2014-02-05 80 0
Код ТТ2 SKU1 2014-03-08 100 0
Для каждой ТТ+SKU отмечается та строка единицей в поле "Отметка", если Цена предыдущая и Цена следующая равны, и Текущая цена больше Предыдущей цены. Для каждой ТТ+SKU не отмечается первая и последняя строка.

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

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

WITH Т3 AS (
	SELECT
		[Код ТТ],
		[Код продукции],	
		[Дата],			
		[Цена],
		[ID],
		(CASE
			WHEN [Код ТТ] = [Код ТТ пред]  AND  [Код ТТ] = [Код ТТ след] AND 
				[Код продукции] = [Код продукции пред] AND [Код продукции] = [Код продукции след] AND
				[Цена пред] = [Цена след] AND [Цена пред] IS NOT NULL AND [Цена след] IS NOT NULL AND [Цена] > [Цена пред] THEN
			1
		 ELSE
			0
		END) AS [Отметка]
	FROM
		(SELECT		
			[Код ТТ],
			[Код продукции],	
			[Дата],			
			[Цена],	
			[ID],		
			LAG([Цена]) OVER(Partition by [Код ТТ], [Код продукции] ORDER BY ID) [Цена пред],
			LEAD([Цена]) OVER(Partition by [Код ТТ], [Код продукции] ORDER BY ID) [Цена след],
			LAG([Код ТТ]) OVER(Partition by [Код ТТ], [Код продукции] ORDER BY ID) [Код ТТ пред],
			LEAD([Код ТТ]) OVER(Partition by [Код ТТ], [Код продукции] ORDER BY ID) [Код ТТ след],
			LAG([Код продукции]) OVER(Partition by [Код ТТ], [Код продукции] ORDER BY ID) [Код продукции пред],
			LEAD([Код продукции]) OVER(Partition by [Код ТТ], [Код продукции] ORDER BY ID) [Код продукции след]
		FROM
			(SELECT		
				[Код ТТ],
				[Код продукции],	
				[Дата],			
				[Цена],
				ROW_NUMBER()over(Partition by [Код ТТ], [Код продукции] order by  Convert(datetime,[Дата],104))	AS [ID]
			FROM
				@Таблица)
			AS Т1 )
		AS Т2
)	

SELECT
	*
FROM
	Т3
ORDER BY	
	[Код ТТ],
	[Код продукции],
	[ID]
Но есть вопрос. Есть сомнение, чую что тут что то не учел. Для данный задачи правильно ли логически написан код в выделенном месте? Как возможно написать оптимальнее?
14 июн 18, 15:35    [21491290]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование оконных функции  [new]
Щукина Анна
Member

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

Очень много лишней работы....
На всё про всё - нужен один lead, один lag и case
14 июн 18, 15:47    [21491338]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование оконных функции  [new]
Щукина Анна
Member

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

Partition by [Код ТТ], [Код продукции] - уже делит всё исходное множество на группы с одинаковым "вектором" ([Код ТТ], [Код продукции]). Поэтому проверять в рамках группы равенство предыдущих и последующих [Код ТТ], [Код продукции] - бессмысленно. Они априори будут одинаковы.
Стало быть, все что нужно - это получить предыдущее - следующее значение цены и проверять их на равенство. Первую / последнюю строку в секции определять по IS NULL-ности одного из значений [lead | lag]: для первой строки в секции lag is null, для последней строки в секции - lead is null...

вот именно потому вам и нужен ровно один lag, ровно один lead и ровно один case для проверок всех "крайних" и "промежуточных" условий
14 июн 18, 16:43    [21491506]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование оконных функции  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
А это вообще за гранью добра и зла.
[Дата] date,
'01.01.2014'
order by  Convert(datetime,[Дата],104)
14 июн 18, 16:49    [21491528]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование оконных функции  [new]
ferzmikk
Member

Откуда:
Сообщений: 2029
Руслан Дамирович
А это вообще за гранью добра и зла.
[Дата] date,
'01.01.2014'
order by  Convert(datetime,[Дата],104)

Почему?
14 июн 18, 20:14    [21492153]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование оконных функции  [new]
ferzmikk
Member

Откуда:
Сообщений: 2029
Щукина Анна
Partition by [Код ТТ], [Код продукции] - уже делит всё исходное множество на группы с одинаковым "вектором" ([Код ТТ], [Код продукции]). Поэтому проверять в рамках группы равенство предыдущих и последующих [Код ТТ], [Код продукции] - бессмысленно. Они априори будут одинаковы.

Без этого в сравнение может попасть предыдущая или следующая строка другой [Код ТТ] и/или [Код продукции].
14 июн 18, 20:52    [21492226]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование оконных функции  [new]
ferzmikk
Member

Откуда:
Сообщений: 2029
Щукина Анна
На всё про всё - нужен один lead, один lag и case

Сложно представляю пока как должен выглядеть код
14 июн 18, 21:21    [21492267]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование оконных функции  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
ferzmikk
Сложно представляю пока как должен выглядеть код
select
 [Код ТТ],
 [Код продукции],
 Дата,
 Цена,
 case
  when
   lag(Цена) over (partition by [Код ТТ], [Код продукции] order by Дата) = lead(Цена) over (partition by [Код ТТ], [Код продукции] order by Дата) and
   Цена > lag(Цена) over (partition by [Код ТТ], [Код продукции] order by Дата)
  then 1
  else 0
 end as Отметка
from
 @Таблица;
14 июн 18, 22:04    [21492326]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование оконных функции  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1466
ferzmikk
Щукина Анна
Partition by [Код ТТ], [Код продукции] - уже делит всё исходное множество на группы с одинаковым "вектором" ([Код ТТ], [Код продукции]). Поэтому проверять в рамках группы равенство предыдущих и последующих [Код ТТ], [Код продукции] - бессмысленно. Они априори будут одинаковы.

Без этого в сравнение может попасть предыдущая или следующая строка другой [Код ТТ] и/или [Код продукции].
вы процитированное вами моё сообщение про partition прочитали? смысл написанного осознали?

На всякий случай повторюсь - предложение partition by разбивает всю выборку на отдельные секции (считайте, что это аналог group by для агрегатных функций). Оконная функция ВСЕГДА работает в рамках текущей секции, так же как и агрегатная всегда считает агрегаты в рамках заданного условия group by. Если вы указали Partition by [Код ТТ], [Код продукции], то оконная функция физически не сможет увидеть "предыдущая или следующая строка другой [Код ТТ] и/или [Код продукции]", так как она заведомо ограничена рамками секции, задаваемыми сочетанием значений пар ([Код ТТ] и [Код продукции]).

В интернете много информации по логике работы оконных функций. Почитайте. Всё доступно расписано. Не думаю, что мне удастся объяснить вам более доступно.... Но если что - обращайтесь. Постараюсь всё "разжевать" и "показать на пальцах"

Для начала проштудируйте эту статью. Пусть вас не смущает, что она по ораклу, а функции там называют "аналитическими". Это так исторически сложилось... Суть и логика реализации от этого - не меняется. Оно одинаково работает что в оракле, что в мс скл, что в DB/2 и прочих СУДБ...
15 июн 18, 05:55    [21492529]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование оконных функции  [new]
ferzmikk
Member

Откуда:
Сообщений: 2029
invm
ferzmikk
Сложно представляю пока как должен выглядеть код
select
 [Код ТТ],
 [Код продукции],
 Дата,
 Цена,
 case
  when
   lag(Цена) over (partition by [Код ТТ], [Код продукции] order by Дата) = lead(Цена) over (partition by [Код ТТ], [Код продукции] order by Дата) and
   Цена > lag(Цена) over (partition by [Код ТТ], [Код продукции] order by Дата)
  then 1
  else 0
 end as Отметка
from
 @Таблица;

А как правильно добавить следующую запись
ROW_NUMBER()over(Partition by [Код ТТ], [Код продукции] order by  Convert(datetime,[Дата],104))	AS [ID]

чтобы получить следующий результат?
+ Нужно получить такой результат
Код ТТ Код продукции Дата Цена ОтметкаID
Код ТТ1 SKU1 2014-01-01 100 01
Код ТТ1 SKU1 2014-01-02 110 1NULL
Код ТТ1 SKU1 2014-01-03 100 02
Код ТТ1 SKU1 2014-01-04 100 03
Код ТТ1 SKU1 2014-01-05 120 04
Код ТТ1 SKU1 2014-01-08 120 05
Код ТТ1 SKU1 2014-01-10 100 06
Код ТТ1 SKU2 2014-01-01 100 01
Код ТТ1 SKU2 2014-01-02 100 02
Код ТТ1 SKU2 2014-01-03 90 03
Код ТТ1 SKU2 2014-01-04 80 04
Код ТТ1 SKU2 2014-01-05 90 05
Код ТТ2 SKU1 2014-01-01 100 01
Код ТТ2 SKU1 2014-01-02 100 02
Код ТТ2 SKU1 2014-02-03 80 03
Код ТТ2 SKU1 2014-02-04 90 1NULL
Код ТТ2 SKU1 2014-02-05 80 04
Код ТТ2 SKU1 2014-03-08 100 05
То есть не нумерует те строки, которые в поле "Отметка" имеет значение 1.
15 июн 18, 08:14    [21492625]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование оконных функции  [new]
Посетитель
Member

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

в partition by добавляете отметку, а сам row_number() убираете внутрь кейса по полю отметка.
идея понятна?
15 июн 18, 08:23    [21492633]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование оконных функции  [new]
ferzmikk
Member

Откуда:
Сообщений: 2029
Посетитель
ferzmikk,

в partition by добавляете отметку, а сам row_number() убираете внутрь кейса по полю отметка.
идея понятна?

Нет
15 июн 18, 08:47    [21492673]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование оконных функции  [new]
Посетитель
Member

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

печально.

select *
      ,case [Отметка] 
              when 0 
              then ROW_NUMBER()over(Partition by [Код ТТ], [Код продукции],[Отметка] order by  Convert(datetime,[Дата],104))	
       end AS [ID] 
  from (select
         [Код ТТ],
         [Код продукции],
         Дата,
         Цена,
         case
          when
           lag(Цена) over (partition by [Код ТТ], [Код продукции] order by Дата) = lead(Цена) over (partition by [Код ТТ], [Код продукции] order by Дата) and
           Цена > lag(Цена) over (partition by [Код ТТ], [Код продукции] order by Дата)
          then 1
          else 0
         end as [Отметка]
        from
         @Таблица) a;
15 июн 18, 08:54    [21492680]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование оконных функции  [new]
Посетитель
Member

Откуда:
Сообщений: 1384
ferzmikk ,
и вообще, зачем вам вот это?
 Convert(datetime,[Дата],104)


дата изначально в строковом формате что ли?
15 июн 18, 08:56    [21492686]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование оконных функции  [new]
ferzmikk
Member

Откуда:
Сообщений: 2029
Посетитель
ferzmikk ,
и вообще, зачем вам вот это?
 Convert(datetime,[Дата],104)


дата изначально в строковом формате что ли?

Да
15 июн 18, 12:23    [21493398]     Ответить | Цитировать Сообщить модератору
 Re: Правильное использование оконных функции  [new]
ferzmikk
Member

Откуда:
Сообщений: 2029
Всем спасибо!
15 июн 18, 14:15    [21493842]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить