동적쿼리

매개변수로 인해 쿼리 스트링이 변경될 수 있다. 그 중에서도 컬럼명이나 테이블명, 데이터베이스 명이 바뀔 수 있을 때 SQL을 실시간으로 작성하여 DBMS가 처리하도록 한 것이다.


피벗

쉽게 설명하면 컬럼에 있는 속성들을 컬럼으로 바꿔서 옆으로 펼치는 것을 말한다.


PIVOT [집계함수] FOR [컬럼명] IN ([컬럼에 있는 속성명])

 

SELECT 절에서 사용할 컬럼중에서 피벗으로 하고 싶은 컬럼을 정해서 FOR뒤에 쓴다. 그리고 IN뒤에는 그 컬럼의 속성들중에 옆으로 펼치고 싶은 속성들을 쓴다. 그리고 이를 기준으로 집계하고싶은 컬럼을  PIVOT뒤에 써주면 된다.


사용할 테이블이다




쿼리


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
DECLARE @COL NVARCHAR(255), @SQL NVARCHAR(255);
SET @COL = N'';
 
SELECT @COL += N', P.' + QUOTENAME(WORK_DT +'|'+MAX(WORK_DT_NM))
  FROM TBL1
 WHERE WORK_DT LIKE '201707%'
 GROUP BY WORK_DT;
 
 
