MySQL 8.0 주요 변경점 — Window Function, CTE, Histogram

함수() OVER (
    PARTITION BY 그룹_컬럼
    ORDER BY 정렬_컬럼
    ROWS/RANGE 프레임_지정
)

-- 부서별로 급여 순위 매기기
SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM employees;

| name   | department | salary | rank_in_dept |
|--------|-----------|--------|-------------|
| 김철수  | 개발       | 6000   | 1           |
| 이영희  | 개발       | 5500   | 2           |
| 박민수  | 개발       | 5000   | 3           |
| 정수진  | 마케팅     | 5800   | 1           |
| 최진우  | 마케팅     | 5200   | 2           |

SELECT
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    RANK() OVER (ORDER BY salary DESC) AS ranking,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_ranking
FROM employees;

| name | salary | row_num | ranking | dense_ranking |
|------|--------|---------|---------|--------------|
| A    | 6000   | 1       | 1       | 1            |
| B    | 6000   | 2       | 1       | 1            |
| C    | 5500   | 3       | 3       | 2            |  ← RANK: 3, DENSE_RANK: 2
| D    | 5000   | 4       | 4       | 3            |

-- 이전 달 대비 매출 비교
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS diff
FROM monthly_revenue;

| month   | revenue | prev_month_revenue | diff  |
|---------|---------|-------------------|-------|
| 2025-01 | 1000    | NULL              | NULL  |
| 2025-02 | 1200    | 1000              | 200   |
| 2025-03 | 1100    | 1200              | -100  |

-- 누적 합계
SELECT
    date,
    amount,
    SUM(amount) OVER (ORDER BY date) AS running_total
FROM orders;

-- 이동 평균 (최근 3건)
SELECT
    date,
    amount,
    AVG(amount) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM orders;

-- 서브쿼리 버전 (읽기 어려움)
SELECT * FROM (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees GROUP BY department
) dept_avg
WHERE avg_salary > 5000;

-- CTE 버전 (읽기 쉬움)
WITH dept_avg AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT * FROM dept_avg WHERE avg_salary > 5000;

WITH
active_users AS (
    SELECT id, name FROM users WHERE status = 'active'
),
user_orders AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
)
SELECT
    u.name,
    COALESCE(o.order_count, 0) AS orders
FROM active_users u
LEFT JOIN user_orders o ON u.id = o.user_id;

-- 조직도 테이블
CREATE TABLE org (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

-- CEO부터 모든 하위 직원 조회
WITH RECURSIVE org_tree AS (
    -- 시작점 (Anchor)
    SELECT id, name, manager_id, 0 AS depth
    FROM org WHERE manager_id IS NULL

    UNION ALL

    -- 재귀 (하위 조직원 탐색)
    SELECT o.id, o.name, o.manager_id, t.depth + 1
    FROM org o
    JOIN org_tree t ON o.manager_id = t.id
)
SELECT CONCAT(REPEAT('  ', depth), name) AS org_chart
FROM org_tree;

CEO
  VP Engineering
    Team Lead A
      Developer 1
      Developer 2
    Team Lead B
  VP Marketing
    Marketing Manager

-- Histogram 생성
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 100 BUCKETS;

-- Histogram 확인
SELECT
    SCHEMA_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    JSON_EXTRACT(HISTOGRAM, '$.\"histogram-type\"') AS type,
    JSON_EXTRACT(HISTOGRAM, '$.\"number-of-buckets-specified\"') AS buckets
FROM information_schema.COLUMN_STATISTICS;

-- Histogram 삭제
ANALYZE TABLE orders DROP HISTOGRAM ON status;

-- 역할 생성
CREATE ROLE 'read_only', 'read_write', 'admin';

-- 역할에 권한 부여
GRANT SELECT ON mydb.* TO 'read_only';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'read_write';
GRANT ALL PRIVILEGES ON mydb.* TO 'admin';

-- 사용자에게 역할 부여
GRANT 'read_only' TO 'analyst'@'%';
GRANT 'read_write' TO 'app_user'@'10.0.%';

-- 기본 역할 설정
SET DEFAULT ROLE 'read_only' TO 'analyst'@'%';

-- 5.7: latin1 → 8.0: utf8mb4
SHOW VARIABLES LIKE 'character_set_server';  -- utf8mb4
SHOW VARIABLES LIKE 'collation_server';       -- utf8mb4_0900_ai_ci

-- 5.7: mysql_native_password
-- 8.0: caching_sha2_password
-- 호환성 문제 시 변경 가능
ALTER USER 'user'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

-- 8.0에서는 이 명령이 원자적
DROP TABLE IF EXISTS t1, t2, t3;
-- 모두 삭제되거나, 하나도 삭제되지 않음

-- 인덱스를 보이지 않게
ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;

-- 성능 테스트 후 다시 보이게
ALTER TABLE orders ALTER INDEX idx_status VISIBLE;

-- 5.7에서는 DESC가 무시됨
-- 8.0에서는 실제로 내림차순 인덱스 생성
CREATE INDEX idx_created ON orders (created_at DESC);

-- 복합 인덱스에서도 방향 혼합 가능
CREATE INDEX idx_mixed ON orders (customer_id ASC, created_at DESC);

-- 락을 기다리지 않고 즉시 에러
SELECT * FROM orders WHERE id = 1 FOR UPDATE NOWAIT;

-- 잠긴 행을 건너뜀
SELECT * FROM orders WHERE status = 'pending'
FOR UPDATE SKIP LOCKED LIMIT 10;

-- JSON 값에 대한 집계
SELECT JSON_ARRAYAGG(name) FROM users WHERE department = '개발';
-- 결과: ["김철수", "이영희", "박민수"]

SELECT JSON_OBJECTAGG(name, salary) FROM users WHERE department = '개발';
-- 결과: {"김철수": 6000, "이영희": 5500, "박민수": 5000}

-- JSON 테이블 함수
SELECT *
FROM JSON_TABLE(
    '[{"name": "A", "score": 90}, {"name": "B", "score": 85}]',
    '$[*]' COLUMNS (
        name VARCHAR(10) PATH '$.name',
        score INT PATH '$.score'
    )
) AS jt;