SQL筆試題｜網(wǎng)約車司機(jī)完單情況分析

◎ 根據(jù)司機(jī)完單表求2017年7月1日-2017年7月31日，有過10天以上的完單并且總完單量在20單以上的司機(jī)id，司機(jī)姓名，司機(jī)完單天數(shù)、司機(jī)完單數(shù)

◎ 根據(jù)司機(jī)信息表(driver_info)和司機(jī)匯總表(driver_collect）取出近2017.07.01-2017.07.31完單大于30單的司機(jī)姓名及電話

司機(jī)完單表 driver_daily

司機(jī)信息表 driver_info

司機(jī)匯總表 driver_collect

數(shù)據(jù)擴(kuò)展

因?yàn)轭}目中給的數(shù)據(jù)樣例比較少，因此給大家擴(kuò)展了些數(shù)據(jù)，方便大家理解與練習(xí)，下圖只截取部分?jǐn)?shù)據(jù)，完整數(shù)據(jù)可以在文末查看。

司機(jī)完單表

司機(jī)匯總表

參考解答

※ 2017年7月1日-2017年7月31日，有過10天以上的完單并且總完單量在20單以上的司機(jī)id，司機(jī)姓名，司機(jī)完單天數(shù)、司機(jī)完單數(shù)

☆ 解析：

① 2017年7月1日-2017年7月31日 -- 可通過WHERE篩選年是2017，月份是7。

② 司機(jī)完單天數(shù)、司機(jī)完單數(shù) -- 先通過司機(jī)ID進(jìn)行聚合，并對完單天數(shù)和完單量進(jìn)行聚合求和。

③ 10天以上的完單并且總完單量在20單以上 -- 聚合后通過HAVING篩選即可。

SELECT driver_id,driver_name,
       COUNT(DISTINCT d_day)完單天數(shù),
       COUNT(DISTINCT order_id)完單數(shù)
FROM driver_daily
WHERE d_year=2017 AND d_month=7
GROUP BY driver_id
HAVING 完單天數(shù) >=10 AND 完單數(shù) >= 20;

☆ 結(jié)果:

※ 近2017.07.01-2017.07.31完單大于30單的司機(jī)姓名及電話

☆ 解析：

① 完單在司機(jī)匯總表，司機(jī)姓名及電話在司機(jī)信息表，因此需要將兩個表鏈接。

② 2017.07.01-2017.07.31 -- 可通過WHERE篩選年是2017，月份是7。

③ 完單大于30單 -- 需要按照司機(jī)ID driver_id?聚合，將訂單ID聚合后計數(shù)，再通過HAVING篩選大于30單的數(shù)據(jù)。

SELECT driver_name,driver_phone,
       COUNT(DISTINCT order_id)
FROM driver_info
LEFT JOIN driver_collect 
ON driver_info.driver_id = driver_collect.driver_id
WHERE d_year=2017 AND d_month=7
GROUP BY driver_info.driver_id
HAVING COUNT(DISTINCT order_id) > 30;

☆ 結(jié)果:

建表與導(dǎo)數(shù)

為方便小伙伴們操作聯(lián)系，數(shù)據(jù)庫建表和導(dǎo)入數(shù)據(jù)代碼給你貼出來了。

# create database STUDIO;
use STUDIO;

create table driver_daily(
driver_id varchar(10),
driver_name varchar(10),
city_id varchar(10),
city_name varchar(10),
order_id varchar(10),
d_year int,
d_month int,
d_day int
);

