손 호성 (Kind511@dreamwiz.com) Deep Inside T-SQL Query Programming 손 호성 (Kind511@dreamwiz.com)

Presentation on theme: "손 호성 (Kind511@dreamwiz.com) Deep Inside T-SQL Query Programming 손 호성 (Kind511@dreamwiz.com)"— Presentation transcript:

1 손 호성 (Kind511@dreamwiz.com)
Deep Inside T-SQL Query Programming 손 호성

2 강사 소개 현) 맥스무비 연구개발 팀장 Deep Inside T-SQL 테크닉(영진닷컴)
SQL Server 2000 Bible (영진닷컴) Practical Database Design (삼각형프레스) SQL Megazine “Deep inside SQL Server 2000” (From ~)

3 Agenda The Query Query Components Join & Subquery Architecture
Query Optimizer Real-world Technique

4 The Query

5 데이터베이스는 테트리스와 같다 Insert 사건 발생 사건 변경 Delete 사건 종료 Update
데이터베이스는 테트리스와 같은 것이다. 데이터는 무작위로 떨어져 내리고, 그것들은 순서를 두지 않고 쌓여만 간다. 사건은 인서 사건 종료 Update

6 데이터베이스는 테트리스와 같다 Select 쿼리 무작위로 쌓인 데이터 블럭들 의도된 집합
데이터베이스는 테트리스와 같은 것이다. 데이터는 무작위로 떨어져 내리고, 그것들은 순서를 두지 않고 쌓여만 간다. 사건은 인서 의도된 집합

7 실제 데이터의 더미에서 이를 찾는 과정 쿼리라는 것은 어떤 결과를 예상하고, 이 결과를 만들기 위해서 방법을 지시하는 것이다.
쿼리는 의도되는 결과를 유도하기 위해서 던지는 질문이다.

8 실제 데이터의 더미에서 이를 찾는 과정 쿼리라는 것은 어떤 결과를 예상하고, 이 결과를 만들기 위해서 방법을 지시하는 것이다.
쿼리는 의도되는 결과를 유도하기 위해서 던지는 질문이다.

9 실제 데이터의 더미에서 이를 찾는 과정 쿼리라는 것은 어떤 결과를 예상하고, 이 결과를 만들기 위해서 방법을 지시하는 것이다.
쿼리는 의도되는 결과를 유도하기 위해서 던지는 질문이다.

10 쿼리를 작성할때 고민할것! 더 짧은 탐색 경로를 가지도록 해야 한다 - 커버드 인덱스
더 짧은 탐색 경로를 가지도록 해야 한다 - 커버드 인덱스 더 적은 페이지들을 읽도록 쿼리를 작성해야 한다 - 로우의 길이를 줄여라 - 범위를 제한하라 읽은 순서 그대로 사용할 수 있다면 좋다 - 클러스터드 인덱스를 이용 한번 읽어들인 데이터를 재활용해라 - 복제 기법 사용

11 쿼리가 프로그래밍인가? 쿼리라는 것은 “Route selection” 프로그래밍이란 “Make a program”
어떤 일을 하는 무엇을 만드는것? 일을 어떻게 처리하도록 지시하는것? 로봇을 만드는것? 로봇을 일을 하게 만드는 것?

12 Query와 Question의 차이 Query 이 결과를 내려면 어떻게 해야 하나? How to ... ?
Question 이 문제에 대한 답은 뭐냐? What ... ? 쿼리라는 것은 어떤 결과를 예상하고, 이 결과를 만들기 위해서 방법을 지시하는 것이다. 쿼리는 의도되는 결과를 유도하기 위해서 던지는 질문이다.

13 결국 쿼리라는 작업은? 최대한 대상이 되는 집합을 줄이고 가장 적은 비용을 들이면서 원하는 형태로 변형하는 작업
쿼리라는 것은 어떤 결과를 예상하고, 이 결과를 만들기 위해서 방법을 지시하는 것이다. 쿼리는 의도되는 결과를 유도하기 위해서 던지는 질문이다.

14 Query Components

15 몇 개의 SQL 키워드로 모든 쿼리를 해결 SELECT select_list FROM table_source
[ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ] 몇가지 돼지 않는 SELECT 키워드를 응용하면, 어떠한 결과집합도 찾아낼 수가 있다.

16 Query란? SELECT문 혹은 쿼리라는 것은 단순히 테이블의 내용을 가져(Fetch)오는 것이 아니라
의도하는 집합(Set)을 만들어내는 과정이다. 따라서, 그 쿼리의 구성요소(Components)들의 의미들도 이러한 집합에 관련해서 생각해야만 한다.

