Tolan/dailyCount/sql/create_curr_daily.sql

/*
  CURR 按日统计表 curr_daily
  昨日活跃用户中今日仍活跃的占比，由 dailyCount 脚本每小时从 user_daily_activity 计算
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `curr_daily`;
CREATE TABLE `curr_daily` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `activity_date` date NOT NULL COMMENT '统计日期(当日)',
  `prev_day_active_count` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '昨日活跃用户数',
  `retained_count` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '昨日活跃中今日仍活跃人数',
  `curr_rate_pct` decimal(5,2) DEFAULT NULL COMMENT 'CURR百分比(retained/prev*100)，昨日无活跃为NULL',
  `total_active_today` int(10) unsigned DEFAULT NULL COMMENT '当日DAU(可选)',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_activity_date` (`activity_date`),
  KEY `idx_activity_date` (`activity_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='CURR按日统计表';

SET FOREIGN_KEY_CHECKS = 1;