hdb/sql/dataBase.sql

SET NAMES utf8mb4;
-- ----------------------------
-- Table structure for 用户认证
-- ----------------------------
DROP TABLE IF EXISTS `t_auth_user`;
CREATE TABLE `t_auth_user`  (
  `id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'id',
  `user_code` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '用户登录账号',
  `password` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '密码',
  `open_id` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'openid',
  `nike_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '昵称',
  `avatar_url` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '头像',
  `parent_user_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '父账号',
  `state` tinyint(1) NULL DEFAULT NULL COMMENT '0 无效，1有效',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '登录用户' ROW_FORMAT = Dynamic;
 -- 初始账号密码 admin/admin123!@#
INSERT INTO `db_operation`.`t_auth_user`(`id`, `user_code`, `password`, `open_id`, `nike_name`, `avatar_url`, `parent_user_id`, `state`, `create_time`) VALUES ('1305345329560653826', 'admin', '9b045c31ea6aff22f11de768a37fa0e5', NULL, NULL, 'http://pic.51yuansu.com/pic3/cover/01/69/80/595f67c2aff1e_610.jpg', NULL, 1, '2020-09-14 11:19:13');

-- ----------------------------
-- Table structure for 公众号列表
-- ----------------------------
DROP TABLE IF EXISTS `t_account`;
CREATE TABLE `t_account`  (
  `id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '主键',
  `authorizer_app_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '授权方appid',
  `authorizer_access_token` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '授权方accessToken',
  `authorizer_refresh_token` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '授权方刷新token',
  `expires_in` int(0) NULL DEFAULT NULL COMMENT 'token过期时间',
  `nick_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '昵称',
  `head_img` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '头像',
  `service_type_info` json NULL COMMENT '公众号类型',
  `verify_type_info` json NULL COMMENT '公众号认证类型',
  `user_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '原始 ID',
  `principal_name` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '主体名称',
  `alias` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '公众号所设置的微信号，可能为空',
  `business_info` json NULL COMMENT '用以了解功能的开通状况（0代表未开通，1代表已开通）',
  `qrcode_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '二维码图片的 URL',
  `state` tinyint(0) NULL DEFAULT NULL COMMENT '状态',
  `create_time` datetime(0) NULL DEFAULT NULL,
  `create_user_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '创建人',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '公众号' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for 公众号粉丝
-- ----------------------------
DROP TABLE IF EXISTS `t_account_fans`;
CREATE TABLE `t_account_fans`  (
  `id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'id',
  `account_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '公众号id',
  `open_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'openid',
  `nick_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'nickname',
  `sex` tinyint(1) NULL DEFAULT NULL COMMENT '用户的性别，值为1时是男性，值为2时是女性，值为0时是未知',
  `city` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '城市',
  `province` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '省份',
  `country` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '国家',
  `head_img_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'headimgurl',
  `union_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `subscribe` tinyint(1) NULL DEFAULT NULL COMMENT '1是关注',
  `subscribe_time` bigint(0) NULL DEFAULT NULL COMMENT '关注时间',
  `subscribe_scene` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '关注的渠道来源',
  `subscribe_scene_label` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '关注的渠道来源zh',
  `tag_id_list` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '标签id集合tagid_list',
  `last_inactive_time` bigint(0) NULL DEFAULT NULL COMMENT '最后互动时间',
  `group_id` int(0) NULL DEFAULT NULL COMMENT '分组',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '备注',
  `update_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '公众号粉丝' ROW_FORMAT = Dynamic;



-- ----------------------------
-- Table structure for 公众号标签
-- ----------------------------
DROP TABLE IF EXISTS `t_account_tag`;
CREATE TABLE `t_account_tag`  (
  `id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'id',
  `wx_id` int(0) NULL DEFAULT NULL COMMENT '微信返回的id',
  `account_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '公众号id',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '标签名称',
  `fans_count` int(0) NULL DEFAULT NULL COMMENT '粉丝数量 微信字段count',
  `create_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '公众号标签' ROW_FORMAT = Dynamic;


-- ----------------------------
-- Table structure for 公众号粉丝统计
-- ----------------------------
DROP TABLE IF EXISTS `t_account_fans_stat`;
CREATE TABLE `t_account_fans_stat`  (
  `id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'id',
  `account_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '公众号id',
  `new_num` int(0) NULL DEFAULT 0 COMMENT '新增数量',
  `cancel_num` int(0) NULL DEFAULT 0 COMMENT '取关数量',
  `inactive_num` int(0) NULL DEFAULT 0 COMMENT '活跃数量',
  `total_fans_num` int(0) NULL DEFAULT 0 COMMENT '总粉丝数',
  `add_num` int(0) NULL DEFAULT 0 COMMENT '净增数量',
  `page_read_num` int(0) NULL DEFAULT 0 COMMENT '阅读数量',
  `new_rate` decimal(5, 2) NULL DEFAULT NULL COMMENT '新增比例',
  `cancel_rate` decimal(5, 2) NULL DEFAULT NULL COMMENT '取关比例',
  `inactive_rate` decimal(5, 2) NULL DEFAULT NULL COMMENT '活跃数量',
  `total_fans_rate` decimal(5, 2) NULL DEFAULT NULL COMMENT '总粉丝比例',
  `add_rate` decimal(5, 2) NULL DEFAULT NULL COMMENT '净增比例',
  `page_read_rate` decimal(5, 2) NULL DEFAULT NULL COMMENT '阅读比例',
  `stat_date` date NULL DEFAULT NULL COMMENT '统计日期',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建日期',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '公众号粉丝统计' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for 粉丝动作统计
-- ----------------------------
DROP TABLE IF EXISTS `t_fans_action_stat`;
CREATE TABLE `t_fans_action_stat`  (
  `id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'id',
  `account_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '公众号id',
  `opend_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'openid',
  `action` int(0) NULL DEFAULT NULL COMMENT '粉丝动作 0-粉丝消息,1-新关注,2-关注,3-取关,4-扫描二维码,5-菜单点击',
  `create_time` bigint(0) NULL DEFAULT NULL COMMENT '创建时间 秒级',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '粉丝动作统计' ROW_FORMAT = Dynamic;