為什么選問題二

個人沒有詳細的看三個題目的具體內容，只是看了三個題目的題目名稱，

最后覺得問題二比較有意思，然后就選擇了問題二進行解答。

問題二

首先來看一下阿里云數據庫SQL挑戰賽賽題二：游戲游玩情況的問題描述，首先有一張表，表名Activity

表中的字段就是以上四個字段，建表語句

CREATE TABLE `Activity` (`player_id` int(11) NOT NULL,`device_id` int(11) NOT NULL,`event_date` date NOT NULL,`games_played` int(11) NOT NULL,PRIMARY KEY (`player_id`,`event_date`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

這張表的業務就是記錄某些游戲的玩家的活動情況。每一行是一個玩家在指定日期的游玩記錄，包含了設備信息，以及總共玩了多少款游戲。那么問題二來了，問：【注冊首周內至少有兩次登錄的玩家占總玩家的比例，四舍五入到小數點后兩位】

準備工作

個人的阿里云賬號已經沒有試用資格了，只能在我自己的本地數據庫測試了，然后下載了為賽題準備的數據集，先在本地數據庫建表

然后通過數據庫連接工具navicat 導入數據，導入數據的具體步驟這里就不再演示了，直接看導入數據后的結果

解決方案

數據導入之后，就可以開始著手分析問題了，根據題目要求，查詢【注冊首周內至少有兩次登錄的玩家占總玩家的比例，四舍五入到小數點后兩位】，那么問題應該分為兩步處理：
1.獲取當前表中總玩家數；
2.獲取當前表中注冊首周內至少有兩次登錄的玩家數；
分析完成之后我們開始按步驟處理，查詢對應的數據。

第一步

首先需要查詢當前表中總玩家數，查詢語句

SELECT COUNT(DISTINCT player_id) FROM Activity;

執行結果

可以看到總玩家數量是1000；

第二步

首先我們先觀察一下數據結構

可以看到相同player_id下event_date默認就是升序排列的，那么我們就不用再單獨進行升序來獲取注冊時間了。下面我們只需要獲取每一個player_id的前兩條記錄，并且比較這兩條記錄的event_date是否在一周內，那么這樣統計出來的數據就是【注冊首周內至少有兩次登錄記錄的玩家數了】。

初步嘗試

考慮到這里需要取每個player_id下的前面兩條記錄，那么我們可以寫sql

SELECT t.player_id,t.event_date FROM Activity t WHERE( SELECT COUNT(*) FROM Activity t1 WHERE t1.player_id = t.player_id AND t1.event_date < t.event_date ) < 2ORDER BY t.player_id;

執行結果如圖

根據執行結果可以看到我們是獲取了每個player_id下面的前面兩條記錄，但是在此基礎上再進行event_date日期的比較在一周內的話難以執行，那么又想了另外一種方案。

再次嘗試

對于上面無法進行event_date日期比較的境況，后來又考慮了一種方向，既然要進行event_date日期字段的比較，那么首先要確保當前player_id下的兩條記錄是在一條記錄上，那么后續通過比較event_date日期字段是否在一周內就可以直接判斷當前player_id是滿足條件的數據了。那么下面就按這個思路來寫sql。

主表自關聯

首先主表自關聯，將后續的時間都挪到上一條記錄的后面，方便后續進行event_date日期字段的比較

SELECT t.player_id,t.event_date,t1.event_date event_date2 FROM Activity tLEFT JOIN Activity t1 ON t.player_id=t1.player_id;

執行結果如圖

這里可以看到我們想要的數據已經出現了，這個時候其實只要GROUP BY t.player_id就可以每個player_id 下獲取一條數據，但是目前的sql獲取的是第一條

1 2015-02-14  2015-02-14

這樣的數據，并不是我們想要的第二條符合要求的數據，那么我們可以排除第一條數據就可以了，改寫sql,同時直接加上GROUP BY t.player_id

SELECT t.player_id,t.event_date,t1.event_date event_date2 FROM Activity tLEFT JOIN Activity t1 ON t.player_id=t1.player_id AND t.event_date != t1.event_date GROUP BY t.player_id;

執行結果如圖

這樣我們就得到了按player_id分組，并且前兩次登錄的時間在同一行數據的結果了，下面只需要對當前數據按照event_date進行比較就可以得到【當前表中注冊首周內至少有兩次登錄的玩家】

查詢滿足條件數據

根據上面的分析我們繼續改寫sql

SELECT * FROM (SELECT t.player_id,t.event_date,t1.event_date event_date2 FROM Activity tLEFT JOIN Activity t1 ON t.player_id=t1.player_id AND t.event_date != t1.event_date GROUP BY t.player_id ) t2 WHERE TIMESTAMPDIFF(DAY,t2.event_date,t2.event_date2) < 7;

執行結果如圖

這里我們就可以看到所有滿足【當前表中注冊首周內至少有兩次登錄的玩家】條件的玩家了，下面統計數量的話把查詢字段的換成COUNT()即可

SELECT COUNT(*) FROM (SELECT t.player_id,t.event_date,t1.event_date event_date2 FROM Activity tLEFT JOIN Activity t1 ON t.player_id=t1.player_id AND t.event_date != t1.event_date GROUP BY t.player_id ) t2 WHERE TIMESTAMPDIFF(DAY,t2.event_date,t2.event_date2) < 7;

執行結果如圖

那么整個問題二到這里也就結束了，問題二的結果就是

-- 0.014SELECT 14/1000;

結果如圖

到這里整個問題二的解答就完工了。

解題感受

最初是因為對這個題的名稱比較敢興趣，后來點進去看了題目詳細內容之后，就更有興趣了，工作中由于工作方向的不同，不太容易遇到類似的場景，因此剛開始解題確實繞路了。這里就不再寫出來了，畢竟繞路不是什么開心的事哈。
為什么覺得這個是經典SQL，過去的業務邏輯，寫sql的話有時候一時想不起來的，基本上沒多久也就寫完了，這次寫SQL，如果對數據結構沒有分析到位的話，還是很容易繞路的，比較有誤導性，當時當你繞到正路上的時候，你再看這個SQL又回覺得特別簡單，沒什么難度。其實這就是寫SQL的樂趣，需要針對需求，結合數據結構深入分析，才能快速完成業務功能，完成之后回頭再看又比較簡單，哈哈。以上就是個人解題感悟，敬請指導。