tkdm_mysql_ddl-2017-03-07.sql 10.6 KB
-- --------------------------------------------------------
-- 主机:                           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 */;