[Oracle] 중급 문법 정리

 

 

 

GROUP BY expression

select a, b, c from tbl 
	where c > 20
    GROUP BY ();

 

GROUP BY 문법을 통해 expression으로 행 그룹을 생성하고 해당 그룹을 하나의 행으로 묶는다.

WHERE 조건 뒤에 위치하고, WHERE 절이 실행된 후 실행된다.

 

expression 부분에 b를 넣어주면 b별로 값을 집계한다.

값이 b인 행들을 그룹으로 모아서 결과를 반환하고, 집계 함수와 함께 사용해 그룹들에 대한 통계 값을 얻어올 때 사용한다.

 

그룹으로 묶는 expression을 여러 개 사용해도 된다. 적절하게 사용해 통계 관련 처리를 편하게 하자.

 

 

GROUP BY ROLLUP (expression)
GROUP BY CUBE (expression)
GROUPING SETS (expression)
GROUPING (expression)

 

 

오라클은 쿼리 작성의 편의를 위해 GROUP BY의 여러 확장 기능을 제공한다.

 

ROLLUP 문법은 expression의 계층별 소계와 총계를 집계해서 반환해준다.

ROLLUP (a, b, c) 는 (a, b, c) (a, b) (a) () 에 대한 총계를,

ROLLUP (a) 는 (a) () 에 대한 총계를 반환한다.

 

CUBE 문법은 expression을 조합해서 총계를 반환한다.

CUBE (a, b) 는 (a, b) (a) (b) () 에 대한 총계를 반환한다.

 

GROUPING SETS 문법도 계층별 총계를 집계하지만, 집계할 계층을 직접 지정할 수 있다.

GROUPING SETS (a, b, ROLLUP (c)) 는 (a) (b) (c) () 에 대한 총계를 반환한다.

 

GROUPING 함수는 expression이 행 그룹에 포함되면 0을 반환하고 포함되지 않으면 1을 반환한다.

널 처리 또는 결과의 정렬 순서를 다룰 때 사용된다.

 

 

HAVING CONDITION
select sum (a) from tbl where sum (a) > 20 -- 에러 --
select sum (a) from tbl having sum (a) > 20 -- 정상 작동 --

 

 

HAVING 문법으로 조회할 행 그룹을 선택한다. (WHERE 문법과 유사한 역할을 한다)

WHERE 절은 GROUP BY 보다 먼저 실행되기에 집계 함수를 활용할 수 없지만, HAVING 은 함께 사용할 수 있다.

 

HAVING은 select보다 먼저 수행되고 GROUP BY 함수 없이도 사용할 수 있다.

 

동작 순서를 고려해서 쿼리를 작성해 성능을 최대한 끌어올리자.

 

 

 

select a.c1, b.c1
	from tb1 a, tb2 b
    where a.c1 = b.c1;

 

 

JOIN 을 사용하면 하나의 쿼리로 다수의 테이블을 조회할 수 있다.

 

일반적으로는 테이블의 PK와 FK를 기준으로 연관지어서 조인을 실행하지만, 위의 예시처럼 아무런 연관이 없는 테이블을 엮어서 조인할 수도 있다.

 

 

for(int i=0; i<tbl1.length(); i++) {
	for(int j=0; j<tb2.length(); j++) {
    	if(tbl1.c1 == tbl2.c1) 결과 반환
    }
}

 

 

반복문으로 생각하면 조인이 내부적으로 어떻게 동작하는지 이해하기 쉽다.

우선 조인하는 테이블의 수와 테이블 내부 데이터의 수에 따라서 비교하는 데이터가 결정된다.

여기서 조건식을 만족하는 부분과 만족하지 않는 부분을 생각해보자.

 

 

 

 

집합 관계로 표현하면 위와 같다. 

위의 예시처럼 별도의 설정을 진행하지 않은 경우 이너 조인의 값이 반환된다.

 

select a.c1 b.c1 from t1 a, t2 b 
	where b.c1(+) = a.c1;

 

쿼리에 (+) 를 추가하면 아우터 조인을 수행할 수 있다.

조인 조건에 (+) 를 사용해 아우터 기준이 되는 행이 무조건 반환되도록 설정한다.

 

b.c1 에 (+) 가 붙어있으니 a에 대한 항목이 있는 경우 PASS로 설정된다.