17 Think Different !!!

18 사고를 바꾼 단순한 예제:MAX 함수 #SalesName

19 사고를 바꾼 단순한 예제:MAX 함수 “점포별로 물품 수량이 몇개씩인가?” 질문>
select stor_id, sum(qty) totalQty from #SalesName group by stor_id

20 사고를 바꾼 단순한 예제:MAX 함수 점포명도 나왔으면 좋겠는데, 어떻게 하지? 외부 조인 잘못된 결과 1>
select x.stor_id, y.stor_name, x.totalQty from ( select stor_id, sum(qty) totalQty from #SalesName group by stor_id ) x left outer join pubs.dbo.stores y on x.stor_id = y.stor_id 외부 조인 2> select stor_id,stor_name,sum(qty) from #SalesName group by stor_id,stor_name 잘못된 결과

21 사고를 바꾼 단순한 예제:MAX 함수 점포명도 나왔으면 좋겠는데, 어떻게 하지? 집합의 속성을 알고 있어야만 사용 가능하다
Solution> select stor_id,max(stor_name) stor_name, sum(qty) totalQty from #SalesName group by stor_id 집합의 속성을 알고 있어야만 사용 가능하다

22 사고를 바꾼 단순한 예제:MAX 함수 최대값을 찾는다 -> 집계 범위의 요소를 선택함
MAX 함수는 SQLER들에게 가장 쉬운 함수 중의 하나이다. 하지만, MAX 함수를 단순히 최대값(Max value)를 찾는 데에만 사용한다면, 함수의 본래 의미를 제대로 사용하는 것이 아니다. 개념의 전환이 필요하다. 최대값을 찾는다 -> 집계 범위의 요소를 선택함 “따라서 동일 범위 혹은 의미적으로 유사범위이면 범위 중에서 어떤 값을 선택하더라도 동일하다”

23 SELECT 구성요소의 의미 SELECT select_list FROM table_source
[ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ] 집합의 원소를 선택 원본 집합을 선택 데이터 블럭을 찾아가는 경로 집합을 요약 요약된 결과를 걸러주는 역할 몇가지 돼지 않는 SELECT 키워드를 응용하면, 어떠한 결과집합도 찾아낼 수가 있다. 각 사건들의 순서를 지정

24 Count & Sum Count와 Sum 함수는 본질적으로 같은 함수.
만일 어떤 인스턴스가 출현하고(1) 출현하지 않고(0)의 값만을 가진다면 Count=Sum Count는 인스턴스의 출현만을 계산하고 Sum은 인스턴스의 누적치를 계산가능 몇가지 돼지 않는 SELECT 키워드를 응용하면, 어떠한 결과집합도 찾아낼 수가 있다.

25 Count 처럼 사용된 Sum select CustomerID,
SUM((Case when ShipCountry = 'USA' then 1 else 0 end)) USA_TotalOrders, SUM((Case when ShipCountry = 'UK' then 1 else 0 end)) UK_TotalOrders from Northwind..Orders group by CustomerID order by 2 desc,3 desc

26 Count & Sum 컴비네이션

27 Count & Sum 컴비네이션 select (case when x.TotalOrders < 20 then '00-19'
when x.TotalOrders >= 20 and x.TotalOrders < 30 then '20-29' when x.TotalOrders >= 30 then '30-99' end) UserGroup, Sum(x.TotalOrders) TotalOrders from ( select CustomerID,Count(OrderID) TotalOrders from Northwind..Orders group by CustomerID ) x group by end)

28 Min & Max 나. 코끼리 나. 개미 코끼리 옆에 붙은 개미는 큰 영향을 미치지 않는다

29 Min & Max “각 도서 분류별로 연간 가장 많이 팔린 도서번호는?” Pubs.dbo.Titles Result set

30 Min & Max 없다!!! 도서번호가 “각 도서 분류별로 연간 가장 많이 팔린 도서번호는?”
이게 단순한 Group By 쿼리일까? select Type,max(ytd_sales) max_ytd_sales from pubs.dbo.titles group by Type 없다!!! 도서번호가

31 Min & Max “각 도서 분류별로 연간 가장 많이 팔린 도서번호는?”
select x.type,y.title_id,x.max_ytd_sales from ( select Type,max(ytd_sales) max_ytd_sales from pubs.dbo.titles group by Type ) as x left outer join pubs.dbo.titles as y on x.type = y.type and x.max_ytd_sales = y.ytd_sales

32 Min & Max “각 도서 분류별로 연간 가장 많이 팔린 도서번호는?” select Type, substring( max(
dbo.LPAD(ytd_sales,10,0) + title_id ) ,11,6) MaxSaledTitle, max(ytd_sales) Max_ytd_sales from pubs.dbo.titles group by type

