1-4. DATABASE (WHERE SELECT, EXISTS, BETWEEN, GROUP BY, HAVING, GROUPING, ROLLUP, RANK)

WHERE SELECT, EXISTS, BETWEEN, GROUP BY, HAVING

 

WHERE & HAVING 비교

1.  having은 전체 결과, where은 개별 행
2.  having은 집계된 값에서 지정된 조건을 충족하는 행만 반환하도록 지정
   ex) 총 거래 건수가 100건이 넘는 매장의 이름과 거래 건수는?
3.  having은 그룹을 필터링 / where은 행을 필터링
4. 집계 함수는 having과 함께 사용 가능
5. where절을 having 에 포함된 하위 쿼리에 있지 않으면 집계 함수와 함께 사용 불가능
   (COUNT, MIN, MAX, SUM, AVG 등)
6. GROUP BY 앞에 WHERE. GROUP BY 뒤에 HAVING

Having은 그룹화 또는 집계가 발생한 후 레코드를 필터링하는데 사용된다.
Where은 그룹화 또는 집계가 발생하기 전에 레코드를 필터링하는데 사용된다.

 

EXISTS

EXISTS(서브 쿼리)는 서브 쿼리의 결과가 "한 건이라도 존재하면" TRUE 없으면 FALSE를 리턴

EXISTS는 서브 쿼리에 일치하는 결과가 한 건이라도 있으면 쿼리를 더 이상 수행하지 않는다.

 

ROLLUP & GROUP BY 

GROUP BY

• 성별 기준으로 여자 및 남자 각 몇 명인지 출력

ROLLUP

• GROUP BY 결과를 총 집계도 출력
• 조인테이블도 가능하다

 

 

---

GROUPING, ROLLUP, RANK

참고:

https://gent.tistory.com/386, https://jhnyang.tistory.com/473,  https://myjamong.tistory.com/191

 

GROUPING

• 그룹쿼리에서 사용하는 함수로 파라미터의 평가값이 NULL 이면 1, NULL 이 아닌 경우에는 0을 반환
• GROUPING 함수에서 사용될 컬럼은 반드시 GROUP BY 절에서 명시해야 함
• ROLLUP을 이용하여 NULL로 묶인 컬럼에 CASE문으로 나눠서 출력해도 됨

SELECT
CASE
	WHEN GROUPING(D.SALES_NAME) = 1 THEN '합계 TOTAL'
    WHEN D.SALES_NAME IS NULL THEN '매출 없는'
    ELSE D.SALES_NAME
END 부서명, SUM(D.SALES_TOTAL)

컬럼별로 사용 가능함

SELECT job
     , deptno
     , SUM(sal) 
     , GROUPING(job)
     , GROUPING(deptno)
  FROM emp
 WHERE job IN ('ANALYST', 'MANAGER')
 GROUP BY ROLLUP(job, deptno)

GROUPING_ID

GROUPING_ID 함수는 인자로 들어온 각각 칼럼의 GROUPING 함수 값을 2진수로 합쳐 해당 2진수 값을 10진수로 변환한 값을 반환

 

RANK 

참고 링크: https://gent.tistory.com/266

SELECT ENAME 
     , SAL 
     , RANK() OVER (ORDER BY SAL DESC)       RANK 
     , DENSE_RANK() OVER (ORDER BY SAL DESC) DENSE_RANK 
  FROM EMP 
 ORDER BY SAL DESC

 

RANK() 함수는 2순위가 2명 이므로 3순위를 건너뛰고 2순위 다음은 4순위로 표시

DENSE_RANK() 함수는 2순위가 2명 이여도 다음 순위는 3순위로 표시

순위가 겹치지 않는다면 RANK나 DENSE_RANK는 큰 차이가 없음

 

 

중복 순위 값 없애기

급여 외에 보너스 컬럼을 추가하여 급여 순위를 조회하면 중복 순위 없이 순차적인 순위가 표시

SELECT ENAME 
     , SAL
     , COMM
     , RANK() OVER (ORDER BY SAL DESC, COMM DESC) RANNK 
  FROM EMP 
 ORDER BY SAL DESC, COMM DESC

그룹별 순위 구하기

조회된 결과에서 그룹별로 순위를 부여해야 하는 경우에는 PARTITION BY절을 추가하면 해당 그룹 내 순위가 표시

SELECT DEPT 
     , ENAME 
     , SAL 
     , COMM 
     , RANK() OVER (PARTITION BY DEPT ORDER BY SAL DESC, COMM DESC) RANK 
  FROM EMP 
 ORDER BY DEPT, SAL DESC, COMM DESC

그룹별 최소값, 최대값 구하기

KEEP() 함수와 FIRST, LAST 키워드를 활용하면 그룹 내에 최소값, 최대값을 쉽게 구할 수 있다. DENSE_RANK 함수만 사용 가능

SELECT DEPT 
     , ENAME 
     , SAL 
     , MIN(SAL) KEEP(DENSE_RANK FIRST ORDER BY SAL) OVER(PARTITION BY DEPT) SAL_MIN 
     , MAX(SAL) KEEP(DENSE_RANK LAST ORDER BY SAL) OVER(PARTITION BY DEPT)  SAL_MAX 
  FROM EMP 
 ORDER BY DEPT, SAL DESC