역정규화와 집계 데이터

DROP TABLE IF EXISTS summary_drift_demo;
DROP TABLE IF EXISTS post_summary_drift_demo;

CREATE TABLE post_summary_drift_demo (
  post_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  author_id BIGINT UNSIGNED NOT NULL,
  view_count BIGINT UNSIGNED NOT NULL,
  status VARCHAR(16) NOT NULL
);
CREATE TABLE summary_drift_demo (
  member_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  total_views BIGINT UNSIGNED NOT NULL
);

INSERT INTO post_summary_drift_demo
  (author_id, view_count, status)
VALUES (1, 50, 'PUBLISHED');
INSERT INTO summary_drift_demo VALUES (1, 50);

INSERT INTO post_summary_drift_demo
  (author_id, view_count, status)
VALUES (1, 40, 'PUBLISHED');
-- 여기서 process가 종료되어 아래 동기화 문장은 실행되지 않았다고 가정한다.
-- UPDATE summary_drift_demo
-- SET total_views = total_views + 40
-- WHERE member_id = 1;

SELECT
  SUM(s.view_count) AS source_views,
  summary.total_views AS cached_views,
  summary.total_views - SUM(s.view_count) AS drift_views
FROM post_summary_drift_demo AS s
JOIN summary_drift_demo AS summary ON summary.member_id = s.author_id
WHERE s.status = 'PUBLISHED'
GROUP BY summary.member_id, summary.total_views;

source SUM(view_count): 90
cached total_views: 50
drift_views: -40
SQL constraint error: none

DROP TABLE IF EXISTS member_post_summary_demo;
DROP TABLE IF EXISTS summary_post_fixture;
DROP TABLE IF EXISTS summary_member_fixture;

CREATE TABLE summary_member_fixture (
  id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(80) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE summary_post_fixture (
  id BIGINT UNSIGNED NOT NULL,
  author_id BIGINT UNSIGNED NOT NULL,
  view_count BIGINT UNSIGNED NOT NULL,
  status VARCHAR(16) NOT NULL,
  published_at DATETIME(6) NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (author_id) REFERENCES summary_member_fixture (id)
);

INSERT INTO summary_member_fixture (id, name)
VALUES (1, '안동민'), (2, '김소라');

INSERT INTO summary_post_fixture
  (id, author_id, view_count, status, published_at)
VALUES
  (1, 1, 50, 'PUBLISHED', '2026-07-10 10:00:00'),
  (2, 1, 40, 'PUBLISHED', '2026-07-11 11:00:00'),
  (3, 2, 20, 'DRAFT', NULL);

CREATE TABLE member_post_summary_demo (
  member_id BIGINT UNSIGNED PRIMARY KEY,
  published_count INT UNSIGNED NOT NULL DEFAULT 0,
  total_views BIGINT UNSIGNED NOT NULL DEFAULT 0,
  source_cutoff_at DATETIME(6) NOT NULL,
  rebuilt_at DATETIME(6) NOT NULL,
  CONSTRAINT fk_summary_demo_member FOREIGN KEY (member_id)
    REFERENCES summary_member_fixture (id)
    ON DELETE CASCADE
);

-- 재구축 전 잘못 남은 캐시를 의도적으로 넣는다.
INSERT INTO member_post_summary_demo
  (member_id, published_count, total_views, source_cutoff_at, rebuilt_at)
VALUES
  (1, 1, 50, '2026-07-15 00:00:00', '2026-07-14 00:00:00'),
  (2, 9, 999, '2026-07-15 00:00:00', '2026-07-14 00:00:00');

INSERT INTO member_post_summary_demo
  (member_id, published_count, total_views, source_cutoff_at, rebuilt_at)
SELECT
  m.id AS member_id,
  COUNT(p.id) AS published_count,
  COALESCE(SUM(p.view_count), 0) AS total_views,
  '2026-07-15 00:00:00.000000' AS source_cutoff_at,
  CURRENT_TIMESTAMP(6) AS rebuilt_at
FROM summary_member_fixture AS m
LEFT JOIN summary_post_fixture AS p
  ON p.author_id = m.id
 AND p.status = 'PUBLISHED'
 AND p.published_at < '2026-07-15 00:00:00.000000'
GROUP BY m.id
ON DUPLICATE KEY UPDATE
  published_count = VALUES(published_count),
  total_views = VALUES(total_views),
  source_cutoff_at = VALUES(source_cutoff_at),
  rebuilt_at = VALUES(rebuilt_at);

SELECT member_id, published_count, total_views
FROM member_post_summary_demo
ORDER BY member_id;

source published_count: 2
summary published_count: 2
source total_views: 90
summary total_views: 90
member 2 source/summary: 0 / 0
drift rows after rebuild: 0
same source + cutoff rerun: aggregate values unchanged; rebuilt_at refreshed

WITH source_total AS (
  SELECT m.id AS member_id,
         COUNT(p.id) AS published_count,
         COALESCE(SUM(p.view_count), 0) AS total_views
  FROM summary_member_fixture AS m
  LEFT JOIN summary_post_fixture AS p
    ON p.author_id = m.id
   AND p.status = 'PUBLISHED'
   AND p.published_at < '2026-07-15 00:00:00'
  GROUP BY m.id
)
SELECT src.member_id,
       src.total_views AS source_views,
       COALESCE(sumry.total_views, 0) AS cached_views
FROM source_total AS src
LEFT JOIN member_post_summary_demo AS sumry
  ON sumry.member_id = src.member_id
WHERE sumry.member_id IS NULL
   OR src.published_count <> COALESCE(sumry.published_count, 0)
   OR src.total_views <> COALESCE(sumry.total_views, 0);