Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Алгоритм выбора строк таблицы по максимальному значению поля  [new]
сопр777
Guest
Добрый день!

Имеется такой пример:
-- Исходные данные
create table #t
(
	id int,
	id_order int,
	price int,
	quantity int,
	id_type int
)

INSERT INTO #t (id, id_order, price, quantity, id_type) VALUES ( 1,  1, 120,  20, 2 )
INSERT INTO #t (id, id_order, price, quantity, id_type) VALUES ( 2,  1, 100,  30, 0 ) -- ! ZERO

INSERT INTO #t (id, id_order, price, quantity, id_type) VALUES ( 3,  2, 153, 120, 0 ) -- ! ZERO
INSERT INTO #t (id, id_order, price, quantity, id_type) VALUES ( 4,  2, 165, 271, 0 ) -- ! ZERO

INSERT INTO #t (id, id_order, price, quantity, id_type) VALUES ( 5,  3, 113,   2, 4 )
INSERT INTO #t (id, id_order, price, quantity, id_type) VALUES ( 6,  3, 157,   3, 7 )

INSERT INTO #t (id, id_order, price, quantity, id_type) VALUES ( 7,  4, 153, 120, 1 )
INSERT INTO #t (id, id_order, price, quantity, id_type) VALUES ( 8,  4, 165, 271, 2 )

INSERT INTO #t (id, id_order, price, quantity, id_type) VALUES ( 9,  5, 172, 205, 1 )
INSERT INTO #t (id, id_order, price, quantity, id_type) VALUES (10,  5, 631, 337, 8 )
INSERT INTO #t (id, id_order, price, quantity, id_type) VALUES (11,  5, 153, 120, 9 )

INSERT INTO #t (id, id_order, price, quantity, id_type) VALUES (12,  6, 300,  25, 2 )
INSERT INTO #t (id, id_order, price, quantity, id_type) VALUES (13,  6, 310,  15, 0 ) -- ! ZERO

INSERT INTO #t (id, id_order, price, quantity, id_type) VALUES (14,  7, 158, 365, 7 )
INSERT INTO #t (id, id_order, price, quantity, id_type) VALUES (15,  7, 195, 120, 9 )
INSERT INTO #t (id, id_order, price, quantity, id_type) VALUES (16,  7, 195, 271, 0 ) -- ! ZERO

INSERT INTO #t (id, id_order, price, quantity, id_type) VALUES (17,  8, 195, 571, 0 ) -- ! ZERO
INSERT INTO #t (id, id_order, price, quantity, id_type) VALUES (18,  9, 295, 671, 1 )
INSERT INTO #t (id, id_order, price, quantity, id_type) VALUES (19, 10, 395, 771, 2 )
INSERT INTO #t (id, id_order, price, quantity, id_type) VALUES (20, 11, 495, 871, 0 ) -- ! ZERO

-- Попытка решения
select *
from #t
where id in (
				select max(g.id) as id
				from #t as g
				where (g.price in (
									select MAX(l.price) as max_price 
									from #t as l 
									where (l.id_order = g.id_order)
									group by l.id_order
								 )
					  )
					 -- and
					 -- (
						--case when (g.id_type = 0) and (exists (select id_order
						--									   from #t
						--									   where (id_type <> 0) and (id_order = g.id_order))) 
						--then 0 else 1 end = 1
					 -- )
				group by g.id_order
			)

DROP TABLE #t


Необходимо из таблицы #t отобрать для каждого order_id строку с максимальным значением поля price. Причем, если поле type_id = 0, и есть еще строки с тем же order_id, но с type_id отличным от нуля, то нужно выбирать максимальную price среди них. Если же type_id = 0, но строк с тем же order_id и type_id <> 0 нет, то нужно выбрать максимальное среди строк с type_id = 0.

Половину задачи я осилил, выбираются строки с максимальным price в разрезе order_id (значения поля type_id не учитываются).

Вопросы:

1. Можно ли как-то упростить алгоритм (который представлен выше), чтобы было по проще и селектов поменьше?
2. Куда лучше прикрутить условие для type_id?

Спасибо.

К сообщению приложен файл (test search - max.sql - 2Kb) cкачать
30 мар 12, 01:25    [12337381]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм выбора строк таблицы по максимальному значению поля  [new]
Alexander Karavaev
Member

