53 lines
4.9 KiB
SQL
53 lines
4.9 KiB
SQL
SET NAMES utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS `country_dashboard_game_period_metric` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
|
|
`period_type` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '周期类型:DAY/WEEK/MONTH/ALL',
|
|
`period_key` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '周期键',
|
|
`stat_timezone` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Asia/Riyadh' COMMENT '统计时区',
|
|
`period_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '周期名称',
|
|
`period_start_date` date DEFAULT NULL COMMENT '周期开始日期',
|
|
`period_end_date` date DEFAULT NULL COMMENT '周期结束日期,开区间',
|
|
`sys_origin` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '系统来源',
|
|
`country_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'UNKNOWN' COMMENT '国家码',
|
|
`country_name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'UNKNOWN' COMMENT '国家名称',
|
|
`game_provider` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'UNKNOWN' COMMENT '游戏厂商',
|
|
`game_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'UNKNOWN' COMMENT '游戏ID',
|
|
`game_name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'UNKNOWN' COMMENT '游戏名称',
|
|
`consume_amount` decimal(20,2) NOT NULL DEFAULT 0.00 COMMENT '用户消耗',
|
|
`payout_amount` decimal(20,2) NOT NULL DEFAULT 0.00 COMMENT '用户返奖',
|
|
`profit_amount` decimal(20,2) NOT NULL DEFAULT 0.00 COMMENT '平台收益',
|
|
`user_count` bigint NOT NULL DEFAULT 0 COMMENT '去重游戏用户数',
|
|
`order_count` bigint NOT NULL DEFAULT 0 COMMENT '流水笔数',
|
|
`refreshed_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最近刷新时间',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_game_period` (`sys_origin`, `stat_timezone`, `period_type`, `period_key`, `country_code`, `game_provider`, `game_id`),
|
|
KEY `idx_game_period_query` (`sys_origin`, `stat_timezone`, `period_type`, `period_key`, `country_code`),
|
|
KEY `idx_game_period_date` (`sys_origin`, `stat_timezone`, `period_type`, `period_start_date`, `period_end_date`),
|
|
KEY `idx_game_lookup` (`sys_origin`, `stat_timezone`, `game_provider`, `game_id`, `period_type`, `period_key`),
|
|
KEY `idx_game_period_sort` (`sys_origin`, `stat_timezone`, `period_type`, `period_key`, `consume_amount`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='国家数据大屏游戏维度周期预聚合';
|
|
|
|
CREATE TABLE IF NOT EXISTS `country_dashboard_game_period_user_metric` (
|
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
|
|
`period_type` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '周期类型:DAY/WEEK/MONTH/ALL',
|
|
`period_key` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '周期键',
|
|
`stat_timezone` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Asia/Riyadh' COMMENT '统计时区',
|
|
`period_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '周期名称',
|
|
`period_start_date` date DEFAULT NULL COMMENT '周期开始日期',
|
|
`period_end_date` date DEFAULT NULL COMMENT '周期结束日期,开区间',
|
|
`sys_origin` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '系统来源',
|
|
`country_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'UNKNOWN' COMMENT '国家码',
|
|
`country_name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'UNKNOWN' COMMENT '国家名称',
|
|
`game_provider` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'UNKNOWN' COMMENT '游戏厂商',
|
|
`game_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'UNKNOWN' COMMENT '游戏ID',
|
|
`user_id` bigint NOT NULL COMMENT '用户ID',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_game_period_user` (`period_type`, `period_key`, `stat_timezone`, `sys_origin`, `country_code`, `game_provider`, `game_id`, `user_id`),
|
|
KEY `idx_game_user_count` (`period_type`, `period_key`, `stat_timezone`, `sys_origin`, `game_provider`, `game_id`, `country_code`),
|
|
KEY `idx_game_user_lookup` (`sys_origin`, `stat_timezone`, `user_id`, `game_provider`, `game_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='国家数据大屏游戏维度周期用户去重明细';
|