Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Три варианта определения максимальных значений / 25 сообщений из 26, страница 1 из 2
  1  все
16.04.2018, 09:47
    #39630604
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Три варианта определения максимальных значений
Здравствуйте!

 
Есть исходная таблицаКод ТТ SKU Дата ЦенаКод ТТ1 12344 2018-01-08 120Код ТТ1 12344 2018-01-10 110Код ТТ1 12344 2018-01-27 190Код ТТ1 12344 2018-02-02 180Код ТТ1 12344 2018-02-17 110Код ТТ1 12344 2018-03-08 120Код ТТ1 12344 2018-04-01 130Код ТТ1 12344 2018-04-03 140Код ТТ1 12344 2018-06-05 170Код ТТ1 12350 2018-01-08 120Код ТТ1 12350 2018-01-10 130Код ТТ1 12350 2018-01-27 190Код ТТ1 12350 2018-02-02 180Код ТТ1 12350 2018-02-17 110Код ТТ1 12350 2018-03-08 120Код ТТ1 12350 2018-04-01 130Код ТТ1 12350 2018-04-03 140Код ТТ1 12350 2018-06-05 110

Задача. Нужно добавить три вычисляемые меры для каждого ТТ+SKU:
1. Максимальная цена за соответствующий месяц
2. Максимальная цена за соответствующий и предыдущий от соответствующего месяца
3. Максимальная цена за входящий интервал в периоде

Для 2 и 3 пункта в коде в комментарий есть пример для ясности.

Начал писать SQL-запрос. С первым вычисляемым полем разобрался. С вторым и третьим в тупик.

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.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
DECLARE @Таблица table (
	[Код ТТ] nvarchar(8),
	[SKU] nvarchar(8),
	[Дата] date,
	[Цена] decimal(8,0)
);

INSERT INTO
  @Таблица
VALUES 
('Код ТТ1','12344','08.01.2018',120),
('Код ТТ1','12344','10.01.2018',110),
('Код ТТ1','12344','27.01.2018',190),
('Код ТТ1','12344','02.02.2018',180),
('Код ТТ1','12344','17.02.2018',110),
('Код ТТ1','12344','08.03.2018',120),
('Код ТТ1','12344','01.04.2018',130),
('Код ТТ1','12344','03.04.2018',140),
('Код ТТ1','12344','05.06.2018',170),
('Код ТТ1','12350','08.01.2018',120),
('Код ТТ1','12350','10.01.2018',130),
('Код ТТ1','12350','27.01.2018',190),
('Код ТТ1','12350','02.02.2018',180),
('Код ТТ1','12350','17.02.2018',110),
('Код ТТ1','12350','08.03.2018',120),
('Код ТТ1','12350','01.04.2018',130),
('Код ТТ1','12350','03.04.2018',140),
('Код ТТ1','12350','05.06.2018',110)
;

--Определяем номер месяца и год для последующей группировки
WITH ТаблицаОсновная AS (
	SELECT
		Т1.[Код ТТ],
		Т1.[SKU],
		Т1.[Дата],
		Т1.[Цена],
		DATEFROMPARTS (YEAR(Т1.[Дата]), MONTH(Т1.[Дата]),1) AS [Месяц и Год]
	FROM
		@Таблица AS Т1
),

--Вычисляемое поле 1.
--Максимальная цена за соответствующий месяц
Таблица1_1 AS (
	SELECT
		Т1.[Код ТТ],
		Т1.[SKU],			
		Т1.[Месяц и Год],		
		Max(Т1.[Цена]) AS [Макс цена за соответствующий месяц]
	FROM
		ТаблицаОсновная Т1
	GROUP BY
		Т1.[Код ТТ],
		Т1.[SKU],			
		Т1.[Месяц и Год]		
),

--К основной таблице привязываем результат первого вычисляемого поля
Таблица1_2 AS (
	SELECT
		Т1.[Код ТТ],
		Т1.[SKU],
		Т1.[Дата],
		Т1.[Цена],
		Т1.[Месяц и Год],		
		Т2.[Макс цена за соответствующий месяц]
	FROM
		ТаблицаОсновная Т1
	LEFT JOIN
		Таблица1_1 Т2
	ON
		Т1.[Код ТТ] = Т2.[Код ТТ]
		AND
		Т1.[SKU] = Т2.[SKU]	
		AND
		Т1.[Месяц и Год] = Т2.[Месяц и Год]
		
),

