![[-]](/moniwiki/imgs/plugin/arrup.png "[-]")
![[+]](/moniwiki/imgs/plugin/arrdown.png "[+]")
[edit]
1 기본 문제 #
다음과 같은 테이블에서 ikey별a, b, c 중 가장 작은 값을 찾는 것이 문제다. 단, 값은 0보다 커야 한다. ikey는 고유하지 않으며, 값에 따라서 1건이 될 수 있고, 1만 건이 될 수 도 있다.
ikey a b c ----------- ----------- ----------- ----------- 244 382 961 368 275 623 372 947 212 148 744 466 783 75 479 678 301 931 631 998 569 872 154 435 363 654 257 388 437 655 109 327 382 149 534 725 846 471 106 187
이런 컬럼, 컬럼, 컬럼는 다중값 속성을 표현한 테이블로써 설계상 그리 좋지 않은 구조이다. 비정규화된 테이블로 볼 수 있으며, 컬럼이 추가된다면 테이블의 구조를 변경해야 하는 단점이 있다. 기존의 업무는 그대로이고, 단지 컬럼이 더 필요해 졌을 뿐이라면 이 테이블 구조는 좋지 않은 구조이다.
--예제데이터생성스크립트
set nocount on
set statistics io on
if object_id('temp..#temp') is not null
drop table #temp
create table #temp(
ikey int
, a int
, b int
, c int
)
go
declare
@i int
, @i_key int
, @a int
, @b int
, @c int
set @i = 1
while(@i <= 100000)
begin
set @i_key = replace(right(cast(rand() as varchar), 3) , '.', '')
set @a = replace(right(cast(rand() as varchar), 3), '.', '')
set @b = replace(right(cast(rand() as varchar), 3), '.', '')
set @c = replace(right(cast(rand() as varchar), 3), '.', '')
insert #temp values(@i_key, @a, @b, @c)
set @i = @i + 1
end
create index idx on #temp(ikey)
go
예제 데이터 생성 스크립트를 실행하고, ikey의 카디널리티가 가장 많은 것을 보자.
select top 100 ikey, count(*) cnt from #temp group by ikey order by 2 desc ikey cnt ----------- ----------- 804 145 935 141 687 140 918 140 754 138 207 137 588 137
필자는 ikey = 804가 145건으로 가장 많았다. 쉽게 생각하면 다음과 같은 SQL을 작성할 것이다. 그러나 아래와 같은 SQL은 똑같은 데이터를 3번이나 읽어야 했다.
set statistics profile on
--SQL1: UNION ALL을이용
select ikey, min(val) as min_val
from(
select ikey, min(a) as val
from #temp
where a > 0 and ikey = 804
group by ikey
union all
select ikey, min(b)
from #temp
where b > 0 and ikey = 804
group by ikey
union all
select ikey,min(c)
from #temp
where c > 0 and ikey = 804
group by ikey
) a
group by ikey
--검색수3, 논리적읽기수444, 물리적읽기수0, 미리읽기수0, LOB 논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.
--SQL2: Cross Join을이용
select
ikey
, min(
case
when id = 1 and a > 0 then a
when id = 2 and b > 0 then b
when id = 3 and c > 0 then c
end) min_val
from (
select
*
from #temp
where ikey = 804 ) a cross join (select 1 id union all select 2 union all select 3) b
group by ikey
--검색수1, 논리적읽기수148, 물리적읽기수0, 미리읽기수0, LOB 논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.
SQL1의 논리적 읽기 수는 444이고, SQL2는 148이다. 즉, 148 * 3 = 444이므로 3배의 논리적 읽기 수 차이가 나는 것이다. 또한 두 쿼리를 동시에 실행시켜 실제 실행계획에서 비용의 비교를 해보면 필자의 경우는 SQL1이 81%이고, SQL2는 19%이다. 약 4배 차이다. 만일 단일 사용자 환경이 아닌 매우 많은 사용자가 위와 같이 쿼리를 DBMS에 날린다면 부하는 좀 될 것이다. 실제 1만명이 이 쿼리를 동시에 날린다고 생각해 보자. SQL1과SQL2가 어떤 차이를 부를까?
[edit]
2 문제2 #
다음과 같은 테이블이 있다. 원하는 결과를 얻을 수 있는 쿼리를 짜보라.
USE tempdb;
IF EXISTS(SELECT * FROM sysobjects
WHERE ID = (OBJECT_ID('#temp')) AND xtype = 'U')
DROP TABLE #temp
GO
CREATE TABLE #temp(
[#] [tinyint] NULL,
[1] [tinyint] NULL,
[2] [tinyint] NULL,
[3] [tinyint] NULL,
[4] [tinyint] NULL,
[5] [tinyint] NULL,
[6] [tinyint] NULL,
[7] [tinyint] NULL,
[8] [tinyint] NULL,
[9] [tinyint] NULL
)
INSERT INTO #temp VALUES(1, 8, 5, 2, 8, 6, 8, 9, 9, 1);
INSERT INTO #temp VALUES(2, 3, 8, 6, 4, 9, 3, 6, 9, 6);
INSERT INTO #temp VALUES(3, 2 , 8, 9, 3, 4, 3, 7, 4, 9);
INSERT INTO #temp VALUES(4, 1, 9, 4, 5, 7, 9, 9, 5, 6);
INSERT INTO #temp VALUES(5, 7, 6, 2, 9, 7, 9, 9, 6, 8);
INSERT INTO #temp VALUES(6, 5, 2, 9, 6, 5, 8, 6, 8, 9);
INSERT INTO #temp VALUES(7, 4, 4, 2, 5, 9, 5, 8, 6, 9);
INSERT INTO #temp VALUES(8, 5, 9, 9, 3, 7, 7, 6, 2, 6);
INSERT INTO #temp VALUES(9, 2, 3, 2, 7, 9, 6, 6, 2, 9);
/*
# 1 2 3 4 5 6 7 8 9
---- ---- ---- ---- ---- ---- ---- ---- ---- ----
1 8 5 2 8 6 8 9 9 1
2 3 8 6 4 9 3 6 9 6
3 2 8 9 3 4 3 7 4 9
4 1 9 4 5 7 9 9 5 6
5 7 6 2 9 7 9 9 6 8
6 5 2 9 6 5 8 6 8 9
7 4 4 2 5 9 5 8 6 9
8 5 9 9 3 7 7 6 2 6
9 2 3 2 7 9 6 6 2 9
*/
[edit]
3 해결 #
SQL Server 2005 이상의 버전이라면 Pivot/UnPivot을 사용해도 된다. 어쨌든 기본은 Cross Join을 이용해야 쉽다는 것..
with temp(no)
as
(
select 1 no
union all
select no + 1 from temp
where no + 1 <= 9
)--2005버전만 된다. temp라는 테이블은 0 ~ 9의 숫자가 있는 Dumy테이블이라 생각하면 된다.
select
[#] [Row number]
, b.no [Column number]
, case
when b.no=1 then [1]
when b.no=2 then [2]
when b.no=3 then [3]
when b.no=4 then [4]
when b.no=5 then [5]
when b.no=6 then [6]
when b.no=7 then [7]
when b.no=8 then [8]
when b.no=9 then [9]
end [Value]
from #temp a
cross join temp b
/*
Row number Column number Value
---------- ------------- -----
1 1 8
2 1 3
3 1 2
4 1 1
5 1 7
6 1 5
7 1 4
...
*/