여러 열에서 최소값을 선택하는 가장 좋은 방법은 무엇입니까?
SQL Server 2005에서 다음 표가 제공됩니다.
ID Col1 Col2 Col3
-- ---- ---- ----
1 3 34 76
2 32 976 24
3 7 235 3
4 245 1 792
다음 결과를 생성하는 쿼리를 작성하는 가장 좋은 방법은 무엇입니까 (즉, 각 행에 대해 Col1, Col2 및 Col 3의 최소값을 포함하는 열인 최종 열을 생성하는 열 )?
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
최신 정보:
설명을 위해 실제 시나리오에서 (코멘트에서 말했듯이) 데이터베이스가 제대로 정규화되었습니다 . 이러한 "배열"열은 실제 테이블이 아니라 보고서에 필요한 결과 집합에 있습니다. 새로운 요구 사항은 보고서에도이 MinValue 열이 필요하다는 것입니다. 기본 결과 집합을 변경할 수 없으므로 편리한 "탈옥 카드"를 위해 T-SQL을 찾고있었습니다.
아래에 언급 된 CASE 접근 방식을 시도해 보았지만 약간 번거롭지 만 작동합니다. 또한 동일한 행에 두 개의 최소값이 있다는 사실을 수용해야하기 때문에 답변에 명시된 것보다 더 복잡합니다.
어쨌든, 내 제약 조건을 고려할 때 꽤 잘 작동하는 현재 솔루션을 게시 할 것이라고 생각했습니다. 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
나는 이것이 최고의 성능을 제공 할 것이라고 기대하지는 않지만 상황을 감안할 때 (새로운 MinValue 열 요구 사항에 대해서만 모든 쿼리를 다시 설계 할 수는 없음) 매우 우아한 "탈옥 카드".
이를 수행하는 방법은 여러 가지가 있습니다. 내 제안은 케이스 / 언제 사용하는 것입니다. 3 개의 열로 나쁘지 않습니다.
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
사용 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
SELECT ID, Col1, Col2, Col3,
(SELECT MIN(Col) FROM (VALUES (Col1), (Col2), (Col3)) AS X(Col)) AS TheMin
FROM Table
이것을 사용하십시오 :
select least(col1, col2, col3) FROM yourtable
비틀어 "무력한 힘"접근 방식을 사용할 수 있습니다.
SELECT CASE
WHEN Col1 <= Col2 AND Col1 <= Col3 THEN Col1
WHEN Col2 <= Col3 THEN Col2
ELSE Col3
END AS [Min Value] FROM [Your Table]
첫 번째 when 조건이 실패하면 Col1이 가장 작은 값이 아니라는 것을 보장하므로 나머지 조건에서 제거 할 수 있습니다. 후속 조건에서도 마찬가지입니다. 5 개의 열에 대한 쿼리는 다음과 같습니다.
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]
두 개 이상의 열 사이에 동점이있는 경우 가능한 한 빨리 명령문을 <=종료 CASE합니다.
그렇게 할 수있는 가장 좋은 방법은 아마도 되지 는 사람들이 그냥하면 원하는 결과를 달성하기 훨씬 쉬운 방법이있는 의미있는 정보를 추출하는 SQL "체조"를 요구하는 방법으로, 자신의 데이터를 저장 주장하는 것이 이상하다 - 그것을 할 스키마를 좀 더 잘 구성하십시오 :-)
이 작업을 수행 하는 올바른 방법은 다음 표를 만드는 것입니다.
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
와 ID/Col기본 키로 (그리고 아마도 Col여분의 키로서, 사용자의 요구에 따라). 그러면 쿼리가 단순 select min(val) from tbl해지며 where col = 2다른 쿼리에서 사용하여 개별 '이전 열'을 개별적으로 처리 할 수 있습니다 . 또한 '이전 열'수가 증가 할 경우 쉽게 확장 할 수 있습니다.
이렇게하면 쿼리 가 훨씬 쉬워집니다. 당신이 만약 내가 사용하는 경향이 일반적인 가이드 라인이며, 지금까지 데이터베이스 행의 배열처럼 보이는, 당신은 아마 뭔가 잘못하고 있다는 것을 뭔가를하고 데이터를 구조 조정에 대해 생각해야한다.
그러나 어떤 이유로 이러한 열을 변경할 수없는 경우 삽입 및 업데이트 트리거를 사용하고 이러한 트리거가 최소값으로 설정된 다른 열을 추가하는 것이 좋습니다 Col1/2/3. 이렇게하면 작업의 '비용'이 선택에서 업데이트 / 삽입으로 이동합니다. 내 경험상 대부분의 데이터베이스 테이블은 작성된 것보다 훨씬 자주 읽혀 지므로 시간이 지남에 따라 쓰기 비용이 더 효율적입니다.
다른 열 중 하나가 변경 될 때 즉, 행의 최소에만 때문에, 변화 의가 있다고 하면, (데이터가 변경되지 않은 경우 낭비)를 선택 할 때마다 그것을하지 계산해야 할 때. 그러면 다음과 같은 테이블이 생성됩니다.
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
select시간에 결정을 내려야하는 다른 옵션 은 일반적으로 데이터가 삽입 / 업데이트 할 때만 변경되므로 성능 측면에서 좋지 않습니다. 다른 열을 추가하면 DB에서 더 많은 공간을 차지하고 삽입 및 업데이트하지만 선택에 대해 훨씬 더 빠를 수 있습니다 . 선호하는 접근 방식은 우선 순위에 따라 달라야하지만 언급했듯이 대부분의 테이블은 작성된 것보다 훨씬 더 자주 읽습니다 .
예에서와 같이 열이 정수이면 함수를 만듭니다.
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
그런 다음 그것을 사용해야 할 때 나는 할 것입니다.
select col1, col2, col3, dbo.f_min_int(dbo.f_min_int(col1,col2),col3)
열이 5 개 있으면 위는
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 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
이것은 무차별 대입이지만 작동합니다.
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
... min ()은 한 열에서만 작동하고 열에서는 작동하지 않기 때문입니다.
모두 이 질문에 그리고 이 질문은 이 대답을하려고합니다.
요약하면 Oracle에는이를위한 내장 기능이 있다는 것입니다. Sql Server에서는 사용자 정의 함수를 정의하거나 case 문을 사용하는 데 어려움을 겪고 있습니다.
저장 프로 시저를 만들 수 있다면 값의 배열을 취할 수 있으며이를 호출 할 수 있습니다.
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
통합 쿼리에 대한 약간의 비틀기 :
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
SQL 2005를 사용하는 경우 다음과 같이 깔끔하게 수행 할 수 있습니다.
;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
이렇게하면 많은 연산자에서 길을 잃지 않습니다. :)
그러나 이것은 다른 선택보다 느릴 수 있습니다.
당신의 선택입니다 ...
Below I use a temp table to get the minimum of several dates. The first temp table queries several joined tables to get various dates (as well as other values for the query), the second temp table then gets the various columns and the minimum date using as many passes as there are date columns.
This is essentially like the union query, the same number of passes are required, but may be more efficient (based on experience, but would need testing). Efficiency wasn't an issue in this case (8,000 records). One could index 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
For multiple columns its best to use a CASE statement, however for two numeric columns i and j you can use simple math:
min(i,j) = (i+j)/2 - abs(i-j)/2
This formula can be used to get the minimum value of multiple columns but its really messy past 2, min(i,j,k) would be min(i,min(j,k))
SELECT [ID],
(
SELECT MIN([value].[MinValue])
FROM
(
VALUES
([Col1]),
([Col1]),
([Col2]),
([Col3])
) AS [value] ([MinValue])
) AS [MinValue]
FROM Table;
If you know what values you are looking for, usually a status code, the following can be helpful:
select case when 0 in (PAGE1STATUS ,PAGE2STATUS ,PAGE3STATUS,
PAGE4STATUS,PAGE5STATUS ,PAGE6STATUS) then 0 else 1 end
FROM CUSTOMERS_FORMS
I know that question is old, but I was still in the need of the answer and was not happy with other answers so I had to devise my own which is a twist on @paxdiablo´s answer.
I came from land of SAP ASE 16.0, and I only needed a peek at statistics of certain data which are IMHO validly stored in different columns of a single row (they represent different times - when arrival of something was planned, what it was expected when the action started and finally what was the actual time). Thus I had transposed columns into the rows of temporary table and preformed my query over this as usually.
N.B. Not the one-size-fits-all solution ahead!
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
This took some 30 seconds on source set of 630000 rows and used only index-data, so not the thing to run in time-critical process but for things like one-time data inspection or end-of-the-day report you might be fine (but verify this with your peers or superiors, please!). Main bonus of this style for me was that I could readily use more/less columns and change grouping, filtering, etc., especially once data was copyied over.
The additional data (columnName, maxes, ...) were to aid me in my search, so you might not need them; I left them here to maybe spark some ideas :-).
'programing tip' 카테고리의 다른 글
| ggplot2를 사용하여 축에 정수 값만 표시하는 방법 (0) | 2020.11.17 |
|---|---|
| .net 코어에서 유효하지 않은 SSL 인증서 우회 (0) | 2020.11.17 |
| MySQL Workbench에서 전체 데이터베이스 스크립트를 생성하는 방법은 무엇입니까? (0) | 2020.11.17 |
| 하나의 그래프에 여러 상자 그림 플로팅 (0) | 2020.11.17 |
| numpy.timedelta64 값에서 일 추출 (0) | 2020.11.17 |