33 Min & Max select 'P' + dbo.LPad(37985,9,'0')
LPad Function CREATE FUNCTION LPAD( @s varchar(255) ) returns varchar(255) as BEGIN return END select 'P' + dbo.LPad(37985,9,'0') P

34 Min & Max 수치를 고정 문자열로 변환하면 정렬 순서는 동일함 select type,
dbo.LPAD(ytd_sales,10,0) ytd_sales, title_id from pubs.dbo.titles order by type, ytd_sales desc, title_id 뒤의 Title_id 컬럼은 정렬에 미미한 역할을 수행

35 Min & Max 두 필드를 결합해도 정렬 순서는 그대로 유지 select type,
dbo.LPAD(ytd_sales,10,0) + title_id ytd_sales_title_id from pubs.dbo.titles order by type, ytd_sales_title_id desc

36 Min & Max 결합된 컬럼을 분리하면 원래의 값을 찾아내는 것이 가능
select Type,substring(max(dbo.LPAD(ytd_sales,10,0) + title_id),11,6) MaxSaledTitle,max(ytd_sales) Max_ytd_sales from pubs.dbo.titles group by type

37 Min & Max 두 쿼리의 실행 계획 비교 화면

38 Min & Max 만일 뒤의 값이 수치와 같이 고정되지 않은 값이라면 select type,
max(ytd_sales) max_ytd_sales, (max(ytd_sales + price / ) - max(ytd_sales)) * price from pubs.dbo.titles group by type 아주 미미하게 나눈 값을 더하더라도 정렬 순서는 크게 다르지 않다.

39 Query Optimizer

40 쿼리가 어떻게 해석되는가? 1. 쿼리 실행 계획 확인 [Trivial plan optimization]
2. 쿼리 단순화 및 통계 정보 로딩 [Simplification & Stats Loading] 3. 비용 산정 [Cost Estimation] 4. 최종 최적화 수행 [Full Optimization]

41 쿼리가 어떻게 해석되는가?

42 쿼리가 어떻게 해석되는가? 컴파일 과정 파싱 Parsing 시퀀스 트리를 만드는게 목적 문법 검사 수행
SQL 문을 컴파일러 이해 가능한 구조로 변경 정규화 Normalization 개체 바인딩

43 쿼리가 어떻게 해석되는가? INSERT / UPDATE / DELETE & SELECT만이 옵티마이징 대상이 됨

44 쿼리가 어떻게 해석되는가? Trivial Plan Optimization
옵티마이저가 이미 최적화 계획을 알고 있는 경우 재사용 Syntatic Transformation 규칙적으로 변경 가능한 오퍼레이션들을 정리

45 쿼리가 어떻게 해석되는가? 쿼리 재사용[ Trivial plan optimization]
이미 캐쉬에 실행 계획이 있다면 이를 재사용하자!!!

46 쿼리가 어떻게 해석되는가? 쿼리 재사용[ Trivial plan optimization] 단계 실행 쿼리 파싱 시간 1
select * from pubs.dbo.employee 63 2 3 select * from employee 46 4 select * from pubs.dbo.employee where emp_id = 'A-C71970F' 5 select * from pubs.dbo.employee where emp_id = 'AMD15433F' 6 select * from PUBS.DBO.EMPLOYEE where emp_id = 'AMD15433F' 45 7 select * from PUBS.DBO.EMPLOYEE where EMP_ID = 'AMD15433F' 43 8 CacheProc 'A-C71970F' 47 9 CacheProc 'AMD15433F' 10 exec CacheProc 'AMD15433F' 11 CACHEPROC 'AMD15433F'

47 쿼리가 어떻게 해석되는가? 쿼리 재사용[ Trivial plan optimization]

48 쿼리가 어떻게 해석되는가? SARGs [Search Arguments]의 목적은 각 조건절을 비교해서 가져와야할 로우의 수를
줄여 줄 수 있는 핵심 조건이 무엇인지를 판단 알다 시피, =, Between, >,<, Like ‘x%’ 등과 같은 Left-to-right 비교문들이 가능 Right-to-left 비교문들인 like ‘%x’ 컬럼이 변형되어 인덱스 이용 불가인 경우 <>와 같이 전체 값을 비교해야만 결과를 아는 경우는 Non-SARGs AND => 단일 SARG OR => 복수 SARGs로 변환

