Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Правильное использование оконных функции / 16 сообщений из 16, страница 1 из 1
14.06.2018, 15:35
    #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:47
    #39660844
Щукина Анна
Щукина Анна
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Правильное использование оконных функции
ferzmikk,

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

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

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


Почему?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.06.2018, 20:52
    #39660966
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Правильное использование оконных функции
Щукина АннаPartition by [Код ТТ], [Код продукции] - уже делит всё исходное множество на группы с одинаковым "вектором" ([Код ТТ], [Код продукции]). Поэтому проверять в рамках группы равенство предыдущих и последующих [Код ТТ], [Код продукции] - бессмысленно. Они априори будут одинаковы.
Без этого в сравнение может попасть предыдущая или следующая строка другой [Код ТТ] и/или [Код продукции].
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.06.2018, 21:21
    #39660969
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Правильное использование оконных функции
Щукина АннаНа всё про всё - нужен один lead, один lag и case
Сложно представляю пока как должен выглядеть код
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.06.2018, 22:04
    #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
| Ответить | Цитировать | Написать  
15.06.2018, 05:55
    #39661022
Щукина Анна
Щукина Анна
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Правильное использование оконных функции
ferzmikkЩукина АннаPartition by [Код ТТ], [Код продукции] - уже делит всё исходное множество на группы с одинаковым "вектором" ([Код ТТ], [Код продукции]). Поэтому проверять в рамках группы равенство предыдущих и последующих [Код ТТ], [Код продукции] - бессмысленно. Они априори будут одинаковы.
Без этого в сравнение может попасть предыдущая или следующая строка другой [Код ТТ] и/или [Код продукции].вы процитированное вами моё сообщение про partition прочитали? смысл написанного осознали?

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

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

Для начала проштудируйте эту статью . Пусть вас не смущает, что она по ораклу, а функции там называют "аналитическими". Это так исторически сложилось... Суть и логика реализации от этого - не меняется. Оно одинаково работает что в оракле, что в мс скл, что в DB/2 и прочих СУДБ...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.06.2018, 08:14
    #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:23
    #39661039
Посетитель
Посетитель
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Правильное использование оконных функции
ferzmikk,

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

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



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



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


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