1.透视表问题
透视表问题的一大特征就是行列间的转换,核心方法是:case when + group by
Q1:制作一个简单的透视表
创建数据表rowcol
DROP TABLE IF EXISTS `rowcol`;
CREATE TABLE `rowcol` (
`年` int DEFAULT NULL,
`月` int DEFAULT NULL,
`值` decimal(3,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `rowcol` VALUES ('2009', '1', '1.10');
INSERT INTO `rowcol` VALUES ('2009', '2', '1.20');
INSERT INTO `rowcol` VALUES ('2009', '3', '1.30');
INSERT INTO `rowcol` VALUES ('2009', '4', '1.40');
INSERT INTO `rowcol` VALUES ('2010', '1', '2.10');
INSERT INTO `rowcol` VALUES ('2010', '2', '2.20');
INSERT INTO `rowcol` VALUES ('2010', '3', '2.30');
数据表如左表所示,要求做出的透视表如右边所示:
思路:首先按照年份分组,再通过对月进行判断,返回特定的值
SELECT
年
,SUM(CASE WHEN 月=1 THEN 值 ELSE NULL END) 1月
,SUM(CASE WHEN 月=2 THEN 值 ELSE NULL END) 2月
,SUM(CASE WHEN 月=3 THEN 值 ELSE NULL END) 3月
,SUM(CASE WHEN 月=4 THEN 值 ELSE NULL END) 4月
FROM rowcol
GROUP BY 年;
Q2: 创建购买数量透视表
首先创建商品购买记录表数据表
DROP TABLE IF EXISTS purchase_quantity;
CREATE TABLE purchase_quantity(
`year` VARCHAR(8),
quarter VARCHAR(8),
amount INT(8)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;
INSERT INTO
purchase_quantity (`year`,quarter,amount)
VALUE ('2019','1',1200)
,('2019','2',1000)
,('2019','3',800)
,('2019','4',1300)
,('2020','1',1100)
,('2020','2',950)
,('2020','3',700)
,('2020','4',1050);
创建的数据表图示如下,包括年份,季度和数量三个字段。
要求:将数据表转化为以下样式,按照年份和各季度字段进行展示,字段q代表quater
实现代码:
SELECT
`year`
,max(CASE WHEN `quarter`=1 THEN amount ELSE NULL END) q1
,max(CASE WHEN `quarter`=2 THEN amount ELSE NULL END) q2
,max(CASE WHEN `quarter`=3 THEN amount ELSE NULL END) q3
,max(CASE WHEN `quarter`=4 THEN amount ELSE NULL END) q4
FROM purchase_quantity
GROUP BY `year`;
Q3:留存率问题
计算七日留存率、次月、二月等留存率时,会经常用透视表进行展示
创建数据表:由于数据太多,仅展示一部分(700条)
DROP TABLE IF EXISTS `cohort`;
CREATE TABLE `cohort` (
`nick_name` varchar(255) NOT NULL,
`pay_time` datetime DEFAULT NULL,
`order_status` varchar(50) DEFAULT NULL,
`pay_amount` varchar(50) DEFAULT NULL,
`purchase_quantity` varchar(50) DEFAULT NULL,
`province` varchar(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of cohort
-- ----------------------------
INSERT INTO `cohort` VALUES ('入倩出入深', '2019-09-01 00:10:04', '交易成功', '15.2', '1', '江苏省');
INSERT INTO `cohort` VALUES ('愛hya爱', '2019-09-01 00:14:52', '交易成功', '8.4', '1', '广东省');
INSERT INTO `cohort` VALUES ('象95象大', '2019-09-01 02:17:15', '交易成功', '8.4', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('卡哇伊氛十', '2019-09-01 03:37:28', '交易成功', '22', '1', '广西壮族自治区');
INSERT INTO `cohort` VALUES ('一只羊哈阿', '2019-09-01 08:53:50', '交易成功', '85', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('啊别睡别你', '2019-09-01 09:02:39', '交易成功', '22', '1', '安徽省');
INSERT INTO `cohort` VALUES ('henuj', '2019-09-01 09:30:04', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('516yh', '2019-09-01 09:41:45', '交易成功', '101.2', '1', '四川省');
INSERT INTO `cohort` VALUES ('ameag', '2019-09-01 09:49:05', '交易成功', '22', '1', '湖南省');
INSERT INTO `cohort` VALUES ('涵66释于', '2019-09-01 10:36:05', '交易成功', '15.2', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('lsaew', '2019-09-01 10:54:45', '交易成功', '8.4', '1', '湖北省');
INSERT INTO `cohort` VALUES ('6081a', '2019-09-01 11:02:14', '交易成功', '97.8', '1', '浙江省');
INSERT INTO `cohort` VALUES ('家电器阳元', '2019-09-01 11:09:51', '交易成功', '22', '1', '云南省');
INSERT INTO `cohort` VALUES ('271xl', '2019-09-01 11:19:00', '交易成功', '106.2', '2', '浙江省');
INSERT INTO `cohort` VALUES ('271xl', '2019-09-01 11:25:47', '交易成功', '85', '1', '浙江省');
INSERT INTO `cohort` VALUES ('zsl01', '2019-09-01 11:45:39', '交易成功', '8.4', '1', '湖北省');
INSERT INTO `cohort` VALUES ('823hz', '2019-09-01 13:07:08', '交易成功', '8.4', '1', '重庆');
INSERT INTO `cohort` VALUES ('0281h', '2019-09-01 13:07:09', '交易成功', '97.8', '1', '浙江省');
INSERT INTO `cohort` VALUES ('啦啦啦hz', '2019-09-01 13:44:43', '交易成功', '182.8', '2', '上海');
INSERT INTO `cohort` VALUES ('930岱杨', '2019-09-01 14:01:58', '交易成功', '109.6', '2', '广东省');
INSERT INTO `cohort` VALUES ('肥妞哦肥肥', '2019-09-01 14:17:48', '交易成功', '85', '1', '浙江省');
INSERT INTO `cohort` VALUES ('983aw', '2019-09-01 14:27:36', '交易成功', '8.4', '1', '浙江省');
INSERT INTO `cohort` VALUES ('414汉翟', '2019-09-01 16:29:14', '交易成功', '22', '1', '广东省');
INSERT INTO `cohort` VALUES ('609栋陈', '2019-09-01 17:03:54', '交易成功', '109.7', '1', '江苏省');
INSERT INTO `cohort` VALUES ('摸摸哒哥晨', '2019-09-01 18:05:31', '交易成功', '97.8', '1', '浙江省');
INSERT INTO `cohort` VALUES ('再而3而1', '2019-09-01 18:18:54', '交易成功', '101.2', '1', '江苏省');
INSERT INTO `cohort` VALUES ('laiix', '2019-09-01 18:52:45', '交易成功', '22', '1', '黑龙江省');
INSERT INTO `cohort` VALUES ('欣欣!雨唐', '2019-09-01 19:03:13', '交易成功', '106.3', '1', '四川省');
INSERT INTO `cohort` VALUES ('bao哲鲲', '2019-09-01 19:46:12', '交易成功', '22', '1', '上海');
INSERT INTO `cohort` VALUES ('记热情记忘', '2019-09-01 20:02:17', '交易成功', '22', '1', '浙江省');
INSERT INTO `cohort` VALUES ('燕00海禄', '2019-09-01 20:37:57', '交易成功', '89.3', '1', '贵州省');
INSERT INTO `cohort` VALUES ('这世界使天', '2019-09-01 20:40:06', '交易成功', '22', '1', '上海');
INSERT INTO `cohort` VALUES ('520ij', '2019-09-01 21:34:02', '交易成功', '92.7', '1', '上海');
INSERT INTO `cohort` VALUES ('啊啊啊鸣鸣', '2019-09-01 21:45:40', '交易成功', '101.2', '1', '上海');
INSERT INTO `cohort` VALUES ('eam敏郭', '2019-09-01 21:47:19', '交易成功', '8.4', '1', '上海');
INSERT INTO `cohort` VALUES ('是什么火木', '2019-09-01 22:10:42', '交易成功', '15.2', '1', '浙江省');
INSERT INTO `cohort` VALUES ('or琦琪魏', '2019-09-01 22:10:47', '交易成功', '97.8', '1', '安徽省');
INSERT INTO `cohort` VALUES ('王心3有没', '2019-09-01 22:29:16', '交易成功', '97.8', '1', '天津');
INSERT INTO `cohort` VALUES ('章鱼丸炸油', '2019-09-01 22:33:06', '交易成功', '88.4', '1', '江苏省');
INSERT INTO `cohort` VALUES ('殇66辰星', '2019-09-01 22:40:38', '交易成功', '85', '1', '福建省');
INSERT INTO `cohort` VALUES ('661tt', '2019-09-01 22:49:17', '交易成功', '8.4', '1', '广东省');
INSERT INTO `cohort` VALUES ('美丽啊姜是', '2019-09-01 23:25:11', '交易成功', '181.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('满星空满溢', '2019-09-01 23:56:51', '交易成功', '22', '1', '江苏省');
INSERT INTO `cohort` VALUES ('013bt', '2019-09-02 00:07:04', '交易成功', '106.2', '2', '广东省');
INSERT INTO `cohort` VALUES ('梁蹄猪蹄梁', '2019-09-02 01:46:07', '交易成功', '8.4', '1', '广西壮族自治区');
INSERT INTO `cohort` VALUES ('y微微l刘', '2019-09-02 08:15:14', '交易成功', '8.4', '1', '山东省');
INSERT INTO `cohort` VALUES ('象象丶象象', '2019-09-02 09:13:14', '交易成功', '85', '1', '北京');
INSERT INTO `cohort` VALUES ('620bt', '2019-09-02 09:16:47', '交易成功', '8.4', '1', '浙江省');
INSERT INTO `cohort` VALUES ('聂阿福阿聂', '2019-09-02 09:44:43', '交易成功', '39', '1', '北京');
INSERT INTO `cohort` VALUES ('333琳琳', '2019-09-02 10:06:48', '交易成功', '8.4', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('146bt', '2019-09-02 10:54:46', '交易成功', '22', '1', '浙江省');
INSERT INTO `cohort` VALUES ('laxow', '2019-09-02 11:13:39', '交易成功', '22', '1', '浙江省');
INSERT INTO `cohort` VALUES ('里的你时在', '2019-09-02 12:17:14', '交易成功', '88.4', '1', '福建省');
INSERT INTO `cohort` VALUES ('眠77未月', '2019-09-02 12:24:02', '交易成功', '39', '1', '上海');
INSERT INTO `cohort` VALUES ('018鹏常', '2019-09-02 12:27:57', '交易成功', '85', '1', '山东省');
INSERT INTO `cohort` VALUES ('121ad', '2019-09-02 12:28:28', '交易成功', '182.8', '2', '辽宁省');
INSERT INTO `cohort` VALUES ('836嘻1', '2019-09-02 12:44:37', '交易成功', '88.4', '1', '上海');
INSERT INTO `cohort` VALUES ('源ii溯江', '2019-09-02 13:00:46', '交易成功', '22', '1', '江西省');
INSERT INTO `cohort` VALUES ('的回忆生人', '2019-09-02 13:59:22', '交易成功', '8.4', '1', '北京');
INSERT INTO `cohort` VALUES ('_99bt', '2019-09-02 14:02:24', '交易成功', '181.9', '1', '广东省');
INSERT INTO `cohort` VALUES ('ayjas', '2019-09-02 15:29:39', '交易成功', '101.2', '1', '浙江省');
INSERT INTO `cohort` VALUES ('谢啊谢吗谢', '2019-09-02 16:56:28', '交易成功', '22', '1', '广东省');
INSERT INTO `cohort` VALUES ('亲笨笨盼靓', '2019-09-02 17:07:12', '交易成功', '22', '1', '广东省');
INSERT INTO `cohort` VALUES ('312zz', '2019-09-02 18:16:57', '交易成功', '88.4', '1', '湖南省');
INSERT INTO `cohort` VALUES ('n30am', '2019-09-02 18:42:39', '交易成功', '97.8', '1', '广东省');
INSERT INTO `cohort` VALUES ('阿姨1阿艾', '2019-09-02 18:48:00', '交易成功', '181.9', '1', '江苏省');
INSERT INTO `cohort` VALUES ('ou1是我', '2019-09-02 19:31:46', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('011bt', '2019-09-02 19:35:13', '交易成功', '581.4', '1', '湖北省');
INSERT INTO `cohort` VALUES ('181ux', '2019-09-02 19:44:36', '交易成功', '96.9', '1', '江苏省');
INSERT INTO `cohort` VALUES ('100九小', '2019-09-02 19:49:04', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('400然浩', '2019-09-02 19:59:09', '交易成功', '15.2', '1', '江苏省');
INSERT INTO `cohort` VALUES ('123龙乌', '2019-09-02 20:02:41', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('狠姐姐姐狠', '2019-09-02 20:08:19', '交易成功', '16.9', '1', '湖北省');
INSERT INTO `cohort` VALUES ('仙宝贝仙神', '2019-09-02 20:09:37', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('156身安', '2019-09-02 20:10:36', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('小蠢蠢蠢蠢', '2019-09-02 20:15:05', '交易成功', '109.7', '1', '广东省');
INSERT INTO `cohort` VALUES ('821hs', '2019-09-02 20:41:27', '交易成功', '16.9', '1', '上海');
INSERT INTO `cohort` VALUES ('123神王', '2019-09-02 20:45:50', '交易成功', '97.8', '1', '山东省');
INSERT INTO `cohort` VALUES ('毅大爷嘉谢', '2019-09-02 20:51:39', '交易成功', '97.8', '1', '广东省');
INSERT INTO `cohort` VALUES ('013bt', '2019-09-02 21:05:19', '交易成功', '16.9', '1', '江苏省');
INSERT INTO `cohort` VALUES ('5bfek', '2019-09-02 21:13:59', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('356bt', '2019-09-02 21:30:19', '交易成功', '16.9', '1', '湖北省');
INSERT INTO `cohort` VALUES ('9贝贝51', '2019-09-02 22:29:13', '交易成功', '22', '1', '湖北省');
INSERT INTO `cohort` VALUES ('807bt', '2019-09-02 22:50:27', '交易成功', '39', '1', '湖南省');
INSERT INTO `cohort` VALUES ('704uj', '2019-09-02 23:06:55', '交易成功', '101.2', '1', '广东省');
INSERT INTO `cohort` VALUES ('的心i勇i', '2019-09-02 23:28:40', '交易成功', '190.4', '1', '福建省');
INSERT INTO `cohort` VALUES ('666年伍', '2019-09-02 23:56:52', '交易成功', '97.8', '1', '浙江省');
INSERT INTO `cohort` VALUES ('思妞妞宝宝', '2019-09-03 00:50:21', '交易成功', '16.9', '1', '福建省');
INSERT INTO `cohort` VALUES ('的女人加撒', '2019-09-03 02:07:38', '交易成功', '88.4', '1', '江苏省');
INSERT INTO `cohort` VALUES ('很想睡iv', '2019-09-03 08:07:56', '交易成功', '106.3', '1', '河北省');
INSERT INTO `cohort` VALUES ('走伙伴啊走', '2019-09-03 08:29:13', '交易成功', '8.4', '1', '广东省');
INSERT INTO `cohort` VALUES ('521颗一', '2019-09-03 08:43:47', '交易成功', '8.4', '1', '山东省');
INSERT INTO `cohort` VALUES ('vxm小洪', '2019-09-03 09:09:48', '交易成功', '16.9', '1', '江苏省');
INSERT INTO `cohort` VALUES ('呆西仔呆呆', '2019-09-03 09:15:08', '交易成功', '16.9', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('291晴林', '2019-09-03 09:30:42', '交易成功', '88.4', '1', '广东省');
INSERT INTO `cohort` VALUES ('花lu些那', '2019-09-03 10:23:25', '交易成功', '8.4', '1', '河北省');
INSERT INTO `cohort` VALUES ('317bt', '2019-09-03 10:33:52', '交易成功', '22', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('299hh', '2019-09-03 10:35:05', '交易成功', '22', '1', '广东省');
INSERT INTO `cohort` VALUES ('012bt', '2019-09-03 10:54:57', '交易成功', '15.2', '1', '广西壮族自治区');
INSERT INTO `cohort` VALUES ('662之梦', '2019-09-03 10:55:54', '交易成功', '8.4', '1', '广东省');
INSERT INTO `cohort` VALUES ('311事百', '2019-09-03 11:19:59', '交易成功', '22', '1', '浙江省');
INSERT INTO `cohort` VALUES ('550健小', '2019-09-03 12:07:36', '交易成功', '126.7', '1', '浙江省');
INSERT INTO `cohort` VALUES ('614仔杨', '2019-09-03 13:20:30', '交易成功', '13.5', '1', '内蒙古自治区');
INSERT INTO `cohort` VALUES ('鱼臭猫鱼懒', '2019-09-03 13:22:48', '交易成功', '97.8', '1', '湖北省');
INSERT INTO `cohort` VALUES ('ice香花', '2019-09-03 13:29:14', '交易成功', '181.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('123亚徐', '2019-09-03 13:34:44', '交易成功', '8.4', '1', '四川省');
INSERT INTO `cohort` VALUES ('待下去这就', '2019-09-03 14:19:09', '交易成功', '101.2', '1', '山东省');
INSERT INTO `cohort` VALUES ('打12柠青', '2019-09-03 15:05:29', '交易成功', '22', '1', '福建省');
INSERT INTO `cohort` VALUES ('gp6at', '2019-09-03 15:41:16', '交易成功', '22', '1', '江苏省');
INSERT INTO `cohort` VALUES ('华尔兹舞珺', '2019-09-03 15:51:35', '交易成功', '33.8', '3', '广东省');
INSERT INTO `cohort` VALUES ('802ul', '2019-09-03 17:29:26', '交易成功', '16.9', '1', '湖北省');
INSERT INTO `cohort` VALUES ('222um', '2019-09-03 17:30:07', '交易成功', '97.8', '1', '河南省');
INSERT INTO `cohort` VALUES ('959女天', '2019-09-03 17:48:49', '交易成功', '8.4', '1', '河南省');
INSERT INTO `cohort` VALUES ('339唧唧', '2019-09-03 17:55:46', '交易成功', '84.6', '6', '浙江省');
INSERT INTO `cohort` VALUES ('ianul', '2019-09-03 17:59:55', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('小确信慧佳', '2019-09-03 18:01:49', '交易成功', '84.6', '6', '浙江省');
INSERT INTO `cohort` VALUES ('333么什', '2019-09-03 18:26:24', '交易成功', '8.4', '1', '北京');
INSERT INTO `cohort` VALUES ('yanhz', '2019-09-03 18:35:19', '交易成功', '15.2', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('230yc', '2019-09-03 20:07:31', '交易成功', '85', '1', '广东省');
INSERT INTO `cohort` VALUES ('sun方陈', '2019-09-03 20:40:31', '交易成功', '181.9', '1', '江苏省');
INSERT INTO `cohort` VALUES ('202ot', '2019-09-03 21:07:52', '交易成功', '8.4', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('打喷嚏麦闭', '2019-09-03 21:14:10', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('花野裕野花', '2019-09-03 21:15:40', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('897bt', '2019-09-03 21:18:48', '交易成功', '16.9', '1', '河北省');
INSERT INTO `cohort` VALUES ('519ow', '2019-09-03 21:18:52', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('148bt', '2019-09-03 21:19:33', '交易成功', '16.9', '1', '四川省');
INSERT INTO `cohort` VALUES ('e77hs', '2019-09-03 21:20:37', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('012bt', '2019-09-03 21:29:17', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('大盒子大何', '2019-09-03 21:31:08', '交易成功', '97.8', '1', '四川省');
INSERT INTO `cohort` VALUES ('迟永铠永迟', '2019-09-03 21:32:33', '交易成功', '8.4', '1', '山东省');
INSERT INTO `cohort` VALUES ('828bt', '2019-09-03 21:32:41', '交易成功', '16.9', '1', '四川省');
INSERT INTO `cohort` VALUES ('发桔梗发短', '2019-09-03 21:35:47', '交易成功', '16.9', '1', '安徽省');
INSERT INTO `cohort` VALUES ('rus15', '2019-09-03 21:37:39', '交易成功', '16.9', '1', '湖北省');
INSERT INTO `cohort` VALUES ('218ws', '2019-09-03 21:38:18', '交易成功', '16.9', '1', '上海');
INSERT INTO `cohort` VALUES ('238叶王', '2019-09-03 21:38:58', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('尾细尾尾细', '2019-09-03 21:41:35', '交易成功', '16.9', '1', '天津');
INSERT INTO `cohort` VALUES ('待完整等只', '2019-09-03 21:43:03', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('wsdsa', '2019-09-03 21:47:01', '交易成功', '16.9', '1', '上海');
INSERT INTO `cohort` VALUES ('951语未', '2019-09-03 21:47:52', '交易成功', '16.9', '1', '安徽省');
INSERT INTO `cohort` VALUES ('朵朵c王a', '2019-09-03 21:50:25', '交易成功', '16.9', '1', '上海');
INSERT INTO `cohort` VALUES ('012bt', '2019-09-03 21:50:38', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('008xh', '2019-09-03 21:53:19', '交易成功', '16.9', '1', '上海');
INSERT INTO `cohort` VALUES ('7死鱼91', '2019-09-03 21:57:20', '交易成功', '16.9', '1', '安徽省');
INSERT INTO `cohort` VALUES ('三百首止不', '2019-09-03 22:10:07', '交易成功', '22', '1', '江苏省');
INSERT INTO `cohort` VALUES ('靖妞呗小叫', '2019-09-03 22:16:36', '交易成功', '16.9', '1', '广东省');
INSERT INTO `cohort` VALUES ('新久旧新新', '2019-09-03 22:21:53', '交易成功', '16.9', '1', '广东省');
INSERT INTO `cohort` VALUES ('dgded', '2019-09-03 22:23:07', '交易成功', '16.9', '1', '广东省');
INSERT INTO `cohort` VALUES ('怎样丶欢喜', '2019-09-03 22:34:13', '交易成功', '16.9', '1', '陕西省');
INSERT INTO `cohort` VALUES ('346bt', '2019-09-03 22:45:12', '交易成功', '16.9', '1', '广东省');
INSERT INTO `cohort` VALUES ('没错i没蒽', '2019-09-03 22:47:35', '交易成功', '16.9', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('046_t', '2019-09-03 22:58:00', '交易成功', '16.9', '1', '安徽省');
INSERT INTO `cohort` VALUES ('酱33子兔', '2019-09-03 23:04:06', '交易成功', '16.9', '1', '广东省');
INSERT INTO `cohort` VALUES ('lsn轻年', '2019-09-03 23:13:21', '交易成功', '27.1', '1', '内蒙古自治区');
INSERT INTO `cohort` VALUES ('128rb', '2019-09-03 23:26:24', '交易成功', '97.8', '1', '广东省');
INSERT INTO `cohort` VALUES ('ssuxx', '2019-09-03 23:33:34', '交易成功', '16.9', '1', '上海');
INSERT INTO `cohort` VALUES ('ng7hz', '2019-09-03 23:41:44', '交易成功', '22', '1', '江苏省');
INSERT INTO `cohort` VALUES ('gie美张', '2019-09-04 00:31:00', '交易成功', '186.2', '2', '北京');
INSERT INTO `cohort` VALUES ('5039华', '2019-09-04 08:05:25', '交易成功', '22', '1', '江苏省');
INSERT INTO `cohort` VALUES ('783bt', '2019-09-04 08:24:20', '交易成功', '101.2', '1', '江苏省');
INSERT INTO `cohort` VALUES ('范佳旭佳范', '2019-09-04 09:04:08', '交易成功', '8.4', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('ing不做', '2019-09-04 09:08:36', '交易成功', '101.2', '1', '山东省');
INSERT INTO `cohort` VALUES ('杨羊999', '2019-09-04 09:12:08', '交易成功', '8.4', '1', '四川省');
INSERT INTO `cohort` VALUES ('767ur', '2019-09-04 09:14:07', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('730ol', '2019-09-04 09:14:35', '交易成功', '16.9', '1', '广东省');
INSERT INTO `cohort` VALUES ('宝宝楠宝大', '2019-09-04 09:20:43', '交易成功', '22', '1', '北京');
INSERT INTO `cohort` VALUES ('ianli', '2019-09-04 09:21:52', '交易成功', '16.9', '1', '云南省');
INSERT INTO `cohort` VALUES ('839一把', '2019-09-04 09:26:36', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('和肖苒肖和', '2019-09-04 09:27:55', '交易成功', '16.9', '1', '河南省');
INSERT INTO `cohort` VALUES ('漫小双漫浪', '2019-09-04 09:30:46', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('的妈妈听听', '2019-09-04 09:31:25', '交易成功', '16.9', '1', '上海');
INSERT INTO `cohort` VALUES ('suieb', '2019-09-04 09:35:05', '交易成功', '16.9', '1', '广东省');
INSERT INTO `cohort` VALUES ('012bt', '2019-09-04 09:42:35', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('010儿婷', '2019-09-04 09:44:57', '交易成功', '16.9', '1', '广东省');
INSERT INTO `cohort` VALUES ('天11找寻', '2019-09-04 09:48:05', '交易成功', '16.9', '1', '江苏省');
INSERT INTO `cohort` VALUES ('灬末日ad', '2019-09-04 09:51:20', '交易成功', '16.9', '1', '江苏省');
INSERT INTO `cohort` VALUES ('ne12林', '2019-09-04 09:52:52', '交易成功', '8.4', '1', '安徽省');
INSERT INTO `cohort` VALUES ('就起来三我', '2019-09-04 09:56:23', '交易成功', '16.9', '1', '江苏省');
INSERT INTO `cohort` VALUES ('周迪迪迪周', '2019-09-04 10:10:10', '交易成功', '16.9', '1', '江苏省');
INSERT INTO `cohort` VALUES ('012bt', '2019-09-04 10:27:42', '交易成功', '16.9', '1', '北京');
INSERT INTO `cohort` VALUES ('凡凡凡ri', '2019-09-04 10:28:02', '交易成功', '16.9', '1', '广东省');
INSERT INTO `cohort` VALUES ('205im', '2019-09-04 10:47:06', '交易成功', '97.8', '1', '陕西省');
INSERT INTO `cohort` VALUES ('isyle', '2019-09-04 10:49:46', '交易成功', '8.4', '1', '四川省');
INSERT INTO `cohort` VALUES ('ukiaw', '2019-09-04 10:55:31', '交易成功', '16.9', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('312ay', '2019-09-04 11:06:13', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('心晴军晴心', '2019-09-04 11:24:58', '交易成功', '16.9', '1', '江苏省');
INSERT INTO `cohort` VALUES ('379_t', '2019-09-04 11:25:50', '交易成功', '16.9', '1', '河南省');
INSERT INTO `cohort` VALUES ('粒li子栗', '2019-09-04 11:34:59', '交易成功', '8.4', '1', '浙江省');
INSERT INTO `cohort` VALUES ('界有爱在翀', '2019-09-04 11:36:10', '交易成功', '101.2', '1', '福建省');
INSERT INTO `cohort` VALUES ('活被套被生', '2019-09-04 11:41:42', '交易成功', '15.2', '1', '安徽省');
INSERT INTO `cohort` VALUES ('e宝呗一唯', '2019-09-04 11:45:15', '交易成功', '16.9', '1', '江苏省');
INSERT INTO `cohort` VALUES ('324lh', '2019-09-04 11:47:46', '交易成功', '156.4', '11', '上海');
INSERT INTO `cohort` VALUES ('011il', '2019-09-04 11:50:23', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('amo_m', '2019-09-04 11:59:54', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('594因爱', '2019-09-04 12:02:25', '交易成功', '16.9', '1', '安徽省');
INSERT INTO `cohort` VALUES ('012bt', '2019-09-04 12:04:48', '交易成功', '16.9', '1', '江苏省');
INSERT INTO `cohort` VALUES ('忆子安末夜', '2019-09-04 12:06:23', '交易成功', '16.9', '1', '河北省');
INSERT INTO `cohort` VALUES ('talk杨', '2019-09-04 12:11:01', '交易成功', '101.2', '1', '福建省');
INSERT INTO `cohort` VALUES ('鲤半仙半鲤', '2019-09-04 12:15:47', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('intls', '2019-09-04 12:28:43', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('喵喵哒喵噜', '2019-09-04 12:36:37', '交易成功', '16.9', '1', '广东省');
INSERT INTO `cohort` VALUES ('草yu杂大', '2019-09-04 12:59:57', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('015eb', '2019-09-04 13:00:31', '交易成功', '89.3', '1', '重庆');
INSERT INTO `cohort` VALUES ('013花无', '2019-09-04 13:09:28', '交易成功', '88.4', '1', '江苏省');
INSERT INTO `cohort` VALUES ('csx生浮', '2019-09-04 13:21:22', '交易成功', '16.9', '1', '福建省');
INSERT INTO `cohort` VALUES ('霓晚晴霓虹', '2019-09-04 13:35:24', '交易成功', '16.9', '1', '上海');
INSERT INTO `cohort` VALUES ('abyxx', '2019-09-04 13:37:48', '交易成功', '16.9', '1', '广东省');
INSERT INTO `cohort` VALUES ('灯盏0盏灯', '2019-09-04 14:00:50', '交易成功', '16.9', '1', '湖南省');
INSERT INTO `cohort` VALUES ('123yy', '2019-09-04 14:05:50', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('ust达阿', '2019-09-04 14:13:30', '交易成功', '16.9', '1', '广东省');
INSERT INTO `cohort` VALUES ('199间本', '2019-09-04 14:14:07', '交易成功', '16.9', '1', '重庆');
INSERT INTO `cohort` VALUES ('osslb', '2019-09-04 14:28:03', '交易成功', '16.9', '1', '江苏省');
INSERT INTO `cohort` VALUES ('930佳佳', '2019-09-04 14:29:48', '交易成功', '97.8', '1', '四川省');
INSERT INTO `cohort` VALUES ('513bt', '2019-09-04 14:32:34', '交易成功', '16.9', '1', '湖北省');
INSERT INTO `cohort` VALUES ('年无果99', '2019-09-04 14:32:36', '交易成功', '16.9', '1', '山东省');
INSERT INTO `cohort` VALUES ('树19头木', '2019-09-04 14:35:01', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('272bt', '2019-09-04 14:35:24', '交易成功', '16.9', '1', '四川省');
INSERT INTO `cohort` VALUES ('你的笑国倾', '2019-09-04 14:36:04', '交易成功', '8.4', '1', '湖南省');
INSERT INTO `cohort` VALUES ('876jd', '2019-09-04 14:36:07', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('dxt21', '2019-09-04 14:43:11', '交易成功', '16.9', '1', '广东省');
INSERT INTO `cohort` VALUES ('仙晗仔仙阿', '2019-09-04 14:45:15', '交易成功', '16.9', '1', '四川省');
INSERT INTO `cohort` VALUES ('俏女郎心开', '2019-09-04 14:47:01', '交易成功', '16.9', '1', '江苏省');
INSERT INTO `cohort` VALUES ('721ak', '2019-09-04 14:47:49', '交易成功', '16.9', '1', '广东省');
INSERT INTO `cohort` VALUES ('315bt', '2019-09-04 14:49:00', '交易成功', '16.9', '1', '广东省');
INSERT INTO `cohort` VALUES ('惠ok惠顿', '2019-09-04 14:53:20', '交易成功', '16.9', '1', '广东省');
INSERT INTO `cohort` VALUES ('能成疯爱5', '2019-09-04 14:57:09', '交易成功', '16.9', '1', '湖北省');
INSERT INTO `cohort` VALUES ('246hz', '2019-09-04 15:00:03', '交易成功', '16.9', '1', '安徽省');
INSERT INTO `cohort` VALUES ('薄明丽薄淡', '2019-09-04 15:03:42', '交易成功', '16.9', '1', '安徽省');
INSERT INTO `cohort` VALUES ('_33bt', '2019-09-04 15:04:15', '交易成功', '16.9', '1', '山东省');
INSERT INTO `cohort` VALUES ('就行咯我叫', '2019-09-04 15:05:12', '交易成功', '16.9', '1', '江苏省');
INSERT INTO `cohort` VALUES ('158_t', '2019-09-04 15:08:55', '交易成功', '16.9', '1', '广东省');
INSERT INTO `cohort` VALUES ('_w4iv', '2019-09-04 16:05:54', '交易成功', '97.8', '1', '四川省');
INSERT INTO `cohort` VALUES ('蚊蚊蚊蕉笨', '2019-09-04 16:11:32', '交易成功', '16.9', '1', '广东省');
INSERT INTO `cohort` VALUES ('minzz', '2019-09-04 16:16:47', '交易成功', '16.9', '1', '广东省');
INSERT INTO `cohort` VALUES ('孙影霏影孙', '2019-09-04 16:17:26', '交易成功', '16.9', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('023xx', '2019-09-04 16:18:08', '交易成功', '16.9', '1', '上海');
INSERT INTO `cohort` VALUES ('e丶练ps', '2019-09-04 16:20:50', '交易成功', '16.9', '1', '贵州省');
INSERT INTO `cohort` VALUES ('ian月秋', '2019-09-04 16:27:10', '交易成功', '16.9', '1', '江苏省');
INSERT INTO `cohort` VALUES ('801eb', '2019-09-04 16:33:17', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('933bt', '2019-09-04 16:58:17', '交易成功', '181.9', '1', '山西省');
INSERT INTO `cohort` VALUES ('dwl文邓', '2019-09-04 17:04:05', '交易成功', '8.4', '1', '贵州省');
INSERT INTO `cohort` VALUES ('g71il', '2019-09-04 17:12:02', '交易成功', '8.4', '1', '江苏省');
INSERT INTO `cohort` VALUES ('晒太阳我陪', '2019-09-04 17:14:06', '交易成功', '109.6', '2', '重庆');
INSERT INTO `cohort` VALUES ('肆人帮有我', '2019-09-04 17:20:27', '交易成功', '25.4', '1', '江苏省');
INSERT INTO `cohort` VALUES ('701_t', '2019-09-04 17:20:43', '交易成功', '16.9', '1', '江苏省');
INSERT INTO `cohort` VALUES ('美人鱼uw', '2019-09-04 18:05:09', '交易成功', '8.4', '1', '广西壮族自治区');
INSERT INTO `cohort` VALUES ('877bt', '2019-09-04 18:06:23', '交易成功', '8.4', '1', '广西壮族自治区');
INSERT INTO `cohort` VALUES ('王汪汪王阿', '2019-09-04 18:13:32', '交易成功', '16.9', '1', '四川省');
INSERT INTO `cohort` VALUES ('栗栗a栗陈', '2019-09-04 18:21:08', '交易成功', '42.2', '4', '四川省');
INSERT INTO `cohort` VALUES ('994乳小', '2019-09-04 18:24:38', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('臻明朗川沥', '2019-09-04 18:29:48', '交易成功', '16.9', '1', '河北省');
INSERT INTO `cohort` VALUES ('wenoj', '2019-09-04 18:37:34', '交易成功', '16.9', '1', '广东省');
INSERT INTO `cohort` VALUES ('尘郴尘辰沉', '2019-09-04 19:00:20', '交易成功', '16.9', '1', '四川省');
INSERT INTO `cohort` VALUES ('juk_w', '2019-09-04 19:05:19', '交易成功', '16.9', '1', '山东省');
INSERT INTO `cohort` VALUES ('512sz', '2019-09-04 19:14:19', '交易成功', '22', '1', '江苏省');
INSERT INTO `cohort` VALUES ('它的人娃泥', '2019-09-04 20:18:46', '交易成功', '33.8', '3', '河南省');
INSERT INTO `cohort` VALUES ('nieab', '2019-09-04 20:24:02', '交易成功', '16.9', '1', '上海');
INSERT INTO `cohort` VALUES ('_00bt', '2019-09-04 20:32:43', '交易成功', '109.7', '1', '湖南省');
INSERT INTO `cohort` VALUES ('傻宝珺宝傻', '2019-09-04 21:00:29', '交易成功', '22', '1', '江苏省');
INSERT INTO `cohort` VALUES ('991er', '2019-09-04 21:03:18', '交易成功', '39', '1', '浙江省');
INSERT INTO `cohort` VALUES ('美丽啊园园', '2019-09-04 21:18:47', '交易成功', '13.5', '1', '海南省');
INSERT INTO `cohort` VALUES ('017hc', '2019-09-04 21:29:01', '交易成功', '15.2', '1', '四川省');
INSERT INTO `cohort` VALUES ('zmx11', '2019-09-04 21:33:49', '交易成功', '85', '1', '浙江省');
INSERT INTO `cohort` VALUES ('zmx11', '2019-09-04 21:41:13', '交易成功', '8.4', '1', '浙江省');
INSERT INTO `cohort` VALUES ('惹惹惹珩是', '2019-09-04 22:00:38', '交易成功', '25.3', '3', '江西省');
INSERT INTO `cohort` VALUES ('427bt', '2019-09-04 22:14:08', '交易成功', '8.4', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('013ag', '2019-09-04 23:46:12', '交易成功', '33.8', '3', '福建省');
INSERT INTO `cohort` VALUES ('verok', '2019-09-05 00:48:54', '交易成功', '88.4', '1', '河北省');
INSERT INTO `cohort` VALUES ('913雅小', '2019-09-05 01:05:01', '交易成功', '89.3', '1', '广东省');
INSERT INTO `cohort` VALUES ('711啦巴', '2019-09-05 04:55:37', '交易成功', '39', '1', '四川省');
INSERT INTO `cohort` VALUES ('就这吧倒拉', '2019-09-05 05:57:00', '交易成功', '8.4', '1', '湖北省');
INSERT INTO `cohort` VALUES ('鹿鹿y鹿y', '2019-09-05 08:56:37', '交易成功', '15.2', '1', '四川省');
INSERT INTO `cohort` VALUES ('灿灿d灿小', '2019-09-05 09:13:22', '交易成功', '101.2', '1', '云南省');
INSERT INTO `cohort` VALUES ('大帅哥珺田', '2019-09-05 09:18:58', '交易成功', '8.4', '1', '重庆');
INSERT INTO `cohort` VALUES ('大帅哥珺田', '2019-09-05 09:24:34', '交易成功', '101.2', '1', '重庆');
INSERT INTO `cohort` VALUES ('176_t', '2019-09-05 09:57:17', '交易成功', '33.8', '3', '江苏省');
INSERT INTO `cohort` VALUES ('575bt', '2019-09-05 10:17:58', '交易成功', '8.4', '1', '吉林省');
INSERT INTO `cohort` VALUES ('811eh', '2019-09-05 10:24:28', '交易成功', '16.9', '1', '山东省');
INSERT INTO `cohort` VALUES ('246色紫', '2019-09-05 10:45:51', '交易成功', '15.2', '1', '江西省');
INSERT INTO `cohort` VALUES ('958zl', '2019-09-05 11:25:38', '交易成功', '8.4', '1', '广东省');
INSERT INTO `cohort` VALUES ('小小欣小杨', '2019-09-05 11:34:35', '交易成功', '22', '1', '浙江省');
INSERT INTO `cohort` VALUES ('力如嫣力魅', '2019-09-05 12:56:18', '交易成功', '8.4', '1', '山东省');
INSERT INTO `cohort` VALUES ('梓霖吖纪小', '2019-09-05 13:14:29', '交易成功', '101.2', '1', '山东省');
INSERT INTO `cohort` VALUES ('宝宝鑫宝大', '2019-09-05 13:26:54', '交易成功', '8.4', '1', '黑龙江省');
INSERT INTO `cohort` VALUES ('字绣纺晓晓', '2019-09-05 13:56:13', '交易成功', '88.4', '1', '浙江省');
INSERT INTO `cohort` VALUES ('零零六零二', '2019-09-05 14:51:49', '交易成功', '33.8', '3', '上海');
INSERT INTO `cohort` VALUES ('6lq51', '2019-09-05 15:17:13', '交易成功', '22', '1', '山东省');
INSERT INTO `cohort` VALUES ('alyih', '2019-09-05 16:12:14', '交易成功', '109.7', '1', '四川省');
INSERT INTO `cohort` VALUES ('911il', '2019-09-05 16:23:11', '交易成功', '101.2', '1', '北京');
INSERT INTO `cohort` VALUES ('pa2kk', '2019-09-05 17:58:53', '交易成功', '25.4', '1', '北京');
INSERT INTO `cohort` VALUES ('蔡耀雄耀蔡', '2019-09-05 18:12:22', '交易成功', '33.8', '3', '广东省');
INSERT INTO `cohort` VALUES ('hhy31', '2019-09-05 19:24:42', '交易成功', '8.4', '1', '上海');
INSERT INTO `cohort` VALUES ('719bt', '2019-09-05 19:42:26', '交易成功', '97.8', '1', '福建省');
INSERT INTO `cohort` VALUES ('寞如墨寞寂', '2019-09-05 19:48:23', '交易成功', '15.2', '1', '湖南省');
INSERT INTO `cohort` VALUES ('288bt', '2019-09-05 19:49:24', '交易成功', '93.5', '1', '北京');
INSERT INTO `cohort` VALUES ('华88华祥', '2019-09-05 20:01:26', '交易成功', '109.7', '1', '广东省');
INSERT INTO `cohort` VALUES ('变化啊生小', '2019-09-05 20:16:49', '交易成功', '8.4', '1', '山东省');
INSERT INTO `cohort` VALUES ('神话o欧北', '2019-09-05 20:33:10', '交易成功', '8.4', '1', '湖南省');
INSERT INTO `cohort` VALUES ('751jx', '2019-09-05 20:36:09', '交易成功', '97.8', '1', '江苏省');
INSERT INTO `cohort` VALUES ('工作室熙雨', '2019-09-05 21:41:33', '交易成功', '97.8', '1', '江苏省');
INSERT INTO `cohort` VALUES ('a75ss', '2019-09-05 21:42:55', '交易成功', '190.4', '1', '山东省');
INSERT INTO `cohort` VALUES ('鹏go鹏力', '2019-09-05 21:54:14', '交易成功', '33.8', '3', '吉林省');
INSERT INTO `cohort` VALUES ('全泰浩爱大', '2019-09-05 22:16:14', '交易成功', '8.4', '1', '江苏省');
INSERT INTO `cohort` VALUES ('314思杨', '2019-09-05 22:24:20', '交易成功', '39', '1', '江苏省');
INSERT INTO `cohort` VALUES ('rwzep', '2019-09-05 22:39:09', '交易成功', '16.8', '3', '广东省');
INSERT INTO `cohort` VALUES ('的学徒古尼', '2019-09-05 23:03:47', '交易成功', '16.8', '3', '山东省');
INSERT INTO `cohort` VALUES ('她爱你阿小', '2019-09-05 23:32:11', '交易成功', '101.2', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('张浩轩爱我', '2019-09-06 00:49:13', '交易成功', '8.4', '1', '上海');
INSERT INTO `cohort` VALUES ('333il', '2019-09-06 01:39:06', '交易成功', '97.8', '1', '广东省');
INSERT INTO `cohort` VALUES ('alett', '2019-09-06 07:19:30', '交易成功', '16.8', '3', '北京');
INSERT INTO `cohort` VALUES ('子小逗鼻黑', '2019-09-06 07:59:19', '交易成功', '8.4', '1', '江苏省');
INSERT INTO `cohort` VALUES ('比从前念想', '2019-09-06 08:34:02', '交易成功', '8.4', '1', '河北省');
INSERT INTO `cohort` VALUES ('020人丑', '2019-09-06 09:20:17', '交易成功', '22', '1', '山西省');
INSERT INTO `cohort` VALUES ('叉酷酷酷叉', '2019-09-06 09:27:00', '交易成功', '33.8', '2', '天津');
INSERT INTO `cohort` VALUES ('886bt', '2019-09-06 09:50:03', '交易成功', '67.7', '5', '上海');
INSERT INTO `cohort` VALUES ('hsj场退', '2019-09-06 10:02:43', '交易成功', '22', '1', '甘肃省');
INSERT INTO `cohort` VALUES ('wei格格', '2019-09-06 10:09:11', '交易成功', '16.9', '1', '山东省');
INSERT INTO `cohort` VALUES ('儿77光阳', '2019-09-06 10:22:39', '交易成功', '33.8', '3', '江苏省');
INSERT INTO `cohort` VALUES ('1ll73', '2019-09-06 10:50:31', '交易成功', '22', '1', '江苏省');
INSERT INTO `cohort` VALUES ('舟皮皮文陈', '2019-09-06 10:59:55', '交易成功', '8.4', '1', '浙江省');
INSERT INTO `cohort` VALUES ('144bt', '2019-09-06 11:18:34', '交易成功', '181.9', '1', '福建省');
INSERT INTO `cohort` VALUES ('毛33兔小', '2019-09-06 11:59:44', '交易成功', '8.4', '1', '北京');
INSERT INTO `cohort` VALUES ('n学群ux', '2019-09-06 12:13:59', '交易成功', '15.2', '1', '北京');
INSERT INTO `cohort` VALUES ('344bt', '2019-09-06 12:32:08', '交易成功', '8.4', '1', '浙江省');
INSERT INTO `cohort` VALUES ('524源凯', '2019-09-06 12:34:10', '交易成功', '101.2', '1', '重庆');
INSERT INTO `cohort` VALUES ('7817s', '2019-09-06 12:50:54', '交易成功', '92.7', '1', '黑龙江省');
INSERT INTO `cohort` VALUES ('qchby', '2019-09-06 13:23:16', '交易成功', '97.8', '1', '浙江省');
INSERT INTO `cohort` VALUES ('len叔怪', '2019-09-06 14:33:01', '交易成功', '16.8', '3', '上海');
INSERT INTO `cohort` VALUES ('077ak', '2019-09-06 14:33:14', '交易成功', '16.8', '3', '辽宁省');
INSERT INTO `cohort` VALUES ('veriw', '2019-09-06 14:35:01', '交易成功', '16.8', '3', '北京');
INSERT INTO `cohort` VALUES ('ifeym', '2019-09-06 14:35:49', '交易成功', '16.8', '3', '北京');
INSERT INTO `cohort` VALUES ('wystt', '2019-09-06 14:39:23', '交易成功', '16.8', '3', '江苏省');
INSERT INTO `cohort` VALUES ('ony叶壹', '2019-09-06 14:40:31', '交易成功', '16.8', '3', '江西省');
INSERT INTO `cohort` VALUES ('junjf', '2019-09-06 14:44:02', '交易成功', '16.8', '3', '上海');
INSERT INTO `cohort` VALUES ('lespa', '2019-09-06 14:45:46', '交易成功', '16.8', '3', '福建省');
INSERT INTO `cohort` VALUES ('iwmok', '2019-09-06 14:48:54', '交易成功', '97.8', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('556bt', '2019-09-06 14:54:14', '交易成功', '16.8', '3', '广西壮族自治区');
INSERT INTO `cohort` VALUES ('不矫情丽美', '2019-09-06 15:05:57', '交易成功', '8.4', '1', '河北省');
INSERT INTO `cohort` VALUES ('885as', '2019-09-06 15:12:08', '交易成功', '16.8', '3', '山东省');
INSERT INTO `cohort` VALUES ('s月萤sm', '2019-09-06 15:27:23', '交易成功', '16.8', '3', '广东省');
INSERT INTO `cohort` VALUES ('621bt', '2019-09-06 15:31:36', '交易成功', '88.4', '1', '山西省');
INSERT INTO `cohort` VALUES ('最可耐鸭鸭', '2019-09-06 15:46:26', '交易成功', '8.4', '1', '福建省');
INSERT INTO `cohort` VALUES ('oyyay', '2019-09-06 15:47:32', '交易成功', '16.8', '3', '北京');
INSERT INTO `cohort` VALUES ('804eb', '2019-09-06 15:52:09', '交易成功', '16.8', '3', '四川省');
INSERT INTO `cohort` VALUES ('方环宇环方', '2019-09-06 15:57:29', '交易成功', '16.8', '3', '上海');
INSERT INTO `cohort` VALUES ('咪92小喵', '2019-09-06 16:04:16', '交易成功', '16.8', '3', '安徽省');
INSERT INTO `cohort` VALUES ('720op', '2019-09-06 16:04:35', '交易成功', '16.8', '3', '北京');
INSERT INTO `cohort` VALUES ('a_霏if', '2019-09-06 16:08:50', '交易成功', '16.8', '3', '广东省');
INSERT INTO `cohort` VALUES ('008ep', '2019-09-06 16:09:17', '交易成功', '16.8', '3', '山东省');
INSERT INTO `cohort` VALUES ('h绘绘绘h', '2019-09-06 16:32:42', '交易成功', '16.8', '3', '湖北省');
INSERT INTO `cohort` VALUES ('winen', '2019-09-06 16:35:12', '交易成功', '16.8', '3', '河北省');
INSERT INTO `cohort` VALUES ('566in', '2019-09-06 16:36:40', '交易成功', '16.8', '3', '福建省');
INSERT INTO `cohort` VALUES ('水柔蓝柔水', '2019-09-06 16:40:26', '交易成功', '16.8', '3', '上海');
INSERT INTO `cohort` VALUES ('1cqqc', '2019-09-06 16:50:17', '交易成功', '16.8', '3', '北京');
INSERT INTO `cohort` VALUES ('jl0ja', '2019-09-06 16:52:16', '交易成功', '16.8', '3', '上海');
INSERT INTO `cohort` VALUES ('807风看', '2019-09-06 17:11:15', '交易成功', '16.9', '1', '广东省');
INSERT INTO `cohort` VALUES ('terlb', '2019-09-06 17:13:03', '交易成功', '16.8', '3', '上海');
INSERT INTO `cohort` VALUES ('inger', '2019-09-06 17:14:26', '交易成功', '16.8', '3', '湖北省');
INSERT INTO `cohort` VALUES ('gwuub', '2019-09-06 17:20:35', '交易成功', '16.8', '3', '江苏省');
INSERT INTO `cohort` VALUES ('821子妞', '2019-09-06 17:24:45', '交易成功', '101.2', '1', '广东省');
INSERT INTO `cohort` VALUES ('iddsz', '2019-09-06 17:41:19', '交易成功', '16.8', '3', '浙江省');
INSERT INTO `cohort` VALUES ('傻丫头果糖', '2019-09-06 17:47:12', '交易成功', '50.7', '4', '江苏省');
INSERT INTO `cohort` VALUES ('家商店家战', '2019-09-06 17:50:23', '交易成功', '16.8', '3', '辽宁省');
INSERT INTO `cohort` VALUES ('007星晨', '2019-09-06 18:06:12', '交易成功', '16.8', '3', '四川省');
INSERT INTO `cohort` VALUES ('花等嗳岸彼', '2019-09-06 18:09:40', '交易成功', '22', '1', '湖南省');
INSERT INTO `cohort` VALUES ('婷啊噢婷张', '2019-09-06 18:16:35', '交易成功', '16.8', '3', '广西壮族自治区');
INSERT INTO `cohort` VALUES ('9迷途迷9', '2019-09-06 18:33:36', '交易成功', '189.6', '2', '福建省');
INSERT INTO `cohort` VALUES ('鑫zz鑫丁', '2019-09-06 18:59:35', '交易成功', '16.8', '3', '山西省');
INSERT INTO `cohort` VALUES ('685bt', '2019-09-06 19:00:24', '交易成功', '16.8', '3', '山西省');
INSERT INTO `cohort` VALUES ('705uj', '2019-09-06 19:04:05', '交易成功', '16.8', '3', '江苏省');
INSERT INTO `cohort` VALUES ('阿肯色红粉', '2019-09-06 19:04:40', '交易成功', '16.8', '3', '广西壮族自治区');
INSERT INTO `cohort` VALUES ('152ix', '2019-09-06 19:43:03', '交易成功', '16.8', '3', '山东省');
INSERT INTO `cohort` VALUES ('女qy疫瘟', '2019-09-06 19:51:11', '交易成功', '97.8', '1', '上海');
INSERT INTO `cohort` VALUES ('的范范治来', '2019-09-06 20:10:36', '交易成功', '22', '1', '湖北省');
INSERT INTO `cohort` VALUES ('813ys', '2019-09-06 20:17:12', '交易成功', '33.8', '3', '广东省');
INSERT INTO `cohort` VALUES ('017il', '2019-09-06 20:26:39', '交易成功', '16.8', '3', '浙江省');
INSERT INTO `cohort` VALUES ('宁mm宁婴', '2019-09-06 20:28:47', '交易成功', '22', '1', '广东省');
INSERT INTO `cohort` VALUES ('石杰哥石柳', '2019-09-06 20:43:16', '交易成功', '16.8', '3', '广西壮族自治区');
INSERT INTO `cohort` VALUES ('diotd', '2019-09-06 20:45:33', '交易成功', '16.8', '3', '北京');
INSERT INTO `cohort` VALUES ('111佳陈', '2019-09-06 20:58:40', '交易成功', '8.4', '1', '江苏省');
INSERT INTO `cohort` VALUES ('983sp', '2019-09-06 21:02:04', '交易成功', '16.8', '3', '贵州省');
INSERT INTO `cohort` VALUES ('137泽牛', '2019-09-06 21:18:33', '交易成功', '15.2', '1', '河南省');
INSERT INTO `cohort` VALUES ('凛啊!凛凛', '2019-09-06 21:21:42', '交易成功', '16.9', '1', '四川省');
INSERT INTO `cohort` VALUES ('当当o叮o', '2019-09-06 21:27:26', '交易成功', '16.8', '3', '广东省');
INSERT INTO `cohort` VALUES ('znb77', '2019-09-06 21:28:49', '交易成功', '33.8', '3', '湖南省');
INSERT INTO `cohort` VALUES ('的小薇风威', '2019-09-06 21:38:37', '交易成功', '534.7', '1', '上海');
INSERT INTO `cohort` VALUES ('版伟仔版正', '2019-09-06 21:42:38', '交易成功', '16.8', '3', '河南省');
INSERT INTO `cohort` VALUES ('slvil', '2019-09-06 21:56:17', '交易成功', '16.8', '3', '北京');
INSERT INTO `cohort` VALUES ('nan月十', '2019-09-06 22:03:19', '交易成功', '16.8', '3', '江苏省');
INSERT INTO `cohort` VALUES ('高兴8属纯', '2019-09-06 22:10:22', '交易成功', '22', '1', '广东省');
INSERT INTO `cohort` VALUES ('意吃鱼意愿', '2019-09-06 22:13:14', '交易成功', '16.8', '3', '山东省');
INSERT INTO `cohort` VALUES ('小鸽子角嘴', '2019-09-06 22:17:11', '交易成功', '22', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('331_t', '2019-09-06 22:49:15', '交易成功', '16.9', '1', '广东省');
INSERT INTO `cohort` VALUES ('宝宝lel', '2019-09-06 22:51:52', '交易成功', '22', '1', '四川省');
INSERT INTO `cohort` VALUES ('bineb', '2019-09-06 23:02:01', '交易成功', '16.8', '3', '北京');
INSERT INTO `cohort` VALUES ('1sdds', '2019-09-06 23:12:45', '交易成功', '16.8', '3', '北京');
INSERT INTO `cohort` VALUES ('小姐7丽美', '2019-09-06 23:20:12', '交易成功', '8.4', '1', '重庆');
INSERT INTO `cohort` VALUES ('的林峰爱我', '2019-09-07 00:02:23', '交易成功', '106.3', '1', '四川省');
INSERT INTO `cohort` VALUES ('一一娃一一', '2019-09-07 00:08:23', '交易成功', '97.8', '1', '四川省');
INSERT INTO `cohort` VALUES ('652hp', '2019-09-07 00:12:07', '交易成功', '16.8', '3', '浙江省');
INSERT INTO `cohort` VALUES ('人60瓜南', '2019-09-07 00:13:00', '交易成功', '16.8', '3', '北京');
INSERT INTO `cohort` VALUES ('欣33欣明', '2019-09-07 00:29:21', '交易成功', '181.9', '1', '四川省');
INSERT INTO `cohort` VALUES ('市市草庆重', '2019-09-07 00:54:19', '交易成功', '109.7', '1', '重庆');
INSERT INTO `cohort` VALUES ('_33bt', '2019-09-07 01:03:04', '交易成功', '16.8', '3', '广东省');
INSERT INTO `cohort` VALUES ('123实其', '2019-09-07 01:07:41', '交易成功', '88.4', '1', '黑龙江省');
INSERT INTO `cohort` VALUES ('小猫儿空悟', '2019-09-07 01:53:13', '交易成功', '16.8', '3', '北京');
INSERT INTO `cohort` VALUES ('721as', '2019-09-07 02:12:48', '交易成功', '16.8', '3', '北京');
INSERT INTO `cohort` VALUES ('006iq', '2019-09-07 08:16:38', '交易成功', '189.6', '2', '河北省');
INSERT INTO `cohort` VALUES ('啦啦啦啦啦', '2019-09-07 08:34:10', '交易成功', '16.9', '1', '四川省');
INSERT INTO `cohort` VALUES ('510ur', '2019-09-07 08:35:43', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('wh6善上', '2019-09-07 08:57:43', '交易成功', '97.8', '1', '安徽省');
INSERT INTO `cohort` VALUES ('5xx13', '2019-09-07 08:58:36', '交易成功', '97.8', '1', '河北省');
INSERT INTO `cohort` VALUES ('鑫娟娟鑫丶', '2019-09-07 09:16:54', '交易成功', '102.9', '1', '甘肃省');
INSERT INTO `cohort` VALUES ('小潘子小j', '2019-09-07 09:17:24', '交易成功', '97.8', '1', '上海');
INSERT INTO `cohort` VALUES ('146bt', '2019-09-07 09:17:50', '交易成功', '97.8', '1', '吉林省');
INSERT INTO `cohort` VALUES ('933iy', '2019-09-07 09:18:59', '交易成功', '97.8', '1', '河南省');
INSERT INTO `cohort` VALUES ('276bt', '2019-09-07 09:21:31', '交易成功', '109.7', '1', '浙江省');
INSERT INTO `cohort` VALUES ('步空港人万', '2019-09-07 09:24:26', '交易成功', '97.8', '1', '江苏省');
INSERT INTO `cohort` VALUES ('子77一做', '2019-09-07 09:35:37', '交易成功', '25.4', '1', '浙江省');
INSERT INTO `cohort` VALUES ('andus', '2019-09-07 09:37:33', '交易成功', '22', '1', '江西省');
INSERT INTO `cohort` VALUES ('木小未木木', '2019-09-07 09:45:13', '交易成功', '97.8', '1', '浙江省');
INSERT INTO `cohort` VALUES ('000爱可', '2019-09-07 09:45:13', '交易成功', '97.8', '1', '湖北省');
INSERT INTO `cohort` VALUES ('tuoem', '2019-09-07 09:51:37', '交易成功', '16.8', '3', '湖南省');
INSERT INTO `cohort` VALUES ('612bt', '2019-09-07 09:56:32', '交易成功', '16.8', '3', '湖南省');
INSERT INTO `cohort` VALUES ('202jw', '2019-09-07 09:56:39', '交易成功', '8.4', '1', '上海');
INSERT INTO `cohort` VALUES ('007是只', '2019-09-07 10:03:52', '交易成功', '97.8', '1', '北京');
INSERT INTO `cohort` VALUES ('orane', '2019-09-07 10:36:43', '交易成功', '97.8', '1', '上海');
INSERT INTO `cohort` VALUES ('兽妹妹妹兽', '2019-09-07 10:38:29', '交易成功', '89.3', '1', '山东省');
INSERT INTO `cohort` VALUES ('oerab', '2019-09-07 10:43:29', '交易成功', '16.8', '3', '北京');
INSERT INTO `cohort` VALUES ('人哎呦爱可', '2019-09-07 10:47:53', '交易成功', '97.8', '1', '湖南省');
INSERT INTO `cohort` VALUES ('353yz', '2019-09-07 11:15:11', '交易成功', '97.8', '1', '陕西省');
INSERT INTO `cohort` VALUES ('里养鱼里海', '2019-09-07 11:26:41', '交易成功', '16.8', '3', '江苏省');
INSERT INTO `cohort` VALUES ('002lb', '2019-09-07 11:36:15', '交易成功', '16.9', '1', '广东省');
INSERT INTO `cohort` VALUES ('oured', '2019-09-07 11:51:16', '交易成功', '16.8', '3', '河北省');
INSERT INTO `cohort` VALUES ('236筱陈', '2019-09-07 11:58:15', '交易成功', '16.9', '1', '四川省');
INSERT INTO `cohort` VALUES ('981it', '2019-09-07 12:07:45', '交易成功', '8.4', '1', '上海');
INSERT INTO `cohort` VALUES ('小米子爱钟', '2019-09-07 12:17:41', '交易成功', '97.8', '1', '黑龙江省');
INSERT INTO `cohort` VALUES ('699ve', '2019-09-07 12:20:45', '交易成功', '97.8', '1', '广东省');
INSERT INTO `cohort` VALUES ('刁o刁o刁', '2019-09-07 12:49:11', '交易成功', '33.8', '3', '北京');
INSERT INTO `cohort` VALUES ('wanug', '2019-09-07 12:56:17', '交易成功', '22', '1', '江西省');
INSERT INTO `cohort` VALUES ('012bt', '2019-09-07 13:12:54', '交易成功', '97.8', '1', '广东省');
INSERT INTO `cohort` VALUES ('123柔温', '2019-09-07 13:13:38', '交易成功', '102.9', '1', '甘肃省');
INSERT INTO `cohort` VALUES ('3741y', '2019-09-07 13:22:53', '交易成功', '97.8', '1', '湖北省');
INSERT INTO `cohort` VALUES ('850哗哗', '2019-09-07 13:36:43', '交易成功', '97.8', '1', '广东省');
INSERT INTO `cohort` VALUES ('情殇5一那', '2019-09-07 13:44:37', '交易成功', '97.8', '1', '山东省');
INSERT INTO `cohort` VALUES ('ianac', '2019-09-07 14:11:50', '交易成功', '16.8', '3', '广东省');
INSERT INTO `cohort` VALUES ('董小姐来原', '2019-09-07 14:31:40', '交易成功', '97.8', '1', '上海');
INSERT INTO `cohort` VALUES ('红舞鞋根草', '2019-09-07 14:36:50', '交易成功', '97.8', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('013bt', '2019-09-07 14:38:42', '交易成功', '97.8', '1', '河南省');
INSERT INTO `cohort` VALUES ('985bt', '2019-09-07 14:47:40', '交易成功', '97.8', '1', '北京');
INSERT INTO `cohort` VALUES ('与君绝此从', '2019-09-07 15:03:16', '交易成功', '85', '1', '浙江省');
INSERT INTO `cohort` VALUES ('003tn', '2019-09-07 15:09:29', '交易成功', '16.8', '3', '贵州省');
INSERT INTO `cohort` VALUES ('王尔德行飞', '2019-09-07 15:29:48', '交易成功', '8.4', '1', '福建省');
INSERT INTO `cohort` VALUES ('238ym', '2019-09-07 15:36:18', '交易成功', '97.8', '1', '湖北省');
INSERT INTO `cohort` VALUES ('ue落川忘', '2019-09-07 15:51:28', '交易成功', '97.8', '1', '广东省');
INSERT INTO `cohort` VALUES ('469bt', '2019-09-07 15:54:21', '交易成功', '88.4', '1', '河南省');
INSERT INTO `cohort` VALUES ('987hz', '2019-09-07 16:41:37', '交易成功', '97.8', '1', '江苏省');
INSERT INTO `cohort` VALUES ('俟骏子俟万', '2019-09-07 16:44:45', '交易成功', '97.8', '1', '江苏省');
INSERT INTO `cohort` VALUES ('的王婷酷酷', '2019-09-07 16:56:39', '交易成功', '16.9', '1', '陕西省');
INSERT INTO `cohort` VALUES ('531bt', '2019-09-07 17:11:29', '交易成功', '33.8', '3', '江苏省');
INSERT INTO `cohort` VALUES ('的账号春阿', '2019-09-07 17:47:49', '交易成功', '8.4', '1', '浙江省');
INSERT INTO `cohort` VALUES ('fox拉彼', '2019-09-07 17:50:28', '交易成功', '8.4', '1', '北京');
INSERT INTO `cohort` VALUES ('i21il', '2019-09-07 18:50:36', '交易成功', '16.8', '3', '辽宁省');
INSERT INTO `cohort` VALUES ('向死亡愿我', '2019-09-07 18:59:20', '交易成功', '101.2', '1', '浙江省');
INSERT INTO `cohort` VALUES ('miniw', '2019-09-07 19:08:01', '交易成功', '8.4', '1', '天津');
INSERT INTO `cohort` VALUES ('梦缘宝梦贺', '2019-09-07 19:32:42', '交易成功', '101.2', '1', '广东省');
INSERT INTO `cohort` VALUES ('315ep', '2019-09-07 20:14:05', '交易成功', '22', '1', '安徽省');
INSERT INTO `cohort` VALUES ('1qi21', '2019-09-07 20:31:06', '交易成功', '101.2', '1', '广东省');
INSERT INTO `cohort` VALUES ('的草莓强坚', '2019-09-07 20:54:40', '交易成功', '16.8', '3', '江苏省');
INSERT INTO `cohort` VALUES ('865bt', '2019-09-07 21:53:38', '交易成功', '33.8', '3', '贵州省');
INSERT INTO `cohort` VALUES ('857哥淘', '2019-09-07 22:25:24', '交易成功', '15.2', '1', '江苏省');
INSERT INTO `cohort` VALUES ('736bt', '2019-09-07 22:29:28', '交易成功', '22', '1', '江苏省');
INSERT INTO `cohort` VALUES ('亚洁洁亚郑', '2019-09-07 22:47:02', '交易成功', '85', '1', '安徽省');
INSERT INTO `cohort` VALUES ('en1us', '2019-09-07 23:06:46', '交易成功', '22', '1', '北京');
INSERT INTO `cohort` VALUES ('027eh', '2019-09-07 23:19:33', '交易成功', '16.8', '3', '浙江省');
INSERT INTO `cohort` VALUES ('897bt', '2019-09-07 23:21:26', '交易成功', '16.9', '1', '陕西省');
INSERT INTO `cohort` VALUES ('乐子皮子乐', '2019-09-07 23:55:09', '交易成功', '88.4', '1', '湖南省');
INSERT INTO `cohort` VALUES ('肥硕硕小廖', '2019-09-07 23:55:14', '交易成功', '16.8', '3', '浙江省');
INSERT INTO `cohort` VALUES ('023zl', '2019-09-08 00:42:34', '交易成功', '16.8', '3', '云南省');
INSERT INTO `cohort` VALUES ('711啦巴', '2019-09-08 00:44:28', '交易成功', '97.8', '1', '四川省');
INSERT INTO `cohort` VALUES ('700ht', '2019-09-08 01:07:39', '交易成功', '16.8', '3', '上海');
INSERT INTO `cohort` VALUES ('yxueb', '2019-09-08 01:30:26', '交易成功', '16.8', '3', '上海');
INSERT INTO `cohort` VALUES ('团子酱桃核', '2019-09-08 04:05:46', '交易成功', '16.8', '3', '广西壮族自治区');
INSERT INTO `cohort` VALUES ('13季87', '2019-09-08 07:30:29', '交易成功', '16.8', '3', '江苏省');
INSERT INTO `cohort` VALUES ('的烟火娜娜', '2019-09-08 07:55:41', '交易成功', '16.8', '3', '山东省');
INSERT INTO `cohort` VALUES ('漂流声盆木', '2019-09-08 08:24:18', '交易成功', '16.9', '1', '安徽省');
INSERT INTO `cohort` VALUES ('gioaf', '2019-09-08 09:02:34', '交易成功', '16.8', '3', '上海');
INSERT INTO `cohort` VALUES ('an9ag', '2019-09-08 09:23:11', '交易成功', '32.2', '1', '天津');
INSERT INTO `cohort` VALUES ('628eb', '2019-09-08 09:53:02', '交易成功', '16.8', '3', '广东省');
INSERT INTO `cohort` VALUES ('qaq风借', '2019-09-08 10:02:14', '交易成功', '15.2', '1', '河南省');
INSERT INTO `cohort` VALUES ('924la', '2019-09-08 10:07:36', '交易成功', '16.9', '1', '上海');
INSERT INTO `cohort` VALUES ('的妈咪比呆', '2019-09-08 10:07:54', '交易成功', '85', '1', '浙江省');
INSERT INTO `cohort` VALUES ('8kjej', '2019-09-08 10:33:28', '交易成功', '16.8', '3', '浙江省');
INSERT INTO `cohort` VALUES ('543rf', '2019-09-08 10:40:30', '交易成功', '15.2', '1', '安徽省');
INSERT INTO `cohort` VALUES ('瑟69瑟锦', '2019-09-08 10:57:44', '交易成功', '16.8', '3', '北京');
INSERT INTO `cohort` VALUES ('870先康', '2019-09-08 11:24:59', '交易成功', '101.2', '1', '天津');
INSERT INTO `cohort` VALUES ('姬麒骋姬菲', '2019-09-08 11:49:19', '交易成功', '16.8', '3', '辽宁省');
INSERT INTO `cohort` VALUES ('982_t', '2019-09-08 12:14:26', '交易成功', '8.4', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('欧尼酱欧尹', '2019-09-08 12:29:15', '交易成功', '106.3', '1', '湖北省');
INSERT INTO `cohort` VALUES ('以成夕以沐', '2019-09-08 12:45:05', '交易成功', '16.8', '3', '福建省');
INSERT INTO `cohort` VALUES ('011bt', '2019-09-08 12:49:58', '交易成功', '97.8', '1', '广东省');
INSERT INTO `cohort` VALUES ('108jl', '2019-09-08 12:51:53', '交易成功', '22', '1', '江苏省');
INSERT INTO `cohort` VALUES ('623bt', '2019-09-08 13:11:49', '交易成功', '22', '1', '江苏省');
INSERT INTO `cohort` VALUES ('910bt', '2019-09-08 13:13:15', '交易成功', '22', '1', '江苏省');
INSERT INTO `cohort` VALUES ('ungvt', '2019-09-08 13:40:44', '交易成功', '16.8', '3', '北京');
INSERT INTO `cohort` VALUES ('顾宇帆宇顾', '2019-09-08 14:02:40', '交易成功', '25.4', '1', '江苏省');
INSERT INTO `cohort` VALUES ('043bt', '2019-09-08 14:38:02', '交易成功', '32.2', '1', '浙江省');
INSERT INTO `cohort` VALUES ('轩大爷级超', '2019-09-08 15:13:55', '交易成功', '22', '1', '北京');
INSERT INTO `cohort` VALUES ('萝蜜咻萝菠', '2019-09-08 15:48:58', '交易成功', '8.4', '1', '吉林省');
INSERT INTO `cohort` VALUES ('刘法初法刘', '2019-09-08 16:20:47', '交易成功', '33.8', '3', '广东省');
INSERT INTO `cohort` VALUES ('爱小屋彤恋', '2019-09-08 16:49:46', '交易成功', '15.2', '1', '吉林省');
INSERT INTO `cohort` VALUES ('uowrp', '2019-09-08 17:14:09', '交易成功', '8.4', '1', '北京');
INSERT INTO `cohort` VALUES ('儿11易李', '2019-09-08 17:31:03', '交易成功', '109.7', '1', '河北省');
INSERT INTO `cohort` VALUES ('023琴z', '2019-09-08 18:30:30', '交易成功', '22', '1', '福建省');
INSERT INTO `cohort` VALUES ('派大星绵海', '2019-09-08 18:59:33', '交易成功', '89.3', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('299子包', '2019-09-08 19:08:47', '交易成功', '109.7', '1', '江西省');
INSERT INTO `cohort` VALUES ('_细嗅有心', '2019-09-08 19:19:55', '交易成功', '101.2', '1', '云南省');
INSERT INTO `cohort` VALUES ('wanug', '2019-09-08 19:34:58', '交易成功', '85', '1', '江西省');
INSERT INTO `cohort` VALUES ('024uc', '2019-09-08 20:13:32', '交易成功', '33.8', '3', '江苏省');
INSERT INTO `cohort` VALUES ('anmul', '2019-09-08 20:57:05', '交易成功', '101.2', '1', '湖北省');
INSERT INTO `cohort` VALUES ('wqq51', '2019-09-08 21:13:49', '交易成功', '8.4', '1', '江西省');
INSERT INTO `cohort` VALUES ('251ik', '2019-09-08 21:15:36', '交易成功', '97.8', '1', '广东省');
INSERT INTO `cohort` VALUES ('l33yh', '2019-09-08 21:15:50', '交易成功', '89.3', '1', '江苏省');
INSERT INTO `cohort` VALUES ('hghuy', '2019-09-08 21:35:40', '交易成功', '15.2', '1', '山西省');
INSERT INTO `cohort` VALUES ('rry是我', '2019-09-08 21:59:35', '交易成功', '38.9', '3', '甘肃省');
INSERT INTO `cohort` VALUES ('8786成', '2019-09-08 22:56:19', '交易成功', '8.4', '1', '江苏省');
INSERT INTO `cohort` VALUES ('uck喋喋', '2019-09-08 23:25:15', '交易成功', '33.8', '3', '广西壮族自治区');
INSERT INTO `cohort` VALUES ('我小猪们他', '2019-09-08 23:49:32', '交易成功', '22', '1', '上海');
INSERT INTO `cohort` VALUES ('055bt', '2019-09-08 23:54:58', '交易成功', '93.5', '1', '山东省');
INSERT INTO `cohort` VALUES ('棋两年紫邓', '2019-09-09 00:00:22', '交易成功', '8.4', '1', '河南省');
INSERT INTO `cohort` VALUES ('找崇光找寻', '2019-09-09 00:01:08', '交易成功', '135.9', '2', '海南省');
INSERT INTO `cohort` VALUES ('小忧虑爷二', '2019-09-09 00:02:38', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('e9雯le', '2019-09-09 00:09:09', '交易成功', '97.8', '1', '贵州省');
INSERT INTO `cohort` VALUES ('ky猫wt', '2019-09-09 00:11:12', '交易成功', '16.8', '3', '上海');
INSERT INTO `cohort` VALUES ('get换替', '2019-09-09 00:13:41', '交易成功', '92.7', '1', '江西省');
INSERT INTO `cohort` VALUES ('biu阿明', '2019-09-09 00:22:45', '交易成功', '33.8', '3', '北京');
INSERT INTO `cohort` VALUES ('006甜甜', '2019-09-09 00:22:59', '交易成功', '8.4', '1', '安徽省');
INSERT INTO `cohort` VALUES ('承者的承继', '2019-09-09 00:51:58', '交易成功', '8.4', '1', '云南省');
INSERT INTO `cohort` VALUES ('花皮疍花松', '2019-09-09 00:56:57', '交易成功', '22', '1', '广东省');
INSERT INTO `cohort` VALUES ('眼泪影泪眼', '2019-09-09 01:03:28', '交易成功', '8.4', '1', '安徽省');
INSERT INTO `cohort` VALUES ('isa年刘', '2019-09-09 01:34:59', '交易成功', '88.4', '1', '江苏省');
INSERT INTO `cohort` VALUES ('612公爱', '2019-09-09 01:38:59', '交易成功', '22', '1', '河南省');
INSERT INTO `cohort` VALUES ('555佳李', '2019-09-09 02:44:52', '交易成功', '8.4', '1', '河南省');
INSERT INTO `cohort` VALUES ('兮波波兮朱', '2019-09-09 07:27:48', '交易成功', '22', '1', '四川省');
INSERT INTO `cohort` VALUES ('559博袁', '2019-09-09 07:44:38', '交易成功', '22', '1', '上海');
INSERT INTO `cohort` VALUES ('真不错叫我', '2019-09-09 08:21:19', '交易成功', '22', '1', '河南省');
INSERT INTO `cohort` VALUES ('l08up', '2019-09-09 08:38:56', '交易成功', '109.7', '1', '江苏省');
INSERT INTO `cohort` VALUES ('013bt', '2019-09-09 08:40:00', '交易成功', '97.8', '1', '山东省');
INSERT INTO `cohort` VALUES ('key嘻悠', '2019-09-09 08:55:14', '交易成功', '16.8', '3', '河南省');
INSERT INTO `cohort` VALUES ('233长兵', '2019-09-09 08:56:01', '交易成功', '8.4', '1', '湖北省');
INSERT INTO `cohort` VALUES ('531dh', '2019-09-09 09:17:08', '交易成功', '101.2', '1', '江苏省');
INSERT INTO `cohort` VALUES ('984as', '2019-09-09 09:28:54', '交易成功', '85', '1', '上海');
INSERT INTO `cohort` VALUES ('找崇光找寻', '2019-09-09 09:49:31', '交易成功', '13.5', '1', '海南省');
INSERT INTO `cohort` VALUES ('zmn莉茉', '2019-09-09 10:04:35', '交易成功', '8.4', '1', '河北省');
INSERT INTO `cohort` VALUES ('967bt', '2019-09-09 10:23:42', '交易成功', '15.2', '1', '江苏省');
INSERT INTO `cohort` VALUES ('leeuy', '2019-09-09 10:27:42', '交易成功', '97.8', '1', '上海');
INSERT INTO `cohort` VALUES ('一女子大大', '2019-09-09 11:06:22', '交易成功', '15.2', '1', '湖北省');
INSERT INTO `cohort` VALUES ('120蕾张', '2019-09-09 11:33:58', '交易成功', '186.2', '2', '江苏省');
INSERT INTO `cohort` VALUES ('629ta', '2019-09-09 11:51:25', '交易成功', '97.8', '1', '安徽省');
INSERT INTO `cohort` VALUES ('219文刘', '2019-09-09 12:17:58', '交易成功', '33.8', '3', '广东省');
INSERT INTO `cohort` VALUES ('hjx破小', '2019-09-09 12:56:58', '交易成功', '186.2', '2', '北京');
INSERT INTO `cohort` VALUES ('002jb', '2019-09-09 13:26:06', '交易成功', '8.4', '1', '天津');
INSERT INTO `cohort` VALUES ('孽22爱我', '2019-09-09 13:31:50', '交易成功', '101.2', '1', '河南省');
INSERT INTO `cohort` VALUES ('oyiuh', '2019-09-09 13:44:17', '交易成功', '8.4', '1', '江苏省');
INSERT INTO `cohort` VALUES ('偶麦噶神男', '2019-09-09 13:45:29', '交易成功', '182.8', '2', '江苏省');
INSERT INTO `cohort` VALUES ('999茂房', '2019-09-09 14:05:57', '交易成功', '8.4', '1', '山东省');
INSERT INTO `cohort` VALUES ('啊冰块块冰', '2019-09-09 14:35:31', '交易成功', '16.9', '1', '河北省');
INSERT INTO `cohort` VALUES ('r轻尘陌阡', '2019-09-09 14:40:05', '交易成功', '8.4', '1', '四川省');
INSERT INTO `cohort` VALUES ('依梓呀啊小', '2019-09-09 14:55:38', '交易成功', '101.2', '1', '浙江省');
INSERT INTO `cohort` VALUES ('善良i是我', '2019-09-09 15:09:44', '交易成功', '33.8', '3', '福建省');
INSERT INTO `cohort` VALUES ('999果果', '2019-09-09 15:10:42', '交易成功', '8.4', '1', '四川省');
INSERT INTO `cohort` VALUES ('个字迈要非', '2019-09-09 15:52:04', '交易成功', '33.8', '3', '重庆');
INSERT INTO `cohort` VALUES ('丽五号丽小', '2019-09-09 16:47:18', '交易成功', '8.4', '1', '江苏省');
INSERT INTO `cohort` VALUES ('萌萌哒扯扯', '2019-09-09 17:21:31', '交易成功', '8.4', '1', '云南省');
INSERT INTO `cohort` VALUES ('802hm', '2019-09-09 18:21:54', '交易成功', '8.4', '1', '河南省');
INSERT INTO `cohort` VALUES ('013bt', '2019-09-09 18:23:32', '交易成功', '39', '1', '浙江省');
INSERT INTO `cohort` VALUES ('213劲可', '2019-09-09 18:41:58', '交易成功', '8.4', '1', '吉林省');
INSERT INTO `cohort` VALUES ('周琛喜琛周', '2019-09-09 18:46:53', '交易成功', '8.4', '1', '吉林省');
INSERT INTO `cohort` VALUES ('401镜眼', '2019-09-09 18:55:08', '交易成功', '8.4', '1', '吉林省');
INSERT INTO `cohort` VALUES ('248bt', '2019-09-09 19:05:57', '交易成功', '8.4', '1', '四川省');
INSERT INTO `cohort` VALUES ('红了血寞寂', '2019-09-09 19:16:48', '交易成功', '33.8', '3', '江苏省');
INSERT INTO `cohort` VALUES ('_55bt', '2019-09-09 19:43:33', '交易成功', '92.7', '1', '山东省');
INSERT INTO `cohort` VALUES ('wkj生浮', '2019-09-09 19:55:59', '交易成功', '22', '1', '湖北省');
INSERT INTO `cohort` VALUES ('清22素姜', '2019-09-09 19:59:13', '交易成功', '109.6', '2', '重庆');
INSERT INTO `cohort` VALUES ('雪小柒雪听', '2019-09-09 20:06:21', '交易成功', '22', '1', '四川省');
INSERT INTO `cohort` VALUES ('912嘣嘎', '2019-09-09 20:11:04', '交易成功', '8.4', '1', '吉林省');
INSERT INTO `cohort` VALUES ('198bt', '2019-09-09 20:27:04', '交易成功', '8.4', '1', '广东省');
INSERT INTO `cohort` VALUES ('499bt', '2019-09-09 20:32:49', '交易成功', '8.4', '1', '江苏省');
INSERT INTO `cohort` VALUES ('姐22陌陌', '2019-09-09 21:15:49', '交易成功', '101.2', '1', '安徽省');
INSERT INTO `cohort` VALUES ('只00of', '2019-09-09 21:16:14', '交易成功', '8.4', '1', '江苏省');
INSERT INTO `cohort` VALUES ('pjwqz', '2019-09-09 21:27:35', '交易成功', '16.9', '1', '上海');
INSERT INTO `cohort` VALUES ('710er', '2019-09-09 21:27:43', '交易成功', '16.9', '1', '上海');
INSERT INTO `cohort` VALUES ('695有没', '2019-09-09 21:45:54', '交易成功', '16.9', '1', '上海');
INSERT INTO `cohort` VALUES ('小尼玛哟哎', '2019-09-09 21:50:37', '交易成功', '109.7', '1', '上海');
INSERT INTO `cohort` VALUES ('我爱笑89', '2019-09-09 22:01:49', '交易成功', '22', '1', '湖南省');
INSERT INTO `cohort` VALUES ('725福幸', '2019-09-09 22:04:58', '交易成功', '16.9', '1', '黑龙江省');
INSERT INTO `cohort` VALUES ('834jc', '2019-09-09 22:16:50', '交易成功', '8.4', '1', '广东省');
INSERT INTO `cohort` VALUES ('蔷薇0二0', '2019-09-09 22:17:49', '交易成功', '181.9', '1', '陕西省');
INSERT INTO `cohort` VALUES ('lmela', '2019-09-09 22:31:40', '交易成功', '8.4', '1', '江苏省');
INSERT INTO `cohort` VALUES ('萌兔子只那', '2019-09-09 22:42:43', '交易成功', '189.6', '2', '河南省');
INSERT INTO `cohort` VALUES ('gyaaw', '2019-09-09 22:59:17', '交易成功', '16.8', '3', '湖北省');
INSERT INTO `cohort` VALUES ('344bt', '2019-09-09 23:12:55', '交易成功', '85', '1', '浙江省');
INSERT INTO `cohort` VALUES ('096uh', '2019-09-09 23:28:10', '交易成功', '85', '1', '浙江省');
INSERT INTO `cohort` VALUES ('宝的猫大周', '2019-09-09 23:29:18', '交易成功', '564.4', '1', '四川省');
INSERT INTO `cohort` VALUES ('dzz泽泽', '2019-09-09 23:50:46', '交易成功', '118.2', '1', '山西省');
INSERT INTO `cohort` VALUES ('鲇小鱼小鲇', '2019-09-10 00:40:34', '交易成功', '8.4', '1', '浙江省');
INSERT INTO `cohort` VALUES ('yii李小', '2019-09-10 01:38:12', '交易成功', '101.2', '1', '吉林省');
INSERT INTO `cohort` VALUES ('兔er小北', '2019-09-10 01:39:41', '交易成功', '8.4', '1', '浙江省');
INSERT INTO `cohort` VALUES ('205猫猫', '2019-09-10 06:48:15', '交易成功', '8.4', '1', '安徽省');
INSERT INTO `cohort` VALUES ('dzl有没', '2019-09-10 08:16:05', '交易成功', '92.7', '1', '江苏省');
INSERT INTO `cohort` VALUES ('yuew马', '2019-09-10 08:16:59', '交易成功', '15.2', '1', '河南省');
INSERT INTO `cohort` VALUES ('871bt', '2019-09-10 08:17:03', '交易成功', '8.4', '1', '广东省');
INSERT INTO `cohort` VALUES ('ely2h', '2019-09-10 09:32:34', '交易成功', '33.8', '3', '上海');
INSERT INTO `cohort` VALUES ('巫二胖二巫', '2019-09-10 09:34:36', '交易成功', '97.8', '1', '江苏省');
INSERT INTO `cohort` VALUES ('肖瑞玲lr', '2019-09-10 10:05:10', '交易成功', '85', '1', '广东省');
INSERT INTO `cohort` VALUES ('客与鸠客野', '2019-09-10 10:26:17', '交易成功', '22', '1', '四川省');
INSERT INTO `cohort` VALUES ('e热血ib', '2019-09-10 10:39:11', '交易成功', '101.2', '1', '重庆');
INSERT INTO `cohort` VALUES ('小思思小小', '2019-09-10 10:47:52', '交易成功', '89.3', '1', '广东省');
INSERT INTO `cohort` VALUES ('固笑意之王', '2019-09-10 11:08:01', '交易成功', '16.8', '3', '江苏省');
INSERT INTO `cohort` VALUES ('ssj宋老', '2019-09-10 11:28:37', '交易成功', '22', '1', '北京');
INSERT INTO `cohort` VALUES ('蔡姐姐叫请', '2019-09-10 11:45:46', '交易成功', '16.9', '1', '安徽省');
INSERT INTO `cohort` VALUES ('声军哈声溢', '2019-09-10 12:00:38', '交易成功', '181.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('沫a1允苏', '2019-09-10 12:07:06', '交易成功', '97.8', '1', '陕西省');
INSERT INTO `cohort` VALUES ('llaol', '2019-09-10 12:59:12', '交易成功', '8.4', '1', '上海');
INSERT INTO `cohort` VALUES ('opoeh', '2019-09-10 13:23:08', '交易成功', '97.8', '1', '湖北省');
INSERT INTO `cohort` VALUES ('zgrqm', '2019-09-10 13:33:26', '交易成功', '92.7', '1', '广东省');
INSERT INTO `cohort` VALUES ('p妈妈gz', '2019-09-10 14:01:45', '交易成功', '39', '1', '湖南省');
INSERT INTO `cohort` VALUES ('庄俊知俊庄', '2019-09-10 15:30:46', '交易成功', '101.2', '1', '福建省');
INSERT INTO `cohort` VALUES ('205hz', '2019-09-10 15:44:08', '交易成功', '25.4', '1', '浙江省');
INSERT INTO `cohort` VALUES ('翔最淘翔富', '2019-09-10 15:48:40', '交易成功', '97.8', '1', '广东省');
INSERT INTO `cohort` VALUES ('609bt', '2019-09-10 16:36:56', '交易成功', '33.8', '3', '河北省');
INSERT INTO `cohort` VALUES ('jiaij', '2019-09-10 17:51:55', '交易成功', '16.8', '3', '江苏省');
INSERT INTO `cohort` VALUES ('有个啊个有', '2019-09-10 18:04:26', '交易成功', '16.8', '3', '江苏省');
INSERT INTO `cohort` VALUES ('032bt', '2019-09-10 18:22:53', '交易成功', '189.6', '2', '江苏省');
INSERT INTO `cohort` VALUES ('two45', '2019-09-10 18:29:17', '交易成功', '50.8', '2', '广东省');
INSERT INTO `cohort` VALUES ('winil', '2019-09-10 18:33:06', '交易成功', '8.4', '1', '北京');
INSERT INTO `cohort` VALUES ('961美朱', '2019-09-10 19:01:35', '交易成功', '16.9', '1', '湖南省');
INSERT INTO `cohort` VALUES ('221eh', '2019-09-10 19:05:28', '交易成功', '109.7', '1', '上海');
INSERT INTO `cohort` VALUES ('852bt', '2019-09-10 19:42:22', '交易成功', '55.8', '4', '甘肃省');
INSERT INTO `cohort` VALUES ('ng0eh', '2019-09-10 19:42:48', '交易成功', '8.4', '1', '山西省');
INSERT INTO `cohort` VALUES ('464bt', '2019-09-10 19:53:40', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('042_t', '2019-09-10 20:17:43', '交易成功', '16.9', '1', '上海');
INSERT INTO `cohort` VALUES ('_00bt', '2019-09-10 20:37:18', '交易成功', '8.4', '1', '江苏省');
INSERT INTO `cohort` VALUES ('仙女铭am', '2019-09-10 20:48:31', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('ly静hs', '2019-09-10 21:01:02', '交易成功', '85', '1', '浙江省');
INSERT INTO `cohort` VALUES ('凉安心凉微', '2019-09-10 21:02:34', '交易成功', '22', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('子so子柚', '2019-09-10 21:23:19', '交易成功', '33.8', '3', '陕西省');
INSERT INTO `cohort` VALUES ('7e7na', '2019-09-10 21:23:58', '交易成功', '16.8', '3', '北京');
INSERT INTO `cohort` VALUES ('詹梓钰梓詹', '2019-09-10 21:27:15', '交易成功', '22', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('aby小郑', '2019-09-10 21:30:31', '交易成功', '33.8', '3', '山东省');
INSERT INTO `cohort` VALUES ('t81qq', '2019-09-10 21:44:33', '交易成功', '101.2', '1', '天津');
INSERT INTO `cohort` VALUES ('君不知心丶', '2019-09-10 21:52:58', '交易成功', '97.8', '1', '上海');
INSERT INTO `cohort` VALUES ('aby小郑', '2019-09-10 22:15:06', '交易成功', '16.9', '1', '山东省');
INSERT INTO `cohort` VALUES ('颠xi颠孟', '2019-09-10 22:22:31', '交易成功', '564.4', '1', '江苏省');
INSERT INTO `cohort` VALUES ('王陛下呼直', '2019-09-10 22:48:09', '交易成功', '16.9', '1', '河南省');
INSERT INTO `cohort` VALUES ('g60ah', '2019-09-10 22:55:30', '交易成功', '33.8', '3', '北京');
INSERT INTO `cohort` VALUES ('910af', '2019-09-10 23:09:05', '交易成功', '16.8', '3', '北京');
INSERT INTO `cohort` VALUES ('516xz', '2019-09-10 23:15:02', '交易成功', '15.2', '1', '湖南省');
INSERT INTO `cohort` VALUES ('099mx', '2019-09-10 23:37:29', '交易成功', '13.5', '1', '内蒙古自治区');
INSERT INTO `cohort` VALUES ('管佳梅佳管', '2019-09-10 23:52:34', '交易成功', '85', '1', '浙江省');
INSERT INTO `cohort` VALUES ('爱a萌a爱', '2019-09-11 01:04:01', '交易成功', '33.8', '3', '河南省');
INSERT INTO `cohort` VALUES ('ile木木', '2019-09-11 02:08:53', '交易成功', '8.4', '1', '山西省');
INSERT INTO `cohort` VALUES ('013bt', '2019-09-11 07:27:04', '交易成功', '15.2', '1', '山东省');
INSERT INTO `cohort` VALUES ('179_t', '2019-09-11 07:54:33', '交易成功', '181.9', '1', '广东省');
INSERT INTO `cohort` VALUES ('000ks', '2019-09-11 08:10:23', '交易成功', '16.8', '3', '广东省');
INSERT INTO `cohort` VALUES ('983bt', '2019-09-11 08:52:07', '交易成功', '109.7', '1', '浙江省');
INSERT INTO `cohort` VALUES ('520狐鹿', '2019-09-11 09:05:02', '交易成功', '16.9', '1', '四川省');
INSERT INTO `cohort` VALUES ('365hz', '2019-09-11 09:05:30', '交易成功', '92.7', '1', '陕西省');
源数据表和透视表如下图所示:
分步骤操作代码:
-- 核心思想:透视表 行索引为首次活跃时间,列索引为周期
-- 查看交易类型
SELECT
order_status
FROM cohort
GROUP BY order_status;
-- 步骤一:筛选订单状态为“交易成功”的行,并存到视图
-- 创建成功订单视图
CREATE VIEW 成功订单 AS
SELECT
nick_name
,pay_time
FROM cohort
WHERE order_status = '交易成功';
-- 步骤二:得到每个用户首次下单时间,并创建视图
CREATE VIEW 首单时间 AS
SELECT
nick_name
,pay_time
,MIN(pay_time) over(PARTITION BY nick_name) first_order
FROM 成功订单;
-- 步骤三:计算当前订单和首单月份差
CREATE VIEW 首单月份差 AS
SELECT
nick_name
,DATE_FORMAT(first_order,'%Y年%m月') AS 首单年月
,pay_time
,TIMESTAMPDIFF(MONTH,first_order,pay_time) AS 当前订单和首单时间差 -- 重点:理解TIMESTAMPDIFF函数的使用场景
FROM 首单时间 t
WHERE t.first_order is NOT NULL;
-- 步骤四:去除重复数据,进行分组
CREATE VIEW 留存视图 AS
SELECT
首单年月
,当前订单和首单时间差
,COUNT(DISTINCT t.nick_name) 留存数
FROM 首单月份差 t
GROUP BY 首单年月,当前订单和首单时间差
ORDER BY 首单年月;
-- 创建透视表
SELECT
首单年月
,首月订单量
,SUM(CASE WHEN 当前订单和首单时间差=1 THEN 留存率 ELSE 0 END) '+1月'
,SUM(CASE WHEN 当前订单和首单时间差=2 THEN 留存率 ELSE 0 END) '+2月'
,SUM(CASE WHEN 当前订单和首单时间差=3 THEN 留存率 ELSE 0 END) '+3月'
,SUM(CASE WHEN 当前订单和首单时间差=4 THEN 留存率 ELSE 0 END) '+4月'
,SUM(CASE WHEN 当前订单和首单时间差=5 THEN 留存率 ELSE 0 END) '+5月'
FROM
(SELECT
*
,first_value(t.留存数) over(PARTITION BY 首单年月) 首月订单量
,t.留存数/first_value(t.留存数) over(PARTITION BY 首单年月) 留存率
FROM 留存视图 t) a
GROUP BY 首单年月;
2.炸裂操作问题
所谓炸裂,就是将某一行按照其内容特征进行分裂的过程,可以想象孙悟空从石头里蹦出来时,那块仙石崩开为无数小石块的情形。
创建数据表
DROP TABLE IF EXISTS fresh_food;
CREATE TABLE fresh_food(
user_id VARCHAR(8),
app VARCHAR(30)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;
INSERT INTO
fresh_food (user_id,app)
VALUE ('u001','A,B')
,('u002','C,D,A')
,('u003','E')
,('u004','A')
,('u005','F,D')
,('u006','E,G')
,('u007','C,B')
,('u008','H,J')
,('u009','J')
,('u010','A,K,E');
以上是关于各用户使用A、B等类型APP的数据表,要求:查询App覆盖率(安装了某个App的用户数)。
思路:创建一个序号列,与原数据表进行连接,按照某种规则使源数据炸裂为特定数目的行。
SELECT
n_app
,COUNT(*) App覆盖率
FROM
(SELECT
user_id
,SUBSTRING_INDEX(SUBSTRING_INDEX(app,',',id),',',-1) n_app -- 使用substring_index 或者 substr 进行切割
FROM fresh_food
LEFT JOIN
(SELECT row_number() over() id FROM fresh_food) temp -- 创建序号列
ON id <= LENGTH(REPLACE(app,',',''))) t -- 重点:理解连接条件,根据字符串字符数和序号进行匹配
GROUP BY n_app;
最终查询结果如下
注意点:创建序号列时,要尽量保证序号列行数长度足够炸裂使用,可以通过表连接增加序号数。
3.连续天数问题
常见的连续天数问题比如:用户连续登录最大天数,活动打卡连续时间问题。
Q1:查询用户最大连续登录天数
创建数据表:
DROP TABLE IF EXISTS `temp`;
CREATE TABLE `temp` (
`uid` int NOT NULL,
`dt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`is_sign_in` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `temp` VALUES ('1', '2021-05-01 08:25:36', '1');
INSERT INTO `temp` VALUES ('2', '2021-05-01 00:00:00', '1');
INSERT INTO `temp` VALUES ('3', '2021-05-01 00:00:00', '1');
INSERT INTO `temp` VALUES ('4', '2021-05-01 00:00:00', '1');
INSERT INTO `temp` VALUES ('5', '2021-05-01 00:00:00', '1');
INSERT INTO `temp` VALUES ('1', '2021-05-02 00:00:00', '1');
INSERT INTO `temp` VALUES ('2', '2021-05-02 00:00:00', '1');
INSERT INTO `temp` VALUES ('3', '2021-05-02 00:00:00', '1');
INSERT INTO `temp` VALUES ('4', '2021-05-02 00:00:00', '1');
INSERT INTO `temp` VALUES ('5', '2021-05-02 00:00:00', '1');
INSERT INTO `temp` VALUES ('1', '2021-05-03 00:00:00', '1');
INSERT INTO `temp` VALUES ('2', '2021-05-03 00:00:00', '1');
INSERT INTO `temp` VALUES ('3', '2021-05-03 00:00:00', '1');
INSERT INTO `temp` VALUES ('1', '2021-05-04 00:00:00', '1');
INSERT INTO `temp` VALUES ('2', '2021-05-04 00:00:00', '1');
INSERT INTO `temp` VALUES ('4', '2021-05-05 00:00:00', '1');
INSERT INTO `temp` VALUES ('2', '2021-05-05 00:00:00', '1');
INSERT INTO `temp` VALUES ('3', '2021-05-05 00:00:00', '1');
INSERT INTO `temp` VALUES ('4', '2021-05-05 00:00:00', '1');
INSERT INTO `temp` VALUES ('1', '2021-05-08 00:00:00', '1');
INSERT INTO `temp` VALUES ('2', '2021-05-08 00:00:00', '1');
INSERT INTO `temp` VALUES ('5', '2021-05-08 00:00:00', '1');
INSERT INTO `temp` VALUES ('1', '2021-05-09 00:00:00', '1');
INSERT INTO `temp` VALUES ('4', '2021-05-09 00:00:00', '1');
INSERT INTO `temp` VALUES ('1', '2021-05-10 00:00:00', '1');
INSERT INTO `temp` VALUES ('1', '2021-05-11 00:00:00', '1');
INSERT INTO `temp` VALUES ('3', '2021-05-11 00:00:00', '1');
INSERT INTO `temp` VALUES ('1', '2021-05-01 11:02:24', '1');
数据表如下,包括用户编码,登陆时间,登录状态(都为真)三个字段。
思路:通过用户id分组,再对时间进行排序,统计最大连续天数。连续天数有一个特征,即为公差为1day的等差数列。因此,我们可以创建一个同样公差为1的等差序号列,两者做差连续天数就能够变为相同的值,此时只需要统计相同值的数目,并从中选取最大的数,这就是最大连续天数。
SELECT
uid
,MAX(amo) 最大连续登陆天数
FROM
(SELECT
*
,COUNT(*) amo
FROM
(SELECT
*
,DATE_SUB(ndt,INTERVAL rk DAY) 差值时间
FROM
(SELECT
uid
,SUBSTR(dt,1,10) ndt
,row_number() over(PARTITION BY uid) rk -- 由于同一天不同时间段
FROM temp
GROUP BY uid, SUBSTR(dt,1,10)) a) t
GROUP BY uid,差值时间) tt
GROUP BY uid;
4.复购率和回购率问题
复购率是一个时间窗口的多次消费行为,回购率是两个时间窗口内的消费行为。复购率与回购率越高,反应消费者对品牌的忠诚度就越高,反之则越低
4.1复购率
- 复购率的定义
- 按照用户计算: 在某时间窗口内重复消费用户(消费两次及以上的用户)在总消费用户中占比
- 按照交易计算: 在某时间窗口内重复消费交易次数在总交易次数中占比
创建数据表 costitem
CREATE TABLE `costitem` (
`id` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
`created_at` date DEFAULT NULL,
`cost` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
INSERT INTO `costitem` VALUES ('jack', '2020-01-01', '10');
INSERT INTO `costitem` VALUES ('tony', '2020-01-02', '15');
INSERT INTO `costitem` VALUES ('jack', '2020-02-03', '23');
INSERT INTO `costitem` VALUES ('tony', '2020-01-04', '29');
INSERT INTO `costitem` VALUES ('jack', '2020-01-05', '46');
INSERT INTO `costitem` VALUES ('jack', '2020-04-06', '42');
INSERT INTO `costitem` VALUES ('tony', '2020-01-07', '50');
INSERT INTO `costitem` VALUES ('jack', '2020-01-08', '55');
INSERT INTO `costitem` VALUES ('mart', '2020-04-08', '62');
INSERT INTO `costitem` VALUES ('mart', '2020-04-09', '68');
INSERT INTO `costitem` VALUES ('neil', '2020-05-10', '12');
INSERT INTO `costitem` VALUES ('mart', '2020-04-11', '75');
INSERT INTO `costitem` VALUES ('neil', '2020-06-12', '80');
INSERT INTO `costitem` VALUES ('mart', '2020-04-13', '94');
Q1: 要求计算用户的月复购率,即当前月份购买2次及以上的客户占所有客户比例
思路:1. 计算每个用户每个月购买几次
2. 根据购买次数计算每个用户是否复购
3. 计算每个月有多少个用户复购
实现代码:
SELECT
month
-- ,COUNT(是否复购 = '复购')/COUNT(*) 月复购率
,CONCAT(ROUND(COUNT(是否复购 = '复购')/COUNT(*)*100,2),'%') 月复购率 -- 规定下小数点位数和形式,美化一下
FROM
(SELECT
month
,CASE WHEN 购买次数>1 THEN '复购' ELSE NULL END 是否复购
FROM
(SELECT
id
,DATE_FORMAT(created_at,'%Y年%m月') month -- 创建月份列,并按照月为单位进行分组
,COUNT(*) 购买次数
FROM costitem
GROUP BY id,month) t) t1
GROUP BY month;
查询结果:
4.2回购
- 回购率的定义
- 是某一个时间窗口内消费的用户,在下一个时间窗口仍旧消费的占比
Q1:要求计算每个月份的回购率,仍然使用 costitem 数据表
思路:
1. 计算每个用户每个月的购买记录
2. 错位自连接-后表的购买月要比前表的购买月大1month
3. 按购买月分组统计后表的用户数/前表用户数的比例得到每月的回购率
实现代码
SELECT
month
,COUNT(DISTINCT CASE WHEN cuowei-month =1 THEN id ELSE NULL END)/COUNT(*) 回购率
FROM
(SELECT
id
,SUBSTR(created_at,1,4) year
,SUBSTR(created_at,6,2) month
,lead(SUBSTR(created_at,6,2),1) over(PARTITION BY id) cuowei
FROM costitem
GROUP BY id,year,month
ORDER BY id) t
GROUP BY month;
-- 我们也可以不使用month函数,对时间进行切片处理
SELECT
dt
,COUNT(DISTINCT CASE WHEN 时间差=1 THEN id ELSE NULL END)/COUNT(*) 回购率
FROM
(SELECT
id
,DATE_FORMAT(created_at,'%Y年%m月') dt
,DATE_FORMAT(lead(created_at,1) over(PARTITION BY id ORDER BY created_at),'%Y年%m月') ndt
,TIMESTAMPDIFF(MONTH,CONCAT(SUBSTR(created_at,1,7),'-1'),CONCAT(SUBSTR(lead(created_at,1)
over(PARTITION BY id ORDER BY created_at),1,7),'-1')) 时间差 -- 注意:TIMESTAMPDIFF函数只能识别'0000-00-00'时间格式
FROM costitem
GROUP BY id,DATE_FORMAT(created_at,'%Y年%m月')
ORDER BY id,created_at) t
GROUP BY dt
查询结果
5.累加问题
常见的有:求店铺累计销售额,求单个用户累计消费等。我们可以用窗口函数进行求解
Q1:计算每个用户所有月累计消费金额和最大消费金额
创建数据:这里使用的仍然是costitem 数据表,相关代码见“4.复购率和回购率问题”一节。
实现代码
SELECT
id
,month
,MAX(m) over(PARTITION BY id) 最大消费金额
,SUM(s) over(PARTITION BY id ORDER BY month) 累计消费金额
FROM
(SELECT
id
,MONTH(created_at) month
,SUM(cost) s
,MAX(cost) m
FROM costitem
GROUP BY id,MONTH(created_at)
ORDER BY id,MONTH(created_at)) t
当数据粒度和分组单位不一致时,需要用group by进行聚合
查询结果
Q2:每个用户每个月累计消费金额,最大消费金额
使用costitem 数据表
实现代码:
SELECT
*
,MAX(cumsum) over(PARTITION BY id,MONTH(created_at)) 月最大消费金额
FROM
(SELECT
*
,SUM(cost) over(PARTITION BY id,MONTH(created_at) ORDER BY created_at) cumsum
FROM costitem) t
查询结果
未完待续...