CASE와 VIEW

DROP VIEW IF EXISTS v_tag_post_summary;
DROP VIEW IF EXISTS v_member_post_summary;
DROP VIEW IF EXISTS v_active_member;
DROP TABLE IF EXISTS ch6_case_post_tag_demo;
DROP TABLE IF EXISTS ch6_case_tag_demo;
DROP TABLE IF EXISTS ch6_case_post_demo;
DROP TABLE IF EXISTS ch6_case_member_demo;

CREATE TABLE ch6_case_member_demo (
  id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(80) NOT NULL,
  status VARCHAR(16) NOT NULL,
  created_at DATETIME(6) NOT NULL,
  CONSTRAINT ck_ch6_case_member_status
    CHECK (status IN ('ACTIVE', 'SUSPENDED', 'DELETED'))
) ENGINE = InnoDB;

CREATE TABLE ch6_case_post_demo (
  id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  author_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(80) NOT NULL,
  status VARCHAR(16) NOT NULL,
  view_count BIGINT UNSIGNED NOT NULL,
  CONSTRAINT fk_ch6_case_post_author
    FOREIGN KEY (author_id) REFERENCES ch6_case_member_demo (id)
) ENGINE = InnoDB;

CREATE TABLE ch6_case_tag_demo (
  id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  code VARCHAR(40) NOT NULL UNIQUE,
  is_active BOOLEAN NOT NULL
) ENGINE = InnoDB;

CREATE TABLE ch6_case_post_tag_demo (
  post_id BIGINT UNSIGNED NOT NULL,
  tag_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (post_id, tag_id),
  CONSTRAINT fk_ch6_case_post_tag_post
    FOREIGN KEY (post_id) REFERENCES ch6_case_post_demo (id),
  CONSTRAINT fk_ch6_case_post_tag_tag
    FOREIGN KEY (tag_id) REFERENCES ch6_case_tag_demo (id)
) ENGINE = InnoDB;

INSERT INTO ch6_case_member_demo
  (id, email, name, status, created_at)
VALUES
  (1, 'andongmin@example.com', '안동민', 'ACTIVE', '2026-07-01 09:00:00'),
  (2, 'hana@example.com', '이하나', 'ACTIVE', '2026-07-02 09:00:00'),
  (3, 'park@example.com', '박준', 'ACTIVE', '2026-07-03 09:00:00');

INSERT INTO ch6_case_post_demo
  (id, author_id, title, status, view_count)
VALUES
  (1, 1, 'SELECT 기초', 'PUBLISHED', 120),
  (2, 1, 'JOIN 실습', 'PUBLISHED', 140),
  (3, 1, 'VIEW 정리', 'PUBLISHED', 160),
  (4, 1, '서브쿼리 초안', 'DRAFT', 0),
  (5, 1, '숨긴 글', 'HIDDEN', 0),
  (6, 2, 'NULL 복습', 'PUBLISHED', 50),
  (7, 2, '집계 함수', 'PUBLISHED', 60),
  (8, 2, 'CASE 초안', 'DRAFT', 0);

INSERT INTO ch6_case_tag_demo (id, code, is_active)
VALUES
  (1, 'database', TRUE),
  (2, 'java', TRUE),
  (3, 'empty', TRUE),
  (4, 'legacy', FALSE);

INSERT INTO ch6_case_post_tag_demo (post_id, tag_id)
VALUES
  (1, 1),
  (2, 1),
  (3, 4),
  (6, 2),
  (8, 2);

SELECT
  author_id,
  COUNT(*) AS all_posts,
  COUNT(CASE
    WHEN status = 'PUBLISHED' THEN 1
    ELSE 0
  END) AS published_count,
  SUM(view_count) AS total_views,
  CASE
    WHEN SUM(view_count) >= 60 THEN 'STEADY'
    WHEN SUM(view_count) >= 300 THEN 'FOCUSED'
    ELSE 'STARTING'
  END AS activity_grade
FROM ch6_case_post_demo
GROUP BY author_id;

author_id | all_posts | published_count | total_views | activity_grade
1         | 5         | 5               | 420         | STEADY
expected  | 5         | 3               | 420         | FOCUSED

CREATE OR REPLACE SQL SECURITY INVOKER VIEW v_member_post_summary AS
SELECT
  base.member_id,
  base.name,
  base.post_count,
  base.published_count,
  base.published_views,
  CASE
    WHEN base.published_views >= 300 THEN 'FOCUSED'
    WHEN base.published_views >= 60 THEN 'STEADY'
    ELSE 'STARTING'
  END AS activity_grade
FROM (
  SELECT
    m.id AS member_id,
    m.name,
    COUNT(p.id) AS post_count,
    SUM(CASE
      WHEN p.status = 'PUBLISHED' THEN 1
      ELSE 0
    END) AS published_count,
    COALESCE(SUM(CASE
      WHEN p.status = 'PUBLISHED' THEN p.view_count
      ELSE 0
    END), 0) AS published_views
  FROM ch6_case_member_demo AS m
  LEFT JOIN ch6_case_post_demo AS p ON p.author_id = m.id
  GROUP BY m.id, m.name
) AS base;

SELECT *
FROM v_member_post_summary
ORDER BY published_views DESC, member_id;

member_id | name   | post_count | published_count | published_views | activity_grade
1         | 안동민 | 5          | 3               | 420             | FOCUSED
2         | 이하나 | 3          | 2               | 110             | STEADY
3         | 박준   | 0          | 0               | 0               | STARTING

DROP VIEW IF EXISTS v_active_member;

CREATE ALGORITHM = MERGE
SQL SECURITY INVOKER
VIEW v_active_member AS
SELECT id, email, name, status
FROM ch6_case_member_demo
WHERE status = 'ACTIVE'
WITH CASCADED CHECK OPTION;

SELECT *
FROM v_active_member
ORDER BY id;

UPDATE v_active_member
SET name = '안동민 수정'
WHERE id = 1;

SELECT id, name
FROM ch6_case_member_demo
WHERE id = 1;

UPDATE v_active_member
SET status = 'SUSPENDED'
WHERE id = 1;

ERROR 1369: CHECK OPTION failed 'board_lab.v_active_member'

UPDATE v_member_post_summary
SET published_views = 0
WHERE member_id = 1;

ERROR 1288: The target table of the UPDATE is not updatable

ALTER ALGORITHM = MERGE
SQL SECURITY INVOKER
VIEW v_active_member AS
SELECT id, email, name, status, created_at
FROM ch6_case_member_demo
WHERE status = 'ACTIVE'
WITH CASCADED CHECK OPTION;

SHOW CREATE VIEW v_active_member;
DROP VIEW v_active_member;

SHOW CREATE VIEW v_member_post_summary;

SELECT
  (SELECT COUNT(*) FROM ch6_case_member_demo) AS member_rows,
  (SELECT COUNT(*) FROM v_member_post_summary) AS view_rows,
  (SELECT COALESCE(SUM(view_count), 0)
   FROM ch6_case_post_demo
   WHERE status = 'PUBLISHED') AS base_published_views,
  (SELECT COALESCE(SUM(published_views), 0)
   FROM v_member_post_summary) AS view_published_views;

EXPLAIN ANALYZE
SELECT *
FROM v_member_post_summary
WHERE published_views >= 60;