1. WITH CTE
WITH 절은 CTE(Common Table Expression)
공통 테이블 식을 표현하기 위한 구문이며, SQLServer 2005부터 등장하였다.
MSDN의 CTE 페이지를 참고하자.
CTE는 SELECT, INSERT, UPDATE, DELETE 또는 CREATE VIEW 문
하나의 실행 범위 내에서 정의되는 임시 결과 집합이라고 볼 수 있다.
CTE는 개체로 저장되지 않고 쿼리 지속 시간 동안만 존재한다는 점에서 파생 테이블과 비슷하나,
CTE는 파생 테이블과 달리 자체 참조가 가능하며 동일 쿼리에서 여러 번 참조될 수 있다.
CTE를 사용하여 다음과 같은 것들을 수행할 수 있다.
- 재귀 쿼리를 만들 수 있다 (다음 챕터에서...)
- 일반적인 뷰 사용이 필요하지 않을 때, 즉 메타데이터에 정의를 저장할 필요가 없을 때 뷰를 대체할 수 있다
- 스칼라 하위 SELECT에서 파생된 열 또는 비결정적이거나 외부 액세스가 없는 함수를 기준으로 그룹화할 수 있다
- 동일 문에서 결과 테이블을 여러 번 참조할 수 있다
CTE를 사용하면 가독성이 향상되고 복잡한 쿼리를 쉽게 유지 관리할 수 있는 이점이 있다.
쿼리를 개별적이고 단순한 논리적 구성 블록으로 나눈 뒤에,.
이 단순한 블록을 사용하여 최종 결과 집합이 생성될 때까지 보다 복잡한 중간 CTE를 작성할 수 있다.
CTE는 함수, 저장 프로시저, 트리거 또는 뷰 같은 사용자 정의 루틴에서 정의될 수 있다.
결론적으로, CTE는 기존의 뷰, 파생 테이블, 임시 테이블 등으로
사용되던 것을 대신할 수 있으며, 더 간결한 식으로 보이는 장점이 있다.
CTE는 비재귀적 CTE와 재귀적 CTE로 나뉘어지는데, 이번 챕터에서는 비재귀적 CTE에 대해서만 다루겠다.
2. CTE 구문
CTE는 CTE를 나타내는 식 이름, 선택적인 열 목록 및 CTE를 정의하는 쿼리로 구성되어 있다.
CTE를 정의한 후에는 SELECT, INSERT, UPDATE 또는 DELETE 문에서 테이블이나 뷰처럼 참조할 수 있다.
CTE는 CREATE VIEW 문에서 정의하는 SELECT 문의 일부분으로도 사용될 수 있다.
CTE의 기본 구문 구조는 다음과 같다.
- -- expression_name의 CTE를 생성한다.
- WITH expression_name [ ( column_name [,...n] ) ]
- AS
- (
- CTE_query_definition
- )
- -- 이후 expression_name을 마치 테이블명처럼 사용할 수 있다.
- SELECT <column_list> FROM expression_name
(SELECT 외에 UPDATE 등도 가능하지만, 주요 사용되는 용도는 SELECT와 부합된다)
기존에는 SELECT <column-name...> FROM 실제DB테이블을 사용하였지만,
CTE는 바로 위의 WITH 절에서 정의한 expression_name 테이블명을 사용하는 것만 다르다.
즉, 'WITH expression_name AS...' 형식의 테이블이 하나 더 있다고 생각하면 쉽다.
3. 예제
이해를 돕기 위해서는 예제가 최고이니...
앞서 사용하였던 BuyTable에서 총구매액을 다시 구해보도록 하자.
- SELECT ID, SUM(Amount * Price) AS TotalCost FROM BuyTable
- GROUP BY ID
위 결과를 총구매액이 많은 사용자 순서로 정렬할 때, GROUP BY에 이어 ORDER BY를 첨가해도 된다.
하지만, 위 쿼리의 결과가 바로 UserTotalCost라는 테이블이라고 생각하면 어떨까?
그렇다면, 정렬하는 쿼리는 아래와 같이 간단해 진다.
SELECT * FROM UserTotalCost ORDER BY TotalCost DESC
이것이 바로 위에서 얘기했던 CTE의 장점 중 하나인 구문 단순화이다.
지금까지의 내용을 모두 쿼리로 작성하면 아래와 같다.
- -- WITH절이 시작되기 전 마지막 구문은 세미콜론(;)으로 끝나야 한다
- -- 또는 GO 구문을 사용해 주어야 한다.
- -- USE Son
- -- GO
- USE Son;
- -- UserTotalCost라는 이름의 CTE
- WITH UserTotalCost(ID, TotalCost)
- AS
- (
- SELECT ID, SUM(Amount * Price) AS TotalCost FROM BuyTable
- GROUP BY ID
- )
- -- 마치 실제 테이블처럼 사용이 가능하다
- SELECT * FROM UserTotalCost ORDER BY TotalCost DESC
마지막 SELECT * FROM의 UserTotalCost는 실존하는 테이블이 아니라,
바로 위에서 WITH 구문으로 만든 SELECT의 결과이다.
여기에서 CTE의 AS (...)내의 칼럼 수와 WITH UserTotalCost(...)의 칼럼 수는 동일해야 한다.
이 UserTotalCost는 이 쿼리문이 완전히 종료되면 소멸하게 된다.
그리고, 위 예제에서 보듯이 WITH 절 앞 구문은 반드시 'GO' 또는 세미콜론(;)으로 끝나야 한다.
그렇지 않으면, 아래와 같이 에러가 발생한다.
메시지 319, 수준 15, 상태 1, 줄 3
키워드 'with' 근처의 구문이 잘못되었습니다.
이 문이 공통 테이블 식이거나, xmlnamespaces 절이거나, 변경 내용 추적 컨텍스트 절인 경우에는
이전 문을 세미콜론으로 종료해야 합니다.
4. CTE의 매력
지금까지의 예제만 본다면 사실 CTE를 굳이 써야 하나 싶지만,
아래와 같은 시나리오를 생각해 본다면, 많은 부분의 단순화 -> 명확한 쿼리문 작성이 가능하게 된다.
"각 지역별로 가장 큰 키의 평균값을 구하라"
위와 같은 목표를 달성하려면, 우선 쿼리를 세분화된 단계로 생각해 볼 필요가 있다.
- 각 지역별로 가장 큰 키를 쿼리한다.
- 이들의 평균값을 계산한다.
가장 큰 값을 구하는 것도 집계함수(MAX)이고, 이들의 평균을 구하는 것도 집계함수(AVG)이다.
하위쿼리를 쓴다해도 이를 하나의 SELECT 구문으로는 처리할 수 없다.
이럴 때 다음과 같이 CTE를 활용하는 것이다.
1. 각 지역별로 가장 큰 키를 쿼리한다.
- SELECT Addr, MAX(Height) FROM UserTable
- GROUP BY Addr
2. 이 쿼리를 WITH 구문으로 묶는다.
- WITH AddrMaxHeight(Addr, MaxHeight)
- AS
- (
- SELECT Addr, MAX(Height) AS MaxHeight FROM UserTable
- GROUP BY Addr
- )
3. 키의 평균을 구하는 쿼리를 작성한다.
- USE Son
- GO
- WITH AddrMaxHeight(Addr, MaxHeight)
- AS
- (
- SELECT Addr, MAX(Height) AS MaxHeight FROM UserTable
- GROUP BY Addr
- )
- SELECT AVG(MaxHeight * 1.0) AS [각 지역별 최고 키들의 평균] FROM AddrMaxHeight
복잡한 단계의 쿼리를 작성해야 할 경우에 이러한 단계로 분할해서 생각하면,
이전보다 쉽게 SQL문을 작성할 수 있을 것이다.
5. 중복 CTE
CTE는 다음과 같은 형식의 중복 CTE를 지원한다.
- WITH
- AAA(columns...)
- AS (query of AAA),
- BBB(columns...)
- AS (query of BBB),
- CCC(columns...)
- AS (query of CCC)
- SELECT * FROM [AAA or BBB or CCC]
위와 같이 사용시 주의할 점이, CCC의 AS 쿼리문에서는 AAA, BBB를 참조할 수 있지만, 그 반대의 참조는 불가능하다.
즉, 아직 정의되지 않은 CTE를 미리 참조할 수 없다는 의미이다.
'개발 > DB' 카테고리의 다른 글
| @@trancount (0) | 2015.06.15 |
|---|---|
| MSSQL 컬럼추가, 수정, 삭제 (0) | 2015.05.14 |
| mssql 2012 추가 및 변경점 (0) | 2015.03.20 |
| N 따옴표 [N'] (0) | 2015.03.09 |
| IF 조건문 EXISTS 사용 예 (값 유무 체크) (0) | 2015.03.05 |