SQL Server 2012 T-SQL: CHOOSE, IIF 논리함수



새롭게 제공되는 내용 중에서 CHOOSE, IIF를 한번 사용해보도록 하겠습니다.


SELECT 구문에서 조건에 따라 값을 나타내려고 할 경우 CASE WHEN 문을 사용해서 처리했습니다. CASE 구문을 작성하면 코드가 길어지고 가독성이 좀 떨어지는 측면이 있었습니다.


CHOOSE

SQL Server 2012에서는 보다 더 간결하고 쉽게 처리 가능한 CHOOSE, IIF 구문이 제공됩니다.


아래 구문을 한번 살펴보시죠~


 

1
2
3
4
5
6
7
8
9
SELECT  CASE DATEPART(WEEKDAY, GETDATE())
                                WHEN 1 THEN N'일요일'
                                WHEN 2 THEN N'월요일'
                                WHEN 3 THEN N'화요일'
                                WHEN 4 THEN N'수요일'
                                WHEN 5 THEN N'목요일'
                                WHEN 6 THEN N'금요일'
                                WHEN 7 THEN N'토요일'
                     END
cs


참고로

 SELECT DATENAME (WEEKDAY, '20130730') -- 화요일

 SELECT DATEPART (WEEKDAY, '20130730') -- 3

이다.


위 구문의 경우는 CASE WHEN 구문으로 작성한 내용인데 CHOOSE 구문으로 변경해보도록 하겠습니다. 더 간결해보입니다.


1
SELECT CHOOSE(DATEPART(WEEKDAY, GETDATE()) , 
N'일요일',N'월요일',N'화요일',N'수요일',N'목요일',N'금요일',N'토요일')
cs


CHOOSE 논리함수의 구문의 규칙은 아래와 같습니다.


CHOOSE ( index, val_1, val_2 [, val_n ] )

 

Index 는 1부터 시작하는 정수이며 val_1.., 등은 인덱스에 매치되는 임의의 데이터 형식입니다.


그러므로 1부터 반환하는 정수가 나오는 인수를 맨 처음에, 나머지는 해당 값을 정의하면 됩니다.


