일별 통계 재계산

DROP TABLE IF EXISTS daily_post_stats_bad;
CREATE TABLE daily_post_stats_bad (
  author_id BIGINT UNSIGNED NOT NULL,
  stat_date DATE NOT NULL,
  published_count INT UNSIGNED NOT NULL,
  total_views BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (author_id, stat_date)
) ENGINE=InnoDB;

INSERT INTO daily_post_stats_bad VALUES (1, '2026-07-15', 1, 60) AS new
ON DUPLICATE KEY UPDATE
  published_count = published_count + new.published_count,
  total_views = total_views + new.total_views;

-- 응답 손실 뒤 같은 배치를 재시도한다.
INSERT INTO daily_post_stats_bad VALUES (1, '2026-07-15', 1, 60) AS new
ON DUPLICATE KEY UPDATE
  published_count = published_count + new.published_count,
  total_views = total_views + new.total_views;

SELECT * FROM daily_post_stats_bad;

author_id | stat_date  | published_count | total_views
1         | 2026-07-15 | 2               | 120

DROP PROCEDURE IF EXISTS rebuild_daily_post_stats;
DROP TABLE IF EXISTS daily_post_stats;
CREATE TABLE daily_post_stats (
  author_id BIGINT UNSIGNED NOT NULL,
  stat_date DATE NOT NULL,
  published_count INT UNSIGNED NOT NULL,
  total_views BIGINT UNSIGNED NOT NULL,
  source_cutoff_at DATETIME(6) NOT NULL,
  rebuild_run_id BINARY(16) NOT NULL,
  rebuilt_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (author_id, stat_date),
  CONSTRAINT fk_daily_stat_member FOREIGN KEY (author_id)
    REFERENCES members (id) ON DELETE RESTRICT,
  CONSTRAINT chk_daily_stat_values CHECK (published_count > 0),
  INDEX ix_daily_stat_date (stat_date, author_id),
  INDEX ix_daily_stat_cutoff (source_cutoff_at)
) ENGINE=InnoDB;

DELIMITER //
CREATE PROCEDURE rebuild_daily_post_stats(
  IN p_from DATE,
  IN p_to DATE,
  IN p_cutoff DATETIME(6),
  IN p_run BINARY(16)
)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    DROP TEMPORARY TABLE IF EXISTS next_daily_stat;
    RESIGNAL;
  END;

  IF p_from IS NULL OR p_to IS NULL OR p_cutoff IS NULL OR p_run IS NULL
     OR p_to <= p_from OR p_cutoff < CAST(p_to AS DATETIME) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'invalid rebuild boundary';
  END IF;

  DROP TEMPORARY TABLE IF EXISTS next_daily_stat;
  CREATE TEMPORARY TABLE next_daily_stat (
    author_id BIGINT UNSIGNED NOT NULL,
    stat_date DATE NOT NULL,
    published_count INT UNSIGNED NOT NULL,
    total_views BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (author_id, stat_date)
  ) ENGINE=InnoDB;

  INSERT INTO next_daily_stat
  SELECT author_id, DATE(published_at), COUNT(*), SUM(view_count)
  FROM posts
  WHERE status = 'PUBLISHED'
    AND published_at >= p_from AND published_at < p_to
    AND published_at < p_cutoff
  GROUP BY author_id, DATE(published_at);

  START TRANSACTION;
  DELETE FROM daily_post_stats
  WHERE stat_date >= p_from AND stat_date < p_to;
  INSERT INTO daily_post_stats
    (author_id, stat_date, published_count, total_views,
     source_cutoff_at, rebuild_run_id)
  SELECT author_id, stat_date, published_count, total_views, p_cutoff, p_run
  FROM next_daily_stat;
  COMMIT;
  DROP TEMPORARY TABLE next_daily_stat;
END//
DELIMITER ;

SET @post_id = (
  SELECT id FROM posts
  WHERE status = 'PUBLISHED'
  ORDER BY id LIMIT 1
);
SET @author_id = (SELECT author_id FROM posts WHERE id = @post_id);
UPDATE posts
SET view_count = 95,
    created_at = '2026-07-15 08:00:00',
    published_at = '2026-07-15 09:00:00'
WHERE id = @post_id;

SET @run_a = UUID_TO_BIN('88888888-8888-4888-8888-888888888888');
CALL rebuild_daily_post_stats('2026-07-15', '2026-07-16', '2026-07-16 01:00:00', @run_a);
SET @checksum_a = (
  SELECT COALESCE(SUM(published_count * 1000003 + total_views), 0)
  FROM daily_post_stats WHERE stat_date = '2026-07-15'
);
CALL rebuild_daily_post_stats('2026-07-15', '2026-07-16', '2026-07-16 01:00:00', @run_a);
SET @checksum_b = (
  SELECT COALESCE(SUM(published_count * 1000003 + total_views), 0)
  FROM daily_post_stats WHERE stat_date = '2026-07-15'
);

SELECT author_id, stat_date, published_count, total_views, source_cutoff_at
FROM daily_post_stats
ORDER BY author_id, stat_date;

SELECT @checksum_a AS first_checksum, @checksum_b AS second_checksum;

author_id | stat_date  | published_count | total_views | source_cutoff_at
1         | 2026-07-15 | 1               | 95          | 2026-07-16 01:00:00.000000

first_checksum | second_checksum
1000098        | 1000098