49 쿼리가 어떻게 해석되는가? 첫번째 컬럼을 이용해서 SARGs로 유용한 인덱스가 있는지를 확인함.
이떄 선택도(Selectivity) 통계 데이터를 이용하여 조건을 판단 인덱스를 통해서 반환할 로우수를 히스토그램 정보 기반으로 판단하여 예측

50 쿼리가 어떻게 해석되는가? 각 스텝의 조인 순서와 조인 메서드(Loop, Merge, Hash) 결정하며, 이때 발생 가능한
논리적 읽기수와 소요되는 메모리량에 기반해서 판단(이후 다시 설명)

51 쿼리가 어떻게 해석되는가? 실행 계획들은 프로시저 캐시에 탑재됨(실제로 데이터 캐시와 프로시저 캐시가 구분되지는 않음)
Plan Aging 임시 쿼리(Ad-hoc query)는 거의 무효화되는 비용 프로시저는 초기 비용으로 세팅됨 자주 사용되는 실행 계획들은 카운팅되어서 누적 사용되며, 오랜 기간 캐시에 남아 있을 수 있음 Master.dbo.syscacheobjects 테이블에서 조회 가능

52 쿼리가 어떻게 해석되는가? 실행 계획은 Memory Grant Scheduler로 보내어짐
실행 계획에 저장된 실행시 필요한 최소 및 최대 요구 메모리를 기준으로 실행을 결정함 요구된 메모리의 최소 50% 이상의 메모리를 이용할 수 있을때, 실행함. Sort, Merge, Hash가 없다면, 곧바로 실행함. 실행 대기중으로 25초 가량(예상 실행 시간의 25배) 이 지난 후에도 가용한 메모리가 없으면 실행 중지

53 쿼리 옵티마이저가 더 낳은 선택을 할 수 있도록 쿼리를 만들자 !!!

54 Join Architecture

55 Join Method : Summary

56 Join Method : Nested Loop
For i=1 To 100 Some Code ... Next Loop 범위를 탐색 For i=1 To 100 For j=1 To 100 If i=J Then ... Some Code ... Next Nested Loop 두개의 범위를 비교

57 Join Method : Nested Loop

58 Join Method : Sort Merge

59 Join Method : Hash Match

60 Join Method : Summary 포커에서 카드가 완전한지 여부를 확인할 때
네스티드 루프 카드 한장을 뽑아서 나머지 카드들이랑 다 비교해 본다. 소트 머지 카드를 네개의 그룹으로 분류해서 정렬하고 한번에 한장씩 들춰낸다. 해시 매치 카드를 한장씩 꺼내서 카드를 1번 해시에서 조커 해시까지 충당해 넣는다 만일 4장이 모이지 않은 그룹은 해당 카드가 모자란 것이다.

61 Subquery Architecture

62 서브 쿼리 평면화 [Subquery Flattening]
상관되지 않은 서브 쿼리 평면화된 서브 쿼리 [Flattened subquery] 실체화된 서브 쿼리 [Matierlized subquery] 상관된 서브 쿼리 [Correlated subquery] 인라인 뷰 [Inline view]

63 실체화된 서브쿼리 일반적인 서브쿼리로 값이 상수화되어서 처리
select pub_name from pubs.dbo.publishers where pub_id in ('0877') where pub_id = ('0877') select title from pubs.dbo.titles where ytd_sales = (select max(ytd_sales) from pubs.dbo.titles) 일반적인 서브쿼리들로 내부 쿼리라는 의미는 안의 결과가 먼저 처리된다는 의미

