[SQL Server] Chapter 08. 고급쿼리

*본 포스팅은 개인의 학습 내용을 정리한 것입니다.

 

1. CASE 함수

 - 조건에 따라 값을 변경할 때 사용하면 좋음. (DECODE문도 있음. 이 둘은 적절히 사용하면 좋다)

 (1) 단순 CASE문, 검색된 CASE문

   - 표현식

1) 단순 CASE    CASE <input_expression> WHEN <when_expression> THEN <result_expression>    [, ... n]    [ ELSE <else_result_expression> ]  2) 검색된 CASE    CASE WHEN <boolean_expression> THEN <result_expression>    [, ... n]    [ ELSE <else_result_expression> ]

 

 단순 case함수 예제 

쿼리>  SELECT au_fname, au_lname,    CASE state      WHEN 'CA' THEN 'California'      WHEN 'KS' THEN 'KAnsas'      WHEN 'TN' THEN 'Tennessee'      WHEN 'OR' THEN 'Oregon'      WHEN 'MI' THEN 'Michigan'     END AS statename  FROM authors ORDER BY au_lname  결과>  au_fname  au_lname    statename -----------------------------------------   park         jack            Michigan   park         one            Oregon   lee          one            California   kim          one             KAnsas   lee          sa               Oregon   lee         sam             California   lee         samsa         California   lee          two            California   kim        yong            Tennessee   park      young           Tennessee

 

 

 검색된 case함수 예제

쿼리>  SELECT  title_id, CAST(title AS varchar(20)) AS title, price, PriceCategory =    CASE       WHEN price IS NULL THEN '미정'      WHEN price < 20000 THEN '저가'      WHEN price >= 20000 and price < 30000 THEN '중가'      ELSE '고가'    END  FROM titles  ORDER BY price 결과>  title_id        title          price       pricecategory -----------------------------------------------------  PS2091      test1        NULL           미정  PS2092      test2        NULL           미정  BU7832      test9        NULL           미정  MC2222  sample2      NULL           미정  PS3333     test13       NULL           미정  BU1032      test8        2000            저가  MC3021   sample      3000             저가  PS7777      test4       13000            저가  PC9999  net ettique  15000            저가  MU2912    test15      21000            중가  PC8888    test12       22000            중가  PS2016     hey         23000            중가  MS2292   test16       26000            중가  MC3026   test3        30000            고가  MC7892   test14      31000            고가  PC2099   test17       32000            고가  TC7777    test7       35000            고가  BU1111    test5       41000            고가  TC4203    test6       42000            고가  BU2075   lalala       50000            고가

 

 

 (2) 상관하위쿼리를 사용한 CASE문

  CASE문에서 비교할 컬럼을 하위쿼리를 이용하여 처리할 수 있다.

상관하위쿼리 예

아래 예제는 각 점포에서의 판매 건이 2건 초과면 활성, 아니면 비활성으로 표기하는 쿼리

stor_id = 1: 3건, stor_id = 2: 2건, 그외는 1건  쿼리>  SELECT stor_id, stor_name, CASE WHEN    (SELECT COUNT(*) FROM sales WHERE stor_id = s.stor_id ) > 2 THEN '활성'    ELSE '비활성'    END AS status  FROM Stores s  결과>  stor_id   stor_name    status ------------------------------   0001       가게1         활성   0002       가게2        비활성   0003       가게3        비활성   0004       가게4        비활성

 - 또한 CASE문은 중첩하여 사용이 가능하다.

 

