SQL筆試題｜網(wǎng)約車司機(jī)完單情況分析
??點(diǎn)擊關(guān)注｜選擇星標(biāo)｜干貨速遞??
一個(gè)數(shù)據(jù)工作者面試數(shù)據(jù)相關(guān)崗位，SQL查詢語(yǔ)句是必不可少的筆試環(huán)節(jié)，今天給大家?guī)?lái)了某廠一道面試題，附上參考答案，希望能夠幫到大家！
◎ 根據(jù)司機(jī)完單表求2017年7月1日-2017年7月31日，有過(guò)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ī)id | 司機(jī)名稱 | 城市id | 城市名稱 | 訂單id | 年 | 月 | 日 |
|---|---|---|---|---|---|---|---|
| driver_id | driver_name | city_id | city_name | ordre_id | year | month | day |
| 111 | 王** | 32 | 廈門市 | 12233 | 2017 | 7 | 1 |
| 202 | 林** | 32 | 廈門市 | 32234 | 2017 | 9 | 1 |
司機(jī)信息表 driver_info
| driver_id | driver_name | driver_phone |
|---|---|---|
| 110 | 王** | 159*****4134 |
| 111 | 林** | 159*****7134 |
| 222 | 張** | 159*****8134 |
司機(jī)匯總表 driver_collect
| driver_id | order_id | year | month | day |
|---|---|---|---|---|
| 111 | 111 | 2017 | 7 | 1 |
| 222 | 112 | 2017 | 7 | 1 |
數(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日，有過(guò)10天以上的完單并且總完單量在20單以上的司機(jī)id，司機(jī)姓名，司機(jī)完單天數(shù)、司機(jī)完單數(shù)
☆ 解析：
① 2017年7月1日-2017年7月31日 -- 可通過(guò)WHERE篩選年是2017，月份是7。
② 司機(jī)完單天數(shù)、司機(jī)完單數(shù) -- 先通過(guò)司機(jī)ID進(jìn)行聚合，并對(duì)完單天數(shù)和完單量進(jìn)行聚合求和。
③ 10天以上的完單并且總完單量在20單以上 -- 聚合后通過(guò)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é)果:
| driver_id | driver_name | 完單天數(shù) | 完單數(shù) |
|---|---|---|---|
| 202 | 林** | 11 | 21 |
※ 近2017.07.01-2017.07.31完單大于30單的司機(jī)姓名及電話
☆ 解析：
① 完單在司機(jī)匯總表，司機(jī)姓名及電話在司機(jī)信息表，因此需要將兩個(gè)表鏈接。
② 2017.07.01-2017.07.31 -- 可通過(guò)WHERE篩選年是2017，月份是7。
③ 完單大于30單 -- 需要按照司機(jī)ID driver_id?聚合，將訂單ID聚合后計(jì)數(shù)，再通過(guò)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é)果:
| driver_name | driver_phone | count(distinct order_id) |
|---|---|---|
| 張** | 159****8134 | 31 |
建表與導(dǎo)數(shù)
為方便小伙伴們操作聯(lián)系，數(shù)據(jù)庫(kù)建表和導(dǎo)入數(shù)據(jù)代碼給你貼出來(lái)了。
# 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;
評(píng)論
圖片
表情