Qual é a melhor maneira de selecionar o valor mínimo de várias colunas?

Dada a tabela a seguir no SQL Server 2005:

ID Col1 Col2 Col3 -- ---- ---- ---- 1 3 34 76 2 32 976 24 3 7 235 3 4 245 1 792 

Qual é a melhor maneira de escrever a consulta que produz o seguinte resultado (ou seja, um que produz a coluna final – uma coluna contendo os valores mínimos de Col1, Col2 e Col 3 para cada linha )?

 ID Col1 Col2 Col3 TheMin -- ---- ---- ---- ------ 1 3 34 76 3 2 32 976 24 24 3 7 235 3 3 4 245 1 792 1 

ATUALIZAR:

Para esclarecimento (como eu disse nos comentários) no cenário real, o database está devidamente normalizado . Essas colunas “matriz” não estão em uma tabela real, mas estão em um conjunto de resultados que é necessário em um relatório. E o novo requisito é que o relatório também precise dessa coluna MinValue. Eu não posso mudar o conjunto de resultados subjacente e, portanto, eu estava olhando para o T-SQL para um prático “sair do cartão de prisão”.

Eu tentei a abordagem CASE mencionada abaixo e funciona, embora seja um pouco incômodo. Também é mais complicado do que o indicado nas respostas porque você precisa atender ao fato de que existem dois valores mínimos na mesma linha.

Enfim, pensei em postar minha solução atual que, dadas as minhas limitações, funciona muito bem. Utiliza o operador UNPIVOT:

 with cte (ID, Col1, Col2, Col3) as ( select ID, Col1, Col2, Col3 from TestTable ) select cte.ID, Col1, Col2, Col3, TheMin from cte join ( select ID, min(Amount) as TheMin from cte UNPIVOT (Amount for AmountCol in (Col1, Col2, Col3)) as unpvt group by ID ) as minValues on cte.ID = minValues.ID 

Vou dizer antecipadamente que não espero que isso ofereça o melhor desempenho, mas dadas as circunstâncias (não posso redesenhar todas as consultas apenas para o novo requisito da coluna MinValue), é um elegante “sair da cadeia” cartão”.

É provável que haja muitas maneiras de conseguir isso. Minha sugestão é usar Case / When para fazer isso. Com 3 colunas, não é tão ruim.

 Select Id, Case When Col1 < Col2 And Col1 < Col3 Then Col1 When Col2 < Col1 And Col2 < Col3 Then Col2 Else Col3 End As TheMin From YourTableNameHere 

Usando CROSS APPLY :

 SELECT ID, Col1, Col2, Col3, MinValue FROM YourTable CROSS APPLY (SELECT MIN(d) MinValue FROM (VALUES (Col1), (Col2), (Col3)) AS a(d)) A 

Violino SQL

A melhor maneira é não fazê-lo – é estranho que as pessoas insistam em armazenar seus dados de uma forma que requeira “ginástica” SQL para extrair informações significativas quando há maneiras muito mais fáceis de alcançar o resultado desejado se você apenas normalizar seu esquema corretamente .

O jeito certo de fazer isso, na minha opinião, é ter a seguinte tabela:

 ID Col Val -- --- --- 1 1 3 1 2 34 1 3 76 2 1 32 2 2 976 2 3 24 3 1 7 3 2 235 3 3 3 4 1 245 4 2 1 4 3 792 

com ID/Col como chave primária e possivelmente Col como uma chave extra, dependendo de suas necessidades. Então sua consulta se torna simples

 select min(val) from tbl 

e você ainda pode tratar as colunas antigas individuais separadamente usando

 where col = 2 

em suas outras consultas. Isso também permite uma expansão fácil se o número de colunas antigas crescer.

Isso torna suas consultas muito mais fáceis. A orientação geral que costumo usar é, se você tiver algo parecido com um array em uma linha de database, provavelmente está fazendo algo errado e deve pensar em reestruturar os dados.


No entanto, se por algum motivo você não puder alterar essas colunas, sugiro usar a inserção e a atualização de gatilhos e adicionar outra coluna com esses gatilhos definidos no mínimo em Col1/2/3 . Isso moverá o ‘custo’ da operação para longe da seleção para a atualização / inserção onde ela pertence – a maioria das tabelas de database na minha experiência é lida com muito mais frequência do que escrita, incorrendo no custo de gravação ser mais eficiente ao longo do tempo.