SET @SQL = N'SELECT P.CODE, P.NAME,' + STUFF(@COL,1,2,'')+'
               FROM 
               (
                  SELECT A.CODE, A.NAME, A.WORK_DT+''|''+A.WORK_DT_NM AS COL_GBN,
                         A.AMT
                    FROM TBL1 AS A
                   WHERE A.WORK_DT LIKE '2017%' 
               )AS F
               PIVOT
               (
                   SUM(AMT) FOR COL_GBN IN('
                   +STUFF(REPLACE(@COL, ', P.[', ',['),1,1,'')
                   + ')
               )AS P
               ORDER BY P.CODE;';
 
 
EXEC SP_EXECUTESQL @SQL
cs


※ QUOTENAME() 필드값에 대괄호([])를 쳐준다. 이 의미는 문자열로 표현하겠다는 의미이다.


   SELECT할때, A즉 TBL1테이블에 있는 컬럼을 SELECT할 수 없다.(FROM 후에 PIVOT이 실행되어서 테이블형태를

   PIVOT실행후 형태로 가지고있는듯)

결과



참조링크(UNPIVOT과 PIVOT에 대하여)


저작자표시

'SQL > MS-SQL' 카테고리의 다른 글

특정경우를 만족하는 행 SELECT  (0) 2017.09.04
FOR XML  (0) 2017.09.04
WINDOW FUNCTION  (0) 2017.09.04
sp_executesql  (0) 2017.09.03
재귀쿼리  (0) 2017.09.02

 sp_executesql장점


1. Query 에 매개변수(입력/출력)을 정의 할수 있다.

2. 매개변수 사용으로 인하여 쿼리 최적화 프로그램이 컴파일된 실행플랜을 재사용할 확률이 높아진다.

3. sp_executesql은 시스템저장프로시져가 아닌 확장 저장프로시져이다.


sp_executesql을 사용할때도 주의할점


1. 동적 SQL문에서는 RETURN문을 사용할 수 없다.

2. 로컬 별수를 액세스 할 수 없다.

3. 현재 데이터베이스가 변경되지 않는다.




저작자표시

'SQL > MS-SQL' 카테고리의 다른 글

특정경우를 만족하는 행 SELECT  (0) 2017.09.04
FOR XML  (0) 2017.09.04
WINDOW FUNCTION  (0) 2017.09.04
Dynamic Query와 Pivot  (0) 2017.09.03
재귀쿼리  (0) 2017.09.02

회사에는 여러부서가 있다. 그리고 그안에 또 팀에 있고...

이들을 계층형으로 보고싶을때, 어느팀이 어느 부서에 속해있는지를 알고싶은 경우가 있다. 또한 각 최상위 부서별로 무언가에 대한 계산을 해야할때, 최상위부서에 속한 부서들을 모두 파악할 필요가 있다. 

이때 재귀 쿼리를 사용하면 계층형태로 부서를 나타낼수있고, 따라서 최상위부서도 파악할수 있다.

재귀쿼리란?

CTE(Common Table Expression : 공통 테이블 식)를 사용하면 자기 자신을 참조하는 재귀쿼리를 만들수 있다. 재귀적 CTE는 최초 CTE가 반복적으로 실행되어 전체 결과 집합을 얻을 때까지 데이터의 하위집합을 반환하는 CTE다.


우선, CTE란, SELECT, INSERT, UPDATE, DELETE 또는 CREATE VIEW 문 하나의 실행 범위 내에서 정의되는 임시 결과 집합이다. CTE는 개체로 저장되지 않고 쿼리 지속 시간 동안만 존재한다는 점에서 파생 테이블과 비슷하다. 그러나 CTE는 파생 테이블과 달리 자체 참조가 가능하며 동일 쿼리에서 여러 번 참조될 수 있다는점에서 파생 테이블과는 다르다


※ 파생테이블 

- 쿼리에서 테이블 원본으로 사용되는 결과 집합입니다. 다이어그램 창에서 파생 테이블을 쿼리에 추가할 수 있다.

- FROM, WHERE절과 같이 쿼리를 통해서 리턴된 테이블이다.

재귀적 CTE의 구조

1. 루틴의 호출

재귀적 CTE의 첫 번째 호출은 하나 이상의 쿼리 정의로 구성된다. CTE 구조의 기본 결과 집합을 형성하기 때문에 앵커 멤버라고 한다. CTE 자체를 참조하지 않는 경우 앵커 멤버로 간주된다. 모든 앵커 멤버 쿼리 정의를 첫 번째 재귀 멤버 정의 앞에 배치하고 UNION ALL 연산자를 사용하여 마지막 앵커 멤버를 첫 번째 재귀 멤버와 조인해야 한다.


2. 루틴의 재귀 호출

재귀 호출에는 CTE 자체를 참조하는 UNION ALL 연산자로 조인된 하나 이상의 쿼리문이 포함된다. 이러한 쿼리 정의를 재귀 멤버라고 한다.


3. 종료 확인

종료 확인은 암시적으로 수행된다. 이전 호출에서 반환되는 행이 없을 때 재귀가 중지된다.

실행체계

1. CTE 식을 앵커 멤버와 재귀 멤버로 분할한다.


2. 앵커 멤버를 실행하여 첫 번째 호출 또는 기본 결과 집합(T0)을 만든다.


3. Ti는 입력으로 사용하고 Ti+1은 출력으로 사용하여 재귀 멤버를 실행한다.


4. 빈 집합이 반환될 때까지 3단계를 반복한다.


5. 결과 집합을 반환한다. 이것은 T0에서 Tn까지의 UNION ALL한 결과 값이다.


예제

예제에서 사용할 TREE_TABLE은 



이다.


이 TREE_TABLE을 재귀쿼리를 활용하여 계층형구조로 바꾸어 볼것이다.

그에 해당하는 쿼리는 다음과 같다.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH tree_query AS (
--Anchor member definition
 SELECT id , parent_id , name , 
        convert(varchar(255), id) sort ,
   FROM tree_table WHERE parent_id = 1 
 
UNION ALL 
 
--Recursive member definition
SELECT B.id , B.parent_id , B.name , 
        convert(varchar(255), convert(nvarchar,C.sort) + ' > ' + convert(varchar(255), B.id)) sort,
  FROM tree_table B, tree_query C
 WHERE B.parent_id = C.id 
 
)
 
 SELECT id, parent_id, name, SORT
   FROM tree_query 
  ORDER BY SORT
cs



핵심은 재귀멤버를 SELECT하는 쿼리에서 현재 TREE_TABLE에 가리키는 행의 부모 컬럼이 TREE_QUERY에 존재하는 ID와 같은지를 확인하여 ROW를 COLLECTION하는 것이다.

자식필드에서 부모의 코드값과 CTE에서 자신의 코드값이 같은 행을 찾아야 무한루프에 빠지지 않는다. 


그리고 WITH절 앞에 ; 을 붙이지 않으면 프로시저를 실행할때 오류가 나기때문에 반드시 ; 을 붙혀주도록하자.


재귀공통테이블식의 재귀부분에는 외부조인을 사용할 수 없다. 이너 조인은 되네? NULL때문에 안되는건가?


SELECT시에 CONVERT(VARCHAR(255),A)를 해주는 이유는 앵커멤버와 재귀맴버의 유형을 일치시켜주기 위해서이다. 만에 하나라도 다르면 에러가 발생하니까...


앵커맴버가 최상위 그룹의 행이므로, 재귀맴버들을 각각의 앵커맴버의 자식 행이된다. 따라서 최상위부서를 구하는 컬럼을 명시해두면 각부서의 최상위 부서를 알 수 있다.

결과


참조 : https://technet.microsoft.com/ko-kr/library/ms186243(v=sql.105).aspx





저작자표시

'SQL > MS-SQL' 카테고리의 다른 글

특정경우를 만족하는 행 SELECT  (0) 2017.09.04
FOR XML  (0) 2017.09.04
WINDOW FUNCTION  (0) 2017.09.04
Dynamic Query와 Pivot  (0) 2017.09.03
sp_executesql  (0) 2017.09.03


1. 구문 분석(Parsing)

해당 쿼리가 문법적으로 틀리지 않은지 확인. 해당 구문을 SQL 서버가 이해할 수 있는 단위들로 분해하는 과정.


만약 구문이 부정확하다면 여기서 처리를 중단. 이 문장이 일괄 처리(batch) 내에 있다면 일괄 처리 전체를 중단.(Batch abort : Batch 중 하나라도 syntax error가 있다면 전체 batch가 실행되지 않는다.)

2. 최적화(Optimization)

통계나 조각 정보 등을 바탕으로 실행 계획을 만들어 낸다. 쿼리처리에서 매우 중요한 단계


① 쿼리 분석 : 검색 제한자(SARG)인지 조인 조건인지 판단.


② 인덱스 선택 : 분포 통계 정보를 이용하여 인덱스검색이나 테이블 스캔 중의 하나를 선택. 여러 인덱스 중 가장 효율적인 인덱스 하나를 선택


③ 조인 처리 : JOIN, UNION, GROUP BY, ORDER BY 절을 가지고 있는지 확인하여 적절한 작업 순서를 선택


이 단계의 출력은 실행 계획(Execution Plan) 이다.


SQL 최적화단계(Optimizer)

- Query Transformer : 쿼리블록으로 나누어 변형된 몇 종류의 쿼리문을 생산,  서브쿼리를 조인으로 변경한다든지, 뷰의 해체작업, 인라인뷰의 해체작업, FROM절의 테이블제거작업등을 거쳐 쿼리를 변형한다.

 

- Estimator : Query Transformer에서 생성된 몇종류의 SQL문장의 모든 Cost를 측정한다. Selectivity(선택도) , Cardinality, Cost등세가지 다른 측정방법을 이용하며 최소의 비용을 갖는 SQL문장을Plan Generator에게 넘긴다.    

- Plan Generator : 선택된 저비용 SQL문의 실행계획을 생성하여 Row Source Generator에게 넘긴다. 이렇게 생성한 실행계획도나중을 위해 Library Cache에 저장해 둔다.


예)
SELECT empno, ename,dname

  FROM emp e, dept d

