[SQLD] select 실행 순서 / on 과 where 의 차이 / 집계함수 ( count ) / 그룹 함수와 계층 쿼리 ( rollup, cube, grouping sets, connect by )

 

SQLD를 공부하면서 실무에서 도움 될만한 내용에 대해 간단히 정리해보려고 한다. 

 

 

• select 문 실행 순서 

select 문 실행순서는 from -> where -> group by -> having -> select -> order by 순으로 실행 된다.

 

 

• on과 where의 차이 

where 은 결과 출력에 대해 관여하지만 on 은 join 하는 대상 데이터에 대해 관여한다. 예를 들어 on id  = 1000 가 조인 조건에 있을 경우 id = 1000 인 데이터들만 join에 관여하지만, 그렇지 않은 데이터들도 null 값으로 출력된다. ( 단, outer join 경우에 해당한다. inner join 일 경우 교집합에 대해서만 출력되기 때문이다. )  만약 where 에 id = 1000 이 있다면 결과 값에 관여 되므로 id = 1000 가 아닌 데이터들은 출력되지 않는다.

 

 

 

• count(*)와 count(column)의 차이

count(*)는 null까지 포함해서 출력하지만 count(column) 은 null 이 아닌 것만 조회해서 출력한다.

 

 

• rollup 

rollup 은 group by의 칼럼에 의해 Subtotal을 만들어 주는 함수다.  group by 가 먼저 실행된 뒤 rollup 이 실행된다.

rollup을 할 때 group by 구에 칼럼이 두 개 이상 오면 순서에 따라 결과가 달라진다.

 

다음 예제를 보면 이해하기 쉽다.

select
	deptno, job, sum(sal) 
from emp
 group by rollup(deptno, job);

 

 

 

rollup 을 하지 않았다면 deptno 별 소계와 전체 합계가 출력되지 않았겠지만 ( 표에서 3750, 10875, 14400, 29025 값에 해당 ) rollup 을 진행하여 소계와 전체 합계가 추가되어 출력되는 모습을 볼 수 있다. 

 rollup 은 먼저 오는 칼럼의 소계가 집계되기 때문에 rollup ( deptno, job ) 실행 시 deptno를 기준으로 소계가 집계되는 모습을 볼 수 있다. 

 

rollup(()) 꼴로 사용할 경우,  소계가 출력되지 않고 총합계만 출력된다.  ( 2024 노랑이 82번에 유사한 문제가 출제 됐다 )  

즉 group by rollup ((job, deptno)) 로 실행할 경우 다음과 같은 결과가 나타난다.

 

job	deptno	sal
clerk	10	1300
clerk	20	1700
analyst	10	3000
analyst	20	3500
null	null	9500

 

 

 

• cube

cube는 cube 함수에 제시한 칼럼에 대해서 결합 가능한 모든 집계를 계산한다. 즉 다차원 집계를 제공한다고 볼 수 있다.

예를 들어 부서와 직업을 cube로 사용하면 부서별 합계, 직업별 합계, 부서별 직업별 합계, 전체 합계가 조회된다. 

 

 

select deptno, job, sum(sal)
from emp
group by cube(deptno, job);

 

 

 

 

 

위 결과에서 (10,  null,  3750), (20, nul, 10875), (30, null, 14400)는 부서별 합계, 

(null, clerk, 4150), (null, analyst, 6000), (null, manager, 8275), (null, salesman, 5600), (null, president, 5000) 은 직업별 합계, (null, null, 29025)는 전체 합계에 해당하며 이 외의 칼럼은 모두 직업별 부서별 합계에 해당한다.

 

 

• grouping sets

groupby sets 함수는 group by에 나오는 칼럼의 순서와 관계없이 다양한 소계를 만들 수 있다.  소계를 출력할 수 있다는 점에서 rollup 과 유사하지만 rollup 이 컬럼의 순서에 따라 결과가 다르게 도출 되었던 것과 다르게 컬럼의 순서와 관계 없이 개별적으로 모두 출력된다는 점이 다르다.

  예를 들어 group by grouping sets( 상품 id, 월 )을 사용할 경우  rollup 이라면 상품 id 를 기준으로 소계가 집계 되겠지만,  grouping sets 은 개별로 집계 된다.  ( 2024 노랭이 83 번에 관련 문제가 나온다 )  

 

상품ID	월	매출액
null	2014.10	4500
null	2014.11	4500
null	2014.12	5000
p001	null	5500
p002	null	4500
p003	null	4000

 