insert into driver_daily values
('111','王**','32','廈門市','12233',2017,7,1),
('111','王**','32','廈門市','12234',2017,7,1),
('111','王**','32','廈門市','12235',2017,7,1),
('111','王**','32','廈門市','12236',2017,7,1),
('111','王**','32','廈門市','12237',2017,7,1),
('111','王**','32','廈門市','12238',2017,7,1),
('111','王**','32','廈門市','12239',2017,7,1),
('111','王**','32','廈門市','12240',2017,7,1),
('111','王**','32','廈門市','12241',2017,7,1),
('111','王**','32','廈門市','12242',2017,7,1),
('111','王**','32','廈門市','12243',2017,7,1),
('111','王**','32','廈門市','12244',2017,7,1),
('111','王**','32','廈門市','12245',2017,7,1),
('111','王**','32','廈門市','12246',2017,7,1),
('111','王**','32','廈門市','12247',2017,7,1),
('111','王**','32','廈門市','12248',2017,7,1),
('111','王**','32','廈門市','12249',2017,7,1),
('111','王**','32','廈門市','12250',2017,7,1),
('111','王**','32','廈門市','12251',2017,7,1),
('111','王**','32','廈門市','12252',2017,7,1),
('202','林**','32','廈門市','32234',2017,7,1),
('202','林**','32','廈門市','32235',2017,7,1),
('202','林**','32','廈門市','32236',2017,7,2),
('202','林**','32','廈門市','32237',2017,7,2),
('202','林**','32','廈門市','32238',2017,7,3),
('202','林**','32','廈門市','32239',2017,7,3),
('202','林**','32','廈門市','32240',2017,7,4),
('202','林**','32','廈門市','32241',2017,7,4),
('202','林**','32','廈門市','32242',2017,7,5),
('202','林**','32','廈門市','32243',2017,7,5),
('202','林**','32','廈門市','32244',2017,7,6),
('202','林**','32','廈門市','32245',2017,7,6),
('202','林**','32','廈門市','32246',2017,7,7),
('202','林**','32','廈門市','32247',2017,7,7),
('202','林**','32','廈門市','32248',2017,7,7),
('202','林**','32','廈門市','32249',2017,7,8),
('202','林**','32','廈門市','32250',2017,7,8),
('202','林**','32','廈門市','32251',2017,7,8),
('202','林**','32','廈門市','32252',2017,7,9),
('202','林**','32','廈門市','32253',2017,7,9),
('202','林**','32','廈門市','32254',2017,7,10),
('202','林**','32','廈門市','32254',2017,7,11);

create table driver_info(
driver_id varchar(10),
driver_name varchar(10),
driver_phone varchar(20)
);

insert into driver_info values('110','王**','159****4134'),
							  ('111','林**','159****7134'),
                              ('222','張**','159****8134');
                              
create table driver_collect(
driver_id varchar(10),
order_id varchar(10),
d_year int,
d_month int,
d_day int
);

insert into driver_collect values('111','111',2017,7,1),
			         ('222','112',2017,7,1),
                                 ('222','113',2017,7,2),
                                 ('222','114',2017,7,3),
                                 ('222','115',2017,7,4),
                                 ('222','116',2017,7,5),
                                 ('222','117',2017,7,6),
                                 ('222','118',2017,7,7),
                                 ('222','119',2017,7,8),
                                 ('222','120',2017,7,9),
                                 ('222','121',2017,7,10),
                                 ('222','122',2017,7,11),
                                 ('222','123',2017,7,12),
                                 ('222','124',2017,7,13),
                                 ('222','125',2017,7,14),
                                 ('222','126',2017,7,15),
                                 ('222','127',2017,7,16),
                                 ('222','128',2017,7,17),
                                 ('222','129',2017,7,18),
                                 ('222','130',2017,7,19),
                                 ('222','131',2017,7,20),
                                 ('222','132',2017,7,21),
                                 ('222','133',2017,7,22),
                                 ('222','134',2017,7,23),
                                 ('222','135',2017,7,24),
                                 ('222','136',2017,7,25),
                                 ('222','137',2017,7,26),
                                 ('222','138',2017,7,27),
                                 ('222','139',2017,7,28),
                                 ('222','140',2017,7,29),
                                 ('222','141',2017,7,30),
                                 ('222','142',2017,7,31),
                                 ('222','143',2017,9,31);
                                 
select * from driver_daily;
select * from driver_info;
select * from driver_collect;

往期精彩回顧




適合初學(xué)者入門人工智能的路線及資料下載
機(jī)器學(xué)習(xí)及深度學(xué)習(xí)筆記等資料打印
機(jī)器學(xué)習(xí)在線手冊
深度學(xué)習(xí)筆記專輯
《統(tǒng)計學(xué)習(xí)方法》的代碼復(fù)現(xiàn)專輯
AI基礎(chǔ)下載
黃海廣老師《機(jī)器學(xué)習(xí)課程》視頻課
黃海廣老師《機(jī)器學(xué)習(xí)課程》711頁完整版課件
本站qq群955171419，加入微信群請掃碼：
往期精彩回顧




適合初學(xué)者入門人工智能的路線及資料下載
機(jī)器學(xué)習(xí)及深度學(xué)習(xí)筆記等資料打印
機(jī)器學(xué)習(xí)在線手冊
深度學(xué)習(xí)筆記專輯
《統(tǒng)計學(xué)習(xí)方法》的代碼復(fù)現(xiàn)專輯
AI基礎(chǔ)下載
黃海廣老師《機(jī)器學(xué)習(xí)課程》視頻課
黃海廣老師《機(jī)器學(xué)習(xí)課程》711頁完整版課件
本站qq群955171419，加入微信群請掃碼：