SQL 스크립팅

-- 타입과 기본값을 지정한 변수 선언
DECLARE target_date DATE DEFAULT CURRENT_DATE() - INTERVAL 1 DAY;
DECLARE batch_size INT DEFAULT 1000;
DECLARE status_msg STRING DEFAULT '';
DECLARE total_amount DECIMAL(12,2);

-- 변수에 값 할당
SET total_amount = 0.00;

DECLARE row_count INT;
DECLARE max_date DATE;

-- 단일 값 쿼리 결과를 변수에 할당
SET row_count = (SELECT COUNT(*) FROM silver.orders WHERE order_date = target_date);
SET max_date = (SELECT MAX(order_date) FROM silver.orders);

-- 변수 값 할당
SET row_count = 100;
SET status_msg = '처리 완료';

-- 표현식으로 할당
SET batch_id = CONCAT('batch_', DATE_FORMAT(CURRENT_TIMESTAMP(), 'yyyyMMdd_HHmmss'));

-- 쿼리 결과로 할당
SET total_revenue = (
    SELECT SUM(amount) FROM gold.daily_revenue
    WHERE sale_date = target_date
);

DECLARE row_count INT;
DECLARE status STRING;

SET row_count = (SELECT COUNT(*) FROM silver.orders WHERE order_date = CURRENT_DATE() - INTERVAL 1 DAY);

IF row_count = 0 THEN
    SET status = 'NO_DATA';
    INSERT INTO audit.alerts (message, severity, created_at)
    VALUES ('어제 주문 데이터가 없습니다', 'CRITICAL', CURRENT_TIMESTAMP());

ELSEIF row_count < 100 THEN
    SET status = 'LOW_VOLUME';
    INSERT INTO audit.alerts (message, severity, created_at)
    VALUES ('어제 주문이 예상보다 적습니다: ' || CAST(row_count AS STRING), 'WARNING', CURRENT_TIMESTAMP());

ELSE
    SET status = 'NORMAL';
    -- 정상: Gold 테이블 갱신
    INSERT OVERWRITE gold.daily_summary
    SELECT
        CURRENT_DATE() - INTERVAL 1 DAY AS summary_date,
        COUNT(*) AS order_count,
        SUM(amount) AS total_revenue
    FROM silver.orders
    WHERE order_date = CURRENT_DATE() - INTERVAL 1 DAY;
END IF;

SELECT status AS processing_result, row_count AS processed_rows;

-- 최근 12개월에 대해 월별 집계 생성
DECLARE month_offset INT DEFAULT 0;

WHILE month_offset < 12 DO
    DECLARE target_month DATE;
    SET target_month = DATE_TRUNC('MONTH', CURRENT_DATE()) - MAKE_INTERVAL(0, month_offset);

    MERGE INTO gold.monthly_summary AS target
    USING (
        SELECT
            target_month AS month,
            COUNT(*) AS order_count,
            SUM(amount) AS total_revenue,
            AVG(amount) AS avg_order_value
        FROM silver.orders
        WHERE DATE_TRUNC('MONTH', order_date) = target_month
    ) AS source
    ON target.month = source.month
    WHEN MATCHED THEN UPDATE SET *
    WHEN NOT MATCHED THEN INSERT *;

    SET month_offset = month_offset + 1;
END WHILE;

SELECT '12개월 집계 완료' AS result;

-- 여러 테이블에 대해 동일한 처리 반복
DECLARE table_index INT DEFAULT 0;
DECLARE table_count INT;
DECLARE current_table STRING;

-- 처리할 테이블 목록을 임시 뷰로 생성
CREATE TEMPORARY VIEW tables_to_process AS
SELECT table_name FROM catalog.information_schema.tables
WHERE table_schema = 'bronze' AND table_name LIKE '%_raw';

SET table_count = (SELECT COUNT(*) FROM tables_to_process);

WHILE table_index < table_count DO
    SET current_table = (
        SELECT table_name FROM (
            SELECT table_name, ROW_NUMBER() OVER (ORDER BY table_name) AS rn
            FROM tables_to_process
        ) WHERE rn = table_index + 1
    );

    -- 각 테이블에 OPTIMIZE 실행
    OPTIMIZE IDENTIFIER('catalog.bronze.' || current_table);

    SET table_index = table_index + 1;
END WHILE;

DECLARE data_count INT;
SET data_count = (SELECT COUNT(*) FROM silver.orders WHERE order_date = CURRENT_DATE());

IF data_count = 0 THEN
    -- 사용자 정의 오류 발생
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '오늘 날짜의 주문 데이터가 없습니다. 데이터 수집 파이프라인을 확인하세요.';
END IF;

-- 데이터가 있으면 이후 로직 계속
SELECT '데이터 검증 통과. 행 수: ' || CAST(data_count AS STRING) AS result;

-- 계좌 이체: 원자적 트랜잭션
BEGIN ATOMIC
    -- 출금
    UPDATE accounts
    SET balance = balance - 50000
    WHERE account_id = 'A001';

    -- 입금
    UPDATE accounts
    SET balance = balance + 50000
    WHERE account_id = 'A002';

    -- 이체 기록
    INSERT INTO transactions (from_acc, to_acc, amount, txn_time)
    VALUES ('A001', 'A002', 50000, CURRENT_TIMESTAMP());
END;

-- 범용 데이터 품질 검증 스크립트
DECLARE target_table STRING DEFAULT 'catalog.silver.orders';
DECLARE target_date DATE DEFAULT CURRENT_DATE() - INTERVAL 1 DAY;
DECLARE null_count INT;
DECLARE dup_count INT;
DECLARE negative_count INT;
DECLARE total_issues INT DEFAULT 0;

-- 1. NULL 체크
SET null_count = (
    SELECT COUNT(*)
    FROM IDENTIFIER(target_table)
    WHERE order_id IS NULL AND order_date = target_date
);
SET total_issues = total_issues + null_count;

-- 2. 중복 체크
SET dup_count = (
    SELECT COUNT(*) - COUNT(DISTINCT order_id)
    FROM IDENTIFIER(target_table)
    WHERE order_date = target_date
);
SET total_issues = total_issues + dup_count;

-- 3. 음수 금액 체크
SET negative_count = (
    SELECT COUNT(*)
    FROM IDENTIFIER(target_table)
    WHERE amount < 0 AND order_date = target_date
);
SET total_issues = total_issues + negative_count;

-- 4. 결과 기록
INSERT INTO audit.quality_checks (table_name, check_date, null_count, dup_count, negative_count, total_issues, checked_at)
VALUES (target_table, target_date, null_count, dup_count, negative_count, total_issues, CURRENT_TIMESTAMP());

-- 5. 임계치 초과 시 오류 발생
IF total_issues > 0 THEN
    SIGNAL SQLSTATE '45001'
    SET MESSAGE_TEXT = CONCAT(
        '데이터 품질 이슈 발견: NULL=', null_count,
        ', 중복=', dup_count,
        ', 음수금액=', negative_count
    );
END IF;

SELECT 'Quality check passed' AS result;