--Вычисляемое поле 2
--Максимальная цена за 2 месяца, а именно, за соответствующий месяц и предыдущий от соответствующего месяца.
--Для первого месяца только соответствующий месяц

--Примеры:

--Если начало периода это январь 2018 года. Для января 2018 года берется только январь 2018.
--Для ферваля 2018 берется только январь и февраль 2018 г. Для февраля 2018 берется февраль и март 2018.

--Если, начало периода декабрь 2017.  Для декабря 2017 г берется декабрь 2017.
--Для января 2018 берется декабрь 2017 и Январь 2018. Для февраля 2018 берется январь и февраль 2018.

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


--Добавляем нумерацию по месяцам и годам
Таблица2_1 AS (
	SELECT
		Т2.[Месяц и Год],
		ROW_NUMBER()over(order by  Т2.[Месяц и Год]) AS ID
	FROM
		(SELECT DISTINCT
			Т1.[Месяц и Год]			
		FROM	 	
			ТаблицаОсновная Т1) Т2
),

--К основной таблице привязываем нумерацию по месяцам и годам
Таблица2_2 AS (
	SELECT
		Т1.[Код ТТ],
		Т1.[SKU],
		Т1.[Дата],
		Т1.[Цена],
		Т1.[Месяц и Год],
		Т2.ID
	FROM
		ТаблицаОсновная Т1
	LEFT JOIN
		Таблица2_1 Т2
	ON
		Т1.[Месяц и Год] = Т2.[Месяц и Год]
	)

--...


--Вычисляемое поле 3.
--Период делится на интервалы - по 2 месяца. Максимальная цена определяется за соответствующий период.

--Примеры:
--Если с января 2018 начинать, то это Январь-Февраль 2018, Март-Апрель 2018, Май-Июнь 2018, и т.д.
--Если начинать с декабря 2017, то это Декабрь 2017-Январь 2018, Февраль-Март 2018, Апрель-май 2018, и т.д.

--Если период не кратен 2, то последний месяц в периоде как последний (остаточный) интервал

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

--...

--Результат выгрузки
--Пока результат по первому вычисляемому полю
SELECT * FROM Таблица1_2 ORDER BY [Код ТТ], [SKU], Convert(datetime,[Дата],104)


Как правильно написать?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.04.2018, 09:50
    #39630605
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Три варианта определения максимальных значений
ferzmikkМаксимальная цена за
Код: sql
1.
MAX(CASE WHEN дата BETWEEN начало AND конец THEN цена END)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.04.2018, 14:53
    #39630848
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Три варианта определения максимальных значений
AkinaferzmikkМаксимальная цена за
Код: sql
1.
MAX(CASE WHEN дата BETWEEN начало AND конец THEN цена END)


Написал такой запрос
 
Код: 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.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
DECLARE @Таблица table (
	[Код ТТ] nvarchar(8),
	[SKU] nvarchar(8),
	[Дата] date,
	[Цена] decimal(8,0)
);

INSERT INTO
  @Таблица
VALUES 
('Код ТТ1','12344','08.01.2018',120),
('Код ТТ1','12344','10.01.2018',200),
('Код ТТ1','12344','27.01.2018',160),
('Код ТТ1','12344','02.02.2018',190),
('Код ТТ1','12344','17.02.2018',110),
('Код ТТ1','12344','08.03.2018',120),
('Код ТТ1','12344','01.04.2018',130),
('Код ТТ1','12344','03.04.2018',140),
('Код ТТ1','12344','05.06.2018',170),
('Код ТТ1','12350','08.01.2018',120),
('Код ТТ1','12350','10.01.2018',130),
('Код ТТ1','12350','27.01.2018',190),
('Код ТТ1','12350','02.02.2018',180),
('Код ТТ1','12350','17.02.2018',110),
('Код ТТ1','12350','08.03.2018',180),
('Код ТТ1','12350','01.04.2018',130),
('Код ТТ1','12350','03.04.2018',140),
('Код ТТ1','12350','05.06.2018',240)
;

