ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Правильное использование оконных функции
16 сообщений из 16, страница 1 из 1
Правильное использование оконных функции
    #39660835
ferzmikk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ferzmikk
Участник
Здравствуйте!

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

 
Есть исходные данныеКод ТТ Код продукции Дата ЦенаКод ТТ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-запрос
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
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]

Но есть вопрос. Есть сомнение, чую что тут что то не учел. Для данный задачи правильно ли логически написан код в выделенном месте? Как возможно написать оптимальнее?
...
Рейтинг: 0 / 0
14.06.2018, 15:35
| Ответить | Цитировать | Написать  
Правильное использование оконных функции
    #39660844
Фотография Щукина Анна
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Щукина Анна
Участник
ferzmikk,

Очень много лишней работы....
На всё про всё - нужен один lead, один lag и case
...
Рейтинг: 0 / 0
14.06.2018, 15:47
| Ответить | Цитировать | Написать  
Правильное использование оконных функции
    #39660873
Фотография Щукина Анна
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Щукина Анна
Участник
ferzmikk,

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

вот именно потому вам и нужен ровно один lag, ровно один lead и ровно один case для проверок всех "крайних" и "промежуточных" условий
...
Рейтинг: 0 / 0
14.06.2018, 16:43
| Ответить | Цитировать | Написать  
Правильное использование оконных функции
    #39660879
Руслан Дамирович
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Руслан Дамирович
Участник
А это вообще за гранью добра и зла.
Код: sql
1.
2.
3.
[Дата] date,
'01.01.2014'
order by  Convert(datetime,[Дата],104)
...
Рейтинг: 0 / 0
14.06.2018, 16:49
| Ответить | Цитировать | Написать  
Правильное использование оконных функции
    #39660955
ferzmikk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ferzmikk
Участник
Руслан ДамировичА это вообще за гранью добра и зла.
Код: sql
1.
2.
3.
[Дата] date,
'01.01.2014'
order by  Convert(datetime,[Дата],104)


Почему?
...
Рейтинг: 0 / 0
14.06.2018, 20:14
| Ответить | Цитировать | Написать  
Правильное использование оконных функции
    #39660966
ferzmikk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ferzmikk
Участник
Щукина АннаPartition by [Код ТТ], [Код продукции] - уже делит всё исходное множество на группы с одинаковым "вектором" ([Код ТТ], [Код продукции]). Поэтому проверять в рамках группы равенство предыдущих и последующих [Код ТТ], [Код продукции] - бессмысленно. Они априори будут одинаковы.
Без этого в сравнение может попасть предыдущая или следующая строка другой [Код ТТ] и/или [Код продукции].
...
Рейтинг: 0 / 0
14.06.2018, 20:52
| Ответить | Цитировать | Написать  
Правильное использование оконных функции
    #39660969
ferzmikk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ferzmikk
Участник
Щукина АннаНа всё про всё - нужен один lead, один lag и case
Сложно представляю пока как должен выглядеть код
...
Рейтинг: 0 / 0
14.06.2018, 21:21
| Ответить | Цитировать | Написать  
Правильное использование оконных функции
    #39660982
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
ferzmikkСложно представляю пока как должен выглядеть код
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
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
 @Таблица;
...
Рейтинг: 0 / 0
14.06.2018, 22:04
| Ответить | Цитировать | Написать  
Правильное использование оконных функции
    #39661022
Фотография Щукина Анна
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Щукина Анна
Участник
ferzmikkЩукина АннаPartition by [Код ТТ], [Код продукции] - уже делит всё исходное множество на группы с одинаковым "вектором" ([Код ТТ], [Код продукции]). Поэтому проверять в рамках группы равенство предыдущих и последующих [Код ТТ], [Код продукции] - бессмысленно. Они априори будут одинаковы.
Без этого в сравнение может попасть предыдущая или следующая строка другой [Код ТТ] и/или [Код продукции].вы процитированное вами моё сообщение про partition прочитали? смысл написанного осознали?

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

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

Для начала проштудируйте эту статью . Пусть вас не смущает, что она по ораклу, а функции там называют "аналитическими". Это так исторически сложилось... Суть и логика реализации от этого - не меняется. Оно одинаково работает что в оракле, что в мс скл, что в DB/2 и прочих СУДБ...
...
Рейтинг: 0 / 0
15.06.2018, 05:55
| Ответить | Цитировать | Написать  
Правильное использование оконных функции
    #39661037
ferzmikk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ferzmikk
Участник
invmferzmikkСложно представляю пока как должен выглядеть код
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
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
 @Таблица;


А как правильно добавить следующую запись
Код: sql
1.
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 1 NULL Код ТТ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 1 NULL Код ТТ2 SKU1 2014-02-05 80 04Код ТТ2 SKU1 2014-03-08 100 05
То есть не нумерует те строки, которые в поле "Отметка" имеет значение 1.
...
Рейтинг: 0 / 0
15.06.2018, 08:14
| Ответить | Цитировать | Написать  
Правильное использование оконных функции
    #39661039
Посетитель
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Посетитель
Участник
ferzmikk,

в partition by добавляете отметку, а сам row_number() убираете внутрь кейса по полю отметка.
идея понятна?
...
Рейтинг: 0 / 0
15.06.2018, 08:23
| Ответить | Цитировать | Написать  
Правильное использование оконных функции
    #39661049
ferzmikk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ferzmikk
Участник
Посетительferzmikk,

в partition by добавляете отметку, а сам row_number() убираете внутрь кейса по полю отметка.
идея понятна?
Нет
...
Рейтинг: 0 / 0
15.06.2018, 08:47
| Ответить | Цитировать | Написать  
Правильное использование оконных функции
    #39661050
Посетитель
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Посетитель
Участник
ferzmikk,

печально.

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
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;
...
Рейтинг: 0 / 0
15.06.2018, 08:54
| Ответить | Цитировать | Написать  
Правильное использование оконных функции
    #39661053
Посетитель
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Посетитель
Участник
ferzmikk ,
и вообще, зачем вам вот это?
Код: sql
1.
 Convert(datetime,[Дата],104)



дата изначально в строковом формате что ли?
...
Рейтинг: 0 / 0
15.06.2018, 08:56
| Ответить | Цитировать | Написать  
Правильное использование оконных функции
    #39661208
ferzmikk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ferzmikk
Участник
Посетительferzmikk ,
и вообще, зачем вам вот это?
Код: sql
1.
 Convert(datetime,[Дата],104)



дата изначально в строковом формате что ли?
Да
...
Рейтинг: 0 / 0
15.06.2018, 12:23
| Ответить | Цитировать | Написать  
Правильное использование оконных функции
    #39661278
ferzmikk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ferzmikk
Участник
Всем спасибо!
...
Рейтинг: 0 / 0
15.06.2018, 14:15
| Ответить | Цитировать | Написать  
16 сообщений из 16, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Правильное использование оконных функции
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]