有一位學生在找數據分析工作的時候，遇到一個筆試題，內容如下：

現有註冊用戶表table_user，有兩個欄位：user_id(用戶id)、reg_tm(註冊時間)。有訂單表table_order,有三個欄位：order_id(訂單號)、order_tm(下單時間)、user_id(用戶id)。

查詢2019年1月1日至今，每天的註冊用戶數，下單用戶數，以及註冊當天即下單的用戶數（請盡量在一個 sql語句中實現）。

題意分析：

1.要查看的是每一天的情況，所以要以日期為維度進行匯總觀測，也就是group by後面跟日期欄位；

2.要看每天的註冊用戶數（來自用戶表），每天的下單用戶數（來自訂單表），所以要將用戶表和訂單表做橫向連接；

3.兩表連接的欄位是什麼？第一感覺應該是user_id，但是我們通過user_id欄位連接兩表後，兩表都有時間欄位，那以哪個欄位為分組依據呢？比如用戶「小包總」在6月10日註冊了網站，在6月20日下了第一筆訂單，以user_id欄位連接兩表，一個user_id對應兩個時間，以註冊時間為分組依據，得不到準確的當日下單用戶數，以下單時間為分組依據，得不到準確的當日註冊用戶數；

4.不能用user_id做連接欄位，需要用用戶表的註冊時間和訂單表的下單時間作為連接欄位。如果兩個表的時間範圍保持一致，那直接做表連接沒有問題，但如果時間範圍不一致，比如用戶表在6月20日沒有註冊量，在訂單表6月20日有多筆訂單，用戶註冊表在6月10日有多位用戶註冊，而訂單表6月10日沒有訂單。而在MySQL裡面只有左連、右連、內連三種連接方式，不管以何種方式做連接，總會丟失部分日期記錄；

5.只有外連才會不丟失日期數據，而MySQL裡面沒有外連方式，這要怎麼辦？我們可以通過union縱向鏈接的方式構造外連一樣的結果；

綜合以上分析，得到解題思路：

1.將註冊表的註冊時間和訂單表的下單時間做縱向鏈接，生成一個臨時表，只有一個欄位 reg_tm：

select reg_tm from table_user

union

select order_tm from table_order;

2.再用上表和註冊表及訂單表做左連接：

select * from(

select reg_tm from table_user

union

select order_tm from table_order) as table_date

left join table_user on table_date.reg_tm=table_user.reg_tm

left join table_order on table_date.reg_tm=table_order.order_tm;

3.題目要求查詢2019年1月1日至今的數據情況，把這個條件加在where後面：

select * from(

select reg_tm from table_user

union

select order_tm from table_order) as table_date

left join table_user on table_date.reg_tm=table_user.reg_tm

left join table_order on table_date.reg_tm=table_order.order_tm

where table_date.reg_tm>="2019-01-01";

4.題目是查看每天的註冊用戶數，下單用戶數，以及註冊當天即下單的用戶數；需要對日期進行分組，註冊用戶數是對註冊表的user_id進行計數，下單用戶數是對訂單表的user_id進行計數，註冊當天即下單的用戶數是對註冊表的註冊時間與訂單表的註冊時間相等的user_id進行計數。需要注意的是，在將臨時表table_date與table_user左連時，對應關係是一對多，生成的結果是一個多表，再與table_order左連，對應關係是多對多，多對多的情況下，數據一定是有重複的，所以需要去重處理(distinct函數)。另外把沒有結果的null替換成0(ifnull函數），最終代碼如下：

select table_date.reg_tm,ifnull(count(distinct table_user.user_id),0) 註冊用戶數,ifnull(count(distinct table_order.user_id),0) 下單用戶數,ifnull(count(distinct if(table_user.reg_time=table_order.order_time and table_user.user_id=table_order.user_id,table_user.user_id,null)),0) 下單用戶數

from(

select reg_tm from table_user

union

select order_tm from table_order) as table_date

left join table_user on table_date.reg_tm=table_user.reg_tm

left join table_order on table_date.reg_tm=table_order.order_tm

where table_date.reg_tm>="2019-01-01"

group by table_date.reg_tm;

題目是沒有數據的，如果直接看看不懂的話，可以自己先構造一個數據，再嘗試文中的代碼，一步一步理解。

內容較多，可以先碼後看，如果對你有幫助，給個贊和關注哈~，如有其它解題思路，歡迎交流~

推薦閱讀：