64 서브 쿼리 평면화 [Subquery Flattening]
서브쿼리이지만 조인으로 처리됨 select pub_name from pubs.dbo.publishers where pub_id in ( select pub_id from pubs.dbo.titles where type = 'business‘ )

65 서브 쿼리 평면화 [Subquery Flattening]

66 서브 쿼리 평면화 [Subquery Flattening]
select * from some_table whre column in (수십건) whre column in (수십만건) 일반적으로 조인으로 처리되는 것이 비용이 저렴

67 T-SQL Common Technique

68 기본 기술 - Dictionary Table
모든 것을 프로그램으로 처리하려 하지 말자! 테스트용 테이블로 일자별 판매량을 가지는 주문 테이블 1998년도 판매 건수 : 40만건

69 기본 기술 - Dictionary Table
select datepart( weekday, cast( substring(pdate,1,4) + '-' + substring(pdate,5,2) + '-' + substring(pdate,7,2) as smalldatetime) ) 요일, avg(sales) 평균판매량 from date_sales where pdate between ' ' and ' ' group by datepart(weekday,cast(substring(pdate,1,4) + '-' + substring(pdate,5,2) + '-' + substring(pdate,7,2) as smalldatetime)) order by 1 1998년도 요일별 평균 판매량?

70 기본 기술 - Dictionary Table
모든 것을 프로그램으로 처리하려 하지 말자! 일자를 가지고 있는 사전 테이블로 1998년도 365건

71 기본 기술 - Dictionary Table
프로그램으로 해결하려 하지 말고, 새로운 집합을 만들어서 해결 select b.pweek 요일 ,avg(a.sales) 평균판매량 from date_sales a inner join Dic_date b on a.pdate = b.pdate and a.pdate between ' ' and ' ' group by b.pweek order by 1

72 기본 기술 - Dictionary Table
집합과 조인으로 처리하는 것이 데용량 DB에서 유리

73 기본 기술 - Dictionary Table
1998년 일요일날 판매된 데이터를 보자 !!! select * from date_sales where datepart(weekday, cast(substring(pdate,1,4) + '-' + substring(pdate,5,2) + '-' + substring(pdate,7,2) as smalldatetime)) = 1 and pdate between ' ' and ' ‘ select pdate,sales where pdate in ( select pdate from dic_date where pweek =1 and pdate between ' ' and ' ')

74 기본 기술 - Dictionary Table
1998년 일요일날 판매된 데이터를 보자 !!!

75 기본 기술 - 인라인 메모리 뷰 select '남자' c1, '여자' c2 select 1 num union all
존재하지 않는 가상의 데이터를 만드는 방법 select '남자' c1, '여자' c2 select 1 num union all select 2 select 3

76 기본 기술 - Case When SQL에 동적인 힘을 부여하는 문장 select ( case type
when 'UNDECIDED' then 'business' else type end), sum(ytd_sales) from pubs.dbo.titles group by ( else type end)

77 기본 기술 - Case When SQL에 동적인 힘을 부여하는 문장 select * from pubs.dbo.titles
where ytd_sales >= ( case type when 'UNDECIDED' then 0 else 2000 end) (ytd_sales >= 0 and type=‘UNDECIDED’) or (ytd_sales >= 2000 and type <> ‘UNDECIDED’)

78 기본 기술 – Rowset doubling 집합 복제의 주요 기능들 1.요소의 복사 2. 존재하지 않는 데이터를 발생
3. 집합을 여러 차원으로 복사

79 기본 기술 – Rowset doubling 하나의 원소를 가지는 테이블과 크로스 조인
집합 1 집합 2 결과 집합 A A 1 B B 1 1 C C 1 D D 1 동일한 원소를 집합 1에 모두 복제하는 역할

80 기본 기술 – Rowset doubling 하나의 원소를 가지는 테이블과 크로스 조인
create table #DblTest(c1 char(1)) insert #DblTest values('A') insert #DblTest values('B') insert #DblTest values('C') select a.c1,b.c2 from #DblTest a,(select 1 c2) b c1 c2 A 1 B 1 C 1

81 기본 기술 – Rowset doubling 일반적으로 한 쿼리에서 하나의 서브쿼리 값을 연속으로 사용해야 하는 경우
select title_id,sum(qty) title_qty, (select avg(qty) avgQty from pubs.dbo.sales) avgQty, (sum(qty)-(select avg(qty) avgQty from pubs.dbo.sales)) calcQty from pubs.dbo.sales group by title_id

82 기본 기술 – Rowset doubling 이를 크로스 조인하면 요소 값을 복제 하여 사용
select title_id,title_qty,avgQty,title_qty-avgQty as calcQty from ( select title_id,sum(qty) title_qty from pubs.dbo.sales group by title_id ) as a, select avg(qty) avgQty from pubs.dbo.sales ) as b

83 기본 기술 – Rowset doubling 두 쿼리의 성능 비교

84 기본 기술 – Rowset doubling 두개 이상의 원소를 가지는 테이블과 크로스 조인 집합 1 집합 2 결과 집합 A 1
원래 집합 B 1 1 A C 1 B D 1 C A 2 D 복제 집합 B 2 2 C 2 D 2

85 기본 기술 – Rowset doubling 존재하지 않는 값의 발생 !!! create table CustomerArea (
area varchar(10), gender varchar(2), something int ) insert CustomerArea values('서울','남',100) insert CustomerArea values('부산','여',150) insert CustomerArea values('청주','여',200) insert CustomerArea values('서울','여',120) insert CustomerArea values('경기','남',118)

86 기본 기술 – Rowset doubling 지역별 성별 인구수를 집계하자.
select area,gender,sum(something) total from CustomerArea group by area,gender area gender total 경기 남 서울 남 부산 여 서울 여 청주 여

