圖解面試題：雙11使用者如何分析？

【題目】

現有使用者登入時間表，記錄每個使用者的id，姓名，郵箱位址和使用者最後登入時間。表如下：

問題：生成一張臨時表（表名：使用者登入表），表中呈現四列資料分别為：姓名，最後登入時間，登入時間排名，登入天數排名

要求：

1.“登入時間排名”這一列：按時間給出每個人的登入次數，登入時間最早的為1，之後的分别是2，3，4等。

2.“登入天數排名”這一列：按天給出每個人的登入次數，同一天多次登入認為是同一次，最早标記為1，之後以此類推。

【解題思路】

題目沒看懂，對吧？沒事，使用邏輯樹分析方法，将複雜問題拆解為簡單問題。

看看我們已有的字段有：姓名，最後登入時間，未知的字段 ：登入時間排名，登入天數排名兩列，要求的表格如下圖：

接下來看如何得到這個表裡的每一列。

（1）當“每個”出現的時候你能想到什麼？

就是《猴子 從零學會sql》裡講過的：每個出現的時候，就要想到是分組彙總。

表裡能區分“每個人”的是“姓名”，是以按“姓名”來分組（group by或者視窗函數的partiotion by）。

彙總的要求是“登入次數”（登入時間最早的為1，之後的分别是2，3，4等），這句話翻譯成大白話就是用登入時間來排序，最早登入記1，第二次登入記2 ，依次排序。也就是排名。

因為是排名問題，是以使用分組（視窗函數partiotion by 姓名），并按最後登入時間升序排列(order by最後登入時間 asc)，套入視窗函數的文法，得出下面的sql語句：

select 姓名,最後登入時間,

row_number() over (partition by 姓名
                      order by  最後登入時間 asc) as 登入時間排名           

from 使用者登入表 ;

查詢結果如下：

同一天多次登入認為是同一次，也就是同一天排名相同，我們用test1登入次數舉例，如下圖：

排名問題，要使用視窗函數。因為同一天登入的記錄排名相同，不占用下一名次排名，是以用dense_rank函數，

按姓名分組(partiotion by，并按最後登入天數升序排列(order by，升序asc)。套入視窗函數的文法，得出下面的sql語句：

dense_rank() over (partition by 姓名
                      order by 最後登入時間 asc) as 登入天數排名           

得出查詢結果如下：

發現上面的查詢結果裡“登入天數排名”裡是登入時間，而題目要求是登入天數，是以需要使用date_format(date,format)來轉化為時間格式：

date_format(最新登入時間,’%Y%m%d’)。

套入上面的sql語句中就是：

dense_rank() over (partition by 姓名
                      order by date_format( 最後登入時間,'%Y%m&d' ) asc) as 登入天數排名           

3.将得到的2列合并到題目要求的表中

查詢代碼如下：

row_number() over (partition by 姓名
                      order by 最後登入時間 asc) as 登入時間排名,
   dense_rank() over (partition by 姓名
                      order by date_format( 最後登入時間,'%Y%m&d' ) asc) as 登入天數排名           

【本題考點】

1.考察邏輯思維能力，如何将複雜問題轉化為可以解決的子問題能力。這時候可以使用邏輯樹分析方法。

2.本題本質上是“分組排名”問題，就要想到用視窗函數。

3.考察視窗函數的 dense_rank, row_number的差別和使用。

dense_rank函數，如果有并列名次的行，不占用下一名次的位置（即1，1，2，3）；row_number函數：不考慮并列名次的情況（即1，2，3，4）。

4.考察時間格式的資料如何靈活轉換，用date_format(date,format)根據format的指定的格式顯示date值。

【舉一反三】

下表是雙十一客戶購買清單表。雙十一當天，商家為每位客戶購買的第一個商品免單，請找出商品名稱，客戶姓名及購買金額。

參考答案：

1. 雙十一當天每個客戶（分組）第一個下單的商品（按購買時間來排名），是“分組排名”問題，使用視窗函數來解決。

select 客戶姓名 ,商品名稱 ,購買金額,購買時間,

rank() over (partition by 客戶姓名 
                order by 購買時間 asc ) as 下單順序           

from 雙十一客戶購買清單表;

2.題目要求是商家為每位客戶購買的第一個商品免單，接着上面的查詢結果寫，可以加入一個where子句把“下單順序”值為1，把第1個商品篩選出來。

推薦：如何從零學會sql？