상관서브쿼리를 이용해서 행과 행을 비교하기
SQL에서는 같은 행내의 열끼리 비교하는 것은 간단하다. 단순히 WHERE구에 「col_1 = col_2」처럼 쓰면 되기 때문이다. 하지만 다른 행의 열끼리를 비교하는 것은 그리 간단한 일이 아니다. SQL에서 행간 비교시 위력을 발휘하는 것인 상관서브쿼리, 특히 자기결합과 함께 쓰일 때이다.
여기서 잠깐!
상관서브쿼리란..? 내부의 쿼리에서 외부 쿼리의 테이블의 데이터를 참조하는 쿼리. 이때 메인 쿼리의 테이블의 행마다 서브쿼리가 반복 실행되어진다.
사용예_1 : 납품일이 2001년 4월 1일인 상품을 상관서브쿼리를 이용해서 참조
|
--상관서브쿼리--
SELECT * FROM 상품테이블 AA
WHERE '20010401' IN (SELECT 납품일
FROM 주문테이블 BB
WHERE AA.상품코드 = BB.상품코드);
--결과--
상품코드 상품명 단가
───── ─── ──
101 BEER 500
102 JUICE 400 |
사용예_2 :주문개수를 납품일별로 집계해서, 각자의 합계가 주문개수의 최대치를 150넘길 때만 참조
|
--HAVING구에서 상관서브쿼리이용--
SELECT SUM(주문개수),납품일
FROM 주문테이블 PP
GROUP BY 납품일
HAVING SUM(주문개수) > (SELECT 150 + MAX(주문개수)
FROM 주문테이블 QQ
WHERE PP.납품일 = QQ.납품일);
--결과--
SUM(주문개수) 납품일
─────── ────
650 20010401
485 20010402
490 20010403 |
Sales
|
year |
sale |
|
1990 |
50 |
|
1991 |
51 |
|
1992 |
52 |
|
1993 |
52 |
|
1994 |
50 |
|
1995 |
50 |
|
1996 |
49 |
|
1997 |
55 |
위의 테이블을 이용해서 작년과 비교해서 「년간판매실적이 늘었는지 줄었는지 그대로인지」를 SQL문으로 만들어보자. 먼저 「그대로였다」를 SQL문으로 작성해보자.
|
--상관서브쿼리이용--
SELECT year,sale
FROM Sales S1
WHERE sale = (SELECT sale
FROM Sales S2
WHERE S2.year = S1.year - 1)
ORDER BY year;
「Sales」테이블과 별도로 「작년의 행」을 보유하고 있는 집합 S2를 추가한다.서브쿼리내의 「S2.year = S1.year -1」이라는 조건에 의해서 비교대상의 행을 1행 밀어내는 것이다.
--셀프조인이용--
SELECT S1.year, S1.sale
FROM Sales S1, Sales S2
WHERE S2.sale = S1.sale
AND S2.year = S1.year - 1
ORDER BY year;
--결과--
YEAR SALE
----- ----------
1993 52
1995 50 |
이번에는 년도별 판매실적 비교결과를 한눈에 나타내는 SQL문을 만들어보자.
|
--상관쿼리이용--
SELECT S1.year, S1.sale,
CASE WHEN sale =
(SELECT sale
FROM Sales S2
WHERE S2.year = S1.year - 1) THEN '→'
WHEN sale >
(SELECT sale
FROM Sales S2
WHERE S2.year = S1.year - 1) THEN '↑'
WHEN sale
(SELECT sale
FROM Sales S2
WHERE S2.year = S1.year - 1) THEN '↓'
ELSE '-' END AS var
FROM Sales S1
ORDER BY year;
--결과--
YEAR SALE VA
------ ---------- --
1990 50 ―
1991 51 ↑
1992 52 ↑
1993 52 →
1994 50 ↓
1995 50 →
1996 49 ↓
1997 55 ↑
--셀프조인이용--
SELECT S1.year, S1.sale,
CASE WHEN S1.year = S2.year THEN '→'
WHEN S1.year > S2.year THEN '↑'
WHEN S1.year < S2.year THEN '↓'
FROM Sales S1, Sales S2
WHERE S2.year = S1.year -1
ORDER BY S1.year;
--결과--
YEAR SALE VA
------- ---------- --
1991 51 ↑
1992 52 ↑
1993 52 →
1994 50 ↓
1995 50 →
1996 49 ↓
1997 55 ↑ |
Sales2
|
year |
sale |
|
1990 |
50 |
|
1992 |
50 |
|
1993 |
52 |
|
1994 |
55 |
|
1997 |
55 |
과거의 데이터가 빠져있는 년도가 있는 테이블이 있다. 이런 경우 앞의 Sale1과 같이 「올해 -1」과 같은 조건설정이 불가능하다. 가장 일반적으로 가장가까운 행을 비교대상으로 할 필요가 있다. 어느년도에서 봐도 「과거에 가장 가까운 년도」라는 것은 다음 두가지 조건을 만족하는 년도를 말하는 것이다.
1. 자신보다 앞의 년도에 있을 것.
2. 조건 1을 만족하는 년도 중에서 최대값일 것
이 조건을 SQL문으로 바꾸면 다음과 같다.
|
--상관서브쿼리--
SELECT year, sale
FROM Sales2 S1
WHERE sale =
(SELECT sale
FROM Sales2 S2
WHERE S2.year =
(SELECT MAX(year) /* 조건2：조건1을 만족하는 년도 중에서 최대*/
FROM Sales2 S3
WHERE S1.year > S3.year)) /* 조건1：자신보다 과거*/
ORDER BY year;
--셀프조인--
SELECT S2.year AS pre_year, S1.year AS now_year
FROM Sales2 S1, Sales2 S2
WHERE S1.sale = S2.sale
AND S2.year = (SELECT MAX(year)
FROM Sales2 S3
WHERE S1.year > S3.year)
ORDER BY now_year;
--결과--
YEAR SALE
----- ----------
1992 50
1997 55 |
현재의 값과 가장 가까운값의 차분을 구하는 것도 가능하다.
|
--상관서브쿼리 : 시작시점이 결과에 포함되지 않음--
SELECT S2.year AS pre_year,
S1.year AS now_year,
S2.sale AS pre_sale,
S1.sale AS now_sale,
S1.sale - S2.sale AS diff
FROM Sales2 S1, Sales2 S2
WHERE S2.year = (SELECT MAX(year)
FROM Sales2 S3
WHERE S1.year > S3.year)
ORDER BY now_year;
-- 자기외부결합이용 : 시작시점도 결과에 포함됨--
SELECT S2.year AS pre_year,
S1.year AS now_year,
S2.sale AS pre_sale,
S1.sale AS now_sale,
S1.sale - S2.sale AS diff
FROM Sales2 S1LEFT OUTER JOIN Sales2 S2
ON S2.year = (SELECT MAX(year)
FROM Sales2 S3
WHERE S1.year > S3.year)
ORDER BY now_year; |
은행계좌의 입출금을 기록한 테이블에서 누적을 구해보자
Accounts
|
pro_date
(처리일) |
prc_amt
(처리금액) |
|
2006-10-26 |
12,000 |
|
2006-10-28 |
2,500 |
|
2006-10-31 |
-15,000 |
|
2006-11-03 |
34,000 |
|
2006-11-04 |
-5,000 |
|
2006-11-06 |
7,200 |
|
2006-11-11 |
11,000 |
|
---OLAP함수이용--
SELECT prc_date, prc_amt,
SUM(prc_amt) OVER (ORDER BY prc_date) AS onhand_amt
FROM Accounts;
--결과--
PRC_DATE PRC_AMT ONHAND_AMT
-------- ---------- ----------
06-10-26 12000 12000
06-10-28 2500 14500
06-10-31 -15000 -500
06-11-03 34000 33500
06-11-04 -5000 28500
06-11-06 7200 35700
06-11-11 11000 46700
--노이만재귀집합이용--
SELECT prc_date, A1.prc_amt,
(SELECT SUM(prc_amt)
FROM Accounts A2
WHERE A1.prc_date >= A2.prc_date ) AS onhand_amt
FROM Accounts A1
ORDER BY prc_date;
--결과--
PRC_DATE PRC_AMT ONHAND_AMT
-------- ---------- ----------
06-10-26 12000 12000
06-10-28 2500 14500
06-10-31 -15000 -500
06-11-03 34000 33500
06-11-04 -5000 28500
06-11-06 7200 35700
06-11-11 11000 46700
|
이번에는 3행단위로 누적하는 이동누적을 구해보자
|
--OLAP함수 이용--
SELECT prc_date, prc_amt,
SUM(prc_amt) OVER (ORDER BY prc_date
ROWS 2 PRECEDING) AS onhand_amt
FROM Accounts;
--이동누적 구하기--
SELECT prc_date, A1.prc_amt,
(SELECT SUM(prc_amt)
FROM Accounts A2
WHERE A1.prc_date >= A2.prc_date
AND (SELECT COUNT(*)
FROM Accounts A3
WHERE A3.prc_date
BETWEEN A2.prc_date AND A1.prc_date ) <= 3 ) AS mvg_sum
FROM Accounts A1
ORDER BY prc_date;
--결과--
PRC_DATE PRC_AMT ONHAND_AMT
-------- ---------- ----------
06-10-26 12000 12000
06-10-28 2500 14500
06-10-31 -15000 -500
06-11-03 34000 21500
06-11-04 -5000 14000
06-11-06 7200 36200
06-11-11 11000 13200 |
겹쳐지는 기간을 구하기
|
reserver
(숙박자) |
start_date
(투숙일) |
end_date
(출발일) |
|
김철수 |
2006-10-26 |
2006-10-27 |
|
이영희 |
2006-10-28 |
2006-10-31 |
|
박순희 |
2006-10-31 |
2006-11-01 |
|
최민철 |
2006-11-03 |
2006-11-04 |
|
송영수 |
2006-11-03 |
2006-11-05 |
|
고민수 |
2006-11-06 |
2006-11-06 |
위의 테이블은 어느 호텔의 객실 예약상황을 나타내는 표이다. 위 테이블을 이용해서 예약일이 겹치는 기간을 구해보자
|
SELECT reserver, start_date, end_date
FROM Reservations R1
WHERE EXISTS
(SELECT *
FROM Reservations R2
WHERE R1.reserver <> R2.reserver /* 자신이외의 손님과 비교 */
AND ( R1.start_date BETWEEN R2.start_date AND R2.end_date /* 条件(1)：개시일이 다른 기간내에 있음 */
OR R1.end_date BETWEEN R2.start_date AND R2.end_date)); /* 条件(2)：종료일이 다른 기간내에 있음 */
--결과--
RESERVER START_DATE END_DATE
--------------------------- ------------ -----------
이영희 06-10-28 06-10-31
박순희 06-10-31 06-11-01
송영수 06-11-03 06-11-05
최민철 06-11-03 06-11-04 |
위 테이블에서 최민철씨의 투숙일이 3일이 아닌 4일이면 어떻게 될까? 위의 SQL을 이용하면 결과에 송영수씨는 제외된다. 송영수씨의 투숙일과 출발일이 다른 손님들과 겹쳐지지 않게 되기 때문이다. 즉, 위의 쿼리는 상대의 기간을 완전히 포함하고 있는 기간은 선택되지 않는다는 것이다.
이런 기간을 구하기 위해 조건을 추가해보자.
|
--최민철씨의 투숙일 변경--
update reservations
set start_date = '2006-11-04'
where reserver = '최민철';
--위 쿼리를 그대로 이용--
SELECT reserver, start_date, end_date
FROM Reservations R1
WHERE EXISTS
(SELECT *
FROM Reservations R2
WHERE R1.reserver <> R2.reserver /* 자신이외의 손님과 비교 */
AND ( R1.start_date BETWEEN R2.start_date AND R2.end_date /* 条件(1)：개시일이 다른 기간내에 있음 */
OR R1.end_date BETWEEN R2.start_date AND R2.end_date)); /* 条件(2)：종료일이 다른 기간내에 있음 */
--결과(송영수씨가 제외됨)--
RESERVER START_DATE END_DATE
--------------------------- ------------ -----------
이영희 06-10-28 06-10-31
박순희 06-10-31 06-11-01
최민철 06-11-04 06-11-04
--상대를 전부 포함한 기간을 출력--
SELECT reserver, start_date, end_date
FROM Reservations R1
WHERE EXISTS
(SELECT *
FROM Reservations R2
WHERE R1.reserver <> R2.reserver
AND ((R1.start_date BETWEEN R2.start_date AND R2.end_date
OR R1.end_date BETWEEN R2.start_date AND R2.end_date)
OR (R2.start_date BETWEEN R1.start_date AND R1.end_date
AND R2.end_date BETWEEN R1.start_date AND R1.end_date)));
--결과--
RESERVER START_DATE END_DATE
--------------------------- ------------ -----------
이영희 06-10-28 06-10-31
박순희 06-10-31 06-11-01
송영수 06-11-03 06-11-05
최민철 06-11-04 06-11-04
|
RECENT COMMENT