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

糖逗

参考链接：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