1
SELECT CHOOSE ( 3, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result;
cs

--Developer

3이므로 세 번째에 해당하는 Developer를 반환합니다.

 


CHOOSE 논리 함수에 대한 내용은 아래 링크를 참조하십시오.


http://technet.microsoft.com/ko-kr/library/hh213019(v=sql.110).aspx 


IIF

또 다른 논리함수인 IIF 를 한번 살펴보겠습니다. 사실 IIF는 CASE 문의 약식 방법입니다. TRUE, FALSE에 따라 처리할 경우 IIF를 사용할 수 있습니다. IIF 는 SQL Server Reporting의 식에서도 사용이 되었기 때문에 사용해보았다면 적용하시는데 큰 문제 없을 것으로 보입니다.


IIF ( boolean_expression, true_value, false_value ) 

boolean_expression 이 TRUE 이면 true_value 가 반환됩니다. 


 

1
2
3
4
5
6
7
8
9
SELECT
[ProductID], [Name], [ListPrice]
, CASE WHEN [ListPrice] < 500 THEN  N'500이하'
           ELSE 
             CASE WHEN [ListPrice] < 1000 THEN N'1000이하' ELSE N'1000이상'
             END
           END AS ProceLevel
FROM [Production].[Product]
WHERE [ListPrice] >0
cs

 

500보다 작으면 ‘500이하’, 500보다 크고 1000보다 작으면 ‘1000’이하, 1000보다 크면 ‘1000’이상입니다. IIF로 변환해보겠습니다.  


1
2
3
4
5
6
7
8
9
10
11
12
SELECT
[ProductID], [Name], [ListPrice]
, IIF([ListPrice] < 500, N'500이하', IIF([ListPrice] < 1000, N'1000이하',N'1000이상')) AS ProceLevel
FROM [Production].[Product]
WHERE [ListPrice] >0  
/*
522      HL Touring Seat Assembly      196.92  500이하
680      HL Road Frame - Black, 58      1431.50 1000이상
706      HL Road Frame - Red, 58        1431.50 1000이상
707      Sport-100 Helmet, Red 34.99    500이하
708      Sport-100 Helmet, Black          34.99    500이하
*/
cs

 

위 두 구문의 실행계획을 보면 똑같습니다. 편하고 가독성 있는 구문을 쓰면 될 것 같네요~


CASE 문 보다 더 간결한 CHOOSE, IIF 논리함수에 대한 내용을 살펴보았습니다.


출처: http://redju.tistory.com/142 [redJu(홍주)]

저작자표시

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

커서(Cursor)  (0) 2017.09.06
UNION ALL로 소계, 합계 구하기  (0) 2017.09.05
특정경우를 만족하는 행 SELECT  (0) 2017.09.04
FOR XML  (0) 2017.09.04
WINDOW FUNCTION  (0) 2017.09.04

특정한 경우를 만족하는 행을 SELECT하기 위해서 WHERE 절에서


1
2
3
4
5
6
7
8
9
10
(
    CASE WHEN @CD_COMPANY = '1000' AND CODE IN ('300', '310', '320') THEN 1 ELSE 0 END
            WHEN @CD_COMPANY = '2000' AND CODE IN ('300' ) THEN 1 ELSE 0 END
) = 1
      
 
 
(
    CASE WHEN A.CD_COMPANY = '1000' AND A.CODE IN ('100', '200') THEN 1 ELSE 0 END
) = 0
cs


특정한 경우를 만족하는 값만 리턴받고 싶을때


예를 들어서 

회사코드가 1000이고 CODE가 100과 200인 행은 SELECT에서 제외하고 싶을때,


회사코드가 1000이고 CODE가 300, 310, 320인 행과

회사코드가 2000이고 CODE가 300인 행만 리턴받고 싶을때



WHERE절에서 CASE WHEN절을 사용하여 해당 요구사항을 만족 시킬 수 있다.


물론 OR를 사용하여 해당문제를 해결할 수도 있지만, OR절을 사용하게 되면 INDEX가 있음에도 불구하고 FULL SCAN을 유도 하기 때문에 시간이 오래걸린다. 그래서 OR은 되도록 변수에만 사용하여야 한다.


그리고 TRUE는 1 FALSE는 0으로 정해서 가독성을 높이자.

저작자표시

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

UNION ALL로 소계, 합계 구하기  (0) 2017.09.05
CHOOSE, IIF 논리함수  (0) 2017.09.05
FOR XML  (0) 2017.09.04
WINDOW FUNCTION  (0) 2017.09.04
Dynamic Query와 Pivot  (0) 2017.09.03

FOR XML

원래는 디비와 xml을 연동하기 위해서 나온 연산자로써 FOR XML('row') 라고 SELECT절 뒤에 쓰게 되면, 반환 되어서 나오는 컬럼들이 한줄로 나열되고 <row>속성1</row><row>속성2</row>처럼 id에 감싸져서 반환되는 함수이다.





그런데 이런 일뿐 아니라 SELECT된 결과를 한행에 표현하고 싶을때에 사용해도 좋은 함수이다.


코드(간략하게)

1
2
3
4
5
6
7
8
9
10
11
12
SELECT ...
  FROM
(
    SELECT ...
 
           STUFF((
                  SELECT DISTINCT ', '+ CK1.KOR
                    FROM TBL1 CK1
                   WHERE CK1.PK = TBL1.PK 
FOR XML PATH(''))1,2,'')
    ...
     FROM TBL1 T1
)
cs


T1이 먼저 생성되기때문에 나중에 생성되는 CK1을 T1으로 필터링 해주어야 각 행마다 모두 같은 값이 안나온다.
필터링에 신경쓰자!

결과






저작자표시

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

CHOOSE, IIF 논리함수  (0) 2017.09.05
특정경우를 만족하는 행 SELECT  (0) 2017.09.04
WINDOW FUNCTION  (0) 2017.09.04
Dynamic Query와 Pivot  (0) 2017.09.03
sp_executesql  (0) 2017.09.03

WINDOW FUNCTION

행과 행간의 관계를 쉽게 정의하기 위해 만든 함수

윈도우 함수를 활용하면 복잡한 프로그램을 하나의 SQL 문장으로 쉽게 해결할 수 있다.

분석 함수(ANALYTIC FUNCTION)나 순위 함수(RANK FUNCTION)로도 알려져 있는 윈도우 함수 (ANSI/ISOSQL 표준은 WINDOW FUNCTION이란 용어를 사용함)는 데이터웨어하우스에서 발전한 기능이다.


WINDOW FUNCTION 종류

구분종류종류
순위(RANK) 관련RANK, DENSE_RANK, ROW_NUMBER대부분 지원
집계(AGGREGATE) 관련SUM, MAX, MIN, AVG, COUNTSQL Server 경우 Over절 내 Orderby 지원 못함
순서 관련 함수FIRST_VALUE, LAST_VALUE, LAG, LEADORACLE 만 지원
그룹 내 비율 관련 함수CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORTPERCENT_RANK 함수는 ANSI/ISO SQL 표준과 Oracle DBMS에서 지원하고 있으며, NTILE 함수는 ANSI/ISO SQL 표준에는 없지만, Oracle, SQL Server에서 지원하고 있다. RATIO_TO_REPORT 함수는 Oracle에서만 지원되는 함수(현업에서 유용).

선형분석을 포 

CORR, COVAR_POP, COVAR_SAMP, STDDEV, STDDEV_POP, STDDEV_SAMP, VARIANCE, VAR_POP, VAR_SAMP, REGR_(LINEAR REGRESSION), REGR_SLOPE, REGR_INTERCEPT, REGR_COUNT, REGR_R2, REGR_AVGX, REGR_AVGY, REGR_SXX, REGR_SYY, REGR_SXY특화되어있으므로 생략

WINDOW FUNCTION SYNTAX

WINDOW 함수에는 OVER 문구가 키워드로 필수 포함된다.

1
2
3
4
5
6
7
8
SELECT WINDOW_FUNCTION (ARGUMENTS) OVER ( [PARTITION BY 칼럼]] [ORDER BY 절] [WINDOWING 절] )
 FROM 테이블 명;
 
 