DECLARE @ДатаНачала as date;


--Определяем номер месяца и год для последующей группировки
WITH ТаблицаОсновная AS (
	SELECT
		Т1.[Код ТТ],
		Т1.[SKU],
		Т1.[Дата],
		Т1.[Цена],
		DATEFROMPARTS (YEAR(Т1.[Дата]), MONTH(Т1.[Дата]),1) AS [Месяц и Год]
	FROM
		@Таблица AS Т1
),

--Вычисляемое поле 1.
Таблица1 AS (
	SELECT
		ТТ1.[Код ТТ],
		ТТ1.[SKU],
		ТТ1.[Дата],
		ТТ1.[Цена],
		ТТ1.[Месяц и Год],		
		ТТ2.[Макс цена за соответ месяц]
	FROM
		ТаблицаОсновная ТТ1
	LEFT JOIN
		(SELECT
			Т1.[Код ТТ],
			Т1.[SKU],			
			Т1.[Месяц и Год],		
			Max(Т1.[Цена]) AS [Макс цена за соответ месяц]
		FROM
			ТаблицаОсновная Т1
		GROUP BY
			Т1.[Код ТТ],
			Т1.[SKU],			
			Т1.[Месяц и Год]) ТТ2
	ON
		ТТ1.[Код ТТ] = ТТ2.[Код ТТ]
		AND
		ТТ1.[SKU] = ТТ2.[SKU]	
		AND
		ТТ1.[Месяц и Год] = ТТ2.[Месяц и Год]
		
),

--Вычисляемое поле 2
--Максимальная цена за 2 месяца, а именно, за соответствующий месяц и предыдущий от соответствующего месяца.
--Для первого месяца только соответствующий месяц

--Примеры:

--Если начало периода это январь 2018 года. Для января 2018 года берется только январь 2018.
--Для ферваля 2018 берется только январь и февраль 2018 г. Для февраля 2018 берется февраль и март 2018.

--Если, начало периода декабрь 2017.  Для декабря 2017 г берется декабрь 2017.
--Для января 2018 берется декабрь 2017 и Январь 2018. Для февраля 2018 берется январь и февраль 2018.

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


Таблица2_1 AS (
	SELECT		
		Т1.[Код ТТ],
		Т1.[SKU],
		Т1.[Дата],
		Т1.[Цена],
		Т1.[Месяц и Год],
		Т1.[Макс цена за соответ месяц],
		(CASE
			WHEN Т1.[Месяц и Год] = (SELECT MIN([Месяц и Год]) FROM ТаблицаОсновная) THEN Т1.[Месяц и Год]
			WHEN Т1.[Месяц и Год] > (SELECT MIN([Месяц и Год]) FROM ТаблицаОсновная) THEN DATEADD(MONTH, -1,Т1.[Месяц и Год])
		END) AS [Начало],
		Т1.[Месяц и Год] AS [Конец]
	FROM		
		Таблица1 Т1
),

Таблица2_2 AS (
	SELECT
		ТТ1.[Код ТТ],
		ТТ1.[SKU],
		ТТ1.[Дата],
		ТТ1.[Цена],
		ТТ1.[Месяц и Год],		
		ТТ1.[Макс цена за соответ месяц],
		ТТ2.[Макс цена за соответ пред месяц],		
		ТТ1.Начало,
		ТТ1.Конец
	FROM
		Таблица2_1 ТТ1
	LEFT JOIN
		(SELECT
			Т1.[Код ТТ],
			Т1.[SKU],			
			Т1.[Начало],
			Т1.[Конец],			
			MAX(CASE WHEN Т1.[Дата] BETWEEN Т1.[Начало] AND Т1.[Конец] THEN Т1.[Цена] END) AS [Макс цена за соответ пред месяц]
		FROM			
			Таблица2_1 AS Т1
		GROUP BY 
			Т1.[Код ТТ],
			Т1.[SKU],			
			Т1.[Начало],
			Т1.[Конец]) AS ТТ2
	ON
		ТТ1.[Код ТТ] = ТТ2.[Код ТТ]
		AND
		ТТ1.[SKU] = ТТ2.[SKU]	
		AND
		ТТ1.Начало = ТТ2.Начало
		AND
		ТТ1.Конец = ТТ2.Конец
	)