WHERE e.deptno =d.deptno;

 

---------------------------------------------------------------------

| Id  | Operation                                |Name                   |

---------------------------------------------------------------------

|   0|   SELECT STATEMENT                  |                          |

|   1|   NESTED LOOPS                        |                          |

|   2|   NESTED LOOPS                        |                          |

|   3|   TABLE ACCESS FULL                  | DEPT                   |

| * 4|   INDEX RANGE SCAN                 | IDX_EMP_DEPTNO  |

|   5|   TABLE ACCESS BY INDEX ROWID | EMP                    |

------------------------------------------------------------------------

 

 

3. Row Source Generator 

옵티마이저의 최적화 과정을 거치면 SQL처리과정을 표현한 실행계획이 만들어 진다.

이런 실행계획을 실행 가능한 코드 또는 프로시저 형태로 포맷팅(tree형식으로 만듦)하는 작업이 필요하며, 이 역할을 Row-Source Generator가 담당 한다. Row-Source는 레코드 집합을 반복 처리하면서 사용자가 요구한 최종 결과집합을 실제적으로 생성하는데 사용되는 제어 구조를 말한다.

Row Source Generator는 Row Source Tree(명령문에의해 참조되는 테이블의 순서, Access Method, Join Method, Filter / Sort / Aggregation과 같은 데이터 오퍼레이션을 포함)를 생산하며 Row Source는 테이블, 뷰,조인이나 그룹핑의 결과등이 해당된다.