아우터 기준이 어떻게 설정되는지 잘 확인하자. (+) 의 반대편이 아우터로 설정된다. 

 

아우터에 해당하는 값과 이너에 해당하는 값 모두 반환된다. 

 

WHERE 절에 조건이 여러 개 걸려 있더라도 아우터 조인의 로직은 변하지 않으니 위의 경우를 잘 활용해서 파악하자.

 

 

select a.c1, b.c1, b.c2
	from t1 a, t2 b
    where b.c1 = a.c1;

 

테이블 a와 b가 서로 FK를 통해 연관된 경우 (b가 FK를 가진다) 조인 기준에 따라 조인 기준의 행이 늘어날 수 있다.

a.c1 = b.c1 으로 조인 기준을 변경하면 행이 늘어나지 않는다. 

 

조인 차수를 고려해서 SQL을 작성하자. 

 

 

select * from t1 NATURAL JOIN t2;
select * from t1 JOIN t2 USING c1;
t1 a CROSS JOIN t2 b;

 

오라클은 오라클 조인 문법과 ANSI 조인 문법도 지원한다.

 

NATURAL JOIN 절은 이름이 같은 열로 테이블을 등가 조인한다.

USING 절은 설정한 열으로 테이블을 등가 조인한다.

CROSS JOIN은 Cartesian Product를 생성한다. 그냥 테이블을 2개 지정하는 쿼리와 동일한 결과를 뱉는다.

 

 

select a, b from t1 a 
	INNER JOIN t2 b
    ON b.c1 = a.c1;

 

 

ANSI 조인에서는 INNER JOIN으로 조인을 명시한다.

INNER를 생략할 시 기본으로 INNER로 설정되고, 조인 관련 설정은 ON 절을 사용해서 조인 조건과 일반 조건을 분리해 가독성이 높아진다.

 

select a, b from t1 a
	LEFT OUTER 
    JOIN t2 b
    ON b.c1 = a.c1;
    
select a, b from t1 a
	RIGHT OUTER 
    JOIN t2 b
    ON b.c1 = a.c1;

 

 

첫 번째 쿼리는 t1이, 두 번째 쿼리는 t2가 아우터 기준으로 설정된다.

아우터 기준이 아닌 테이블의 일반 조건을 기술하면 아우터 조인이 이너 조인으로 변경되니 주의하자.

아우터 기준이 아닌 테이블의 일반 조건은 ON 절에 기술하자.

 

조인 기준이 아닌 테이블에 대해 필수 관계이면 이너 조인을, 선택 관계이면 아우터 조인으로 조인해야 조인 기준이 모두 반환된다. 모든 데이터들을 반환받으려면 어떻게 조인을 해야 할지 잘 생각하고 쿼리를 작성하자.

 

 

select a, b
	from tbl
    where b >=
        (select AVG(a) from tbl);

 

서브 쿼리를 사용하면 원래대로라면 두 개 이상의 쿼리로 수행해야 하는 작업을 하나의 쿼리로 수행할 수 있다.

쿼리가 다른 쿼리를 포함하는 구조로, 포함하는 쿼리를 메인 쿼리, 포함되는 쿼리를 서브 쿼리라고 부른다.

 

서브 쿼리가 반환하는 행의 개수와 메인 쿼리와의 상관 관계에 따라서 서브 쿼리의 종류도 달라진다.

 

단일 행을 반환하는 경우 집계 함수를 사용하고, 다중 행을 반환하는 경우 IN 함수를 사용하는 등 적절한 함수를 사용해 쿼리를 작성하자.

 

메인 쿼리와의 조인 조건이 걸려있는 서브 쿼리는 상관 서브 쿼리라고 부른다.

조인 조건이 걸려있을 뿐 별다른 부분은 없다. 같은 방식으로 사용할 수 있다. 

 

단일 행 비상관 서브 쿼리인 경우 메인 쿼리가 수행되기 전에 수행돼 수행 결과가 메인 쿼리에 상수로 입력된다.

 

IN 조건은 서브 쿼리를 먼저 조회해 메인 쿼리에 값을 넣어주는 방식으로 작동하고, EXISTS 조건은 메인 쿼리를 먼저 조회해 서브 쿼리로 존재 여부를 확인하는 방식으로 작동한다.

 