Откуда: Москва
Сообщений: 40
Как один из вариантов:
with a (id_order, price, id_type)as
	(select id_order, isnull((select max(price) from #t where #t.id_order = a.id_order and id_type <> 0), 
							(select max(price) from #t where #t.id_order = a.id_order and id_type = 0)),
			(select isnull(max(1),0) from #t where #t.id_order = a.id_order and id_type <> 0) 
	from #t a group by id_order)
select #t.id, #t.id_order, #t.price, #t.quantity, #t.id_type
from  #t
inner join a on a.id_order = #t.id_order and a.price = #t.price and a.id_type <= #t.id_type
30 мар 12, 02:13    [12337429]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм выбора строк таблицы по максимальному значению поля  [new]
aleks2
Guest
declare @t table(
	id int,
	id_order int,
	price int,
	quantity int,
	id_type int
);

INSERT INTO @t (id, id_order, price, quantity, id_type) VALUES ( 1,  1, 120,  20, 2 )
INSERT INTO @t (id, id_order, price, quantity, id_type) VALUES ( 2,  1, 100,  30, 0 ) -- ! ZERO

INSERT INTO @t (id, id_order, price, quantity, id_type) VALUES ( 3,  2, 153, 120, 0 ) -- ! ZERO
INSERT INTO @t (id, id_order, price, quantity, id_type) VALUES ( 4,  2, 165, 271, 0 ) -- ! ZERO

INSERT INTO @t (id, id_order, price, quantity, id_type) VALUES ( 5,  3, 113,   2, 4 )
INSERT INTO @t (id, id_order, price, quantity, id_type) VALUES ( 6,  3, 157,   3, 7 )

INSERT INTO @t (id, id_order, price, quantity, id_type) VALUES ( 7,  4, 153, 120, 1 )
INSERT INTO @t (id, id_order, price, quantity, id_type) VALUES ( 8,  4, 165, 271, 2 )

INSERT INTO @t (id, id_order, price, quantity, id_type) VALUES ( 9,  5, 172, 205, 1 )
INSERT INTO @t (id, id_order, price, quantity, id_type) VALUES (10,  5, 631, 337, 8 )
INSERT INTO @t (id, id_order, price, quantity, id_type) VALUES (11,  5, 153, 120, 9 )

INSERT INTO @t (id, id_order, price, quantity, id_type) VALUES (12,  6, 300,  25, 2 )
INSERT INTO @t (id, id_order, price, quantity, id_type) VALUES (13,  6, 310,  15, 0 ) -- ! ZERO

INSERT INTO @t (id, id_order, price, quantity, id_type) VALUES (14,  7, 158, 365, 7 )
INSERT INTO @t (id, id_order, price, quantity, id_type) VALUES (15,  7, 195, 120, 9 )
INSERT INTO @t (id, id_order, price, quantity, id_type) VALUES (16,  7, 195, 271, 0 ) -- ! ZERO

INSERT INTO @t (id, id_order, price, quantity, id_type) VALUES (17,  8, 195, 571, 0 ) -- ! ZERO
INSERT INTO @t (id, id_order, price, quantity, id_type) VALUES (18,  9, 295, 671, 1 )
INSERT INTO @t (id, id_order, price, quantity, id_type) VALUES (19, 10, 395, 771, 2 )
INSERT INTO @t (id, id_order, price, quantity, id_type) VALUES (20, 11, 495, 871, 0 ) -- ! ZERO

select * FROM
(select *, row_number() OVER(PARTITION BY id_order ORDER BY cast(id_type as bit) DESC, price DESC) N FROM @t) X
WHERE N=1
30 мар 12, 07:18    [12337561]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм выбора строк таблицы по максимальному значению поля  [new]
aleks2
Guest
Или еще короче

select TOP 1 WITH TIES * 
FROM @t
ORDER BY row_number() OVER(PARTITION BY id_order ORDER BY cast(id_type as bit) DESC, price DESC)
30 мар 12, 07:22    [12337565]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм выбора строк таблицы по максимальному значению поля  [new]
Alexander Karavaev
Member

Откуда: Москва
Сообщений: 40
Согласен, так гораздо компактнее. :)
Но тут есть один ньюанс: из описания непонятно какая должна быть выборка если есть несколько строк у которых id_type <> 0 и одинаковый price.
В моём случае выбираются все строки с максимальным price, а в Вашем - только одна из таких строк.
30 мар 12, 12:12    [12339257]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм выбора строк таблицы по максимальному значению поля  [new]
aleks2
Guest
Alexander Karavaev
Согласен, так гораздо компактнее. :)
Но тут есть один ньюанс: из описания непонятно какая должна быть выборка если есть несколько строк у которых id_type <> 0 и одинаковый price.
В моём случае выбираются все строки с максимальным price, а в Вашем - только одна из таких строк.

Замени ROW_NUMBER() на RANK().
30 мар 12, 12:50    [12339551]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм выбора строк таблицы по максимальному значению поля  [new]
Alexander Karavaev
Member

Откуда: Москва
Сообщений: 40
Так совсем согласен :)
30 мар 12, 13:10    [12339769]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм выбора строк таблицы по максимальному значению поля  [new]
сопр777
Guest
Alexander Karavaev и aleks2 огромное вам спасибо за ответы.

Действительно все работает так, как надо, и написано кратко и эстетично.

Буду теперь детально разбираться, как это работает и внедрять к себе в проект.

Еще раз спасибо!

P.s. Попутно подсмотрел пример использования табличной переменной, и почитал ее отличия от временной таблицы. А то все теория-теория, а на практике только тонны однообразных селектов.
31 мар 12, 00:37    [12343979]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить