Jaki jest najlepszy sposób na wybranie wartości minimalnej z kilku kolumn?

Biorąc pod uwagę poniższą tabelę w programie SQL Server 2005:

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

Jaki jest najlepszy sposób napisania zapytania, które daje następujący wynik (tj. Takie, które daje ostatnią kolumnę - kolumnę zawierającą wartości minimalne z Col1, Col2 i Col 3 dla każdego wiersza )?

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

AKTUALIZACJA:

Dla wyjaśnienia (jak powiedziałem w komentarzach) w rzeczywistym scenariuszu baza danych jest odpowiednio znormalizowana . Te kolumny „macierzy” nie znajdują się w rzeczywistej tabeli, ale w zestawie wyników wymaganym w raporcie. Nowym wymaganiem jest to, że raport potrzebuje również tej kolumny MinValue. Nie mogę zmienić bazowego zestawu wyników i dlatego szukałem w T-SQL przydatnej karty „wyjście z więzienia”.

Wypróbowałem opisane poniżej podejście CASE i działa, chociaż jest trochę uciążliwe. Jest to również bardziej skomplikowane niż podano w odpowiedziach, ponieważ należy wziąć pod uwagę fakt, że w tym samym wierszu znajdują się dwie wartości minimalne.

W każdym razie pomyślałem, że opublikuję moje obecne rozwiązanie, które, biorąc pod uwagę moje ograniczenia, działa całkiem nieźle. Wykorzystuje operator 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

Powiem z góry, że nie spodziewam się, że zapewni to najlepszą wydajność, ale biorąc pod uwagę okoliczności (nie mogę przeprojektować wszystkich zapytań tylko dla nowego wymagania kolumny MinValue), jest to dość eleganckie „wyjście z więzienia karta".

Istnieje wiele sposobów, aby to osiągnąć. Proponuję użyć opcji Case / When to zrobić. Z 3 kolumnami nie jest tak źle.

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

Używając CROSS APPLY:

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

SQL Fiddle

SELECT ID, Col1, Col2, Col3, 
    (SELECT MIN(Col) FROM (VALUES (Col1), (Col2), (Col3)) AS X(Col)) AS TheMin
FROM Table

W MySQL użyj tego:

select least(col1, col2, col3) FROM yourtable

Możesz użyć podejścia „brutalnej siły” z niespodzianką:

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

Gdy pierwszy warunek zawodzi, gwarantuje, że Col1 nie jest najmniejszą wartością, dlatego można ją wyeliminować z pozostałych warunków. Podobnie w przypadku dalszych warunków. W przypadku pięciu kolumn zapytanie staje się:

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]

Zwróć uwagę, że jeśli istnieje remis między dwiema lub więcej kolumnami, <=upewnij się, że zakończymy CASEinstrukcję tak wcześnie, jak to możliwe.

Gdyby kolumny były liczbami całkowitymi, jak w twoim przykładzie, utworzyłbym funkcję:

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

wtedy kiedy potrzebuję go użyć, zrobiłbym:

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

jeśli masz 5 kolumn, to powyższe staje się

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)

Najlepszym sposobem na to prawdopodobnie nie jest robienie tego - to dziwne, że ludzie nalegają na przechowywanie swoich danych w sposób, który wymaga "gimnastyki" SQL w celu wydobycia znaczących informacji, podczas gdy istnieją znacznie łatwiejsze sposoby osiągnięcia pożądanego rezultatu, jeśli tylko trochę lepiej skonstruuj swój schemat :-)

Prawo sposobem, aby to zrobić, moim zdaniem, ma mieć następującą tabelę:

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

ze ID/Coljako klucz podstawowy (i ewentualnie Coljako dodatkowy klucz, w zależności od potrzeb). Wtedy zapytanie stanie się proste select min(val) from tbli nadal możesz traktować poszczególne „stare kolumny” oddzielnie, używając ich where col = 2w innych zapytaniach. Pozwala to również na łatwą rozbudowę w przypadku wzrostu liczby „starych kolumn”.

To sprawia, że Twoje pytania tak dużo łatwiejsze. Ogólna wskazówka, której zwykle używam, jest taka, że ​​jeśli kiedykolwiek masz coś, co wygląda jak tablica w wierszu bazy danych, prawdopodobnie robisz coś złego i powinieneś pomyśleć o restrukturyzacji danych.

Jeśli jednak z jakiegoś powodu nie możesz zmienić tych kolumn, sugerowałbym użycie wyzwalaczy wstawiania i aktualizowania oraz dodanie kolejnej kolumny, dla której te wyzwalacze ustawiają minimum Col1/2/3. Spowoduje to przeniesienie `` kosztu '' operacji z wyboru na aktualizację / wstawienie, do którego należy - z mojego doświadczenia wynika, że ​​większość tabel bazy danych jest czytana znacznie częściej niż zapisywana, więc ponoszenie kosztów zapisu wydaje się być bardziej wydajne w czasie.

Innymi słowy, minimalny dla wiersza zmienia się tylko gdy jeden z pozostałych kolumn zmienić, więc to , kiedy należy jej obliczenia, a nie za każdym razem wybrać (który jest zmarnowany, jeśli dane nie ulega zmianie). Otrzymałbyś wtedy stół taki jak:

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

Każda inna opcja, która musi podejmować decyzje w selectczasie, jest zwykle złym pomysłem pod względem wydajności, ponieważ dane zmieniają się tylko przy wstawianiu / aktualizowaniu - dodanie kolejnej kolumny zajmuje więcej miejsca w bazie danych i będzie nieco wolniejsze dla wstawek i aktualizacje, ale może być znacznie szybsze w przypadku selekcji - preferowane podejście powinno zależeć od twoich priorytetów, ale, jak wspomniano, większość tabel jest czytana znacznie częściej niż są zapisywane.

Możesz to również zrobić za pomocą zapytania składającego. Wraz ze wzrostem liczby kolumn należałoby zmodyfikować zapytanie, ale przynajmniej byłaby to prosta modyfikacja.

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

To brutalna siła, ale działa

 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

... ponieważ min () działa tylko na jednej kolumnie, a nie między kolumnami.

Zarówno to pytanie, jak i to pytanie, spróbuj odpowiedzieć na to pytanie.

Podsumowując, Oracle ma wbudowaną funkcję do tego celu, w przypadku Sql Server utkniesz albo definiując funkcję zdefiniowaną przez użytkownika, albo używając instrukcji case.

W przypadku wielu kolumn najlepiej jest użyć instrukcji CASE, jednak w przypadku dwóch kolumn liczbowych i i j można użyć prostej matematyki:

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

Tej formuły można użyć, aby uzyskać minimalną wartość wielu kolumn, ale jej naprawdę nieporządny czas po 2, min (i, j, k) wyniesie min (i, min (j, k))

Jeśli możesz utworzyć procedurę składowaną, może ona przyjąć tablicę wartości i możesz to po prostu wywołać.

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

Małe zmiany w zapytaniu składającym:

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

Jeśli używasz SQL 2005, możesz zrobić coś takiego:

;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

W ten sposób nie zgubisz się w tak wielu operatorach :)

Jednak może to być wolniejsze niż inny wybór.

To Twój wybór...

Poniżej korzystam z tabeli tymczasowej, aby uzyskać minimum kilku dat. Pierwsza tabela tymczasowa wysyła zapytanie do kilku połączonych tabel, aby uzyskać różne daty (a także inne wartości zapytania), a druga tabela tymczasowa pobiera następnie różne kolumny i minimalną datę, używając tylu przebiegów, ile jest kolumn dat.

Jest to zasadniczo podobne do zapytania składającego, wymagana jest taka sama liczba przebiegów, ale może być bardziej wydajna (na podstawie doświadczenia, ale wymagałaby testów). Wydajność nie była w tym przypadku problemem (8000 rekordów). Można indeksować itp.

--==================== 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;

Jeśli wiesz, jakich wartości szukasz, zwykle jest to kod stanu, pomocne mogą być następujące informacje:

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

Wiem, że to pytanie jest stare, ale nadal potrzebowałem odpowiedzi i nie byłem zadowolony z innych odpowiedzi, więc musiałem opracować własne, które jest zwrotem w odpowiedzi na @ paxdiablo .

Przybyłem z krainy SAP ASE 16.0 i potrzebowałem tylko rzucić okiem na statystyki niektórych danych, które są zgodnie z IMHO przechowywane w różnych kolumnach jednego wiersza (reprezentują różne czasy - kiedy planowano przybycie czegoś, czego się spodziewano, kiedy akcja się rozpoczęła iw końcu jaki był faktyczny czas). W ten sposób transponowałem kolumny do wierszy tabeli tymczasowej i jak zwykle wykonałem zapytanie.

Uwaga: nie jest to rozwiązanie uniwersalne!

CREATE TABLE #tempTable (ID int, columnName varchar(20), dataValue int)

INSERT INTO #tempTable 
  SELECT ID, 'Col1', Col1
    FROM sourceTable
   WHERE Col1 IS NOT NULL
INSERT INTO #tempTable 
  SELECT ID, 'Col2', Col2
    FROM sourceTable
   WHERE Col2 IS NOT NULL
INSERT INTO #tempTable 
  SELECT ID, 'Col3', Col3
    FROM sourceTable
   WHERE Col3 IS NOT NULL

SELECT ID
     , min(dataValue) AS 'Min'
     , max(dataValue) AS 'Max'
     , max(dataValue) - min(dataValue) AS 'Diff' 
  FROM #tempTable 
  GROUP BY ID

Zajęło to około 30 sekund na zestawie źródłowym 630000 wierszy i wykorzystywało tylko dane indeksowe, więc nie działało to w procesach krytycznych czasowo, ale do rzeczy takich jak jednorazowa inspekcja danych lub raport na koniec dnia, którym możesz być dobrze (ale sprawdź to z kolegami lub przełożonymi, proszę!). Główną zaletą tego stylu było dla mnie to, że mogłem z łatwością używać więcej / mniej kolumn i zmieniać grupowanie, filtrowanie itp., Zwłaszcza po skopiowaniu danych.

Dodatkowe dane ( columnName, maxes, ...) miały pomóc mi w moich poszukiwaniach, więc może nie są potrzebne; Zostawiłem je tutaj, aby może pobudzić kilka pomysłów :-).