87 기본 기술 – Rowset doubling 이를 크로스 조인하면 요소 값을 복제 하여 사용
select a.area,a.gender,b.gender,a.something from CustomerArea a ,(select '남' gender union all select '여') b area gender gender something 서울 남 남 서울 남 여 부산 여 남 부산 여 여 청주 여 남 청주 여 여 서울 여 남 서울 여 여 경기 남 남 경기 남 여

88 기본 기술 – Rowset doubling 이를 크로스 조인하면 요소 값을 복제 하여 사용 select a.area,
b.gender, sum(case a.gender when b.gender then something else 0 end) TotSomething from CustomerArea a ,(select '남' gender union all select '여') b group by a.area,b.gender order by 1,2 Area Gender TotSomething 경기 남 118 여 부산 150 서울 100 120 청주 200

89 기본 기술 – 복합해서 사용하기 가공된 집합 A 집합 하나의 집합으로 A 집합 요약된 복사본 A 집합 복사본

90 기본 기술 – 복합해서 사용하기 Group By된 집합을 하나 더 복제
select y.c1,x.area,x.gender,x.totSomething from ( select area,gender,sum(something) totSomething from CustomerArea group by area,gender ) x, (select 1 c1 union all select 2) y order by y.c1

91 기본 기술 – 복합해서 사용하기 Group By된 집합을 하나 더 복제 c1 area gender totSomething
경기 남 서울 남 부산 여 서울 여 청주 여 청주 여 서울 여 부산 여 서울 남 경기 남

92 기본 기술 – 복합해서 사용하기 복제 데이터를 집계하기 위해서 같은 요소로 편집 select
(case y.c1 when 2 then '합계' else x.area end) 지역, (case y.c1 when 2 then ' ' else x.gender end) 성별, x.totSomething from ( select area,gender,sum(something) totSomething from CustomerArea group by area,gender ) x, (select 1 c1 union all select 2) y order by 1,2

93 기본 기술 – 복합해서 사용하기 복제 데이터를 집계하기 위해서 같은 요소로 편집 지역 성별 totSomething
경기 남 부산 여 서울 남 서울 여 청주 여 합계 합계 합계 합계 합계

94 기본 기술 – 복합해서 사용하기 Group By된 집합을 하나 더 복제 select
(case y.c1 when 2 then '합계' else x.area end) 지역, (case y.c1 when 2 then ' ' else x.gender end) 성별, sum(x.totSomething) 합계 from ( select area,gender,sum(something) totSomething from CustomerArea group by area,gender ) x, (select 1 c1 union all select 2) y group by (case y.c1 when 2 then 'TOT' else 'PER' end), (case y.c1 when 2 then '합계' else x.area end), (case y.c1 when 2 then ' ' else x.gender end)

95 기본 기술 – 복합해서 사용하기 Group By된 집합을 하나 더 복제 지역 성별 합계
지역 성별 합계 경기 남 부산 여 서울 남 서울 여 청주 여 합계

96 열과 행의 교환 - Columns to rows
SalesPerson table Person Sales1 Sales2 Sales3 Sales4 Philip 100 120 140 90 Brown 80 79 110 Lay 200 280 460 500 Chris 108 109 125 Mckena 30 Branda 15 20 40 55 Jornan 18 160 300

97 열과 행의 교환 - Columns to rows
SalesPerson 테이블의 모든 컬럼을 로우로 펼친다면 >> 가장 쉬운 방법은 Union all select person,'1분기' term, sales1 from SalesPerson union all select person,'2분기' term, sales2 from SalesPerson select person,'3분기' term, sales3 from SalesPerson select person,'4분기' term, sales4 from SalesPerson

98 열과 행의 교환 - Columns to rows
SalesPerson 테이블의 모든 컬럼을 로우로 펼친다면 >> 컬럼 수만큼의 복제를 이용할 수 있음 select person, (case y.num when 1 then '1분기' when 2 then '2분기' when 3 then '3분기' when 4 then '4분기' end) term, when 1 then sales1 when 2 then sales2 when 3 then sales3 when 4 then sales4 end) sales from SalesPerson x, (select 1 num union all select 2 union all select 3 union all select 4) y

99 열과 행의 교환 - Columns to rows
같은 테이블을 네번 읽으므로 비효율적임

100 열과 행의 교환 - Columns to rows
테이블을 한번밖에 읽지 않으므로 비용이 저렴함

101 열과 행의 교환 - Columns to rows
SET STATISTICS IO ON으로 확인한 비용 비교 -- 쿼리 1>> (28개 행 적용됨) 'SalesPerson' 테이블. 스캔 수 4, 논리적 읽기 수 4, 물리적 읽기 수 0, 미리 읽기 수 0. -- 쿼리 2>> 'SalesPerson' 테이블. 스캔 수 1, 논리적 읽기 수 1, 물리적 읽기 수 0, 미리 읽기 수 0.