2. 하위쿼리

 - 쿼리 내에 포함된 쿼리. 최대 32개까지 쿼리를 중첩할 수 있다.

 - SELECT절의 하위 쿼리 : 하나의 값을 반환하여야 한다.

    FROM절의 하위 쿼리 : 여러 열이나 표현식으로 올 수 있다. (제한이 없다)

    WHERE절의 하위 쿼리 : 하나의 열이나 표현식. 또는 행 집합이 올 수 있다.

 (1) 하위쿼리의 제약조건

    1) 비교연산자나 하위 쿼리의 선택 목록에는 식이나 열 이름이 하나만 포함될 수 있다.

    2) 외부쿼리의 WHERE절에 열 이름이 포함되면 하위쿼리 선택목록의 열과 비교 가능한 데이터 형식이어야 함

    3) 하위쿼리의 선택 목록에는 ntext,text,image를 사용할 수 없다.

    4) GROUP BY가 포함된 하위 쿼리에는 DISTINCT키워드 사용 불가

    5) COMPUTE절과 INTO절을 지정할 수 없다. (INTO : 결과를 지정한 행에 입력, COMPUTE:결과의 끝에 요약으로 표시되는 합계를 생성)

    6) TOP이 지정된 경우 ORDER BY만 지정할 수 있다.

    7) 하위 쿼리로 만들어진 뷰는 업데이트 할 수 없다.

    8) EXISTS로 시작하는 하위 쿼리의 선택 목록은 관습상 단일 열 이름 대신 *로 구성할 수 있다. EXISTS로 시작하는 하위 쿼리는 존재유무를 파악하여 TRUE / FALSE를 반환한다.

 

   하위쿼리 예제

   sales 테이블에서, 마지막 주문일에 대한 자료를 찾아보자 

쿼리>  SELECT ord_num, stor_id, title_id, qty FROM sales WHERE ord_date = (SELECT MAX(ord_date) FROM sales)  결과>  ord_num   stor_id   title_id    qty ----------------------------------    724a       0001     PS2092    11    723a       0001     PS2092    8

 

 

 (2) IN을 사용한 하위쿼리

  IN을 사용하여 집합 비교를 하는 하위 쿼리는 조인으로 처리할 수 있다. 다만, 하위쿼리의 결과에 둘 이상의 컬럼을 보이고자 할 경우에는 조인을 사용하여 처리해야 한다.

 

 (3) 하위쿼리로 차집합 구하기

  차집합을 구하기 위해 NOT IN구문을 사용한 하위 쿼리로 작업할 수 있다. 

 NOT IN문 예제

쿼리>  SELECT title_id, title FROM titles WHERE title_id NOT IN ( SELECT title_id FROM sales) 결과>  title_id    title -------------------  sales기록이 없는 모든 data

 

  다중 컬럼에 대해서는 수행할 수 없다.

SELECT * FROM sales WHERE stor_id, ord_num NOT IN(SELECT stor_id,ord_num FROM salesTemp)

  위와 같이 실행하면 메시지170. 에러가 발생한다.

 

 (4) EXISTS사용

  하위쿼리의 결과에 행의 유무에 따라, TRUE, FALSE를 리턴한다.

EXISTS>  SELECT title FROM titles WHERE EXISTS( SELECT * FROM publisher   WHERE pub_id = publisher.pub_id AND city LIKE 'B%')  IN>   SELECT title FROM titles WHERE pub_id IN ( SELECT pub_id FROM publisher WHERE city LIKE 'B%')

 

 (5) 파생 테이블 사용

  - 파생 테이블은 FROM절에 사용하는 하위 쿼리를 말함. 인라인 뷰라고도 불림.

  - 임시테이블을 생성하여 처리할 수 있으나, 파생테이블이 더 적은 비용을 사용함.

   1) 부분 범위 조회 : 부분검색에서 파생쿼리를 사용하면 유리하다

 

 (6) 상관하위쿼리 : 외부 쿼리에서 참조되는 테이블로부터 데이터를 선택하는 등의 작업시 사용할 수 있다.

  실행순서는 아래와 같다.

   1) 외부쿼리의 값을 내부 쿼리(하위쿼리)로 전달

   2) 하위 쿼리의 내용을 수행

   3) 2)의 결과를 외부 쿼리로 전달하고, 조건에 맞는지 확인

   4) 외부쿼리의 모든 행에 대하여 1)~3)의 과정을 반복하여 수행.

 