--...


--Вычисляемое поле 3.
--Период делится на интервалы - по 2 месяца. Максимальная цена определяется за соответствующий период.

--Примеры:
--Если с января 2018 начинать, то это Январь-Февраль 2018, Март-Апрель 2018, Май-Июнь 2018, и т.д.
--Если начинать с декабря 2017, то это Декабрь 2017-Январь 2018, Февраль-Март 2018, Апрель-май 2018, и т.д.

--Если период не кратен 2, то последний месяц в периоде как последний (остаточный) интервал

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

--...

--Результат выгрузки
--Пока результат по первому вычисляемому полю

--SELECT * FROM Таблица2_1 ORDER BY [Код ТТ], [SKU], Convert(datetime,[Дата],104)
SELECT * FROM Таблица2_2 ORDER BY [Код ТТ], [SKU], Convert(datetime,[Дата],104)


что то результат не такой, какой нужен.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.04.2018, 15:12
    #39630873
a_voronin
a_voronin
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Три варианта определения максимальных значений
ferzmikk,

Конструкцию

Код: sql
1.
MAX(...) OVER (PARTITION BY ...)


применить не хотите ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.04.2018, 15:15
    #39630876
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Три варианта определения максимальных значений
a_voroninferzmikk,

Конструкцию

Код: sql
1.
MAX(...) OVER (PARTITION BY ...)


применить не хотите ?
жутко хитро окно бить для "соответствующий и предыдущий от соответствующего месяца"
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.04.2018, 15:25
    #39630888
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Три варианта определения максимальных значений
Все таки, почему поле [Макс цена за соответ пред месяц] не корректно считает: где то Null, где то не корректное значение?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.04.2018, 15:41
    #39630901
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Три варианта определения максимальных значений
ferzmikkНаписал такой запрос
Ну вообще-то предполагалось, что это будет простейший запрос типа
Код: sql
1.
2.
3.
SELECT ТТ, SKU, MAX(CASE для первого условия), MAX(CASE для второго условия), ...
FROM @Таблица
GROUP BY ТТ, SKU


а вовсе не этот страховидный [censored], который ты изобразил...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.04.2018, 07:06
    #39631268
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Три варианта определения максимальных значений
ferzmikkSQL-запрос написал как то развернуто, чтобы понятнее было.
AkinaferzmikkНаписал такой запрос
Ну вообще-то предполагалось, что это будет простейший запрос типа
Код: sql
1.
2.
3.
SELECT ТТ, SKU, MAX(CASE для первого условия), MAX(CASE для второго условия), ...
FROM @Таблица
GROUP BY ТТ, SKU


а вовсе не этот страховидный [censored], который ты изобразил...Знаю, что запрос не оптимальный, пока специально сделал так, чтобы по каждой вычисляемой мере был отдельный запрос.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.04.2018, 08:15
    #39631280
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Три варианта определения максимальных значений
AkinaНу вообще-то предполагалось, что это будет простейший запрос типа
Код: sql
1.
2.
3.
SELECT ТТ, SKU, MAX(CASE для первого условия), MAX(CASE для второго условия), ...
FROM @Таблица
GROUP BY ТТ, SKU


По логике максимальную цену надо находить не по ТТ+SKU, а по ТТ+SKU+[Месяц и год] и причем еще обратиться к предыдущей [Месяц и год], если не первый месяц и год в периоде.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.04.2018, 08:19
    #39631282
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Три варианта определения максимальных значений
ferzmikkПо логике максимальную цену надо находить не по ТТ+SKU, а по ТТ+SKU+[Месяц и год] и причем еще обратиться к предыдущей [Месяц и год], если не первый месяц и год в периоде.Мда... может, есть смысл попробовать сначала понять, что тебе ответили, а?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.04.2018, 16:24
    #39632648
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Три варианта определения максимальных значений
Для второго вычисляемого поля
 
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.
80.
81.
82.
83.
DECLARE @Таблица table (
	[Код ТТ] nvarchar(8),
	[SKU] nvarchar(8),
	[Дата] date,
	[Цена] decimal(8,0)
);