Em outras palavras, o mínimo de uma linha só muda quando uma das outras colunas muda, então é quando você deve calculá-la, não toda vez que você seleciona (o que é perdido se os dados não estão mudando). Você acabaria com uma tabela como:

 ID Col1 Col2 Col3 MinVal -- ---- ---- ---- ------ 1 3 34 76 3 2 32 976 24 24 3 7 235 3 3 4 245 1 792 1 

Qualquer outra opção que tenha que tomar decisões em tempo select geralmente é uma idéia ruim em relação ao desempenho, já que os dados só mudam em inserção / atualização – a adição de outra coluna ocupa mais espaço no database e será um pouco mais lenta para as inserções e atualizações, mas podem ser muito mais rápidas para as seleções – a abordagem preferida deve depender das suas prioridades, mas, como dito, a maioria das tabelas é lida com muito mais freqüência do que as escritas.

Você pode usar a abordagem “força bruta” com uma torção:

 SELECT CASE WHEN Col1 <= Col2 AND Col1 <= Col3 THEN Col1 WHEN Col2 <= Col3 THEN Col2 ELSE Col3 END AS [Min Value] FROM [Your Table] 

Quando o primeiro quando a condição falha, garante que Col1 não é o menor valor, portanto, você pode eliminá-lo do resto das condições. Da mesma forma para condições subseqüentes. Para cinco colunas, sua consulta se torna:

 SELECT CASE WHEN Col1 <= Col2 AND Col1 <= Col3 AND Col1 <= Col4 AND Col1 <= Col5 THEN Col1 WHEN Col2 <= Col3 AND Col2 <= Col4 AND Col2 <= Col5 THEN Col2 WHEN Col3 <= Col4 AND Col3 <= Col5 THEN Col3 WHEN Col4 <= Col5 THEN Col4 ELSE Col5 END AS [Min Value] FROM [Your Table] 

Observe que, se houver um empate entre duas ou mais colunas, <= garante que saímos da instrução CASE mais cedo possível.

Se as colunas fossem inteiros como no seu exemplo, eu criaria uma function:

 create function f_min_int(@a as int, @b as int) returns int as begin return case when @a < @b then @a else coalesce(@b,@a) end end 

então quando eu precisar usá-lo eu faria:

 select col1, col2, col3, dbo.f_min_int(dbo.f_min_int(col1,col2),col3) 

se você tem 5 colums então o acima se torna

 select col1, col2, col3, col4, col5, dbo.f_min_int(dbo.f_min_int(dbo.f_min_int(dbo.f_min_int(col1,col2),col3),col4),col5) 
 SELECT ID, Col1, Col2, Col3, (SELECT MIN(Col) FROM (VALUES (Col1), (Col2), (Col3)) AS X(Col)) AS TheMin FROM Table 

Você também pode fazer isso com uma consulta de união. À medida que o número de colunas aumenta, você precisa modificar a consulta, mas pelo menos seria uma modificação direta.

 Select T.Id, T.Col1, T.Col2, T.Col3, A.TheMin From YourTable T Inner Join ( Select A.Id, Min(A.Col1) As TheMin From ( Select Id, Col1 From YourTable Union All Select Id, Col2 From YourTable Union All Select Id, Col3 From YourTable ) As A Group By A.Id ) As A On T.Id = A.Id 

Isso é força bruta, mas funciona

  select case when col1 <= col2 and col1 <= col3 then col1 case when col2 <= col1 and col2 <= col3 then col2 case when col3 <= col1 and col3 <= col2 then col3 as 'TheMin' end from Table T 

... porque min () funciona apenas em uma coluna e não em colunas.

Tanto esta pergunta como esta pergunta tentam responder a isto.

A recapitulação é que o Oracle tem uma function interna para isso, com o Sql Server você está preso ou definindo uma function definida pelo usuário ou usando instruções case.

Usa isto:

 select least(col1, col2, col3) FROM yourtable 

Se você é capaz de fazer um procedimento armazenado, ele poderia ter uma matriz de valores, e você poderia simplesmente chamar isso.

 select *, case when column1 < columnl2 And column1 < column3 then column1 when columnl2 < column1 And columnl2 < column3 then columnl2 else column3 end As minValue from tbl_example 

