MYSQL(面试真题)【滴滴】【多表连接】

糖逗 · 发表于 2020-7-24 18:28:10

马上注册，结交更多好友，享用更多功能^_^

您需要登录才可以下载或查看，没有账号？立即注册

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

复制代码

账号		自动登录	找回密码
密码			立即注册

MYSQL(面试真题)【滴滴】【多表连接】

马上注册，结交更多好友，享用更多功能^_^

本帖被以下淘专辑推荐: