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

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

+Есть исходная таблица
Код ТТ 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-запрос
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)

Как правильно написать?
16 апр 18, 09:47    [21341565]     Ответить | Цитировать Сообщить модератору
 Re: Три варианта определения максимальных значений  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 17883
ferzmikk
Максимальная цена за
MAX(CASE WHEN дата BETWEEN начало AND конец THEN цена END)
16 апр 18, 09:50    [21341569]     Ответить | Цитировать Сообщить модератору
 Re: Три варианта определения максимальных значений  [new]
ferzmikk
Member

Откуда:
Сообщений: 1507
Akina
ferzmikk
Максимальная цена за
MAX(CASE WHEN дата BETWEEN начало AND конец THEN цена END)

Написал такой запрос
+
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)

что то результат не такой, какой нужен.
16 апр 18, 14:53    [21342585]     Ответить | Цитировать Сообщить модератору
 Re: Три варианта определения максимальных значений  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 3619
ferzmikk,

Конструкцию

MAX(...) OVER (PARTITION BY ...) 

применить не хотите ?
16 апр 18, 15:12    [21342648]     Ответить | Цитировать Сообщить модератору
 Re: Три варианта определения максимальных значений  [new]
TaPaK
Member

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

Конструкцию

MAX(...) OVER (PARTITION BY ...) 

применить не хотите ?

жутко хитро окно бить для "соответствующий и предыдущий от соответствующего месяца"
16 апр 18, 15:15    [21342656]     Ответить | Цитировать Сообщить модератору
 Re: Три варианта определения максимальных значений  [new]
ferzmikk
Member

Откуда:
Сообщений: 1507
Все таки, почему поле [Макс цена за соответ пред месяц] не корректно считает: где то Null, где то не корректное значение?
16 апр 18, 15:25    [21342703]     Ответить | Цитировать Сообщить модератору
 Re: Три варианта определения максимальных значений  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 17883
ferzmikk
Написал такой запрос

Ну вообще-то предполагалось, что это будет простейший запрос типа
SELECT ТТ, SKU, MAX(CASE для первого условия), MAX(CASE для второго условия), ...
FROM @Таблица
GROUP BY ТТ, SKU

а вовсе не этот страховидный [censored], который ты изобразил...
16 апр 18, 15:41    [21342776]     Ответить | Цитировать Сообщить модератору
 Re: Три варианта определения максимальных значений  [new]
ferzmikk
Member

Откуда:
Сообщений: 1507
ferzmikk
SQL-запрос написал как то развернуто, чтобы понятнее было.

Akina
ferzmikk
Написал такой запрос

Ну вообще-то предполагалось, что это будет простейший запрос типа
SELECT ТТ, SKU, MAX(CASE для первого условия), MAX(CASE для второго условия), ...
FROM @Таблица
GROUP BY ТТ, SKU

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

Откуда:
Сообщений: 1507
Akina
Ну вообще-то предполагалось, что это будет простейший запрос типа
SELECT ТТ, SKU, MAX(CASE для первого условия), MAX(CASE для второго условия), ...
FROM @Таблица
GROUP BY ТТ, SKU

По логике максимальную цену надо находить не по ТТ+SKU, а по ТТ+SKU+[Месяц и год] и причем еще обратиться к предыдущей [Месяц и год], если не первый месяц и год в периоде.
17 апр 18, 08:15    [21344159]     Ответить | Цитировать Сообщить модератору
 Re: Три варианта определения максимальных значений  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 17883
ferzmikk
По логике максимальную цену надо находить не по ТТ+SKU, а по ТТ+SKU+[Месяц и год] и причем еще обратиться к предыдущей [Месяц и год], если не первый месяц и год в периоде.
Мда... может, есть смысл попробовать сначала понять, что тебе ответили, а?
17 апр 18, 08:19    [21344168]     Ответить | Цитировать Сообщить модератору
 Re: Три варианта определения максимальных значений  [new]
ferzmikk
Member

Откуда:
Сообщений: 1507
Для второго вычисляемого поля
+SQL-запрос
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 корректно работает, а для последующих - нет.