-- 월요일을 한 주의 시작으로 사용한다.
SELECT
  author_id,
  DATE_SUB(stat_date, INTERVAL WEEKDAY(stat_date) DAY) AS week_start,
  SUM(published_count) AS published_count,
  SUM(total_views) AS total_views
FROM daily_post_stats
GROUP BY author_id,
         DATE_SUB(stat_date, INTERVAL WEEKDAY(stat_date) DAY)
ORDER BY author_id, week_start;

SELECT
  author_id,
  DATE_SUB(stat_date, INTERVAL DAYOFMONTH(stat_date) - 1 DAY) AS month_start,
  SUM(published_count) AS published_count,
  SUM(total_views) AS total_views
FROM daily_post_stats
GROUP BY author_id,
         DATE_SUB(stat_date, INTERVAL DAYOFMONTH(stat_date) - 1 DAY)
ORDER BY author_id, month_start;

SELECT
  author_id,
  MAKEDATE(YEAR(stat_date), 1) AS year_start,
  SUM(published_count) AS published_count,
  SUM(total_views) AS total_views
FROM daily_post_stats
GROUP BY author_id, MAKEDATE(YEAR(stat_date), 1)
ORDER BY author_id, year_start;

author_id | week_start | published_count | total_views
1         | 2026-07-13 | 1               | 95

author_id | month_start | published_count | total_views
1         | 2026-07-01  | 1               | 95

author_id | year_start | published_count | total_views
1         | 2026-01-01 | 1               | 95

SET @report_from = DATE('2026-07-01');
SET @today = DATE('2026-07-15');
SET @live_cutoff = TIMESTAMP('2026-07-15 14:00:00');

WITH historical AS (
  SELECT author_id,
         SUM(published_count) AS published_count,
         SUM(total_views) AS total_views
  FROM daily_post_stats
  WHERE stat_date >= @report_from
    AND stat_date < @today
  GROUP BY author_id
), today_live AS (
  SELECT author_id,
         COUNT(*) AS published_count,
         SUM(view_count) AS total_views
  FROM posts
  WHERE status = 'PUBLISHED'
    AND published_at >= @today
    AND published_at < DATE_ADD(@today, INTERVAL 1 DAY)
    AND published_at < @live_cutoff
  GROUP BY author_id
), combined AS (
  SELECT * FROM historical
  UNION ALL
  SELECT * FROM today_live
)
SELECT author_id,
       SUM(published_count) AS published_count,
       SUM(total_views) AS total_views
FROM combined
GROUP BY author_id
ORDER BY author_id;

author_id | published_count | total_views
1         | 1               | 95

SET @run_b = UUID_TO_BIN('99999999-9999-4999-8999-999999999999');

START TRANSACTION;

INSERT INTO daily_post_stats (
  author_id, stat_date, published_count, total_views,
  source_cutoff_at, rebuild_run_id
)
VALUES (
  1, '2026-07-15', 3, 180,
  '2026-07-16 01:00:00', @run_b
) AS incoming
ON DUPLICATE KEY UPDATE
  published_count = incoming.published_count,
  total_views = incoming.total_views,
  source_cutoff_at = incoming.source_cutoff_at,
  rebuild_run_id = incoming.rebuild_run_id,
  rebuilt_at = CURRENT_TIMESTAMP(6);

INSERT INTO daily_post_stats (
  author_id, stat_date, published_count, total_views,
  source_cutoff_at, rebuild_run_id
)
VALUES (
  1, '2026-07-15', 3, 180,
  '2026-07-16 01:00:00', @run_b
) AS incoming
ON DUPLICATE KEY UPDATE
  published_count = incoming.published_count,
  total_views = incoming.total_views,
  source_cutoff_at = incoming.source_cutoff_at,
  rebuild_run_id = incoming.rebuild_run_id,
  rebuilt_at = CURRENT_TIMESTAMP(6);

SELECT published_count, total_views
FROM daily_post_stats
WHERE author_id = 1 AND stat_date = '2026-07-15';

ROLLBACK;

published_count | total_views
3               | 180

SELECT @checksum_a = @checksum_b AS retry_same_result;

WITH source_value AS (
  SELECT author_id, DATE(published_at) AS stat_date,
         COUNT(*) AS published_count, SUM(view_count) AS total_views
  FROM posts
  WHERE status = 'PUBLISHED'
    AND published_at >= '2026-07-15' AND published_at < '2026-07-16'
    AND published_at < '2026-07-16 01:00:00'
  GROUP BY author_id, DATE(published_at)
), differences AS (
  SELECT s.author_id, s.stat_date
  FROM source_value AS s
  LEFT JOIN daily_post_stats AS d
    ON d.author_id = s.author_id AND d.stat_date = s.stat_date
  WHERE d.author_id IS NULL
     OR d.published_count <> s.published_count
     OR d.total_views <> s.total_views
  UNION ALL
  SELECT d.author_id, d.stat_date
  FROM daily_post_stats AS d
  LEFT JOIN source_value AS s
    ON s.author_id = d.author_id AND s.stat_date = d.stat_date
  WHERE d.stat_date = '2026-07-15' AND s.author_id IS NULL
)
SELECT * FROM differences;

SELECT author_id, stat_date FROM daily_post_stats
GROUP BY author_id, stat_date HAVING COUNT(*) > 1;

retry_same_result
1