INSERT INTO
  @Таблица
VALUES 
('Код ТТ1','12344','08.01.2018',120),
('Код ТТ1','12344','10.01.2018',100),
('Код ТТ1','12344','27.01.2018',320),
('Код ТТ1','12344','02.02.2018',190),
('Код ТТ1','12344','17.02.2018',310),
('Код ТТ1','12344','08.03.2018',290),
('Код ТТ1','12344','01.04.2018',110),
('Код ТТ1','12344','03.04.2018',90),
('Код ТТ1','12344','05.06.2018',170),
('Код ТТ1','12350','08.01.2018',120),
('Код ТТ1','12350','10.01.2018',130),
('Код ТТ1','12350','27.01.2018',190),
('Код ТТ1','12350','02.02.2018',180),
('Код ТТ1','12350','17.02.2018',110),
('Код ТТ1','12350','08.03.2018',180),
('Код ТТ1','12350','01.04.2018',130),
('Код ТТ1','12350','03.04.2018',140),
('Код ТТ1','12350','05.06.2018',240),
('Код ТТ1','12353','01.01.2018',270),
('Код ТТ1','12353','01.02.2018',290),
('Код ТТ1','12353','01.03.2018',300),
('Код ТТ2','12357','01.01.2018',310),
('Код ТТ2','12357','01.02.2018',320),
('Код ТТ2','12357','01.03.2018',330)
;

DECLARE @КолПредМесяцев as integer=1;

WITH
Таблица1 AS(
	SELECT
		Т1.[Код ТТ],
		Т1.[SKU],
		Т1.[Дата],
		Т1.[Цена],
		DATEFROMPARTS (YEAR(Т1.[Дата]), MONTH(Т1.[Дата]),1) AS [Месяц и Год],
		DATEADD(MONTH, -@КолПредМесяцев, DATEFROMPARTS (YEAR(Т1.[Дата]), MONTH(Т1.[Дата]),1))  AS [Месяц и Год пред]
	FROM
		@Таблица AS Т1) 

SELECT
	ТТ1.[Код ТТ],
	ТТ1.[SKU],
	ТТ1.[Дата],
	ТТ1.[Цена],
	ТТ1.[Месяц и Год],
	ТТ1.[Месяц и Год пред],
	ТТ2.[Макс цена]
FROM
	Таблица1 AS ТТ1
OUTER APPLY
	(SELECT
		--Т1.[Код ТТ],
		--Т1.[SKU],
		MAX(Т1.[Цена]) AS [Макс цена] 
	FROM
			Таблица1 Т1
	WHERE
		ТТ1.[Код ТТ] = Т1.[Код ТТ]
		AND
		ТТ1.[SKU] = Т1.[SKU]
		AND
		(ТТ1.[Месяц и Год пред] <= Т1.[Месяц и Год]
		OR
		ТТ1.[Месяц и Год] = Т1.[Месяц и Год])
	--GROUP BY
	--	Т1.[Код ТТ],
	--	Т1.[SKU]
	) AS ТТ2
ORDER BY
	ТТ1.[Код ТТ],
	ТТ1.[SKU],
	Convert(datetime,ТТ1.[Дата],104)

 
