資料分析面試｜SQL真題持續更新
資料分析面試｜SQL真題持續更新
1.查詢次日留存使用者數、三日留存使用者數、七日留存使用者數、次日留存率、三日留存率、七日留存率
3.給定表1(月份，部門ID，業績)和表2（部門ID，部門名稱），要求查出每個部門一月份,二月份的業績（部門ID，部門名稱，一月份業績，二月份業績）
4.每一行代表了這個訂單的id、完成這個訂單司機的id，這個訂單的金額，訂單完成時間，想要一個sql：如果某一天中，任何一個司機完成了5單及以上，且5單的總金額大於50元，把這天和對應的司機id輸出。
詳細解釋連線：https://www.jb51.net/article/118258.htm
3.Sql四大排名函式 rank、dense rank、row_number、ntile
一、手撕程式碼
1.查詢次日留存使用者數、三日留存使用者數、七日留存使用者數、次日留存率、三日留存率、七日留存率
-- 使用者登錄檔
create table user_info
(
user_id varchar(10) primary key,
reg_time datetime
);
insert into user_info
values ('u_01', '2020-01-01 09:15:00'),
('u_02', '2020-01-01 00:04:00'),
('u_03', '2020-01-01 22:16:00'),
('u_04', '2020-01-01 20:32:00'),
('u_05', '2020-01-01 13:59:00'),
('u_06', '2020-01-01 21:28:00'),
('u_07', '2020-01-01 14:03:00'),
('u_08', '2020-01-01 11:00:00'),
('u_09', '2020-01-01 23:57:00'),
('u_10', '2020-01-01 04:46:00'),
('u_11', '2020-01-02 14:21:00'),
('u_12', '2020-01-02 11:15:00'),
('u_13', '2020-01-02 07:26:00'),
('u_14', '2020-01-02 10:34:00'),
('u_15', '2020-01-02 08:22:00'),
('u_16', '2020-01-02 14:23:00'),
('u_17', '2020-01-03 09:20:00'),
('u_18', '2020-01-03 11:21:00'),
('u_19', '2020-01-03 12:17:00'),
('u_20', '2020-01-03 15:26:00');
-- 登陸日誌表
create table login_log
(
user_id varchar(10),
login_time datetime,
primary key (user_id, login_time)
);
insert into login_log
values ('u_02', '2020-01-02 00:14:00'),
('u_10', '2020-01-02 08:32:00'),
('u_03', '2020-01-02 09:20:00'),
('u_08', '2020-01-02 10:07:00'),
('u_04', '2020-01-02 10:29:00'),
('u_09', '2020-01-02 11:45:00'),
('u_05', '2020-01-02 12:19:00'),
('u_01', '2020-01-02 14:29:00'),
('u_15', '2020-01-03 00:26:00'),
('u_14', '2020-01-03 11:18:00'),
('u_11', '2020-01-03 13:18:00'),
('u_16', '2020-01-03 14:33:00'),
('u_06', '2020-01-04 07:51:00'),
('u_18', '2020-01-04 08:11:00'),
('u_07', '2020-01-04 09:27:00'),
('u_10', '2020-01-04 10:59:00'),
('u_20', '2020-01-04 11:51:00'),
('u_03', '2020-01-04 12:37:00'),
('u_17', '2020-01-04 15:07:00'),
('u_08', '2020-01-04 16:35:00'),
('u_01', '2020-01-04 19:29:00'),
('u_14', '2020-01-05 08:03:00'),
('u_12', '2020-01-05 10:27:00'),
('u_15', '2020-01-05 16:33:00'),
('u_19', '2020-01-06 09:03:00'),
('u_20', '2020-01-06 15:26:00'),
('u_04', '2020-01-08 11:03:00'),
('u_05', '2020-01-08 12:54:00'),
('u_06', '2020-01-08 19:22:00'),
('u_13', '2020-01-09 10:20:00'),
('u_15', '2020-01-09 16:40:00'),
('u_18', '2020-01-10 21:34:00');
-- 查詢次日留存使用者數、三日留存使用者數、七日留存使用者數、次日留存率、三日留存率、七日留存率
select date(ui.reg_time) dt, '日期',
count(distinct ui.user_id) 'DNU',
sum(datediff(ll.login_time,ui.reg_time)=1) as '次日留存使用者數',
sum(datediff(ll.login_time,ui.reg_time)=3) as '三日留存使用者數',
sum(datediff(ll.login_time,ui.reg_time)=7) as '七日留存使用者數',
concat(round(sum(datediff(ll.login_time,ui.reg_time)=1) / count(distinct ui.user_id)*100,2) ,'%')'次日留存率',
concat(round(sum(datediff(ll.login_time,ui.reg_time)=3) / count(distinct ui.user_id)*100,2) ,'%') '三日留存率',
concat(round(sum(datediff(ll.login_time,ui.reg_time)=7) / count(distinct ui.user_id)*100,2) ,'%') '七日留存率'
from user_info ui
left join login_log ll on ui.user_id = ll.user_id
group by date(ui.reg_time);注意事項：
1.原時間資料型別是datetime，包含了年月日時分秒，在分組時需要將其轉換為date格式進行分組。
2.datediff返回兩個日期之間的天數（只有值的日期部分參與計算，而且是前減後的日期天數）。
3.sum忽略Null對資料求和，count忽略Null對資料計數（0也會被計數）。
4.round函式用於把數值欄位四舍五日為指定的小數位數。先把小數乘以100，然後round函式保留兩位小數
5.concat函式對資料新增百分號，concat_ws（分隔符,字串,...,字串），兩個函式若有任意引數為Null，則整體結果為Null！
2.使用者消費週期間隔
3.給定表1(月份，部門ID，業績)和表2（部門ID，部門名稱），要求查出每個部門一月份,二月份的業績（部門ID，部門名稱，一月份業績，二月份業績）
查詢結果如圖所示：
-- 建表
create table table1
(
month varchar(14),
dept int(4),
sale int(4)
);
insert into table1
values ('一月份', 1, 10);
insert into table1
values ('一月份', 2, 10);
insert into table1
values ('一月份', 3, 5);
insert into table1
values ('二月份', 2, 8);
insert into table1
values ('二月份', 4, 9);
insert into table1
values ('二月份', 3, 8);
create table table2
(
dept int(4),
dname varchar(30)
);
insert into table2
values (1, '國內業務一部');
insert into table2
values (2, '國內業務二部');
insert into table2
values (3, '國內業務三部');
insert into table2
values (4, '國際業務部');
-- 要求查出每個部門一月份,二月份的業績
select t2.dept,t2.dname,
sum(case when month='一月份' then t1.sale end) as '一月份業績',
sum(case when month='二月份' then t1.sale end) as '二月份業績'
from table2 t2 left join table1 t1 on t2.dept = t1.dept
group by t2.dept, t2.dname
order by t2.dept
4.每一行代表了這個訂單的id、完成這個訂單司機的id，這個訂單的金額，訂單完成時間，想要一個sql：如果某一天中，任何一個司機完成了5單及以上，且5單的總金額大於50元，把這天和對應的司機id輸出。
select date,Driver_id 'driver_id'
from table
group by date,Driver_id
having count(*)>=5 and sum(Amount)>50
5.每一行代表這個使用者是否在當天活躍過，如果一個使用者在當天活躍過，且在未來的第2到第30天又活躍過，則稱其為當天的活躍30天留存使用者，比如表中a和b使用者都在2019/1/1活躍了，a在2019/1/3活躍了，所以滿足在2019/1/1的活躍30天留存條件，b沒有在第2到第30天活躍過，因此不滿足活躍30天留存。我想要每天的活躍使用者數和活躍30天留存使用者數
create table Active
(
User_id varchar(10),
date date
);
insert into Active
values ('a', '2019/01/01'),
('a', '2019/01/03'),
('b', '2019/01/01'),
('b', '2019/05/01');
select a1.date as 'Date',
count(distinct a1.User_id) '活躍使用者數',
sum(case when datediff(a2.date, a1.date) < 30 and datediff(a2.date, a1.date) > 0 then 1 else 0 end) '活躍30天留存使用者數'
from Active a1
inner join Active a2 on a1.User_id = a2.User_id
group by a1.date;
6.每行代表司機開始玩遊戲的時間（start_time）和遊戲結束時間（end_time），請問每個司機結束一場遊戲後，平均多久時間內會開始下場遊戲？如果司機只玩過一次遊戲，就不計算該司機。(使用者平均消費間隔)
create table Driver_game
(
Driver_id varchar(10),
Start_time datetime,
end_time datetime
);
insert into Driver_game
values ('A', '2019/01/01 00:00:00', '2019/01/01 00:20:00'),
('A', '2019/01/01 01:15:00', '2019/01/01 01:30:00'),
('A', '2019/01/01 02:00:00', '2019/01/01 02:15:00'),
('B', '2019/01/01 01:15:00', '2019/01/01 01:30:00');
select Driver_id,avg(TimeStampDiff(minute,end_time,next_start_time)) as '遊戲平均間隔（分鐘）'
from(
select *,lead(start_time) over(partition by driver_id order by start_time)as next_start_time
from Driver_game
)as t1
where next_start_time is not null
group by Driver_id;注意：
1.lead和lag函式，這兩個函式一般用於計算差值，最適用的場景是計算花費時間。舉個例子，有資料是每個使用者瀏覽網頁的時間記錄，將記錄的時間錯位之後，進行兩列相減就可以得到每個使用者瀏覽每個網頁實際花費的時間。lead是用於統計視窗內往下第n行值，lag是用於統計視窗內往上第n行值。
2.MySql計算兩日期時間之間相差的天數,秒數,分鐘數,週數,小時數可以使用TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)，其中unit可以為
FRAC_SECOND 表示間隔是毫秒、SECOND 秒、MINUTE 分鐘、HOUR 小時、DAY 天、WEEK 星期、MONTH 月、QUARTER 季度、YEAR 年
二、理論知識
1.on作為篩選條件，和where有什麼區別？
答：sql的執行順序 on>join>where，先通過on條件生成臨時表，再進行join操作，若是outer join 則on無法對outter join的主表進行條件篩選（這裡說的主表就是left join的左表，right join的右表），而where在join之後，對整張表進行篩選。
【細節】：sql中的連線查詢分為3種， cross join，inner join和outer join。on篩選和where篩選的差別只是針對outer join，也就是平時最常使用的left join和right join。因為outer join 的執行過程分為4步1、先對兩個表執行交叉連線(笛卡爾積)2、應用on篩選器3、新增外部行（outer join）4、應用where篩選器。因此，以left jion為例，在使用left jion時，on和where條件的區別如下：
1、 on條件是在生成臨時表時使用的條件，它不管on中的條件是否為真，都會返回左邊表中的記錄。（先on連線生成臨時表，再outter join新增外部行）
2、where條件是在臨時表生成好後，再對臨時表進行過濾的條件。這時已經沒有left join的含義了，條件不為真的就全部過濾掉。
詳細解釋連線：https://www.jb51.net/article/118258.htm
2.sql怎麼避免笛卡爾積
3.Sql四大排名函式 rank、dense rank、row_number、ntile
- row_number：對select查詢到的資料排序，每一條資料加一個序號。不能用做學生成績排名，一般多用於分頁查詢。
- rank：排名函式，相同成績排名一樣，排名跳躍。
- dense_rank：排名函式，排名連續。
- NTILE(group_num)：將所有記錄分成group_num個組，每組序號一樣（注意：三條資料分 2個區，不可能平均分配。則優先分配較小的區）
4.count()
- count(*)：返回select語句返回結果集的行數，包含Null值和非Null值的所有行。如果用count(*)函式對錶中數字進行計數，而不使用Where子句選擇其他列，則其執行速度非常塊（該優化僅適用MyISAM表，因為MyISAM表的行數儲存在
information_schema資料庫的tables表的table_rows列中，MySQL可以很快地檢索它） - count(1)：和count(*)結果一樣，包含Null值和非Null值的所有行
- count(column)：返回不包含
NULL值的所有行數 - count(distinct column)：返回不包含
NULL值的唯一行數 - count(expression)：返回不包含
NULL值的行數，expression 是表示式，意思是在count中我們可以得到符合特點條件的記錄數。
效率對比：count(1)和count(*)
- 如果表沒有主鍵，那麼count（1）比count（*）快；
- 如果有主鍵，那麼count（主鍵，聯合主鍵）比count（*）快；
- 如果表只有一個欄位，count（*）最快。
- count(1)跟count(主鍵)一樣，只掃描主鍵。count(*)跟count(非主鍵)一樣，掃描整個表。明顯前者更快一些。
效能問題：
- 任何情況下SELECT COUNT(*) FROM tablename是最優選擇(指沒有where的情況）；
- 儘量減少SELECT COUNT(*) FROM tablename WHERE COL = ‘value’ 這種查詢；
- 杜絕SELECT COUNT(COL) FROM tablename WHERE COL2 = ‘value’ 的出現。
以下排行是按照效率，而不是時間
count（*） > count（1） > count（id） > count（欄位）
5.怎麼判斷你寫的sql能不能執行，效率如何
6. sql 視窗函式 執行順序
7.sql中的左連線、右連結、內連線和全連線
內連線：組合兩個表中的記錄，返回關聯欄位相符的記錄，也就是返回兩個表的交集（陰影）部分。
左連線：左(外)連線，左表(a_table)的記錄將會全部表示出來，而右表(b_table)只會顯示符合搜尋條件的記錄。右表記錄不足的地方均為NULL。
右連線：右(外)連線，左表(a_table)只會顯示符合搜尋條件的記錄，而右表(b_table)的記錄將會全部表示出來。左表記錄不足的地方均為NULL。
全連線：union/union all
union語句注意事項：
1.通過union連線的SQL它們分別單獨取出的列數必須相同；
2.不要求合併的表列名稱相同，以第一個sql 表列名為準；
3.使用union 時，完全相等的行，將會被合併，由於合併比較耗時，一般不直接使用 union 進行合併，而是通常採用union all 進行合併；
4.被union 連線的sql 子句，單個子句中不用寫order by ，因為不會有排序的效果。但可以對最終的結果集進行排序；
(select id,name from A order by id) union all (select id,name from B order by id); //沒有排序效果
(select id,name from A ) union all (select id,name from B ) order by id; //有排序效果
練習：m 表(即Match表) 的 hostTeamID 與 guestTeamID 都與 t 表(即Team表) 中的 teamID 關聯。請查出 2006-6-1 到2006-7-1之間舉行的所有比賽，並且用以下形式列出： 拜仁 2：0 不來梅 2006-6-21.
select t2.teamName, a.matchResult, a.teamName, a.matchTime
from t t2
right join (
select *
from m m1
left join t t1 on m1.guestTeamID = t1.teamID) a on t2.teamID = a.hostTeamID
where a.matime between '2006-6-1' and '2006-7-1'相關文章
- AI面試題（持續更新）AI面試題
- Hbase面試題(持續更新)面試題
- Spring面試題（持續更新中）Spring面試題
- 前端面試問題（持續更新）前端面試
- 前端面試問題二（持續更新）前端面試
- PHP面試題總結-持續更新中PHP面試題
- GO面試題集錦快答[持續更新]Go面試題
- 【前端面試】Vue面試題總結(持續更新中)前端Vue面試題
- 前端面試題總結——HTML(持續更新中)前端面試題HTML
- 前端面試題總結——綜合問題(持續更新中)前端面試題
- sql語句小技巧-持續更新SQL
- 整理有關面試普遍問題和回答技巧 (持續更新～)面試
- 前端面試的一些題目(持續更新中)前端面試
- 2019 Vue 面試題彙總（持續更新中...）Vue面試題
- 前端面試題總結——Html5（持續更新中）前端面試題HTML
- 前端面試經典題目彙總（持續更新中）前端面試
- Flutter 問題集，持續更新Flutter
- Deep learning 資料彙總--持續更新
- 京東前端二面高頻手寫面試題（持續更新中）前端面試題
- 【C++】C++常見面試題彙總，持續更新中…C++面試題
- 國產資料庫考試資料彙總（持續更新）資料庫
- leetcode題解【持續更新】LeetCode
- javaScript 習題總結(持續更新)JavaScript
- Flutter資源收集（持續更新）Flutter
- 資料分析中常用小函式彙總【持續更新，個人筆記。。。】函式筆記
- 鴻蒙OS學習資料整理，持續更新中鴻蒙
- Kotlin學習資料彙總(持續更新...)Kotlin
- 總結Java開發面試常問的問題，持續更新中~Java面試
- mysql sql 中實戰小技巧持續更新MySql
- 常用的Sqlite SQL語句（持續更新中）SQLite
- JVM（持續更新。。。）JVM
- FastApi持續更新ASTAPI
- 持續輸出面試題之Nginx面試題Nginx
- 前端演算法類面試總結(持續更新...)前端演算法面試
- 前端工程師面試必備（持續更新中）前端工程師面試
- 面試心得與總結-——答案整理_4 持續更新面試
- 劍指Offer——面試小提示（持續更新中）面試
- 元件形式來分析 Laravel 思想 持續更新中元件Laravel