Um pequeno toque na consulta de união:

 DECLARE @Foo TABLE (ID INT, Col1 INT, Col2 INT, Col3 INT) INSERT @Foo (ID, Col1, Col2, Col3) VALUES (1, 3, 34, 76), (2, 32, 976, 24), (3, 7, 235, 3), (4, 245, 1, 792) SELECT ID, Col1, Col2, Col3, ( SELECT MIN(T.Col) FROM ( SELECT Foo.Col1 AS Col UNION ALL SELECT Foo.Col2 AS Col UNION ALL SELECT Foo.Col3 AS Col ) AS T ) AS TheMin FROM @Foo AS Foo 

Se você usar o SQL 2005, você pode fazer algo legal assim:

 ;WITH res AS ( SELECT t.YourID , CAST(( SELECT Col1 AS c01 , Col2 AS c02 , Col3 AS c03 , Col4 AS c04 , Col5 AS c05 FROM YourTable AS cols WHERE YourID = t.YourID FOR XML AUTO , ELEMENTS ) AS XML) AS colslist FROM YourTable AS t ) SELECT YourID , colslist.query('for $c in //cols return min(data($c/*))').value('.', 'real') AS YourMin , colslist.query('for $c in //cols return avg(data($c/*))').value('.', 'real') AS YourAvg , colslist.query('for $c in //cols return max(data($c/*))').value('.', 'real') AS YourMax FROM res 

Desta forma, você não se perde em tantos operadores 🙂

No entanto, isso pode ser mais lento do que a outra opção.

É a sua escolha…

Abaixo eu uso uma tabela temporária para obter o mínimo de várias datas. A primeira tabela temporária consulta várias tabelas unidas para obter várias datas (bem como outros valores para a consulta), a segunda tabela temporária obtém as várias colunas e a data mínima usando tantas passagens quantas as colunas de data.

Isto é essencialmente como a consulta de união, o mesmo número de passes é necessário, mas pode ser mais eficiente (com base na experiência, mas precisaria de teste). A eficiência não foi um problema neste caso (8.000 registros). Um poderia indexar etc.

 --==================== this gets minimums and global min if object_id('tempdb..#temp1') is not null drop table #temp1 if object_id('tempdb..#temp2') is not null drop table #temp2 select r.recordid , r.ReferenceNumber, i.InventionTitle, RecordDate, i.ReceivedDate , min(fi.uploaddate) [Min File Upload], min(fi.CorrespondenceDate) [Min File Correspondence] into #temp1 from record r join Invention i on i.inventionid = r.recordid left join LnkRecordFile lrf on lrf.recordid = r.recordid left join fileinformation fi on fi.fileid = lrf.fileid where r.recorddate > '2015-05-26' group by r.recordid, recorddate, i.ReceivedDate, r.ReferenceNumber, i.InventionTitle select recordid, recorddate [min date] into #temp2 from #temp1 update #temp2 set [min date] = ReceivedDate from #temp1 t1 join #temp2 t2 on t1.recordid = t2.recordid where t1.ReceivedDate < [min date] and t1.ReceivedDate > '2001-01-01' update #temp2 set [min date] = t1.[Min File Upload] from #temp1 t1 join #temp2 t2 on t1.recordid = t2.recordid where t1.[Min File Upload] < [min date] and t1.[Min File Upload] > '2001-01-01' update #temp2 set [min date] = t1.[Min File Correspondence] from #temp1 t1 join #temp2 t2 on t1.recordid = t2.recordid where t1.[Min File Correspondence] < [min date] and t1.[Min File Correspondence] > '2001-01-01' select t1.*, t2.[min date] [LOWEST DATE] from #temp1 t1 join #temp2 t2 on t1.recordid = t2.recordid order by t1.recordid 
 SELECT [ID], ( SELECT MIN([value].[MinValue]) FROM ( VALUES ([Col1]), ([Col1]), ([Col2]), ([Col3]) ) AS [value] ([MinValue]) ) AS [MinValue] FROM Table; 

Se você sabe quais valores você está procurando, geralmente um código de status, o seguinte pode ser útil:

 select case when 0 in (PAGE1STATUS ,PAGE2STATUS ,PAGE3STATUS, PAGE4STATUS,PAGE5STATUS ,PAGE6STATUS) then 0 else 1 end FROM CUSTOMERS_FORMS 

Para várias colunas, é melhor usar uma instrução CASE, no entanto, para duas colunas numéricas i ej, é possível usar matemática simples:

min (i, j) = (i + j) / 2 – abs (ij) / 2

Esta fórmula pode ser usada para obter o valor mínimo de várias colunas, mas a sua bagunça passada 2, min (i, j, k) seria min (i, min (j, k))