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

Откуда: Москва
Сообщений: 245
Коллеги, что-то совсем загрустил и не хочется уходить (пока) на процедуру, а хочется решить запросом.
Вот таблица:
ID--Рейс--Груз--Груз за рейс--Груз за период--Дист. в грузу--Груз дист за рейс--Груз дист за период
1----01----NULL------94 397----------307 826-----------------0--------------571------------------2 158
1----01----94 397----94 397----------307 826----------------571-------------571------------------2 158
1----02----NULL------71 601----------307 826-----------------0-------------1 587-----------------2 158
1----02----71 601----71 601----------307 826----------------105------------1 587-----------------2 158
1----02----71 601----71 601----------307 826---------------1 402-----------1 587-----------------2 158
1----02----71 601----71 601----------307 826-----------------81------------1 587-----------------2 158


Небольшое описание.
ID - номер транспорта
Рейс - это понятно рейс
А вот рейс разбивается на части( в основном когда в грузу и когда без груза), но иногда состояние в грузу разбивают на несколько этапов, когда необходимо выделить участки.

Задача. Посчитать:
- количество груза за рейс,
- кол-во груза за весь период,
- дистанцию за рейс в грузе,
- дистанцию за период,
и некий фактор, который рассчитывается:
за рейс как кол-во груза * на пройденную дистанцию в грузу
за период сумма (кол-во груза * на пройденную дистанцию в грузу).

Так вот я сломал голову как посчитать кол-во груза за период.. Я выделил в табличке красным цветом то что считаеся не верно.
Запрос следующий:
SELECT 	ID,
	Рейс,
	MAX(Груз) 				Груз,
	MAX(Груз) OVER (PARTITION BY ID, Рейс)	Груз за рейс,
	SUM(MAX(Груз))  OVER (PARTITION BY ID)	Груз за период,
	SUM(Case 
		When состояние = 'в грузу' Then Дистанция
		 Else 0
	End) Дист в грузу,
	SUM(SUM(Case 
		When состояние = 'в грузу' Then Дистанция
		 Else 0
	End)) OVER  (PARTITION BY ID, Рейс)		Дист в грузу за рейс,
	SUM(SUM(Case 
		When состояние = 'в грузу' Then Дистанция
		 Else 0
	End)) OVER  (PARTITION BY ID)		Дист в грузу за период
From table
Group by ID, Рейс


Запрос суммирует 94 397 + 71 601 + 71 601 + 71 601 , а нужно чтобы один раз взяло 71 601 и один раз 94 397,

т.е. 94 397 + 71 601 = 165 998

и соответсвенно не могу сообразить как считать факто за период, когда мне нужно взять сумму из (груз за рейс * дистан за рейс)

Буду очень благодарен за советы.

Реализуется на MS SQL 2008
19 окт 12, 15:43    [13347576]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с запросом -max, sum, partition  [new]
iap
Member

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

приведённый запрос не может работать вообще,
ибо MAX(Груз) OVER (PARTITION BY ID, Рейс) отсутствует как в агрегатных функциях, так и в GROUP BY.
Да и алиасы с пробелами просто так писать не получится.

Так что вопрос ни о чём
19 окт 12, 16:01    [13347716]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с запросом -max, sum, partition  [new]
Pasha
Member

Откуда: Москва
Сообщений: 245
iap
Pasha,

приведённый запрос не может работать вообще,
ибо MAX(Груз) OVER (PARTITION BY ID, Рейс) отсутствует как в агрегатных функциях, так и в GROUP BY.
Да и алиасы с пробелами просто так писать не получится.

Так что вопрос ни о чём


Ну я бы был осторожен с высказыванием, ибо таблица получена из запроса, который написан ниже. сидел мучался переводил на "человеческий лад" название полей, а так все действующее.
19 окт 12, 16:16    [13347806]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с запросом -max, sum, partition  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Pasha
iap
Pasha,

приведённый запрос не может работать вообще,
ибо MAX(Груз) OVER (PARTITION BY ID, Рейс) отсутствует как в агрегатных функциях, так и в GROUP BY.
Да и алиасы с пробелами просто так писать не получится.

Так что вопрос ни о чём


Ну я бы был осторожен с высказыванием, ибо таблица получена из запроса, который написан ниже. сидел мучался переводил на "человеческий лад" название полей, а так все действующее.
Вы бы привели пример таблицы и заполнения ее тестовыми данными.
19 окт 12, 17:12    [13348134]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с запросом -max, sum, partition  [new]
Посетитель
Member

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

мне вот интересно, как
sELECT 	ID,
	Рейс,
	MAX(Груз) 				Груз,
        MAX(Груз) OVER (PARTITION BY ID, Рейс)	Груз за рейс
From table
Group by ID, Рейс

у вас выдает для строк с одинаковыми ID, Рейс разные результаты для [Груз], но одинаковые для [Груз за рейс]?
19 окт 12, 17:36    [13348240]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с запросом -max, sum, partition  [new]
Pasha
Member

Откуда: Москва
Сообщений: 245
По просьбе написал скрипт создания, заоплнения и выборки:
create table #t
	( 	id			int,
		voy			varchar(10),
		start_date	datetime,
		end_date	datetime,
		cargo		real,
		distance	real,
		condition	varchar(20)  	)
	 
INSERT INTO #t(id, voy,	start_date,	end_date, cargo, distance, condition)
Values(1, 01, '20120101', '20120102', 0,	200, 'balast')

INSERT INTO #t(id, voy,	start_date,	end_date, cargo, distance, condition)
Values(1, 01, '20120102', '20120103', 94397,	571, 'loaded')

