|
马上注册,结交更多好友,享用更多功能^_^
您需要 登录 才可以下载或查看,没有账号?立即注册
x
参考链接:https://mp.weixin.qq.com/s?__biz ... =21#wechat_redirect
- USE train;
- #创建表
- DROP TABLE IF EXISTS 司机数据;
- CREATE TABLE 司机数据(
- 日期 DATE,
- 司机id VARCHAR(255),
- 城市id VARCHAR(255),
- 首次完成订单时间 DATE
- );
- INSERT INTO 司机数据
- VALUES('2020-08-01', '1', '100000', '2016-01-01'),
- ('2020-08-02', '4', '100000', '2016-02-24'),
- ('2020-08-07', '16', '200000', '2016-07-08'),
- ('2020-08-11', '20', '200000', '2020-08-01'),
- ('2020-09-29', '71', '300000', '2020-09-01');
- DROP TABLE IF EXISTS 订单数据;
- CREATE TABLE 订单数据(
- 日期 DATE,
- 订单id VARCHAR(255),
- 司机id VARCHAR(255),
- 乘客id VARCHAR(255),
- 产品线id VARCHAR(255),
- 流水 INT
- );
- INSERT INTO 订单数据
- VALUES('2020-08-01', '1001', '1', '301', '1', 200),
- ('2020-08-01', '1002', '1', '302', '1', 100),
- ('2020-08-04', '1008', '4', '308', '1', 100),
- ('2020-09-01', '1036', '32', '336', '2', 128),
- ('2020-09-09', '1044', '40', '344', '3', 136);
-
- DROP TABLE IF EXISTS 在线时长数据;
- CREATE TABLE 在线时长数据(
- 日期 DATE,
- 司机id VARCHAR(255),
- 在线时长 FLOAT(1)
- );
- INSERT INTO 在线时长数据
- VALUES('2020-08-01', '1', 2),
- ('2020-08-01', '2', 1.1),
- ('2020-08-02', '2', 5),
- ('2020-08-02', '3', 3),
- ('2020-08-03', '5', 4);
-
- DROP TABLE IF EXISTS 城市匹配数据;
- CREATE TABLE 城市匹配数据(
- 城市id VARCHAR(255),
- 城市名称 VARCHAR(255)
- );
- INSERT INTO 城市匹配数据
- VALUES('100000', '北京'),
- ('200000', '上海'),
- ('300000', '天津');
-
- #1.分析出2020年8月各城市每天的司机数、快车订单量和快车流水数据。
- #(1)2020年8月各城市每天的司机数(两表连接)
- SELECT 日期, COUNT(*) AS 司机数
- FROM 城市匹配数据 AS data1 RIGHT JOIN 司机数据 AS data2
- ON data1.城市id = data2.城市id
- WHERE 日期 BETWEEN '2020-08-01' AND '2020-08-31'
- GROUP BY 城市名称, 日期;
- #(2)快车订单(三表连接)
- SELECT data3.城市名称, data2.日期, COUNT(data2.订单id) AS 快车订单数量
- FROM 司机数据 data1
- RIGHT JOIN 订单数据 data2
- ON data1.司机id = data2.司机id
- LEFT JOIN 城市匹配数据 data3
- ON data1.城市id = data3.城市id
- WHERE data2.日期 BETWEEN '2020-08-01' AND '2002-08-31'
- AND data2.产品线id = 3
- GROUP BY data3.城市名称, data2.日期;
- #(3)2020年8月各城市每天的快车流水(三表连接)
- SELECT data3.城市名称, data2.日期, SUM(data2.流水) AS 快车流水
- FROM 司机数据 data1
- RIGHT JOIN 订单数据 data2
- ON data1.司机id = data2.司机id
- LEFT JOIN 城市匹配数据 data3
- ON data1.城市id = data3.城市id
- WHERE data2.日期 BETWEEN '2020-08-01' AND '2002-08-31'
- AND data2.产品线id = 3
- GROUP BY data3.城市名称, data2.日期;
- #2.提取2020年8月和9月,每个月的北京市新老司机(首单日期在当月为新司机)的司机数、在线时长和TPH(订单量/在线时长)数据。
- #新司机
- SELECT DATE_FORMAT(日期, '%Y-%m'), COUNT(*)
- FROM 司机数据
- WHERE (日期 BETWEEN '2020-08-01' AND '2020-08-31'
- OR 日期 BETWEEN '2020-09-01' AND '2020-09-30')
- AND YEAR(日期) = YEAR(首次完成订单时间)
- AND MONTH(日期) = MONTH(首次完成订单时间)
- AND 城市id = '100000'
- GROUP BY DATE_FORMAT(日期, '%Y-%m')
- #老司机
- SELECT DATE_FORMAT(日期, '%Y-%m'), COUNT(*)
- FROM 司机数据
- WHERE (日期 BETWEEN '2020-08-01' AND '2020-08-31'
- OR 日期 BETWEEN '2020-09-01' AND '2020-09-30')
- AND (YEAR(日期) != YEAR(首次完成订单时间)
- OR MONTH(日期) != MONTH(首次完成订单时间))
- AND 城市id = '100000'
- GROUP BY DATE_FORMAT(日期, '%Y-%m')
- #老司机在线时长
- DROP TABLE IF EXISTS temp1;
- CREATE TABLE temp1
- SELECT data1.司机id, DATE_FORMAT(data1.日期, '%Y-%m') AS 日期, IF(ISNULL(SUM(在线时长)), 0, SUM(在线时长)) AS 在线总时长
- FROM((
- SELECT *
- FROM 司机数据
- WHERE (日期 BETWEEN '2020-08-01' AND '2020-08-31'
- OR 日期 BETWEEN '2020-09-01' AND '2020-09-30')
- AND (YEAR(日期) != YEAR(首次完成订单时间)
- OR MONTH(日期) != MONTH(首次完成订单时间))
- AND 城市id = '100000') AS data1 LEFT JOIN 在线时长数据 AS data2
- ON data1.司机id = data2.司机id)
- GROUP BY data1.司机id, DATE_FORMAT(data1.日期, '%Y-%m');
- #在线时长和TPH(订单量/在线时长)
- DROP TABLE IF EXISTS temp2;
- CREATE TABLE temp2
- SELECT data1.司机id, DATE_FORMAT(data1.日期, '%Y-%m') AS 日期, COUNT(data2.订单id) AS 总订单数
- FROM((
- SELECT *
- FROM 司机数据
- WHERE (日期 BETWEEN '2020-08-01' AND '2020-08-31'
- OR 日期 BETWEEN '2020-09-01' AND '2020-09-30')
- AND (YEAR(日期) != YEAR(首次完成订单时间)
- OR MONTH(日期) != MONTH(首次完成订单时间))
- AND 城市id = '100000') AS data1 LEFT JOIN 订单数据 AS data2
- ON data1.司机id = data2.司机id)
- GROUP BY data1.司机id, DATE_FORMAT(data1.日期, '%Y-%m');
- SELECT temp1.司机id, temp1.日期, IF(ISNULL(总订单数 / 在线总时长), 0, 总订单数/在线总时长) AS TPH
- FROM temp1 LEFT JOIN temp2 ON temp1.司机id = temp2.司机id
- AND temp1.日期 = temp2.日期
- #3. 分别提取司机数大于20,司机总在线时长大于2小时,订单量大于1,乘客数大于1的城市名称数据。*****
- SELECT a.司机id, a.乘客数, c.城市名称
- FROM(
- SELECT 司机id, COUNT(乘客id) AS 乘客数
- FROM 订单数据
- GROUP BY 司机id
- HAVING COUNT(乘客id) > 10
- ) AS a
- LEFT JOIN (
- SELECT 司机id, 城市id
- FROM 司机数据)AS b
- ON a.司机id = b.司机id
- JOIN 城市匹配数据 AS c
- ON b.城市id = c.城市id
复制代码 |
|