hadoop学习-基于Hive的教育平台数据仓库分析案例（二）意向用户模块sql文件

/* Navicat MySQL Data Transfer Source Server : hadoop01 Source Server Version : 50730 Source Host : hadoop01:3306 Source Database : scrm Target Server Type : MYSQL Target Server Version : 50730 File Encoding : 65001 Date: 2020-09-04 21:52:02 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for `customer_clue` -- ---------------------------- DROP TABLE IF EXISTS `customer_clue`; CREATE TABLE `customer_clue` ( `id` int(11) NOT NULL AUTO_INCREMENT, `create_date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_date_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间', `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否被删除（禁用）', `customer_id` int(11) DEFAULT NULL COMMENT '客户id', `customer_relationship_id` int(11) DEFAULT NULL COMMENT '客户关系id', `session_id` varchar(48) COLLATE utf8_bin DEFAULT '' COMMENT '七陌会话id', `sid` varchar(48) COLLATE utf8_bin DEFAULT '' COMMENT '访客id', `status` varchar(16) COLLATE utf8_bin DEFAULT '' COMMENT '状态（undeal待领取 deal 已领取 finish 已关闭 changePeer 已流转）', `user` varchar(16) COLLATE utf8_bin DEFAULT '' COMMENT '所属坐席', `create_time` datetime DEFAULT NULL COMMENT '七陌创建时间', `platform` varchar(16) COLLATE utf8_bin DEFAULT '' COMMENT '平台来源 （pc-网站咨询|wap-wap咨询|sdk-app咨询|weixin-微信咨询）', `s_name` varchar(32) COLLATE utf8_bin DEFAULT '' COMMENT '用户名称', `seo_source` varchar(255) COLLATE utf8_bin DEFAULT '' COMMENT '搜索来源', `seo_keywords` varchar(255) COLLATE utf8_bin DEFAULT '' COMMENT '关键字', `ip` varchar(48) COLLATE utf8_bin DEFAULT '' COMMENT 'IP地址', `referrer` text COLLATE utf8_bin COMMENT '上级来源页面', `from_url` text COLLATE utf8_bin COMMENT '会话来源页面', `landing_page_url` text COLLATE utf8_bin COMMENT '访客着陆页面', `url_title` varchar(1024) COLLATE utf8_bin DEFAULT '' COMMENT '咨询页面title', `to_peer` varchar(255) COLLATE utf8_bin DEFAULT '' COMMENT '所属技能组', `manual_time` datetime DEFAULT NULL COMMENT '人工开始时间', `begin_time` datetime DEFAULT NULL COMMENT '坐席领取时间 ', `reply_msg_count` int(11) DEFAULT '0' COMMENT '客服回复消息数', `total_msg_count` int(11) DEFAULT '0' COMMENT '消息总数', `msg_count` int(11) DEFAULT '0' COMMENT '客户发送消息数', `comment` varchar(1024) COLLATE utf8_bin DEFAULT '' COMMENT '备注', `finish_reason` varchar(255) COLLATE utf8_bin DEFAULT '' COMMENT '结束类型', `finish_user` varchar(32) COLLATE utf8_bin DEFAULT '' COMMENT '结束坐席', `end_time` datetime DEFAULT NULL COMMENT '会话结束时间', `platform_description` varchar(255) COLLATE utf8_bin DEFAULT '' COMMENT '客户平台信息', `browser_name` varchar(255) COLLATE utf8_bin DEFAULT '' COMMENT '浏览器名称', `os_info` varchar(255) COLLATE utf8_bin DEFAULT '' COMMENT '系统名称', `area` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '区域', `country` varchar(16) COLLATE utf8_bin DEFAULT '' COMMENT '所在国家', `province` varchar(16) COLLATE utf8_bin DEFAULT '' COMMENT '省', `city` varchar(255) COLLATE utf8_bin DEFAULT '' COMMENT '城市', `creator` int(11) DEFAULT '0' COMMENT '创建人', `name` varchar(64) COLLATE utf8_bin DEFAULT '' COMMENT '客户姓名', `idcard` varchar(24) COLLATE utf8_bin DEFAULT '' COMMENT '身份证号', `phone` varchar(24) COLLATE utf8_bin DEFAULT '' COMMENT '手机号', `itcast_school_id` int(11) DEFAULT NULL COMMENT '校区Id', `itcast_school` varchar(128) COLLATE utf8_bin DEFAULT '' COMMENT '校区', `itcast_subject_id` int(11) DEFAULT NULL COMMENT '学科Id', `itcast_subject` varchar(128) COLLATE utf8_bin DEFAULT '' COMMENT '学科', `wechat` varchar(32) COLLATE utf8_bin DEFAULT '' COMMENT '微信', `qq` varchar(32) COLLATE utf8_bin DEFAULT '' COMMENT 'qq号', `email` varchar(56) COLLATE utf8_bin DEFAULT '' COMMENT '邮箱', `gender` varchar(8) COLLATE utf8_bin DEFAULT 'MAN' COMMENT '性别', `level` varchar(8) COLLATE utf8_bin DEFAULT NULL COMMENT '客户级别', `origin_type` varchar(32) COLLATE utf8_bin DEFAULT '' COMMENT '数据来源渠道', `information_way` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '资讯方式', `working_years` date DEFAULT NULL COMMENT '开始工作时间', `technical_directions` varchar(255) COLLATE utf8_bin DEFAULT '' COMMENT '技术方向', `customer_state` varchar(32) COLLATE utf8_bin DEFAULT '' COMMENT '当前客户状态', `valid` bit(1) DEFAULT b'0' COMMENT '该线索是否是网资有效线索', `anticipat_signup_date` date DEFAULT NULL COMMENT '预计报名时间', `clue_state` varchar(32) COLLATE utf8_bin DEFAULT 'NOT_SUBMIT' COMMENT '线索状态', `scrm_department_id` int(11) DEFAULT NULL COMMENT 'SCRM内部部门id', `superior_url` text COLLATE utf8_bin COMMENT '诸葛获取上级页面URL', `superior_source` varchar(1024) COLLATE utf8_bin DEFAULT NULL COMMENT '诸葛获取上级页面URL标题', `landing_url` text COLLATE utf8_bin COMMENT '诸葛获取着陆页面URL', `landing_source` varchar(1024) COLLATE utf8_bin DEFAULT NULL COMMENT '诸葛获取着陆页面URL来源', `info_url` text COLLATE utf8_bin COMMENT '诸葛获取留咨页URL', `info_source` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '诸葛获取留咨页URL标题', `origin_channel` varchar(32) COLLATE utf8_bin DEFAULT '' COMMENT '投放渠道', `course_id` int(32) DEFAULT NULL, `course_name` varchar(255) COLLATE utf8_bin DEFAULT NULL, `zhuge_session_id` varchar(500) COLLATE utf8_bin DEFAULT NULL, `is_repeat` int(4) NOT NULL DEFAULT '0' COMMENT '是否重复线索(手机号维度) 0:正常 1：重复', `tenant` int(11) NOT NULL DEFAULT '0' COMMENT '租户id', `activity_id` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '活动id', `activity_name` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '活动名称', `follow_type` int(4) DEFAULT '0' COMMENT '分配类型，0-自动分配，1-手动分配，2-自动转移，3-手动单个转移，4-手动批量转移，5-公海领取', `shunt_mode_id` int(11) DEFAULT NULL COMMENT '匹配到的技能组id', `shunt_employee_group_id` int(11) DEFAULT NULL COMMENT '所属分流员工组', PRIMARY KEY (`id`), KEY `customer_id` (`customer_id`), KEY `customer_relationship_id` (`customer_relationship_id`), KEY `idcard` (`idcard`), KEY `index_creator` (`creator`), KEY `index_date_time` (`create_date_time`), KEY `phone` (`phone`), KEY `session_id` (`session_id`), CONSTRAINT `customer_clue_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`), CONSTRAINT `customer_clue_ibfk_2` FOREIGN KEY (`customer_relationship_id`) REFERENCES `customer_relationship` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- ---------------------------- -- Records of customer_clue -- ---------------------------- INSERT INTO `customer_clue` VALUES ('1', '2019-11-05 00:24:42', '2020-01-03 18:32:43', '', '2', '2', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, '451', '**', '', '186****0002', '1', '北京', null, null, null, '271****75', '', 'WOMEN', 'A', 'NETSERVICE', null, null, null, null, '', null, 'INVALID_PUBLIC_OLD_CLUE', '53', null, null, null, null, null, null, '', null, null, null, '0', '1', null, null, '0', null, null); INSERT INTO `customer_clue` VALUES ('2', '2019-11-05 00:24:42', '2020-01-03 18:32:43', '', '4', '4', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, n