Tolan/dailyCount/sql/create_user_daily_activity.sql

/*
  用户日活跃聚合表 user_daily_activity
  数据来源：user_events、messages、users，由 dailyCount 脚本每小时汇总
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `user_daily_activity`;
CREATE TABLE `user_daily_activity` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `activity_date` date NOT NULL COMMENT '统计日期(UTC日)',
  `user_id` varchar(64) NOT NULL COMMENT '用户ID',
  `active_flag` tinyint(1) NOT NULL DEFAULT '0' COMMENT '当天是否有≥1次活跃判定事件：1=是',
  `session_count` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '当天去重session数(基于活跃事件)',
  `message_count` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '当天用户消息条数(role=user)',
  `first_seen_date` date DEFAULT NULL COMMENT '用户首次出现日期(来自users.created_at，首次写入后不覆盖)',
  `user_segment_snapshot` varchar(32) DEFAULT NULL COMMENT 'users.user_segment快照',
  `org_id_snapshot` varchar(64) DEFAULT NULL COMMENT 'users.org_id快照',
  `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_user` (`activity_date`,`user_id`),
  KEY `idx_activity_date` (`activity_date`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_first_seen_date` (`first_seen_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户日活跃聚合表';

SET FOREIGN_KEY_CHECKS = 1;