102 열과 행의 교환 - Columns to rows
앞의 예제를 변형하여, 각 요소를 두번씩 복제하고, 1,2분기 3,4분기로 표시하는 예제 select person, (case y.num when 1 then '1,2분기' when 2 then '3,4분기' end) term, (case y.num when 1 then sales1 when 2 then sales3 end) sales1, (case y.num when 1 then sales2 when 2 then sales4 end) sales2 from SalesPerson x,(select 1 num union all select 2) y

103 열과 행의 교환 - Rows to columns
우선, 테이블 자체에 정렬된 시퀀스가 존재해야만 한다. 가장, 간단하면서 강력한 시퀀스 생성 기법 : Identity 함수 select top 100 percent identity(int,1,1) num,fname into #SeqEmp from pubs.dbo.employee order by fname

104 열과 행의 교환 - Rows to columns
상관 쿼리를 이용한 Ranking 테크닉 select num= ( select count(*) from pubs.dbo.employee where fname <= x.fname ),fname from pubs.dbo.employee x order by fname

105 열과 행의 교환 - Rows to columns
Identity함수 상관쿼리 장점 간단하며 강력함 동순위 처리 가능 단점 임시 테이블 생성 같은 값인 경우 다른 순위 어려움 쿼리가 복잡함

106 열과 행의 교환 - Rows to columns
일련의 로우들을 그룹화하기 위한 키를 생성 select ceiling(num/4.0) ceil_num,num,fname from #SeqEmp Ceil_num Num Fname 1 Anabela 2 Ann 3 Annette 4 Aria 5 Carine 6 Carlos 7 Daniel 8 Diego

107 열과 행의 교환 - Rows to columns
각 그룹내의 원소들을 분리하기 위해서 모듈라 연산 select ceiling(num/4.0) ceil_num, (num%4) mod_num,num,fname from #SeqEmp Ceil_num Mod_num Num Fname 1 Anabela 2 Ann 3 Annette 4 Aria 5 Carine 6 Carlos 7 Daniel 8 Diego

108 열과 행의 교환 - Rows to columns
분리된 값들을 각각의 컬럼들로 이동 select ceiling(num/4.0) ceil_num, (num%4) mod_num,num, (case (num%4) when 1 then fname end) fname1, (case (num%4) when 2 then fname end) fname2, (case (num%4) when 3 then fname end) fname3, (case (num%4) when 0 then fname end) fname4 from #SeqEmp

109 열과 행의 교환 - Rows to columns
실제 값이 아닌 경우에는 NULL 값으로 표시됨 Ceil_num Mod_num Num Fname1 Fname2 Fname3 Fname4 1 Anabela NULL 2 Ann 3 Annette 4 Aria 5 Carine 6 Carlos 7 Daniel 8 Diego

110 열과 행의 교환 - Rows to columns
각 그룹에서 Max 값을 구하도록 쿼리 조정 select ceiling(num/4.0) ceil_num, max(case (num%4) when 1 then fname end) fname1, max(case (num%4) when 2 then fname end) fname2, max(case (num%4) when 3 then fname end) fname3, max(case (num%4) when 0 then fname end) fname4 from #SeqEmp group by ceiling(num/4.0)

111 열과 행의 교환 - Rows to columns
각 그룹에서 Max 값을 구하도록 쿼리 조정 Ceil_num Fname1 Fname2 Fname3 Fname4 1 Anabela Ann Annette Aria 2 Carine Carlos Daniel Diego 3 Elizabeth Francisco Gary Helen 4 Helvetius Howard Janine Karin 5 Karla Laurence Lesley Manuel 6 Margaret Maria Martin 7 Martine Mary Matti Miguel 8 Palle Paolo Patricia Paul 9 Paula Pedro Peter Philip 10 Pirkko Rita Roland Sven 11 Timothy Victoria Yoshi NULL

112 컴비네이션 - Ranking & Ceiling
각 페이지 그룹으로 나누어 히스토그램 값 구하기 1. 상관쿼리로 된 Ranking select ceiling(rank/20.0),min(num),max(num) from ( select num,rank=(select count(*) from TempBrd where num <= x.num) from TempBrd x ) as a group by ceiling(rank/20.0) order by 1 2. Identity를 사용하는 Ranking select identity(bigint,1,1) rank,num into NewTempBrd from TempBrd TempBrd에는 10만건의 데이터

