|
马上注册,结交更多好友,享用更多功能^_^
您需要 登录 才可以下载或查看,没有账号?立即注册
x
参考链接:https://mp.weixin.qq.com/s?__biz ... =21#wechat_redirect
- USE train;
- #1. 订单的应答率,完单率分别是多少?
- #创建表
- DROP TABLE IF EXISTS 订单信息表;
- CREATE TABLE 订单信息表(
- order_id INT,
- passenger_id INT,
- call_time TIMESTAMP,
- grab_time TIMESTAMP,
- cancel_time TIMESTAMP,
- finish_time TIMESTAMP
- );
- INSERT INTO 订单信息表
- VALUES (70361, 0001, '2018-03-08 09:07:00', '2018-03-03 09:09:00', '1971-01-01 00:00:00', '2018-03-09 09:37:00'),
- (70362, 0001, '2018-03-09 22:07:00', '2018-03-09 22:07:00', '2018-03-09 22:08:00', '1971-01-01 00:00:00'),
- (70363, 0002, '2018-03-09 20:10:00', '1971-01-01 00:00:00', '2018-03-09 20:11:00', '1971-01-01 00:00:00'),
- (70364, 0003, '2018-03-10 08:00:00', '1971-01-01 00:00:00', '1971-01-01 00:00:00', '1971-01-01 00:00:00'),
- (70365, 0004, '2018-03-12 09:00:00', '2018-03-12 09:01:00', '1971-01-01 00:00:00', '2018-03-12 09:30:00');
- #(1)应答率 = 应答/呼叫 = 有效的grab / call
- SELECT COUNT(*) INTO @count
- FROM 订单信息表;
- SELECT COUNT(grab_time) / @count
- FROM 订单信息表
- WHERE grab_time != '1971-01-01 00:00:00';
- #(2)完单率 = 有效finish_time / 呼叫订单数
- SELECT COUNT(finish_time) / @count
- FROM 订单信息表
- WHERE finish_time != '1971-01-01 00:00:00';
- #2. 呼叫应答时间有多长?
- #有效grab_time - call_time的平均minute
- SELECT SUM(TIMESTAMPDIFF(MINUTE, call_time, grab_time)) / COUNT(grab_time) AS 平均呼叫应答时间
- FROM 订单信息表
- WHERE grab_time != '1971-01-01 00:00:00';
- #3.从这一周的数据来看,呼叫量最高的是哪一个小时(当地时间)?呼叫量最少的是哪一个小时(当地时间)?
- #(1)将巴西时间转为中国时间
- UPDATE 订单信息表 SET call_time = CAST(call_time AS DATETIME);
- UPDATE 订单信息表 SET grab_time = CAST(grab_time AS DATETIME);
- UPDATE 订单信息表 SET cancel_time = CAST(cancel_time AS DATETIME);
- UPDATE 订单信息表 SET finish_time = CAST(finish_time AS DATETIME);
- UPDATE 订单信息表 SET call_time = DATE_SUB(call_time, INTERVAL 11 HOUR);
- UPDATE 订单信息表 SET grab_time = DATE_SUB(grab_time, INTERVAL 11 HOUR);
- UPDATE 订单信息表 SET cancel_time = DATE_SUB(cancel_time, INTERVAL 11 HOUR);
- UPDATE 订单信息表 SET finish_time = DATE_SUB(finish_time, INTERVAL 11 HOUR);
- ALTER TABLE 订单信息表 ADD COLUMN call_time_hour VARCHAR(255);
- UPDATE 订单信息表 SET call_time_hour = DATE_FORMAT(call_time, '%H');
- SELECT call_time_hour, COUNT(call_time_hour) AS 总数
- FROM 订单信息表
- GROUP BY call_time_hour
- ORDER BY 总数 DESC
- LIMIT 1;
- #(2)呼叫量最少的是哪一个小时?
- SELECT call_time_hour, COUNT(call_time_hour) AS 总数
- FROM 订单信息表
- GROUP BY call_time_hour
- ORDER BY 总数
- LIMIT 3;
- #4. 呼叫订单第二天继续呼叫的比例有多少?
- #(1)次日呼叫的用户id
- SELECT COUNT(DISTINCT data2.passenger_id) / @count
- FROM 订单信息表 AS data1 LEFT JOIN 订单信息表 AS data2
- ON data1.passenger_id = data2.passenger_id
- AND TIMESTAMPDIFF(DAY, data1.call_time, data2.call_time) = 1;
复制代码 |
|