위 결과는 group by grouping sets( 상품id, 월 ) 을 실행했을 경우로, 상품과 월별로 각각 소계가 그룹핑되어 집계된 것을 볼 수 있다.   

 

grouping sets ( 상품 id, 월, ()) 꼴로 실행하면 전체 합계도 얻을 수 있다. ()가 전체 합계에 해당한다. 

 

상품ID	월	매출액
null	2014.10	4500
null	2014.11	4500
null	2014.12	5000
p001	null	5500
p002	null	4500
p003	null	4000
null	null	14000

 

 

grouping sets 사용 시 이중괄호를 사용할 경우 상품 id 및 월 별 집계를 출력할 수도 있다. 

 

다음은 grouping sets ( ( 상품 id, 월 )) 를 실행한 결과다.

상품ID	월	매출액
P001	2014.10	1500
P001	2014.11	1500
P001	2024.12	2500
P002	2014.10	1000
P002	2014.11	2000
P002	2014.12	1500
P003	2014.10	2000
P003	2014.11	1000
P003	2014.12	1000

 

 

cube처럼 사용하고 싶을 경우 grouping sets ( (x, y), (x), (y), ())  를 사용하면 cube(x, y)와 동일한 결과를 얻을 수 있다. 

 

 

• connect by

계층형 조회는 oracle 데이터베이스에서 지원하고 계층형으로 데이터를 조회할 수 있다.  트리 형태의 구조를 위에서 아래로 탐색하면서 조회하며 역방향 조회도 지원한다.

 

connect by는 트리 구조로 질의를 수행하는 것으로 start with 구는 시작 조건을 의미하고 connect by prior 은 조인조건에 해당한다. Root 노드부터 하위 노드로의 질의를 수행하는데, root의 level 은 1부터 시작한다.  주요 키워드는 다음과 같다.

 

•  level : 검색 항목의 깊이를 의미한다. 계층 구조에서 가장 상위의 레벨이 1에 해당한다.
• connect_by_root : 계층 구조에서 가장 최상위 값을 표시한다 (  2024년 1회 차 SQLD 시험에 묻는 문제가 나왔다.)
• connect_by_isleaf :  계층 구조에서 최하위를 표시한다.
• sys_conenct_by_path : 계층 구조의 전개 경로를 표시한다.
• nocycle : 순환 구조가 발생 시점까지만 전개된다.
• connect_by_iscycle : 순환 구조 발생 지점을 표시한다.
• order siblings by 칼럼명 : 동일한 level 인 형제 노드 사이에서 정렬을 수행한다.

 

부모 엔터티로부터 자식 엔터티를 찾아가는 검색을 순방향 조회,  자식 엔터티로부터 부모 엔터티를 찾아가는 검색을 역방향 조회라고 말한다.

 

prior 자식 = 부모  일 경우 순방향

prior 부모 = 자식  일 경우 역방향에 해당한다.

 

계층형 쿼리는 외워서 쓰기보다 한번 이해해두면 짜기도 쉽고 짜여있는 걸 해석하기도 편리하다.

 

간단한 예제를 가져왔다. ( 2024 SQLD 노랑이 60번 ) 

 

 

select c3 
from tab1
start with c2 is null
connect by prior c1 = c2
order siblings by c3 desc;

 

 

c1	c2	c3
1	null	a
2	1	b
3	1	c
4	2	d

 

 

실행 순서는

 

1. start with c2 is null 
2. connect by prior c1 = c2
3.  order siblings by c3 desc이다.

c2 가 null 인 행을 먼저 출력하면

 

c1	c2	c3
1	null	a

 

가 출력 되고 이 행이 prior (이전행)에 해당하게 된다.  따라서 2번  prior c1 = c2를 만족하는 행은 

c1 = 1 이므로 c2 = 1 가 성립한다.

 

c1	c2	c3
2	1	b
3	1	c

 

 

order siblings by c3 desc 가 있으므로 두 개를 정렬하면 

c1	c2	c3
3	1	c
2	1	b

 

이 만족되고 이렇게 되면 한 사이클이 종료된다. 

 

다시 connect by 절로 돌아가서 prior c1 = c2를 만족하는 것을 찾으려면  c1 = 2  이므로  c2 = 2 인 

 

4

2

d

 

 

가 다음에 오게 된다.  행이 하나이므로 order siblings by c3 desc 는 동일한 결과를 출력한다.

 

 

위 내용을 이해하면 순방향이든, 역방향이든 쿼리 내용 그대로 출력하면 되기 때문에 어려울 게 없다.