일별 통계 재계산

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)
ON DUPLICATE KEY UPDATE
 published_count=published_count+VALUES(published_count),
 total_views=total_views+VALUES(total_views);

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

source published posts=1
source views=60
target published_count=2
target total_views=120
retry count=2

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_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,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');

first checksum=1000098
second checksum=1000098
checksum equality=true
source-target mismatch rows=0
additive drift=0

-- 월요일을 한 주의 시작으로 사용한다.
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, YEAR(stat_date)
ORDER BY author_id, year_start;

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;

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

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 @checksum_a=@checksum_b AS retry_same_result;

WITH source_value AS(
 SELECT author_id,DATE(published_at) stat_date,COUNT(*) published_count,SUM(view_count) 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 s LEFT JOIN daily_post_stats 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 d LEFT JOIN source_value 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;