1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
-- --------------------------------------------------------
-- 主机: etl.cma5jkozme68.rds.cn-north-1.amazonaws.com.cn
-- 服务器版本: 5.6.27-log - MySQL Community Server (GPL)
-- 服务器操作系统: Linux
-- HeidiSQL 版本: 9.3.0.4984
-- --------------------------------------------------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
-- 导出 tkdm 的数据库结构
CREATE DATABASE IF NOT EXISTS `tkdm` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `tkdm`;
-- 导出 表 tkdm.tkdm_output_active_app_std_week 结构
CREATE TABLE IF NOT EXISTS `tkdm_output_active_app_std_week` (
`appid` varchar(32) DEFAULT NULL COMMENT 'appkey',
`week_dt` date DEFAULT NULL COMMENT '自然周起始第一天',
`category_id` int(11) DEFAULT NULL COMMENT '分类ID',
`isgame` int(11) DEFAULT NULL COMMENT '是否是游戏',
`avg_ins_rate` double DEFAULT NULL COMMENT '激活率均值,计算同category_id下的app的每日的激活率的均值',
`approx_med_ins_rate` double DEFAULT NULL COMMENT '激活率中位数,近似算法',
`bootstarp_med_ins_rate` double DEFAULT NULL COMMENT '激活率中位数 BootStrap 算法',
`ds` date DEFAULT NULL COMMENT '更新日期',
KEY `IDX_tkdm_appid` (`appid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='行业激活率水准--APP';
-- 数据导出被取消选择。
-- 导出 表 tkdm.tkdm_output_active_cid_std_week 结构
CREATE TABLE IF NOT EXISTS `tkdm_output_active_cid_std_week` (
`appid` varchar(32) DEFAULT NULL COMMENT 'appkey',
`week_dt` date DEFAULT NULL COMMENT '自然周起始第一天',
`cid` int(11) DEFAULT NULL COMMENT 'CID',
`category_id` int(11) DEFAULT NULL COMMENT '分类ID',
`isgame` int(11) DEFAULT NULL COMMENT '是否是游戏',
`avg_ins_rate` double DEFAULT NULL COMMENT '激活率均值,计算同category_id下的app的每日的激活率的均值',
`approx_med_ins_rate` double DEFAULT NULL COMMENT '激活率中位数,近似算法',
`bootstarp_med_ins_rate` double DEFAULT NULL COMMENT '激活率中位数 BootStrap 算法',
`ds` date DEFAULT NULL COMMENT '更新日期',
KEY `IDX_tkdm_appid` (`appid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='行业激活率水准--CID';
-- 数据导出被取消选择。
-- 导出 表 tkdm.tkdm_output_payment_explore_day 结构
CREATE TABLE IF NOT EXISTS `tkdm_output_payment_explore_day` (
`dt` date DEFAULT NULL COMMENT '日期',
`appid` varchar(32) DEFAULT NULL COMMENT 'appkey',
`num_ins` int(11) DEFAULT NULL COMMENT '激活数',
`num_pay` int(11) DEFAULT NULL COMMENT '已付费设备数',
`num_potential` int(11) DEFAULT NULL COMMENT '潜力设备数',
`num_low_rmb` int(11) DEFAULT NULL COMMENT '小R',
`num_medium_rmb` int(11) DEFAULT NULL COMMENT '中R',
`num_high_rmb` int(11) DEFAULT NULL COMMENT '大R',
`num_level_1` int(11) DEFAULT NULL COMMENT '6元以下',
`num_level_2` int(11) DEFAULT NULL COMMENT '198元以下 ',
`num_level_3` int(11) DEFAULT NULL COMMENT '198元以上 ',
`ds` date DEFAULT NULL,
KEY `IDX_tkdm_appid` (`appid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='决策支持--付费洞察'
/*!50100 PARTITION BY RANGE (to_days(dt))
(PARTITION pmin VALUES LESS THAN (736664) ENGINE = MyISAM,
PARTITION p201612 VALUES LESS THAN (736695) ENGINE = MyISAM,
PARTITION p201701 VALUES LESS THAN (736726) ENGINE = MyISAM,
PARTITION p201702 VALUES LESS THAN (736754) ENGINE = MyISAM,
PARTITION p201703 VALUES LESS THAN (736785) ENGINE = MyISAM,
PARTITION p201704 VALUES LESS THAN (736815) ENGINE = MyISAM,
PARTITION p201705 VALUES LESS THAN (736846) ENGINE = MyISAM,
PARTITION p201706 VALUES LESS THAN (736876) ENGINE = MyISAM,
PARTITION p201707 VALUES LESS THAN (736907) ENGINE = MyISAM,
PARTITION p201708 VALUES LESS THAN (736938) ENGINE = MyISAM,
PARTITION p201709 VALUES LESS THAN (736968) ENGINE = MyISAM,
PARTITION p201710 VALUES LESS THAN (736999) ENGINE = MyISAM,
PARTITION p201711 VALUES LESS THAN (737029) ENGINE = MyISAM,
PARTITION p201712 VALUES LESS THAN (737060) ENGINE = MyISAM,
PARTITION p201801 VALUES LESS THAN (737091) ENGINE = MyISAM,
PARTITION p201802 VALUES LESS THAN (737119) ENGINE = MyISAM,
PARTITION p201803 VALUES LESS THAN (737150) ENGINE = MyISAM,
PARTITION p201804 VALUES LESS THAN (737180) ENGINE = MyISAM,
PARTITION p201805 VALUES LESS THAN (737211) ENGINE = MyISAM,
PARTITION p201806 VALUES LESS THAN (737241) ENGINE = MyISAM,
PARTITION p201807 VALUES LESS THAN (737272) ENGINE = MyISAM,
PARTITION p201808 VALUES LESS THAN (737303) ENGINE = MyISAM,
PARTITION p201809 VALUES LESS THAN (737333) ENGINE = MyISAM,
PARTITION p201810 VALUES LESS THAN (737364) ENGINE = MyISAM,
PARTITION p201811 VALUES LESS THAN (737394) ENGINE = MyISAM,
PARTITION p201812 VALUES LESS THAN (737425) ENGINE = MyISAM,
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */;
-- 数据导出被取消选择。
-- 导出 表 tkdm.tkdm_output_reten_app_std_week 结构
CREATE TABLE IF NOT EXISTS `tkdm_output_reten_app_std_week` (
`appid` varchar(32) DEFAULT NULL COMMENT 'appkey',
`week_dt` date DEFAULT NULL COMMENT '自然周起始第一天',
`category_id` int(11) DEFAULT NULL COMMENT '分类ID',
`isgame` int(11) DEFAULT NULL COMMENT '是否是游戏',
`avg_d1_rate` double DEFAULT NULL COMMENT '次日留存率 均值,计算同category_id下的app的每日的次日留存率的均值',
`approx_med_d1_rate` double DEFAULT NULL COMMENT '次日留存率中位数,近似算法',
`bootstarp_med_d1_rate` double DEFAULT NULL COMMENT '次日留存率中位数 BootStrap 算法',
`ds` date DEFAULT NULL COMMENT '更新日期',
KEY `IDX_tkdm_appid` (`appid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='行业次日留存率--APP';
-- 数据导出被取消选择。
-- 导出 表 tkdm.tkdm_output_user_cluster_app_day 结构
CREATE TABLE IF NOT EXISTS `tkdm_output_user_cluster_app_day` (
`dt` date DEFAULT NULL COMMENT '日期',
`appid` varchar(32) DEFAULT NULL COMMENT 'appkey',
`category_id` int(11) DEFAULT NULL COMMENT '分类id',
`isgame` int(11) DEFAULT NULL COMMENT '是否属于游戏',
`num_user` int(11) DEFAULT NULL COMMENT '已经获取用户数',
`ds` date DEFAULT NULL,
KEY `IDX_tkdm_appid` (`appid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='决策支持--付费洞察'
/*!50100 PARTITION BY RANGE (to_days(dt))
(PARTITION pmin VALUES LESS THAN (736664) ENGINE = MyISAM,
PARTITION p201612 VALUES LESS THAN (736695) ENGINE = MyISAM,
PARTITION p201701 VALUES LESS THAN (736726) ENGINE = MyISAM,
PARTITION p201702 VALUES LESS THAN (736754) ENGINE = MyISAM,
PARTITION p201703 VALUES LESS THAN (736785) ENGINE = MyISAM,
PARTITION p201704 VALUES LESS THAN (736815) ENGINE = MyISAM,
PARTITION p201705 VALUES LESS THAN (736846) ENGINE = MyISAM,
PARTITION p201706 VALUES LESS THAN (736876) ENGINE = MyISAM,
PARTITION p201707 VALUES LESS THAN (736907) ENGINE = MyISAM,
PARTITION p201708 VALUES LESS THAN (736938) ENGINE = MyISAM,
PARTITION p201709 VALUES LESS THAN (736968) ENGINE = MyISAM,
PARTITION p201710 VALUES LESS THAN (736999) ENGINE = MyISAM,
PARTITION p201711 VALUES LESS THAN (737029) ENGINE = MyISAM,
PARTITION p201712 VALUES LESS THAN (737060) ENGINE = MyISAM,
PARTITION p201801 VALUES LESS THAN (737091) ENGINE = MyISAM,
PARTITION p201802 VALUES LESS THAN (737119) ENGINE = MyISAM,
PARTITION p201803 VALUES LESS THAN (737150) ENGINE = MyISAM,
PARTITION p201804 VALUES LESS THAN (737180) ENGINE = MyISAM,
PARTITION p201805 VALUES LESS THAN (737211) ENGINE = MyISAM,
PARTITION p201806 VALUES LESS THAN (737241) ENGINE = MyISAM,
PARTITION p201807 VALUES LESS THAN (737272) ENGINE = MyISAM,
PARTITION p201808 VALUES LESS THAN (737303) ENGINE = MyISAM,
PARTITION p201809 VALUES LESS THAN (737333) ENGINE = MyISAM,
PARTITION p201810 VALUES LESS THAN (737364) ENGINE = MyISAM,
PARTITION p201811 VALUES LESS THAN (737394) ENGINE = MyISAM,
PARTITION p201812 VALUES LESS THAN (737425) ENGINE = MyISAM,
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */;
-- 数据导出被取消选择。
-- 导出 表 tkdm.tkdm_output_user_cluster_top_all 结构
CREATE TABLE IF NOT EXISTS `tkdm_output_user_cluster_top_all` (
`dt` date DEFAULT NULL COMMENT '日期',
`cid` int(11) DEFAULT NULL COMMENT '渠道ID',
`category_id` int(11) DEFAULT NULL COMMENT '分类ID',
`isgame` int(11) DEFAULT NULL COMMENT '是否属于游戏',
`num_user` int(11) DEFAULT NULL COMMENT '累计用户数(近两个月)',
`top_rank` int(11) DEFAULT NULL COMMENT '排名'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='潜在用户群--Top榜'
/*!50100 PARTITION BY RANGE (to_days(dt))
(PARTITION pmin VALUES LESS THAN (736664) ENGINE = MyISAM,
PARTITION p201612 VALUES LESS THAN (736695) ENGINE = MyISAM,
PARTITION p201701 VALUES LESS THAN (736726) ENGINE = MyISAM,
PARTITION p201702 VALUES LESS THAN (736754) ENGINE = MyISAM,
PARTITION p201703 VALUES LESS THAN (736785) ENGINE = MyISAM,
PARTITION p201704 VALUES LESS THAN (736815) ENGINE = MyISAM,
PARTITION p201705 VALUES LESS THAN (736846) ENGINE = MyISAM,
PARTITION p201706 VALUES LESS THAN (736876) ENGINE = MyISAM,
PARTITION p201707 VALUES LESS THAN (736907) ENGINE = MyISAM,
PARTITION p201708 VALUES LESS THAN (736938) ENGINE = MyISAM,
PARTITION p201709 VALUES LESS THAN (736968) ENGINE = MyISAM,
PARTITION p201710 VALUES LESS THAN (736999) ENGINE = MyISAM,
PARTITION p201711 VALUES LESS THAN (737029) ENGINE = MyISAM,
PARTITION p201712 VALUES LESS THAN (737060) ENGINE = MyISAM,
PARTITION p201801 VALUES LESS THAN (737091) ENGINE = MyISAM,
PARTITION p201802 VALUES LESS THAN (737119) ENGINE = MyISAM,
PARTITION p201803 VALUES LESS THAN (737150) ENGINE = MyISAM,
PARTITION p201804 VALUES LESS THAN (737180) ENGINE = MyISAM,
PARTITION p201805 VALUES LESS THAN (737211) ENGINE = MyISAM,
PARTITION p201806 VALUES LESS THAN (737241) ENGINE = MyISAM,
PARTITION p201807 VALUES LESS THAN (737272) ENGINE = MyISAM,
PARTITION p201808 VALUES LESS THAN (737303) ENGINE = MyISAM,
PARTITION p201809 VALUES LESS THAN (737333) ENGINE = MyISAM,
PARTITION p201810 VALUES LESS THAN (737364) ENGINE = MyISAM,
PARTITION p201811 VALUES LESS THAN (737394) ENGINE = MyISAM,
PARTITION p201812 VALUES LESS THAN (737425) ENGINE = MyISAM,
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */;
-- 数据导出被取消选择。
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;