4. 실행(Execute)

QL실행 엔진이 각각의 Row Source Tree의 각각 로우 소스를 실행하여 결과를 Client로 보낸다. (INSERT/UPDATE/DELETE 같은 DML인 경우 반드시 거쳐야 한다.)

실행기간동안 DB는 메모리에 데이터가 없는 경우 디스크로부터 데이터를 읽으며 SQL문의 실행에 따른 변화를 로깅하거나 데이터 무결성을 지키기 위해 DBLOCK을 걸거나 LATCH를 획득한다.




SELECT 실행 순서 

1. 문법 순서

1. SELECT

2. FROM 

3. WHERE 

4. GROUP BY 

5. HAVING

6. ORDER BY  

2. 실행 순서

1. FROM

2. ON

3. JOIN

4. WHERE

5. GROUP BY

6. HAVING

7. SELECT

8. DISTINCT

9. ORDER BY

10. TOP

3.설명

해당 데이터가 있는 곳을 찾아가서 (FROM)

조건에 맞는 데이터로 필터링하고 (WHERE)

원하는 데이터로 가공 (GROUP BY)

가공한 데이터에서 조건에 맞는 것만(HAVING)

뽑아내서 (SELECT)

정렬한다(ORDER BY).

 

실행순서는 문법, 권한 검사 순서이기도 하고,

Alias 등록 순서 이기도 하다.

별칭(Alias)

FROM 절에서 테이블에 Alias를 사용했다면     (FROM Table1 AS T1)

SELECT, ORDER BY 절에서 사용할 수 있고      (SELECT T1.Col1, ORDER BY T1.Col1)

SELECT 절에서 컬럼에 Alias를 사용했다면       (SELECT T1.Col1 AS a)

ORDER BY 절에서 사용할 수 있다.                 (ORDER BY AS a)



출처 : http://ojc.asia/bbs/board.php?bo_table=LecOrccleTun&wr_id=113

http://wiki.gurubee.net/pages/viewpage.action?pageId=3901322

저작자표시

'SQL > SQL 튜닝' 카테고리의 다른 글

클러스터인덱스와 넌클러스터인덱스  (0) 2017.10.24
Nested Loop, Sort Merge, Hash JOIN  (0) 2017.10.23
옵티마이저 이해하기 - 1  (0) 2017.10.23
INDEX 튜닝  (0) 2017.10.23
쿼리튜닝팁  (0) 2017.09.20

+ Recent posts

티스토리툴바