BETWEEN 사용 타입
 ROWS | RANGE BETWEEN UNBOUNDED PRECEDING | CURRENT ROW 
| VALUE_EXPR PRECEDING/FOLLOWING AND UNBOUNDED FOLLOWING 
| CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING
BETWEEN 미사용 타입
 ROWS | RANGE UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING


cs

WINDOW_FUNCTION : 기존에 사용하던 함수도 있고, 새롭게 WINDOW 함수용으로 추가된 함수도 있다. - ARGUMENTS (인수) : 함수에 따라 0 ~ N개의 인수가 지정될 수 있다.

PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.

ORDER BY 절 : 어떤 항목에 대해 순위를 지정할 지 ORDER BY 절을 기술한다.

WINDOWING 절 : WINDOWING 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다. ROWS는 물리적인 결과 행의 수를, RANGE는 논리적인 값에 의한 범위를 나타내는데, 둘 중의 하나를 선택해서 사용할 수 있다. 다만, WINDOWING 절은 SQL Server에서는 지원하지 않는다.



참고 : http://wiki.gurubee.net/pages/viewpage.action?pageId=26744104

저작자표시

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

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


동적쿼리

매개변수로 인해 쿼리 스트링이 변경될 수 있다. 그 중에서도 컬럼명이나 테이블명, 데이터베이스 명이 바뀔 수 있을 때 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

+ Recent posts

티스토리툴바