public
/
bilibili-go-common
1
0
Fork 0
Code Issues Pull Requests Projects Releases Wiki Activity
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
1 Commit
1 Branch
0 Tags
34 MiB
 
 
 
Branch: master
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'master'
${ noResults }
bilibili-go-common/app/interface/main/mcn/dao/mcn_data.sql

254 lines
14 KiB
Raw Permalink Blame History

#投稿数及昨日增量
CREATE TABLE `dm_con_mcn_archive_d` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
`sign_id` bigint(20) NOT NULL COMMENT 'mcn签约ID',
`mcn_mid` bigint(20) NOT NULL COMMENT 'mcn的mid',
`log_date` date NOT NULL COMMENT '日期',
`up_all` int(11) NOT NULL DEFAULT '0' COMMENT '绑定up主数',
`archive_all` bigint(20) NOT NULL DEFAULT '0' COMMENT '总投稿数',
`archive_inc` bigint(20) NOT NULL DEFAULT '0' COMMENT '投稿数昨日增量',
`play_all` bigint(20) NOT NULL DEFAULT '0' COMMENT '总播放数',
`play_inc` bigint(20) NOT NULL DEFAULT '0' COMMENT '播放数昨日增量',
`danmu_all` bigint(20) NOT NULL DEFAULT '0' COMMENT '总弹幕数',
`danmu_inc` bigint(20) NOT NULL DEFAULT '0' COMMENT '弹幕数昨日增量',
`reply_all` bigint(20) NOT NULL DEFAULT '0' COMMENT '总评论数',
`reply_inc` bigint(20) NOT NULL DEFAULT '0' COMMENT '评论数昨日增量',
`share_all` bigint(20) NOT NULL DEFAULT '0' COMMENT '总分享数',
`share_inc` bigint(20) NOT NULL DEFAULT '0' COMMENT '分享数昨日增量',
`coin_all` bigint(20) NOT NULL DEFAULT '0' COMMENT '总硬币数',
`coin_inc` bigint(20) NOT NULL DEFAULT '0' COMMENT '硬币数昨日增量',
`fav_all` bigint(20) NOT NULL DEFAULT '0' COMMENT '总收藏数',
`fav_inc` bigint(20) NOT NULL DEFAULT '0' COMMENT '收藏数昨日增量',
`like_all` bigint(20) NOT NULL DEFAULT '0' COMMENT '总点赞数',
`like_inc` bigint(20) NOT NULL DEFAULT '0' COMMENT '点赞数昨日增量',
`fans_all` bigint(20) NOT NULL DEFAULT '0' COMMENT '总粉丝数',
`fans_inc` bigint(20) NOT NULL DEFAULT '0' COMMENT '昨日粉丝增量',
`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uni_idx` (`sign_id`,`log_date`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='mcn稿件汇总指标';
#播放/弹幕/评论/分享/硬币/收藏/点赞数每日增量
CREATE TABLE `dm_con_mcn_index_inc_d` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
`sign_id` bigint(20) NOT NULL COMMENT 'mcn签约ID',
`mcn_mid` bigint(20) NOT NULL COMMENT 'mcn的mid',
`log_date` date NOT NULL COMMENT '日期',
`value` bigint(20) NOT NULL DEFAULT '0' COMMENT '当日播放/弹幕/评论/分享/硬币/收藏/点赞数',
`type` varchar(20) NOT NULL COMMENT '分区类型，play、danmu、reply、share、coin、fav、like',
`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uni_idx` (`sign_id`,`log_date`,`type`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='播放/弹幕/评论/分享/硬币/收藏/点赞数每日增量';
#mcn播放/弹幕/评论/分享/硬币/收藏/点赞来源分区
CREATE TABLE `dm_con_mcn_index_source_d` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
`sign_id` bigint(20) NOT NULL COMMENT 'mcn签约ID',
`mcn_mid` bigint(20) NOT NULL COMMENT 'mcn的mid',
`log_date` date NOT NULL COMMENT '日期',
`type_id` int(11) NOT NULL COMMENT '一级分区ID',
`rank` int(11) NOT NULL COMMENT '排名',
`value` bigint(20) NOT NULL DEFAULT '0' COMMENT '一级分区',
`type` varchar(20) NOT NULL COMMENT '分区类型，play、danmu、reply、share、coin、fav、like',
`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uni_idx` (`sign_id`,`log_date`,`type_id`,`type`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='mcn播放/弹幕/评论/分享/硬币/收藏/点赞来源分区';
#mcn稿件播放来源占比
CREATE TABLE `dm_con_mcn_play_source_d` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
`sign_id` bigint(20) NOT NULL COMMENT 'mcn签约ID',
`mcn_mid` bigint(20) NOT NULL COMMENT 'mcn的mid',
`log_date` date NOT NULL COMMENT '日期',
`iphone` bigint(20) NOT NULL COMMENT 'iphone播放量',
`andriod` bigint(20) NOT NULL COMMENT 'andriod播放量',
`pc` bigint(20) NOT NULL COMMENT 'pc播放量',
`h5` bigint(20) NOT NULL COMMENT 'h5播放量',
`other` bigint(20) NOT NULL COMMENT 'other播放量',
`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uni_idx` (`sign_id`,`log_date`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='mcn稿件播放来源占比';
#游客/粉丝性别占比
CREATE TABLE `dm_con_mcn_fans_sex_w` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
`sign_id` bigint(20) NOT NULL COMMENT 'mcn签约ID',
`mcn_mid` bigint(20) NOT NULL COMMENT 'mcn的mid',
`log_date` date NOT NULL COMMENT '日期',
`male` bigint(20) NOT NULL COMMENT '男性人数',
`female` bigint(20) NOT NULL COMMENT '女性人数',
`type` varchar(20) NOT NULL COMMENT '粉丝类型，guest、fans',
`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uni_idx` (`sign_id`,`log_date`,`type`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='游客/粉丝性别占比';
#游客/粉丝年龄分布
CREATE TABLE `dm_con_mcn_fans_age_w` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
`sign_id` bigint(20) NOT NULL COMMENT 'mcn签约ID',
`mcn_mid` bigint(20) NOT NULL COMMENT 'mcn的mid',
`log_date` date NOT NULL COMMENT '日期',
`a` bigint(20) NOT NULL COMMENT '0-16岁人数',
`b` bigint(20) NOT NULL COMMENT '16-25岁人数',
`c` bigint(20) NOT NULL COMMENT '25-40岁人数',
`d` bigint(20) NOT NULL COMMENT '40岁以上人数',
`type` varchar(20) NOT NULL COMMENT '粉丝类型，guest、fans',
`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uni_idx` (`sign_id`,`log_date`,`type`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='游客/粉丝年龄分布';
#游客/粉丝观看途径
CREATE TABLE `dm_con_mcn_fans_play_way_w` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
`sign_id` bigint(20) NOT NULL COMMENT 'mcn签约ID',
`mcn_mid` bigint(20) NOT NULL COMMENT 'mcn的mid',
`log_date` date NOT NULL COMMENT '日期',
`app` bigint(20) NOT NULL COMMENT 'app观看人数',
`pc` bigint(20) NOT NULL COMMENT 'pc观看人数',
`outside` bigint(20) NOT NULL COMMENT '站外观看人数',
`other` bigint(20) NOT NULL COMMENT '其他观看人数',
`type` varchar(20) NOT NULL COMMENT '粉丝类型，guest、fans',
`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uni_idx` (`sign_id`,`log_date`,`type`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='游客/粉丝观看途径';
#游客/粉丝地区分布
CREATE TABLE `dm_con_mcn_fans_area_w` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
`sign_id` bigint(20) NOT NULL COMMENT 'mcn签约ID',
`mcn_mid` bigint(20) NOT NULL COMMENT 'mcn的mid',
`log_date` date NOT NULL COMMENT '日期',
`province` varchar(200) NOT NULL COMMENT '省份',
`user` bigint(20) NOT NULL COMMENT '人数',
`type` varchar(20) NOT NULL COMMENT '粉丝类型，guest、fans',
`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uni_idx` (`sign_id`,`log_date`,`province`,`type`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='游客/粉丝地区分布';
#游客/粉丝倾向分布
CREATE TABLE `dm_con_mcn_fans_type_w` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
`sign_id` bigint(20) NOT NULL COMMENT 'mcn签约ID',
`mcn_mid` bigint(20) NOT NULL COMMENT 'mcn的mid',
`log_date` date NOT NULL COMMENT '日期',
`type_id` int(11) NOT NULL COMMENT '二级分区ID',
`user` bigint(20) NOT NULL COMMENT '人数',
`type` varchar(20) NOT NULL COMMENT '粉丝类型，guest、fans',
`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uni_idx` (`sign_id`,`log_date`,`type_id`,`type`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='游客/粉丝倾向分布';
#游客/粉丝标签地图分布
CREATE TABLE `dm_con_mcn_fans_tag_w` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
`sign_id` bigint(20) NOT NULL COMMENT 'mcn签约ID',
`mcn_mid` bigint(20) NOT NULL COMMENT 'mcn的mid',
`log_date` date NOT NULL COMMENT '日期',
`tag_id` int(11) NOT NULL COMMENT '标签ID',
`user` bigint(20) NOT NULL COMMENT '人数',
`type` varchar(20) NOT NULL COMMENT '粉丝类型，guest、fans',
`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uni_idx` (`sign_id`,`log_date`,`tag_id`,`type`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='游客/粉丝标签地图分布';
#mcn粉丝数相关
CREATE TABLE `dm_con_mcn_fans_d` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
`sign_id` bigint(20) NOT NULL COMMENT 'mcn签约ID',
`mcn_mid` bigint(20) NOT NULL COMMENT 'mcn的mid',
`log_date` date NOT NULL COMMENT '日期',
`fans_all` bigint(20) NOT NULL COMMENT 'mcn总粉丝数',
`fans_inc` bigint(20) NOT NULL COMMENT 'mcn粉丝数昨日增量',
`act_fans` bigint(20) NOT NULL COMMENT 'mcn活跃粉丝数',
`fans_dec_all` bigint(20) NOT NULL COMMENT 'mcn取关粉丝总数',
`fans_dec` bigint(20) NOT NULL COMMENT 'mcn昨日取关粉丝数',
`view_fans_rate` float(3,2) NOT NULL COMMENT '观看活跃度',
`act_fans_rate` float(3,2) NOT NULL COMMENT '互动活跃度',
`reply_fans_rate` float(3,2) NOT NULL COMMENT '评论活跃度',
`danmu_fans_rate` float(3,2) NOT NULL COMMENT '弹幕活跃度',
`coin_fans_rate` float(3,2) NOT NULL COMMENT '投币活跃度',
`like_fans_rate` float(3,2) NOT NULL COMMENT '点赞活跃度',
`fav_fans_rate` float(3,2) NOT NULL COMMENT '收藏活跃度',
`share_fans_rate` float(3,2) NOT NULL COMMENT '分享活跃度',
`live_gift_fans_rate` float(3,2) NOT NULL COMMENT '直播礼物活跃度',
`live_danmu_fans_rate` float(3,2) NOT NULL COMMENT '直播弹幕活跃度',
`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uni_idx` (`sign_id`,`log_date`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='mcn粉丝数相关';
#mcn粉丝按天增量
CREATE TABLE `dm_con_mcn_fans_inc_d` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
`sign_id` bigint(20) NOT NULL COMMENT 'mcn签约ID',
`mcn_mid` bigint(20) NOT NULL COMMENT 'mcn的mid',
`log_date` date NOT NULL COMMENT '日期',
`fans_inc` bigint(20) NOT NULL COMMENT '当日新增粉丝数',
`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uni_idx` (`sign_id`,`log_date`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='mcn粉丝按天增量';
#mcn粉丝按天取关数
CREATE TABLE `dm_con_mcn_fans_dec_d` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
`sign_id` bigint(20) NOT NULL COMMENT 'mcn签约ID',
`mcn_mid` bigint(20) NOT NULL COMMENT 'mcn的mid',
`log_date` date NOT NULL COMMENT '日期',
`fans_dec` bigint(20) NOT NULL COMMENT '当日取关粉丝数',
`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uni_idx` (`sign_id`,`log_date`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='mcn粉丝按天取关数';
#mcn粉丝关注渠道
CREATE TABLE `dm_con_mcn_fans_attention_way_d` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
`sign_id` bigint(20) NOT NULL COMMENT 'mcn签约ID',
`mcn_mid` bigint(20) NOT NULL COMMENT 'mcn的mid',
`log_date` date NOT NULL COMMENT '日期',
`homepage` bigint(20) NOT NULL COMMENT '主站个人空间关注用户数',
`video` bigint(20) NOT NULL COMMENT '主站视频页关注用户数',
`article` bigint(20) NOT NULL COMMENT '专栏关注用户数',
`music` bigint(20) NOT NULL COMMENT '音频关注用户数',
`other` bigint(20) NOT NULL COMMENT '其他关注用户数',
`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uni_idx` (`sign_id`,`log_date`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='mcn粉丝关注渠道';