3. 동적쿼리 작성과 실행

  - 동적으로 쿼리 문자열을 만들고, EXECUTE문을 사용하여 쿼리를 실행할 수 있다.

 

4. 고차원 분석을 위한 GROUP BY사용

  - GROUP BY절에 옵션을 사용하여 더 높은 수준의 분석 자료를 만들 수 있다.

[GROUP BY [ALL] <group_by_expression> [, ... n] [ WITH { CUBE | ROLLUP } ] ]

 (1) ALL : WHERE절에서 지정한 검색 조건에 일치하는 행이 없는 경우에도 같이 표현되기 원할때 사용

 (2) CUBE : GROUP BY에서 제공하는 일반 행 및 요약 행을 결과 집합에 포함하도록 지정.

 (3) ROLLUP : CUBE와 같으나, 그룹의 낮은 수준에서 높은 수준까지의 계층 순서로 요약.

 - GROUPING함수를 사용하여 CUBE/ROLLUP에 의한 요약값 여부 확인 가능

 

5. COMPUTE BY

 - 요약정보를 조회하는데 사용. GROUP BY와의 차이는 원시데이터를 보여주는지(COMPUTE) 여부가 차이가 있다.

 - COMPUTE BY절의 컬럼들은 ORDER BY절에 명시되어 있어야 한다.

 

COMPUTE BY예제

쿼리>  SELECT stor_id, ord_num, title_id, qty FROM sales WHERE stor_id LIKE '76%'  ORDER BY stor_id, qty DESC  COMPUTE AVG(qty), SUM(qty) BY stor_id  COMPUTE AVG(qty), SUM(qty) 결과>  stor_id  ord_num  title_id    qty ------------------------------- 7600        169a     PS2092   50  7600        169a     PS2092   50  7600        171a     PS2092   50  7600        270a     PC9999   45  7600        269a     PC9999   45  avg  sum ----------  48   240    stor_id  ord_num  title_id    qty ------------------------------- 7601        170a     MC3026   30  7601        172a     MC3026   30  avg  sum ----------  30   60    stor_id  ord_num  title_id    qty ------------------------------- 7602        370a     TC7777   70  7602        369a     TC7777   70  avg  sum ----------  70   140   avg  sum -----------  48   440

 

 > '76'으로 시작하는 행들의 결과 값을 보여주고, 그 결과들에 대하여 조회한 결과가 나오고, COMPUTE .. BY..에 의해 각 stor_id별 결과를 출력해 주고, 마지막 줄의 구문으로 인해 해당하는 데이터 전체에 대하여 계산한 결과가 마지막에 출력된다.

 

 (1) COMPUTE BY대신 GROUP BY와 UNION으로 변경

  COMPUTE BY의 결과에 대해 클라이언트에서 부가적으로 해야 하는 추가 작업들이 있다. 따라서, GROUP BY와 UNION을 이용하여 제어하는 것이 더 수월함.

 

 (2)GROUP BY, COMPUTE BY의 동시 사용.

  GROUP BY에 대한 중간 집계를 할 때, CUBE나 ROLLUP을 이용할 수 있지만, COMPUTE BY도 사용 가능하다.

 

6. 조인 연산자에 대한 이해

 (1) 중첩 루프 조인 : 가장 간단하며 많이 사용됨.두 테이블의 크기 차이가 크거나, 둘 다 크기가 적은 경우에 쓰임

 (2) 해시 조인 : 충분히 큰 테이블이 정렬되지 않거나 인덱스가 없을 때, 가장 좋은 성능을 보임.

 (3) 병합 조인 : 조인에 참여하는 테이블이 모두 크고, 크기가 비슷하며, 인덱스가 있거나 정렬된 경우.

 

7. 대량 데이터 복사를 위한 BULK INSERT

 대량의 데이터를 파일에서 부터 삽입하는데 사용한다.

 

- 출처 : SQL Server advanced programming