INSERT INTO #t(id, voy,	start_date,	end_date, cargo, distance, condition)
Values(1, 02, '20120103', '20120105', 0, 300, 'balast')

INSERT INTO #t(id, voy,	start_date,	end_date, cargo, distance, condition)
Values(1, 02, '20120105', '20120106', 71601, 105, 'loaded')

INSERT INTO #t(id, voy,	start_date,	end_date, cargo, distance, condition)
Values(1, 02, '20120106', '20120107', 71601, 1402, 'loaded')

INSERT INTO #t(id, voy,	start_date,	end_date, cargo, distance, condition)
Values(1, 02, '20120107', '20120108', 71601, 81, 'loaded')

SELECT 	id,
		voy,
		start_date,
		end_date,
		MAX(cargo) 			'Груз',
		Max(MAX(cargo)) OVER (PARTITION BY id, voy)	'Груз за рейс',
		SUM(MAX(cargo))  OVER (PARTITION BY id)	'Груз за период',
		SUM(Case 
				When condition = 'loaded' Then distance
				Else 0
			End) 'Дист в грузу',
		SUM(SUM(Case 
					When condition = 'loaded' Then distance
					Else 0
				End)) OVER  (PARTITION BY id, voy)		'Дист в грузу за рейс',
		SUM(SUM(Case 
					When condition = 'loaded' Then distance
					Else 0
				End)) OVER  (PARTITION BY id)		'Дист в грузу за период'
From #t
Group by id,
		voy,
		start_date,
		end_date


Вообщем 'Груз за период' должен равняться 165 998,
сейчас выглядит все вот так
id voy start_date end_date Груз Груз за рейс Груз за период Дист в грузу Дист в грузу за рейс Дист в грузу за период
1 1 2012-01-01 2012-01-02 0 --------- 94397 309200 0 ----- 571 2159
1 1 2012-01-02 2012-01-03 94397 --- 94397 309200 571 --- 571 2159
1 2 2012-01-03 2012-01-05 0 --------- 71601 309200 0 ----- 1588 2159
1 2 2012-01-05 2012-01-06 71601 --- 71601 309200 105 --- 1588 2159
1 2 2012-01-06 2012-01-07 71601 --- 71601 309200 1402 - 1588 2159
1 2 2012-01-07 2012-01-08 71601 --- 71601 309200 81 ---- 1588 2159
22 окт 12, 11:26    [13355223]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с запросом -max, sum, partition  [new]
Pasha
Member

Откуда: Москва
Сообщений: 245
Придумал я как это обойти, ниже запрос.
Можете поругать :)) если все решалось проще (я поменял название таблиц с русского на английский, для вложенного запроса:
Select AA.id,
	   AA.voy,
	   AA.start_date,
	   AA.end_date,
	   AA.CARGO_PASSAGE,
	   AA.CARGO_VOYAGE,
	   SUM(AA.CARGO_VOYAGE / AA.REC_VOYAGE) OVER (PARTITION BY id),
	   AA.DIST_WITH_CARGO,
	   AA.DIST_PER_VOYAGE,
	   AA.DIST_PER_PERIOD
From 
(
SELECT 	id,
		voy,
		start_date,
		end_date,
		MAX(cargo) 			'CARGO_PASSAGE',
		Max(MAX(cargo)) OVER (PARTITION BY id, voy)	'CARGO_VOYAGE',
		COUNT(id) OVER (PARTITION BY id, voy) 'REC_VOYAGE',
		/*--SUM(MAX(cargo)/COUNT(*))  OVER (PARTITION BY id)	'Груз за период',		
		--MAX(SUM(cargo))  OVER (PARTITION BY id)	'Груз за период',*/
		SUM(Case 
				When condition = 'loaded' Then distance
				Else 0
			End) 'DIST_WITH_CARGO',
		SUM(SUM(Case 
					When condition = 'loaded' Then distance
					Else 0
				End)) OVER  (PARTITION BY id, voy)		'DIST_PER_VOYAGE',
		SUM(SUM(Case 
					When condition = 'loaded' Then distance
					Else 0
				End)) OVER  (PARTITION BY id)		'DIST_PER_PERIOD'
From #t
Group by id,
		voy,
		start_date,
		end_date) AA
Group by AA.id,
	   AA.voy,
	   AA.start_date,
	   AA.end_date,
	   AA.CARGO_PASSAGE,
	   AA.CARGO_VOYAGE,
	   AA.REC_VOYAGE,
	   AA.DIST_WITH_CARGO,
	   AA.DIST_PER_VOYAGE,
	   AA.DIST_PER_PERIOD


Результат следующий:
id voy start_date end_date CARGO_PASSAGE CARGO_VOYAGE (No column name) DIST_WITH_CARGO DIST_PER_VOYAGE DIST_PER_PERIOD
1 1 2012-01-01 2012-01-02 0---------- 94397- 165998- 0----- 571-- 2159
1 1 2012-01-02 2012-01-03 94397---- 94397- 165998- 571--- 571-- 2159
1 2 2012-01-03 2012-01-05 0---------- 71601- 165998- 0----- 1588- 2159
1 2 2012-01-05 2012-01-06 71601---- 71601- 165998- 105--- 1588- 2159
1 2 2012-01-06 2012-01-07 71601---- 71601- 165998- 1402- 1588- 2159
1 2 2012-01-07 2012-01-08 71601---- 71601- 165998- 81---- 1588- 2159


Груз за период равен 165998 , это 94397 + 71601
22 окт 12, 13:05    [13356068]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить