SQLD: SQL 기본(4)

1. 단일행 서브 쿼리와 스칼라 서브 쿼리의 이해

단일행 서브 쿼리

• 메인 쿼리의 각 행마다 하나의 결과값만 반환하는 서브 쿼리임
• 비교 연산자(=, >, <, >=, <=, <>)랑 같이 쓸 수 있음
• 여러 행이 반환되면 에러가 발생하니 주의해야 함

예시:

-- 평균 급여보다 많이 받는 직원 찾기
SELECT emp_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

스칼라 서브 쿼리

• SELECT절에서 칼럼처럼 사용할 수 있는 서브 쿼리임
• 반드시 단일값을 반환해야 함
• 성능상 여러 번 실행될 수 있어서 최적화에 주의해야 함

실전 예시:

-- 각 직원의 부서 평균 급여 비교
SELECT 
    emp_name,
    salary,
    (SELECT AVG(salary) FROM employees e2 
     WHERE e2.dept_id = e1.dept_id) as dept_avg
FROM employees e1;

2. 상관 서브 쿼리의 테이블 별칭 활용법

테이블 별칭 미사용 시 발생하는 문제점

• 동일한 칼럼명이 있을 때 어느 테이블의 칼럼인지 모호해짐
• 성능 저하가 발생할 수 있음
• 유지보수가 어려워짐

올바른 별칭 사용법

-- 잘못된 예시
SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) 
                FROM employees 
                WHERE department_id = department_id);

-- 올바른 예시
SELECT *
FROM employees e1
WHERE salary > (SELECT AVG(salary) 
                FROM employees e2
                WHERE e2.department_id = e1.department_id);

3. 집합 연산자의 상세 활용법

UNION

• 중복 제거하면서 합치는 거임
• 자동으로 정렬이 발생해서 성능에 영향을 줄 수 있음

SELECT employee_id, name FROM current_employees
UNION
SELECT employee_id, name FROM retired_employees;

UNION ALL

• 중복 제거 없이 그냥 다 합치는 거임
• 정렬도 안 하니까 UNION보다 성능이 좋음

SELECT amount, 'Income' as type FROM income
UNION ALL
SELECT amount, 'Expense' as type FROM expenses;

INTERSECT

• 양쪽 다 있는 데이터만 가져옴

-- 양쪽 부서에 다 있는 직원 찾기
SELECT emp_id FROM dept_a
INTERSECT
SELECT emp_id FROM dept_b;

MINUS

• 첫 번째 쿼리에서 두 번째 쿼리 결과 빼는 거임

-- A 부서에만 있는 직원 찾기
SELECT emp_id FROM dept_a
MINUS
SELECT emp_id FROM dept_b;

4. EXISTS 서브쿼리의 활용

기본 사용법

• 조건을 만족하는 데이터가 있는지만 확인함
• 성능 최적화를 위해 SELECT 1 사용하는 게 좋음

-- 주문이 있는 고객만 찾기
SELECT customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.customer_id = c.customer_id
);

NOT EXISTS 활용

-- 주문이 없는 고객 찾기
SELECT customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.customer_id = c.customer_id
);

5. 그룹핑 함수의 고급 활용

ROLLUP

• 계층적 집계를 만들어줌

-- 부서/직급별 급여 합계와 전체 합계
SELECT dept_id, job_grade, SUM(salary)
FROM employees
GROUP BY ROLLUP(dept_id, job_grade);

CUBE

• 모든 가능한 조합의 집계를 만들어줌

-- 모든 조합의 매출 집계
SELECT region, product, SUM(sales)
FROM sales_data
GROUP BY CUBE(region, product);

GROUPING SETS

• 원하는 그룹핑만 선택적으로 할 수 있음

-- 부서별, 직급별 각각의 집계
SELECT dept_id, job_grade, SUM(salary)
FROM employees
GROUP BY GROUPING SETS(dept_id, job_grade);

6. 누적 계산의 활용

단순 SUM

-- 부서별 급여 합계
SELECT dept_id, SUM(salary) as total_salary
FROM employees
GROUP BY dept_id;

누적 합계 계산

-- 날짜별 누적 매출
SELECT 
    sale_date,
    amount,
    SUM(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as cumulative_sum
FROM sales;

7. 순위 함수의 차이점과 활용

RANK

• 동일 순위 있으면 건너뛰고 매김

SELECT 
    emp_name,
    salary,
    RANK() OVER (ORDER BY salary DESC) as salary_rank
FROM employees;

DENSE_RANK

• 동일 순위 있어도 순차적으로 매김

SELECT 
    emp_name,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) as dense_salary_rank
FROM employees;

PERCENT_RANK

• 백분위 순위를 계산해줌

SELECT 
    emp_name,
    salary,
    PERCENT_RANK() OVER (ORDER BY salary) * 100 as percentile
FROM employees;

8. ROWNUM의 올바른 사용법

기본적인 사용

-- 상위 5명만 가져오기
SELECT *
FROM employees
WHERE ROWNUM <= 5;

정렬과 함께 사용할 때의 올바른 방법

-- 급여 상위 5명
SELECT *
FROM (
    SELECT *
    FROM employees
    ORDER BY salary DESC
)
WHERE ROWNUM <= 5;

9. WITH TIES의 활용

기본 사용법

-- 상위 5명과 동일 급여 받는 사람들까지
SELECT TOP 5 WITH TIES *
FROM employees
ORDER BY salary DESC;

10. OFFSET과 FETCH NEXT의 페이징 처리

기본 구문

-- 10개 건너뛰고 다음 5개 가져오기
SELECT *
FROM employees
ORDER BY emp_id
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY;

페이징 처리 예시

-- 페이지당 20개씩, 3번째 페이지
SELECT *
FROM products
ORDER BY product_id
OFFSET 40 ROWS
FETCH NEXT 20 ROWS ONLY;

11. 계층형 쿼리 작성법

START WITH CONNECT BY

-- 조직도 구조 조회
SELECT 
    LEVEL,
    LPAD(' ', 2*(LEVEL-1)) || emp_name as hierarchy,
    emp_id,
    manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

12. WITH절과 재귀 쿼리

기본 WITH절

-- 부서별 통계를 임시로 만들어서 사용
WITH dept_stats AS (
    SELECT 
        dept_id,
        AVG(salary) as avg_salary,
        COUNT(*) as emp_count
    FROM employees
    GROUP BY dept_id
)
SELECT * FROM dept_stats WHERE emp_count > 10;

재귀적 WITH절

-- 1부터 10까지의 숫자 생성
WITH RECURSIVE numbers(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;

13. PIVOT의 고급 활용

동적 PIVOT

-- 분기별 매출 피벗
SELECT *
FROM (
    SELECT 
        product_category,
        quarter,
        sales_amount
    FROM sales
)
PIVOT (
    SUM(sales_amount)
    FOR quarter IN (
        'Q1' as Q1_SALES,
        'Q2' as Q2_SALES,
        'Q3' as Q3_SALES,
        'Q4' as Q4_SALES
    )
);

14. UNPIVOT 상세 활용법

기본 UNPIVOT

-- 분기별 매출 데이터를 행으로 변환
SELECT *
FROM quarterly_sales
UNPIVOT (
    amount
    FOR quarter IN (
        Q1_AMOUNT as 'Q1',
        Q2_AMOUNT as 'Q2',
        Q3_AMOUNT as 'Q3',
        Q4_AMOUNT as 'Q4'
    )
);

15. REGEXP_SUBSTR 패턴 매칭

다양한 패턴 예시

-- 이메일에서 사용자명만 추출
SELECT REGEXP_SUBSTR(email, '^[^@]+') FROM users;

-- 전화번호에서 지역번호 추출
SELECT REGEXP_SUBSTR(phone, '^\d{2,3}') FROM contacts;

-- URL에서 도메인 추출
SELECT REGEXP_SUBSTR(url, 'https?://([^/]+)') FROM websites;

복잡한 패턴 매칭

-- 문장에서 숫자만 추출
SELECT REGEXP_SUBSTR(
    'The price is $123.45',
    '\d+(\.\d+)?'
) FROM dual;

-- HTML 태그 제거
SELECT REGEXP_REPLACE(
    '<p>Some text</p>',
    '<[^>]+>',
    ''
) FROM dual;