data-sql/sdk/db_mp_log.sql

/*
SQLyog Ultimate v10.42 
MySQL - 5.7.36 : Database - db_mp_log
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!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' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`db_mp_log` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `db_mp_log`;

/*Table structure for table `log_day_hour_202111` */

DROP TABLE IF EXISTS `log_day_hour_202111`;

CREATE TABLE `log_day_hour_202111` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `date` date NOT NULL DEFAULT '2020-11-01' COMMENT '日期',
  `hour_key` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '时间KEY值 1~24',
  `mem_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '玩家id',
  `agent_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '归属渠道',
  `app_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '游戏ID',
  `reg_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '玩家注册时间',
  `reg_days` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '注册天数',
  `reg_hour_key` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '注册时间key 1~24',
  `game_reg_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '玩家注册时间',
  `game_reg_days` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '注册天数',
  `game_reg_hour_key` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '注册时间key 1~24',
  `login_cnt` int(11) unsigned NOT NULL DEFAULT '1' COMMENT '打开次数',
  `sum_money` decimal(12,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '累计充值',
  `sum_real_money` decimal(12,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '累计真实充值',
  `order_cnt` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '订单数量',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `dh_date_mem_app_device_unique` (`date`,`hour_key`,`mem_id`,`app_id`) USING BTREE,
  KEY `dh_mem_index` (`mem_id`) USING BTREE,
  KEY `dh_game_index` (`app_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='每时统计表';

/*Table structure for table `log_day_hour_switch_202111` */

DROP TABLE IF EXISTS `log_day_hour_switch_202111`;

CREATE TABLE `log_day_hour_switch_202111` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `date` date NOT NULL DEFAULT '2020-11-01' COMMENT '日期',
  `hour_key` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '时间KEY值 1~24',
  `mem_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '玩家id',
  `agent_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '归属渠道',
  `app_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '游戏ID',
  `reg_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '玩家注册时间',
  `reg_days` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '注册天数',
  `reg_hour_key` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '注册时间key 1~24',
  `game_reg_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '玩家注册时间',
  `game_reg_days` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '注册天数',
  `game_reg_hour_key` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '注册时间key 1~24',
  `login_cnt` int(11) unsigned NOT NULL DEFAULT '1' COMMENT '打开次数',
  `sum_money` decimal(12,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '累计充值',
  `sum_real_money` decimal(12,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '累计真实充值',
  `order_cnt` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '订单数量',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `dh_date_mem_app_device_unique` (`date`,`hour_key`,`mem_id`,`app_id`) USING BTREE,
  KEY `dh_mem_index` (`mem_id`) USING BTREE,
  KEY `dh_game_index` (`app_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='每时统计表';

/*Table structure for table `log_log_day_mem_202111` */

DROP TABLE IF EXISTS `log_log_day_mem_202111`;

CREATE TABLE `log_log_day_mem_202111` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL COMMENT '日期',
  `mem_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '玩家ID',
  `agent_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '归属渠道',
  `app_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '游戏ID',
  `reg_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '玩家注册时间',
  `reg_days` int(11) NOT NULL DEFAULT '0' COMMENT '注册天数',
  `role_days` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创角天数',
  `device_id` varchar(64) NOT NULL DEFAULT '' COMMENT '手机设备码',
  `device_cnt` int(11) NOT NULL DEFAULT '1' COMMENT '登陆的设备个数',
  `login_cnt` int(11) NOT NULL DEFAULT '1' COMMENT '登陆次数',
  `sum_money` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '累计充值',
  `sum_real_money` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '累计真实充值',
  `first_pay_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '第一笔充值时间',
  `last_pay_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '最近充值时间',
  `last_money` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '最近充值金额',
  `order_cnt` int(20) unsigned NOT NULL DEFAULT '0' COMMENT '订单数量',
  `order_suc_cnt` int(20) unsigned NOT NULL DEFAULT '0' COMMENT '支付成功订单数量',
  `last_login_ip` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '玩家最近登陆ip',
  `auth_cnt` int(11) NOT NULL DEFAULT '0' COMMENT '授权登陆次数',
  `is_cpa` tinyint(4) unsigned NOT NULL DEFAULT '1' COMMENT '是否计算cpa 1 否 2是',
  `is_auth` tinyint(4) unsigned NOT NULL DEFAULT '1' COMMENT '是否第三方注册 1 否 2是',
  `reg_app_id` int(11) NOT NULL DEFAULT '0' COMMENT '注册游戏ID',
  `is_new_app` tinyint(4) NOT NULL DEFAULT '1' COMMENT '是否游戏新增注册玩家 1 否 2是',
  `is_new_role` tinyint(4) NOT NULL DEFAULT '1' COMMENT '是否游戏新增角色 1 否 2是',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `ldm_date_mem_app_unique` (`date`,`mem_id`,`app_id`) USING BTREE,
  KEY `ldm_ip_index` (`last_login_ip`) USING BTREE,
  KEY `ldm_mem_index` (`mem_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='玩家每日';

/*Table structure for table `log_log_day_mem_switch_202111` */

DROP TABLE IF EXISTS `log_log_day_mem_switch_202111`;

CREATE TABLE `log_log_day_mem_switch_202111` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL COMMENT '日期',
  `mem_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '玩家ID',
  `agent_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '归属渠道',
  `app_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '游戏ID',
  `reg_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '玩家注册时间',
  `reg_days` int(11) NOT NULL DEFAULT '0' COMMENT '注册天数',
  `role_days` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创角天数',
  `device_id` varchar(64) NOT NULL DEFAULT '' COMMENT '手机设备码',
  `device_cnt` int(11) NOT NULL DEFAULT '1' COMMENT '登陆的设备个数',
  `login_cnt` int(11) NOT NULL DEFAULT '1' COMMENT '登陆次数',
  `sum_money` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '累计充值',
  `sum_real_money` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '累计真实充值',
  `first_pay_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '第一笔充值时间',
  `last_pay_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '最近充值时间',
  `last_money` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '最近充值金额',
  `order_cnt` int(20) unsigned NOT NULL DEFAULT '0' COMMENT '订单数量',
  `order_suc_cnt` int(20) unsigned NOT NULL DEFAULT '0' COMMENT '支付成功订单数量',
  `last_login_ip` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '玩家最近登陆ip',
  `auth_cnt` int(11) NOT NULL DEFAULT '0' COMMENT '授权登陆次数',
  `is_cpa` tinyint(4) unsigned NOT NULL DEFAULT '1' COMMENT '是否计算cpa 1 否 2是',
  `is_auth` tinyint(4) unsigned NOT NULL DEFAULT '1' COMMENT '是否第三方注册 1 否 2是',
  `reg_app_id` int(11) NOT NULL DEFAULT '0' COMMENT '注册游戏ID',
  `is_new_app` tinyint(4) NOT NULL DEFAULT '1' COMMENT '是否游戏新增注册玩家 1 否 2是',
  `is_new_role` tinyint(4) NOT NULL DEFAULT '1' COMMENT '是否游戏新增角色 1 否 2是',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `ldm_date_mem_app_unique` (`date`,`mem_id`,`app_id`) USING BTREE,
  KEY `ldm_ip_index` (`last_login_ip`) USING BTREE,
  KEY `ldm_mem_index` (`mem_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='玩家每日';

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;