어떤 기능을 사용하면 성능이 최적화될지를 생각해서 쿼리를 작성하자.

 

WITH w1 as (select a from tbl)
select a
	FROM w1 a;

 

WITH 문법을 사용해 서브 쿼리를 별도로 뺄 수 있다.

서브 쿼리에 이름을 지정해 변수처럼 사용한다.

 

 

UNION ALL
UNION
INTERSECT
MINUS

 

조인 외에도 집합 연산자를 사용해서 데이터 집합을 연결해서 조회할 수 있다.

 

UNION ALL 연산자는 데이터 집합을 수평으로 연결해 기술한 순서대로 데이터 집합을 반환한다. 중복을 고려하지 않는다.

UNION 연산자는 중복 값이 제거된 합집합을 반환한다. 중복 제거를 위해 내부적으로 정렬이 발생한다.

INTERSECT 연산자는 중복 값이 제거된 교집합을 반환한다. 

MINUS 연산자는 중복 값이 제거된 차집합을 반환한다. 중복을 제거할 때는 정렬이 발생한다고 생각하자.

 

OR 조건을 UNION ALL 문법으로 변경하는 등 집합 연산자를 적절히 사용해 쿼리의 성능을 향상시킬 수 있다.

 

JOIN과 UNION을 공부할 때는 벤다이어그램을 그려서 어떤 부분이 반환되는지 확인해보자.

 

 

analyticfunction OVER (expression)

 

분석 함수는 집계 함수의 확장 기능으로 생각하자.

집계 함수에 OVER 키워드를 추가하면 분석 함수로 동작하고, expression에 PARTITION, ORDER BY, WINDOWING 절을 추가하자.

 

PARTITION 절은 분석을 위한 정적 그룹을 지정하고, GROUPY BY절과 유사하게 동작한다.

WINDOWING 절은 파티션 내부의 동적 그룹으로 생각하면 된다. 

 

집계 함수는 데이터를 그룹화하고 각 그룹의 값들을 계산해 결과를 반환하는 함수이고, 분석 함수는 데이터를 그룹화하는 대신 전체 데이터에서 값을 계산하는 함수이다. 

데이터 집합이 변경되는가와 변경되지 않는가에 따른 차이이다.

 

select a, b, ROWNUM from tbl 
	where ROWNUM <= 2;

 

쿼리 결과의 일부분만을 가져올 수 있다.

ROWNUM 은 행이 반환되는 순서대로 순번을 반환한다. 위와 같은 조건을 설정하면 행은 2개만 반환된다.

 

인라인 뷰를 사용해 원하는 결과를 반환받도록 설계하자.

이 부분을 응용해서 페이징 쿼리를 작성할 수 있다.

 

Top-N 쿼리의 ANSI 표준은 ROW LIMITING 절이다.

 

OFFSET / FETCH / ONLY

select a, b from tbl 
	ORDER BY b, a FETCH FIRST 5 ROW ONLY;

 

 

OFFSET으로 건너뛸 행의 개수를 지정하고 FETCH와 ONLY로 반환할 행의 개수를 지정한다.

백분율을 지정해서 상위 n%의 행만 가져오도록 설정할 수도 있다.

 

ANSI 표준 문법을 사용해 쿼리를 더 쉽게 작성하자.

 

 

셀프조인 등으로 테이블이 트리 구조를 이룰 때 계층 쿼리와 재귀 서브 쿼리 팩토링 기능을 통해 데이터를 효과적으로 조회할 수 있다.

 

[START WITH condition] CONNECT BY [NOCYCLE] condition

START WITH a IS NULL
CONNECT BY a = PRIOR b

 

계층 쿼리는 WHERE 다음에 기술되고 FROM이 실행된 후 실행된다.

해당 컬럼이 부모 노드의 컬럼과 일치하는 컬럼을 해당 컬럼이 존재하지 않는 행 까지 조회한다.

 

select * 
	from (select a, b, c from tbl)
    PIVOT (sum(c) FOR b IN (11,22,33))
    ORDER BY 1;

 

 

PIVOT 문법은 행을 열으로 회전시키고, UNPIVOT 문법은 열을 행으로 회전시킨다. ( UNPIVOT은 PIVOT과 반대로 동작한다)

데이터 집합의 형태를 변경할 때 사용한다.