Почему так?
18 апр 18, 16:24    [21349515]     Ответить | Цитировать Сообщить модератору
 Re: Три варианта определения максимальных значений  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 235
ferzmikk,

Для нахождения "2. Максимальная цена за соответствующий и предыдущий от соответствующего месяца" нужно условие
		(ТТ1.[Месяц и Год пред] = Т1.[Месяц и Год]
		OR
		ТТ1.[Месяц и Год] = Т1.[Месяц и Год])
19 апр 18, 05:19    [21350716]     Ответить | Цитировать Сообщить модератору
 Re: Три варианта определения максимальных значений  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 235
Или для красоты:
Т1.[Месяц и Год] in (ТТ1.[Месяц и Год пред], ТТ1.[Месяц и Год])
19 апр 18, 05:21    [21350717]     Ответить | Цитировать Сообщить модератору
 Re: Три варианта определения максимальных значений  [new]
ferzmikk
Member

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

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

Но если задать
DECLARE @КолПредМесяцев as integer=2;
то есть брать не 1, а 2 предыдущих месяца от соответствующего, то не корректно срабатывает.
19 апр 18, 07:28    [21350752]     Ответить | Цитировать Сообщить модератору
 Re: Три варианта определения максимальных значений  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 235
ferzmikk,

Т1.[Месяц и Год] between ТТ1.[Месяц и Год пред] and ТТ1.[Месяц и Год]
19 апр 18, 07:48    [21350770]     Ответить | Цитировать Сообщить модератору
 Re: Три варианта определения максимальных значений  [new]
ferzmikk
Member

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

Спасибо, получилось!
19 апр 18, 07:57    [21350776]     Ответить | Цитировать Сообщить модератору
 Re: Три варианта определения максимальных значений  [new]
ferzmikk
Member

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

Конструкцию

MAX(...) OVER (PARTITION BY ...) 

применить не хотите ?

жутко хитро окно бить для "соответствующий и предыдущий от соответствующего месяца"

Использую такой вариант
+SQL-Запрос
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
Неправильно максимальную цену рассчитывает.

Как правильно написать?
19 апр 18, 11:03    [21351392]     Ответить | Цитировать Сообщить модератору
 Re: Три варианта определения максимальных значений  [new]
TaPaK
Member

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

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

а в каком случае это даст false?
19 апр 18, 11:23    [21351482]     Ответить | Цитировать Сообщить модератору
 Re: Три варианта определения максимальных значений  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 235
TaPaK,

При @КолПредМесяцев < 0 (причем всегда будет False)
19 апр 18, 11:30    [21351504]     Ответить | Цитировать Сообщить модератору
 Re: Три варианта определения максимальных значений  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 5319
Kopelly
TaPaK,

При @КолПредМесяцев < 0 (причем всегда будет False)

стошнило
19 апр 18, 11:31    [21351511]     Ответить | Цитировать Сообщить модератору
 Re: Три варианта определения максимальных значений  [new]
ferzmikk
Member

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

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

а в каком случае это даст false?

а как правильно написать тогда?
19 апр 18, 14:53    [21352187]     Ответить | Цитировать Сообщить модератору
 Re: Три варианта определения максимальных значений  [new]
TaPaK
Member

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

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

а в каком случае это даст false?

а как правильно написать тогда?

на вскидку - никак. Окна тут вряд-ли помогут
19 апр 18, 14:56    [21352191]     Ответить | Цитировать Сообщить модератору
 Re: Три варианта определения максимальных значений  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 235
ferzmikk,

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

+ Запрос
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)
20 апр 18, 04:54    [21353495]     Ответить | Цитировать Сообщить модератору
 Re: Три варианта определения максимальных значений  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 5319
дались эти окна :)

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
20 апр 18, 09:03    [21353636]     Ответить | Цитировать Сообщить модератору
 Re: Три варианта определения максимальных значений  [new]
ferzmikk
Member

Откуда:
Сообщений: 1507
TaPaK
дались эти окна :)

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

В одном месте допущена ошибка
20 апр 18, 10:40    [21353904]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить