SQLD: SQL 기본(5)

SQLD 공부를 위한 주요 개념 정리

---

1. ROLLUP과 집계 함수

• ROLLUP: 특정 컬럼 조합으로 부분 집계와 전체 집계를 생성하는 함수.
  예시: SELECT c1, c2, SUM(col) FROM table GROUP BY ROLLUP (c1, c2);
  • c2와 c1을 차례로 그룹핑하여 다양한 부분 집계를 제공함.
  • 전체 집계: 전체 데이터를 그룹핑한 결과를 마지막 행으로 추가함.

2. GROUPING 함수

• GROUPING 함수: 집계 수준을 판별할 수 있게 함.
  • 예시: SELECT c1, c2, SUM(col), GROUPING(c1) AS g1, GROUPING(c2) AS g2 FROM table GROUP BY ROLLUP(c1, c2);
  • g1이나 g2 값이 1이면 해당 컬럼이 집계 레벨에서 집계됐음을 의미함.

3. ROLLUP의 경우의 수

• ROLLUP의 경우의 수는 각 컬럼이 NULL 또는 그룹 값인 모든 경우의 수를 포함.
  • 컬럼 2개: n+1개의 집계(전체 집계 포함) 제공.
  • 예: ROLLUP(c1, c2)는 {c1, c2}, {c1, NULL}, {NULL, NULL}의 경우로 부분 및 전체 집계를 생성함.

4. LAG와 LEAD

• LAG: 현재 행 기준으로 이전 행의 값을 참조함.
  • 예시: SELECT ename, sal, LAG(sal, 1, 0) OVER (ORDER BY ename) AS prev_sal FROM emp;
• LEAD: 현재 행 기준으로 다음 행의 값을 참조함.
  • 예시: SELECT ename, sal, LEAD(sal, 1, 0) OVER (ORDER BY ename) AS next_sal FROM emp;

5. CONNECT BY와 PRIOR 사용

• 두 쿼리 차이:
  • START WITH ename = 'JONES' CONNECT BY MGR = PRIOR EMPNO AND JOB <> 'ANALYST';: 계층구조 탐색 시, 각 경로에서 ANALYST가 아닌 JOB만 탐색.
  • START WITH ename = 'JONES' CONNECT BY MGR = PRIOR EMPNO WHERE job <> 'ANALYST';: 전체 결과에서 ANALYST인 경우를 제외함.

6. UNPIVOT 예시

• UNPIVOT: 컬럼을 행으로 변환하여 분석을 단순화함.
  • 예시:

    SELECT count (*) AS cnt
    FROM T1 UNPIVOT INCLUDE NULLS (SAL FOR DEPTNO IN (D10_SAL AS 10, D20_SAL AS 20, D30_SAL AS 30))
    WHERE JOB = 'ANALYST';

  • DEPTNO 값을 기준으로 D10_SAL, D20_SAL, D30_SAL 컬럼을 SAL 행 값으로 변환함.

7. ALTER에 따른 데이터 타입 변경 제한

• ALTER: 데이터가 존재할 경우, 일부 데이터 타입 변경이 제한됨.
  • NUMBER(2)에서 NUMBER(1)로 축소할 때 값의 범위 초과가 발생할 수 있어 오류 발생 가능.

8. NUMBER 타입 변경 시 주의사항

• NUMBER(2)에서 NUMBER(1)로 줄이는 경우, 데이터가 1자리 이상일 경우 오류가 발생함.

9. 데이터베이스 권한과 공간 관리

데이터베이스 권한이랑 공간 관리는 아파트 단지 관리랑 비슷함. 이거 보면 INSERT 권한이랑 테이블스페이스 사용 권한 차이 알 수 있음.

1. 테이블스페이스 (아파트 단지)

   • 테이블스페이스는 데이터베이스 물리적 저장 공간임. 아파트 단지랑 비슷함.
   • 예: USERS 테이블스페이스는 대형 아파트 단지라 생각하면 됨.

2. 테이블스페이스 사용 권한 (아파트 입주 권한)

   • ALTER USER username QUOTA 50M ON USERS;
   • 이거는 사용자한테 USERS 아파트 단지에서 50평 크기 공간 쓸 수 있게 해주는 거임.
   • 이 권한 없으면 데이터 저장할 공간 자체가 없어서 아무것도 못 함.

3. INSERT 권한 (특정 아파트에 물건 넣을 수 있는 권한)

   • GRANT INSERT ON T1 TO username;
   • 이거는 사용자한테 T1이라는 특정 아파트(테이블)에 물건(데이터) 넣을 수 있는 열쇠 주는 거임.
   • 근데 이 권한만으로는 부족함. 아파트에 입주할 권한(테이블스페이스 사용 권한)도 필요함.

4. UNLIMITED TABLESPACE 권한 (모든 아파트 단지 무제한 사용 권한)

   • GRANT UNLIMITED TABLESPACE TO username;
   • 이거는 사용자한테 모든 아파트 단지에서 원하는 만큼 공간 쓸 수 있게 해주는 거임.
   • 엄청 강력한 권한이라 조심해서 줘야 함.

5. 권한 조합

   • 데이터 성공적으로 넣으려면 두 가지 권한 다 필요함:
     1. 테이블스페이스 사용 권한 (아파트에 입주할 수 있는 권한)
     2. 특정 테이블에 대한 INSERT 권한 (특정 아파트에 물건 넣을 수 있는 권한)