РезультатКод ТТ SKU Дата Цена Месяц и Год Месяц и Год пред Макс ценаКод ТТ1 12344 2018-01-08 120 2018-01-01 2017-12-01 320Код ТТ1 12344 2018-01-10 100 2018-01-01 2017-12-01 320Код ТТ1 12344 2018-01-27 320 2018-01-01 2017-12-01 320Код ТТ1 12344 2018-02-02 190 2018-02-01 2018-01-01 320Код ТТ1 12344 2018-02-17 310 2018-02-01 2018-01-01 320Код ТТ1 12344 2018-03-08 290 2018-03-01 2018-02-01 310Код ТТ1 12344 2018-04-01 110 2018-04-01 2018-03-01 290Код ТТ1 12344 2018-04-03 90 2018-04-01 2018-03-01 290Код ТТ1 12344 2018-06-05 170 2018-06-01 2018-05-01 170Код ТТ1 12350 2018-01-08 120 2018-01-01 2017-12-01 240 Код ТТ1 12350 2018-01-10 130 2018-01-01 2017-12-01 240 Код ТТ1 12350 2018-01-27 190 2018-01-01 2017-12-01 240 Код ТТ1 12350 2018-02-02 180 2018-02-01 2018-01-01 240 Код ТТ1 12350 2018-02-17 110 2018-02-01 2018-01-01 240 Код ТТ1 12350 2018-03-08 180 2018-03-01 2018-02-01 240 Код ТТ1 12350 2018-04-01 130 2018-04-01 2018-03-01 240 Код ТТ1 12350 2018-04-03 140 2018-04-01 2018-03-01 240 Код ТТ1 12350 2018-06-05 240 2018-06-01 2018-05-01 240Код ТТ1 12353 2018-01-01 270 2018-01-01 2017-12-01 300 Код ТТ1 12353 2018-02-01 290 2018-02-01 2018-01-01 300 Код ТТ1 12353 2018-03-01 300 2018-03-01 2018-02-01 300Код ТТ2 12357 2018-01-01 310 2018-01-01 2017-12-01 330 Код ТТ2 12357 2018-02-01 320 2018-02-01 2018-01-01 330 Код ТТ2 12357 2018-03-01 330 2018-03-01 2018-02-01 330
Для первого ТТ+SKU корректно работает, а для последующих - нет.

Почему так?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2018, 05:19
    #39632904
Kopelly
Kopelly
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Три варианта определения максимальных значений
ferzmikk,

Для нахождения "2. Максимальная цена за соответствующий и предыдущий от соответствующего месяца" нужно условие
Код: sql
1.
2.
3.
		(ТТ1.[Месяц и Год пред] = Т1.[Месяц и Год]
		OR
		ТТ1.[Месяц и Год] = Т1.[Месяц и Год])
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2018, 05:21
    #39632905
Kopelly
Kopelly
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Три варианта определения максимальных значений
Или для красоты:
Код: sql
1.
Т1.[Месяц и Год] in (ТТ1.[Месяц и Год пред], ТТ1.[Месяц и Год])
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2018, 07:28
    #39632922
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Три варианта определения максимальных значений
Kopelly,

Да, работает.

Но если задать
Код: sql
1.
DECLARE @КолПредМесяцев as integer=2;

то есть брать не 1, а 2 предыдущих месяца от соответствующего, то не корректно срабатывает.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2018, 07:48
    #39632928
Kopelly
Kopelly
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Три варианта определения максимальных значений
ferzmikk,

Код: sql
1.
Т1.[Месяц и Год] between ТТ1.[Месяц и Год пред] and ТТ1.[Месяц и Год]
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2018, 07:57
    #39632932
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Три варианта определения максимальных значений
Kopelly,

Спасибо, получилось!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2018, 11:03
    #39633026
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Три варианта определения максимальных значений
TaPaKa_voroninferzmikk,

Конструкцию

Код: sql
1.
MAX(...) OVER (PARTITION BY ...)


применить не хотите ?
жутко хитро окно бить для "соответствующий и предыдущий от соответствующего месяца"
Использую такой вариант
 
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.
DECLARE @Таблица table (
	[Код ТТ] nvarchar(8),
	[SKU] nvarchar(8),
	[Дата] date,
	[Цена] decimal(8,0)
);

INSERT INTO
  @Таблица
VALUES 
('Код ТТ1','12344','08.01.2018',120),
('Код ТТ1','12344','10.01.2018',100),
('Код ТТ1','12344','27.01.2018',320),
('Код ТТ1','12344','02.02.2018',190),
('Код ТТ1','12344','17.02.2018',330),
('Код ТТ1','12344','08.03.2018',290),
('Код ТТ1','12344','01.04.2018',180),
('Код ТТ1','12344','03.04.2018',90),
('Код ТТ1','12344','05.06.2018',170),
('Код ТТ1','12350','08.01.2018',120),
('Код ТТ1','12350','10.01.2018',130),
('Код ТТ1','12350','27.01.2018',190),
('Код ТТ1','12350','02.02.2018',180),
('Код ТТ1','12350','17.02.2018',110),
('Код ТТ1','12350','08.03.2018',180),
('Код ТТ1','12350','01.04.2018',130),
('Код ТТ1','12350','03.04.2018',140),
('Код ТТ1','12350','05.06.2018',240),
('Код ТТ1','12353','01.01.2018',270),
('Код ТТ1','12353','01.02.2018',290),
('Код ТТ1','12353','01.03.2018',300),
('Код ТТ2','12357','01.01.2018',310),
('Код ТТ2','12357','01.02.2018',320),
('Код ТТ2','12357','01.03.2018',330)
;

