자격증/SQLD

02. SQL활용 (1)

염두리안 2024. 5. 21. 20:50
728x90
반응형
  • 서브쿼리) SELECT문 내에 다시 SELECT무을 사용하는 SQL문 / 인라인뷰- FROM구에 SELECT문 사용... 가상의 테이블을 만드는 효과 / 스칼라 서브쿼리 - SELECT문에 서브쿼리 사용
    • 단일행 서브쿼리 / 다중행 서브쿼리
      • 단일행 서브쿼리(Single) : 서브쿼리 실행시 그 결과는 반드시 한 행만 조회 / 비교연산자 사용
      • 다중행 서브쿼리(Multi) : 서브쿼리 실행시 여러 개 행 조회 / 비교연산자(IN, ALL, ANY, EXISTS) 사용
        • IN : 메인쿼리 비교조건이 서브쿼리 결과 중 하나만 동일하면 참(OR 조건)
        • ALL : 메인쿼리와 서브쿼리 결과가 모두 동일시 참... / < ALL : 최솟값 / > ALL : 최댓값 반환
        • ANY : 메인쿼리 비교조건이 서브쿼리 결과 중 하나 이상 동일하면 참 / < ANY : 하나라도 크게 되면 참 / > ANY : 하나라도 작게 되면 참
        • EXISTS : 메인쿼리와 서브쿼리의 결과가 하나라도 존재하면 참
    • 스칼라(Scala) 서브쿼리 : 반드시 한 행과 한 칼럼만 반환하는 서브쿼리... 여러 행 반환시 오류 발생
    • 연관(Correlated) 서브쿼리 : 서브쿼리 내에서 메인쿼리 내의 칼럼을 사용하는 것
// IN - 급여(SAL)가 2000원 보다 큰 사원번호를 조회 후 EMP.EMPNO 조회
IN (SELECT EMPNO FROM EMP WHERE SAL > 2000);
    
// ALL - DEPTNO가 20, 30보다 작거나 같은 것 조회
WHERE DEPTNO <= ALL (20, 30);

// EXISTS - 급여가 2000원보다 큰 사원이 있으면 TRUE 조회
EXISTS (SELECT 1 FROM EMP WHERE SAL > 2000);
  • 조인? 여러 개 릴레이션을 사용해 새로운 릴레이션을 만드는 과정
    • EQUI(등가) 조인(교집합) : 두 개 테이블 간 일치하는 것 조인... '='을 사용해 두 개의 테이블 연결
      • 해시 조인 : EQUI JOIN에서만 사용 가능 / 해시함수? 테이블을 해시 메모리에 적재 후 해시 함수로써 연결하는 방법
      • INNER 조인 : ON문 사용해 테이블 연결
      • INTERSECT 연산 : 두 개 테이블에서 공통된 값 조회
    •  Non-EQUI JOIN(비등가) : 두 개의 테이블을 조인할 때 '=' 가 아닌 다른 비교 연산자들 사용 / 정확히 일치하지 않는 것을 조인하는 것
    • OUTER JOIN : 두 개의 테이블 간에 교집합을 조회하고 한 족 테이블에만 있는 데이터도 포함시켜 조회 / 오라클에선 '(+)' 사용
      • LEFT OUTER JOIN : 두 개 테이블에서 같은 것을 조회하고 왼쪽 테이블에만 있는 것을 포함해서 조회
      • RIGHT OUTER JOIN : 두 개 테이블에서 같은 것을 조회하고 오른쪽 테이블에만 있는 것 포함해서 조회(NULL인 행도 조회됨)
    • CROSS JOIN : 조인 조건구 없이 2개의 테이블을 하나로 조인... 카테시안 곱 발생(ex. 행 14개 테이블, 행 4개 테이블 조인시 14*4 = 56개 행 조회)
    • UNION : 두 개의 테이블을 하나로 만드는 연산 / [주의] 두 개의 테이블의 칼럼 수, 데이터 형식 모두가 일치해야 함... 그렇지 않으면 오류 발생 / 두 개 테이블을 하나로 합치면서 중복된 데이터 제거 ~> 정렬 과정 발생
      • UNION ALL : 두 개의 테이블을 하나로 합치되 중복을 제거하거나 정렬 유발 X
    • MINUS : 두 개의 테이블에서 차집합 조회... 먼저 쓴 SELECT문에는 있고 뒤에 쓰는 SELECT문에는 없는 집합 조회 / MS-SQL에선 EXCEPT

상황 예시

// EQUI JOIN
WHERE EMP.DEPTNO = DEPT.DEPTNO
// INNER JOIN (ON구를 사용해 조인 조건 추가)
SELECT * FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;
// INTERSECT 연산
SELECT DEPTNO FROM EMP
INTERSECT
SELECT DEPTNO FROM DEPT;

// OUTER JOIN
SELECT * FROM DEPT, EMP
WHERE EMP.DEPTNO (+) = DEPT.DEPTNO;
// LEFT OUTER JOIN
SELECT * FROM DEPT LEFT OUTER JOIN EMP
ON EMP.DEPTNO = DEPT,DEPTNO;

// CROSS JOIN
SELECT * FROM EMP CROSS JOIN DEPT;

// UNION
SELECT DEPTNO FROM EMP
UNION
SELECT DEPTNO FROM EMP;
// UNINO ALL
SELECT DEPTNO FROM EMP
UNION ALL
SELECT DEPTNO FROM EMP;

// MINUS
SELECT DEPTNO FROM DEPT
MINUS
SELECT DEPTNO FROM EMP;
  • 그룹함수(Group Function)
    • ROLLUP : Group by 칼럼에 대해 subtotal을 만들어 줌 / ROLLUP 시 GROUP BY에 칼럼이 두 개 이상 오면 순서에 따라서 결과가 달라짐
    • GROUPING 함수 : ROLLUP, CUBE, GROUPING SETS에서 생성되는 합계값을 구분하기 위해서 만들어진 함수... 계산시 1 반환
    • GROUPING SETS 함수 : GROUP BY에 나오는 칼럼의 순서와 관계없이 다양한 소계를 만들 수 있고 개별적으로 모두 처리
    • CUBE 함수 : CUBE 함수에 제시한 칼럼에 대해 결합 가능한 모든 집계 계산 / 다차원 집계 제공해 다양하게 데이터 분석 가능(조합할 수 있는 경우의 수 모두 조합됨)
//ROLLUP - DEPTNO가 NULL이면 '전체 합계' 문자 출력
SELECT DECODE(DEPTNO, NULL, '전체합계', DEPTNO), SUM(SAL) FROM EMP
GROUP BY ROLLUP (DEPTNO);	// ROLLUP 사용시 부서별 합계, 전체합계 계산
GROUP BY ROLLUP (DEPTNO, JOB); // 부서별, 직업별 합계 구함

// GROUPING - 계산된 합계는 1로 표시
SELECT DEPTNO, GROUPING (DEPTNO), JOB, GROUPING(JOB), SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);
// DECODE 사용해 전체 합걔와 부서합계 구분
SELECT DEPTNO,
	DECODE(GROUPING(DEPTNO), 1, '전체합계') TOT, JOB, 
    DECODE(GROUPING(JOB), 1, '부서합계') T_DEPT, SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);

// GROUPING SETS - DEPTNO, JOB을 각각의 그룹으로 합계 계산
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY GROUPING SETS(DEPTNO, JOB);

// CUBE
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY CUBE(DPETNO, JOB);
  • 윈도우 함수(Window Function)
    • 윈도우 함수? 행과 행 간의 관계를 정의하기 위해 제공되는 함수... 순위, 합계, 평균, 행 위치 등 조작
      • 구조
        • ARGUMENTS(인수) : 윈도우 함수에 따라 0 ~ N개의 인수 설정
        • PARTITION BY : 전체 집합 기준에 의해 소그룹으로 나눔
        • ORDER BY : 어떤 항목에 대해 정렬
        • WINDOWING : 행 기준의 범위 정함 / ROW는 물리적 결과의 행수, RANGE는 논리적인 값에 의한 범위
          • ROWS : 부분집합인 윈도우 크기를 물리적 단위로 행의 집합 지정
          • RANGE : 논리적인 주소에 의해 행 집합 지정
          • BETWEEN ~ AND : 윈도우의 시작과 끝의 위치 지정
          • UNBOUNDED PRECEDING : 윈도우의 시작 위치가 첫 번째 행임을 의미
          • UNBOUNDED FOLLOWING : 윈도우 마지막 위치가 마지막 행임을 의미
          • CURRENT ROW : 윈도우 시작 위치가 현재 행임을 의미
    • 순위함수
      • RANK : 특정항목, 파티션에 대해 순위 계산 / 동순위는 동일 값 부여
      • DENSE_RANK : 동일한 순위를 하나의 건수로 계산
      • ROW_NUMBER : 동일 순위에 대해 고유의 순위 부여
    • 집계함수 : SUM, AVG, COUNT, MAX, MIN
    • 행 순서 관련 함수
      • FIRST_VALUE : 파티션에서 가장 처음에 나오는 값을 구함 / MIN 함수를 사용해 같은 결과 구할 수 있음
      • LAST_VALUE : 가장 나중에 나오는 값 구함 / MAX 함수와 같은 결과
      • LAG : 이전 행
      • LEAD : 윈도우에서 특정 위치 행을 가져옴 / 기본값 1
    • 비율 관련 함수
      • CUME_DIST : 파티션 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율 조회 / 0 ~ 1 사이 값
      • PERCENT_RANK : 제일 먼저 나온 것 0, 늦게 나온 것 1로 해서 값이 아닌 행의 순서별 백분율 조회
      • NTILE : 파티션별로 전체 건수를 ARGUMENT 값으로 N 등분한 결과 조회
      • RATIO_TO_REPORT : 파티션 내 전체 SUM(칼럼)에 대한 행 별 칼럼값의 백분율을 소수점까지 조회
// UNBOUNDED PRECEDING/FOLLOWING 예시
SELECT EMPNO, ENAM, SAL,
	SUM(SAL) OVER(ORDER BY SAL
    				ROWS BETWEEN UNBOUNDED PRECEDING	// 첫번째 행
                    AND UNBOUNDED FOLLOWING) TOTSAL		// 마지막 행
	FROM EMP;
    
// RANK
SELECT ENAME, SAL,
	DENSE_RANK() OVER (ORDER BY SAL DESC) ALL RANK,	// 급여 순위 계산(동일 순위는 하나의 건수로)
    RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK	// 직업별 순위 계산
FROM EMP;

// FIRST_VALUE - ENAME의 첫번째 행 가져옴(=부서 내에서 급여가 가장 많은 사람 의미)
// 부서 파티션 생성
SELECT DEPTNO, ENAME, SAL,
FIRST VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING)
AS DEPT_A FROM EMP;

// NTILE - 4등분으로 분할... 급여가 높은 순으로 1~4등분으로 분할
SELECT DEPTNO, ENAME, SAL,
NTILE(4) OVER(ORDER BY SAL DESC) AS N_TILE
FROM EMP;
728x90
반응형