113 컴비네이션 - Ranking & Ceiling
상관 쿼리 비용은 99.99%, Identity는 0.01%

114 컴비네이션 - Ranking & Ceiling
20개의 로우 단위로 그룹화하여 시작 키와 종료 키 조회 select ceiling(rank/20.0) RangeGrp,min(num) RangeLow,max(num) RangeHi from NewTempBrd group by ceiling(rank/20.0) order by 1

115 컴비네이션 - Ranking & Ceiling
특정 페이지 그룹의 시작 키와 종료 키 조회 select ceiling(rank/20.0) RangeGrp,min(num) RangeLow,max(num) RangeHi from NewTempBrd group by ceiling(rank/20.0) having ceiling(rank/20.0) = 37

116 컴비네이션 - Ranking & Ceiling
37번째 페이지 그룹의 모든 데이터 보기 select x.* from TempBrd x, ( select ceiling(rank/20.0) RangeGrp,min(num) RangeLow,max(num) RangeHi from NewTempBrd group by ceiling(rank/20.0) ) y where x.num between y.RangeLow and y.RangeHi and y.RangeGrp = 37 order by num

117 CSV 핸들링 - Encode하기 Multi-value들은 단일 CSV 값으로 모으기
varchar(8000) = + ',','') + title_id from pubs.dbo.titles Title_Ids

118 CSV 핸들링 - Decode하기 X 각 문자열의 길이 만큼 복제하여 구분자에서 끊어먹기 String Length
Num 1 2 3 4 5 6 7 CSV Value C1 C2 C3 C4 C5 C6 C7 A, B, C, D, E, F, G X

119 CSV 핸들링 - Decode하기 FixCSV Sample Table EmpID Terms PayAmt Kosmos
01월,02월,08월,11월 500 David 05월,06월,09월,12월 450 Omega 03월,04월,05월 180 Zinko 12월 250

120 CSV 핸들링 - Decode하기 첫번째 일년과 같이 고정된 복제 길이를 아는 경우 select *
from FixCSV x, Dic_Number y where y.Num <= 12

121 CSV 핸들링 - Decode하기 첫번째 복제 카운트+’월’ 문자열이 없는 경우는 0값을 반환
select *,CharIndex(dbo.LPad(y.Num,2,'0')+'월', x.Terms) from FixCSV x, Dic_Number y where y.Num <= 12

122 CSV 핸들링 - Decode하기 첫번째 값이 0 이상인 경우만을 찾아서 보여주도록 하면 select x.emp_id,
dbo.LPad(y.Num,2,'0') + '월' WorkMonth, x.payAmt from FixCSV x, Dic_Number y where y.Num <= 12 and CharIndex(dbo.LPad(y.Num,2,'0')+'월', x.Terms) > 0

123 CSV 핸들링 - Decode하기 두번째 동적으로 문장의 길이만큼 복제, 약간의 비효율
select *,CharIndex(dbo.LPad(y.Num,2,'0')+'월',x.Terms) from FixCSV x, Dic_Number y where y.Num <= Len(‘,’ + x.Terms + ‘,’)

124 CSV 핸들링 - Decode하기 문자열을 한자씩 파싱해나감
select *,Substring(','+x.Terms+',',y.Num-1,1) ThisChar from FixCSV x, Dic_Number y where y.Num <= Len(‘,’ + x.Terms + ‘,’)

125 CSV 핸들링 - Decode하기 파싱한 문자가 구분자(,)인 경우만을 조회
select *,Substring(','+x.Terms+',',y.Num-1,1) ThisChar from FixCSV x, Dic_Number y where y.Num <= Len(‘,’ + x.Terms + ‘,’) and Substring(','+x.Terms+',',y.Num-1,1) = ','

126 CSV 핸들링 - Decode하기 원래 문자열에서 구분자부터 나머지까지 문자열 자르기 select *,
Substring(','+x.Terms+',',y.Num,len(','+x.Terms+',')) ThisCharBelow from FixCSV x, Dic_Number y where y.Num <= Len(‘,’ + x.Terms + ‘,’) and Substring(','+x.Terms+',',y.Num-1,1) = ','

127 CSV 핸들링 - Decode하기 구분자에서 다음 구분자까지 문자열 잘라내기, 최종 select emp_id,
Substring(','+x.Terms+',',y.Num, CharIndex(',',','+x.Terms+',',y.Num) - y.Num ) Terms, payAmt from FixCSV x, Dic_Number y where y.Num <= Len(‘,’ + x.Terms + ‘,’) and Substring(','+x.Terms+',',y.Num-1,1) = ','

128 © 2003 Microsoft Corporation. All rights reserved.
This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.