DECLARE @КолПредМесяцев as integer=2;

WITH
Таблица1 AS(
	SELECT
		Т1.[Код ТТ],
		Т1.[SKU],
		Т1.[Дата],
		Т1.[Цена],
		DATEFROMPARTS (YEAR(Т1.[Дата]), MONTH(Т1.[Дата]),1) AS [Месяц и Год],
		DATEADD(MONTH, -@КолПредМесяцев, DATEFROMPARTS (YEAR(Т1.[Дата]), MONTH(Т1.[Дата]),1))  AS [Месяц и Год пред]
	FROM
		@Таблица AS Т1) 

SELECT
	ТТ1.[Код ТТ],
	ТТ1.[SKU],
	ТТ1.[Дата],
	ТТ1.[Цена],
	ТТ1.[Месяц и Год],
	ТТ1.[Месяц и Год пред],
	MAX(CASE WHEN ТТ1.[Месяц и Год] between ТТ1.[Месяц и Год пред] and ТТ1.[Месяц и Год] THEN
			ТТ1.[Цена]
		END) OVER (PARTITION BY ТТ1.[Код ТТ], ТТ1.[SKU]) AS [Макс цена]	
FROM
	Таблица1 AS ТТ1
ORDER BY
	ТТ1.[Код ТТ],
	ТТ1.[SKU],
	Convert(datetime,ТТ1.[Дата],104)

 
РезультатКод ТТ SKU Дата Цена Месяц и Год Месяц и Год пред Макс ценаКод ТТ1 12344 2018-01-08 120 2018-01-01 2017-11-01 330Код ТТ1 12344 2018-01-10 100 2018-01-01 2017-11-01 330Код ТТ1 12344 2018-01-27 320 2018-01-01 2017-11-01 330Код ТТ1 12344 2018-02-02 190 2018-02-01 2017-12-01 330Код ТТ1 12344 2018-02-17 330 2018-02-01 2017-12-01 330Код ТТ1 12344 2018-03-08 290 2018-03-01 2018-01-01 330Код ТТ1 12344 2018-04-01 180 2018-04-01 2018-02-01 330Код ТТ1 12344 2018-04-03 90 2018-04-01 2018-02-01 330Код ТТ1 12344 2018-06-05 170 2018-06-01 2018-04-01 330Код ТТ1 12350 2018-01-08 120 2018-01-01 2017-11-01 240Код ТТ1 12350 2018-01-10 130 2018-01-01 2017-11-01 240Код ТТ1 12350 2018-01-27 190 2018-01-01 2017-11-01 240Код ТТ1 12350 2018-02-02 180 2018-02-01 2017-12-01 240Код ТТ1 12350 2018-02-17 110 2018-02-01 2017-12-01 240Код ТТ1 12350 2018-03-08 180 2018-03-01 2018-01-01 240Код ТТ1 12350 2018-04-01 130 2018-04-01 2018-02-01 240Код ТТ1 12350 2018-04-03 140 2018-04-01 2018-02-01 240Код ТТ1 12350 2018-06-05 240 2018-06-01 2018-04-01 240Код ТТ1 12353 2018-01-01 270 2018-01-01 2017-11-01 300Код ТТ1 12353 2018-02-01 290 2018-02-01 2017-12-01 300Код ТТ1 12353 2018-03-01 300 2018-03-01 2018-01-01 300Код ТТ2 12357 2018-01-01 310 2018-01-01 2017-11-01 330Код ТТ2 12357 2018-02-01 320 2018-02-01 2017-12-01 330Код ТТ2 12357 2018-03-01 330 2018-03-01 2018-01-01 330
Неправильно максимальную цену рассчитывает.

