Ещё один вопрос, который регулярно появляется на тематических форумах: "Как получить максимум из нескольких колонок одной таблицы?".
Любую задачу можно решить несколькими способами, эта не исключение. Я попытался собрать несколько вариантов для решения этого вопроса в этой заметке для разных версий SQL Server.
Кроме того, проведу небольшие замеры производительности всех рассмотренных примеров.
Буду рад, если вы предложите ещё несколько вариантов решения этой задачи и с удовольствием дополню эту статью позже.
Начнем с наполнения таблицы тестовыми данными. Для теста я буду использовать небольшую табличку с 5 колонками (a,b,c,d,e). При этом достаточно 5 записей.
if object_id ( N'dbo.test_table', N'U' ) is not null
drop table dbo.test_table;
go
create table dbo.test_table ( id int identity
, a int
, b int
, c int
, d int
, e int
);
go
insert into dbo.test_table
select 10, 5, 12, 56, 2
union all
select 100, 45, 78, 6, 122
union all
select 150, 50, 127, 596, 22
union all
select 3410, 55, 0, -45, 90
union all
select -10, -5, 0, -56, -2;
go
Рис 01. Таблица с тестовыми данными
А теперь несколько примеров, как найти максимум в каждой строке по всем колонкам.
1. Выражение CASE (любая версия)
select *
, max_val = case when ( a >= b ) and ( a >= c ) and ( a >= d ) and ( a >= e )
then a
when ( b >= a ) and ( b >= c ) and ( b >= d ) and ( b >= e )
then b
when ( c >= a ) and ( c >= b ) and ( c >= d ) and ( c >= e )
then c
when ( d >= a ) and ( d >= b ) and ( d >= c ) and ( d >= e )
then d
else e
end
from dbo.test_table
2. Подзапрос и оператор UNION (любая версия)
select *
, max_val = ( select max(i)
from ( select a
union all
select b
union all
select c
union all
select d
union all
select e
) t(i)
)
from dbo.test_table
3. Подзапрос и предложение VALUES (SQL Server 2008 и выше)
select *
, max_val = ( select max(i)
from ( values (a)
,(b)
,(c)
,(d)
,(e)
) t(i)
)
from dbo.test_table
4. Оператор UNPIVOT (SQL Server 2005 и выше)
select id, max(val) as max_val
from ( select *
from dbo.test_table t
unpivot ( val for col in ( [a],[b],[c],[d],[e]) ) up
) t
group by id
5. Оператор UNPIVOT и ранжирующие функции (SQL Server 2005 и выше)
select top 1 with ties id, val
from dbo.test_table t
unpivot ( val for col in ( [a],[b],[c],[d],[e]) ) up
order by row_number() over ( partition by id order by val desc )
6. Оператор CROSS APPLY (SQL Server 2005 и выше)
select *
from dbo.test_table t
cross apply ( select top 1 val
from ( values (a),(b),(c),(d),(e) ) t (val)
order by val desc
) ca (max_val)
--Ещё вариант
select id, a, b, c, d, e, max_val
from dbo.test_table t
cross apply ( select row_number() over (order by val desc) i, val
from ( values (a),(b),(c),(d),(e) ) t (val)
) ca (i, max_val)
where ca.i = 1
7. Inline-функция и CTE (SQL Server 2005 и выше)
if object_id ( N'dbo.f_get_max', N'IF' ) is not null
drop function dbo.f_get_max;
go
create function dbo.f_get_max (
@a int
, @b int
, @c int
, @d int
, @e int
) returns table
as
return
with cte
as
( select val from
( values (@a),(@b),(@c),(@d),(@e)
) t(val)
)
select max(val) as max_val from cte;
go
select * from dbo.test_table t
cross apply dbo.f_get_max ( a, b, c, d, e )
go
А теперь посмотрим на производительность этих запросов, чтобы определить, какой из вариантов более предпочтителен. Тест буду проводить так же на 5 колонках таблицы с 10 млн. записей.
set nocount on;
go
if object_id ( N'dbo.test_table', N'U' ) is not null
drop table dbo.test_table;
go
create table dbo.test_table ( id int identity primary key clustered
, a int
, b int
, c int
, d int
, e int
);
go
--Генерим 10 млн строк
with cte
as
(
select 1 i
union all
select i+1 from cte where i < 10000000
)
insert into dbo.test_table
select checksum ( newid() ) % 1000 as a
, checksum ( newid() ) % 1000 as b
, checksum ( newid() ) % 1000 as c
, checksum ( newid() ) % 1000 as d
, checksum ( newid() ) % 1000 as e
from cte
option ( maxrecursion 0 );
declare @val int;
print '1. Выражение CASE (любая версия)';
set statistics time on;
select @val = case when ( a >= b ) and ( a >= c ) and ( a >= d ) and ( a >= e )
then a
when ( b >= a ) and ( b >= c ) and ( b >= d ) and ( b >= e )
then b
when ( c >= a ) and ( c >= b ) and ( c >= d ) and ( c >= e )
then c
when ( d >= a ) and ( d >= b ) and ( d >= c ) and ( d >= e )
then d
else e
end
from dbo.test_table;
set statistics time off;
print '2. Подзапрос и оператор UNION (любая версия)';
set statistics time on;
select @val = ( select max(i)
from ( select a
union all
select b
union all
select c
union all
select d
union all
select e
) t(i)
)
from dbo.test_table;
set statistics time off;
print '3. Подзапрос и предложение VALUES (SQL Server 2008 и выше)';
set statistics time on;
select @val = ( select max(i)
from ( values (a)
,(b)
,(c)
,(d)
,(e)
) t(i)
)
from dbo.test_table;
set statistics time off;
print '4. Оператор UNPIVOT (SQL Server 2005 и выше)';
set statistics time on;
select @val = max(val)
from ( select *
from dbo.test_table t
unpivot ( val for col in ( [a],[b],[c],[d],[e]) ) up
) t
group by id;
set statistics time off;
print '5. Оператор UNPIVOT и ранжирующие функции (SQL Server 2005 и выше)';
set statistics time on;
select top 1 with ties @val = val
from dbo.test_table t
unpivot ( val for col in ( [a],[b],[c],[d],[e]) ) up
order by row_number() over ( partition by id order by val desc );
set statistics time off;
print '6. Оператор CROSS APPLY (SQL Server 2005 и выше)';
print 'CROSS APPLY 1';
set statistics time on;
select @val = max_val
from dbo.test_table t
cross apply ( select top 1 val
from ( values (a),(b),(c),(d),(e) ) t (val)
order by val desc
) ca (max_val);
print 'CROSS APPLY 2';
select @val = max_val
from dbo.test_table t
cross apply ( select row_number() over (order by val desc) i, val
from ( values (a),(b),(c),(d),(e) ) t (val)
) ca (i, max_val)
where ca.i = 1;
set statistics time off;
print '7. Inline-функция и CTE (SQL Server 2005 и выше)';
set statistics time on;
select @val = max_val from dbo.test_table t
cross apply dbo.f_get_max ( a, b, c, d, e );
set statistics time off;
go
Результат ниже:
1. Выражение CASE (любая версия)
SQL Server Execution Times:
CPU time = 3416 ms, elapsed time = 3416 ms.
----------------------------------------------------------------------
2. Подзапрос и оператор UNION (любая версия)
SQL Server Execution Times:
CPU time = 12777 ms, elapsed time = 3848 ms.
----------------------------------------------------------------------
3. Подзапрос и предложение VALUES (SQL Server 2008 и выше)
SQL Server Execution Times:
CPU time = 13182 ms, elapsed time = 3968 ms.
----------------------------------------------------------------------
4. Оператор UNPIVOT (SQL Server 2005 и выше)
SQL Server Execution Times:
CPU time = 22246 ms, elapsed time = 15966 ms.
----------------------------------------------------------------------
5. Оператор UNPIVOT и ранжирующие функции (SQL Server 2005 и выше)
SQL Server Execution Times:
CPU time = 153957 ms, elapsed time = 239071 ms.
----------------------------------------------------------------------
6. Оператор CROSS APPLY (SQL Server 2005 и выше)
CROSS APPLY 1
SQL Server Execution Times:
CPU time = 22963 ms, elapsed time = 6273 ms.
CROSS APPLY 2
SQL Server Execution Times:
CPU time = 33244 ms, elapsed time = 9270 ms.
----------------------------------------------------------------------
7. Inline-функция и CTE (SQL Server 2005 и выше)
SQL Server Execution Times:
CPU time = 12246 ms, elapsed time = 3787 ms.
Подписаться