Как правильно написать?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2018, 11:23
    #39633048
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Три варианта определения максимальных значений
ferzmikk,

Код: sql
1.
WHEN ТТ1.[Месяц и Год] between ТТ1.[Месяц и Год пред] and ТТ1.[Месяц и Год]


а в каком случае это даст false?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2018, 11:30
    #39633051
Kopelly
Kopelly
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Три варианта определения максимальных значений
TaPaK,

При @КолПредМесяцев < 0 (причем всегда будет False)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2018, 11:31
    #39633053
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Три варианта определения максимальных значений
KopellyTaPaK,

При @КолПредМесяцев < 0 (причем всегда будет False)
стошнило
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2018, 14:53
    #39633225
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Три варианта определения максимальных значений
TaPaKferzmikk,

Код: sql
1.
WHEN ТТ1.[Месяц и Год] between ТТ1.[Месяц и Год пред] and ТТ1.[Месяц и Год]


а в каком случае это даст false?
а как правильно написать тогда?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2018, 14:56
    #39633226
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Три варианта определения максимальных значений
ferzmikkTaPaKferzmikk,

Код: sql
1.
WHEN ТТ1.[Месяц и Год] between ТТ1.[Месяц и Год пред] and ТТ1.[Месяц и Год]


а в каком случае это даст false?
а как правильно написать тогда?
на вскидку - никак. Окна тут вряд-ли помогут
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.04.2018, 04:54
    #39633477
Kopelly
Kopelly
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Три варианта определения максимальных значений
ferzmikk,

С окнами разве что так:

 
Запрос
Код: 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.
WITH Сдвиги as 
(
Select 0 as [Сдвиг]
Union all
Select [Сдвиг]+1 From Сдвиги
Where [Сдвиг]<@КолПредМесяцев
)
,
Таблица1 AS(
	SELECT
		Т1.[Код ТТ],
		Т1.[SKU],
		Т1.[Дата],
		Т1.[Цена],
		DATEFROMPARTS (YEAR(Т1.[Дата]), MONTH(Т1.[Дата]),1) AS [Месяц и Год],
		MAX(Т1.[Цена]) OVER (PARTITION BY Т1.[Код ТТ], Т1.[SKU],DATEADD(MONTH, [Сдвиг], DATEFROMPARTS (YEAR(Т1.[Дата]), MONTH(Т1.[Дата]),1))) AS [Макс цена],
		[Сдвиг]
	FROM
		@Таблица AS Т1
	cross join Сдвиги)
Select * From 
Таблица1 AS ТТ1
Where [Сдвиг]=0
ORDER BY
	ТТ1.[Код ТТ],
	ТТ1.[SKU],
	Convert(datetime,ТТ1.[Дата],104)

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.04.2018, 09:03
    #39633523
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Три варианта определения максимальных значений
дались эти окна :)

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
SELECT 
*
FROM @Таблица a 
CROSS APPLY 
(
	SELECT MAX(b.[Цена]) as MaxCost
	FROM @Таблица b
	WHERE 
		b.[Код ТТ]	= a.[Код ТТ]	AND
		b.[SKU]		= b.[SKU]	AND
		b.[Дата]	BETWEEN DATEADD(day,1,EOMONTH(DATEADD(month,-1*(@КолПредМесяцев+1),a.[Дата]))) AND EOMONTH (a.[Дата])
)  b
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.04.2018, 10:40
    #39633596
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Три варианта определения максимальных значений
TaPaKдались эти окна :)

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
SELECT 
*
FROM @Таблица a 
CROSS APPLY 
(
	SELECT MAX(b.[Цена]) as MaxCost
	FROM @Таблица b
	WHERE 
		b.[Код ТТ]	= a.[Код ТТ]	AND
		b.[SKU]		= b.[SKU]	AND
		b.[Дата]	BETWEEN DATEADD(day,1,EOMONTH(DATEADD(month,-1*(@КолПредМесяцев+1),a.[Дата]))) AND EOMONTH (a.[Дата])
)  b


В одном месте допущена ошибка
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Три варианта определения максимальных значений / 25 сообщений из 26, страница 1 из 2
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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