關注我《程序猿集錦》，獲取主頁更多分享。
背景
今天算法的同事過來問我MySQL中是否可以根據周的維度去統計某個指標？比如按周統計訂單的數量。因為數據量比較大，如果直接全部查詢到內存中去統計可能比較慢。所以他系統能夠在SQL層面先做一次聚合統計，維度是按照周去統計。
剛聽到這個需求，我有點蒙。按照周的方式去統計？MySQL中估計沒有這樣的by week的函數。有按照天統計簡單，按照月統計也簡單，我們只要把日期截取一下轉為YYYY-MM的字符串然後再分組聚合就可以了。但是他要按照周來統計，確實有點難度。
但是MySQL這麼成熟的資料庫軟體了，也不能說實現不了吧。說幹就幹，開整。
環境準備
準備建表語句
使用中使用到的表結構如和建表語句如下：
CREATE TABLE `test` (`id` int(11) NOT NULL AUTO_INCREMENT,`order_no` varchar(32) DEFAULT NULL,`create_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
初始化數據語句
測試使用到的初始化數據使用如下的SQL進行初始化：
/*如下SQL執行多次即可產生多條測試數據*/insertinto test(order_no, create_timestamp)value(substr(rand(),3,18),date_add(date_add(date_add(date_add(now(),interval floor(1+rand()*23)hour),interval floor(1+rand()*60)minute),interval floor(1+rand()*60)second),interval-floor(1+rand()*40)day));/*查看表中的數據*/select*from test;
最後的實驗環境
我們的實驗環境如下所示，要求按周統計出訂單的數目。
思路分析
MySQL中沒有自帶的把一個日期給轉換為周的函數。不過，我們可以採用曲線救國的方式來實現。雖然它沒有直接的函數，但是它自帶的日期類型的函數還是很多的。
思路：我們的目的是把每一個訂單創建時間這個欄位的值，設置為一個可以使用的group by的key。這個key要求是根據同一個周的訂單使用同一個時間。所以，我們需要想辦法把屬於同一個周的訂單的創建時間轉換為一個相同的日期，比如把所有屬於同一個周的所有訂單的創建時間，都轉換為每周的周一這個時間。這樣就可以根據周去聚合統計訂單的數目了。
在把所有訂單的創建時間轉換為這個訂單所屬的周的周一時，就要知道這個訂單的創建時間，距離這個周的一查幾天，這樣在這個訂單的創建時間這個值上減去這個天數，就可以得到周一這個時間。
MySQL中有一個函數叫做weekday(x)，其中x就是傳入的一個日期類型的數據，傳入後，返回這個日期是屬於一周當中的第幾天。是一個整形的數據，值的範圍是[0,6]，兩個邊界值分別代表每周的周一和周日。
一周有七天，當我們傳入一個周一的日期給weekday(x)函數的時候，它會返回一個整形的數字0；當我們傳入一個周日的日期的時候，它會返回一個整形的數字6。示例如下：
基於上面的函數weekday(x)，我們可以得到每一個訂單創建的時間屬於一周當中的第幾天。SQL語句如下所示：
select*, weekday(create_timestamp)as weekdayfrom test limit10;
實驗結果如下所示：
然後我們在基於這個得到的整形的周幾，用日期相加函數data_add()把每一個訂單創建時間改為每一周的周一。使用如下的SQL語句來實現：
select*,weekday(create_timestamp)as weekday,date_add(create_timestamp,interval-(weekday(create_timestamp))day)as mondayfrom test limit10;
結果截圖如下：
此時我們已經得到每個訂單創建時間所對應的周一這個日期，但是在monday這一列中還有時分秒，這將會影響我們進行聚合的時候作為key來使用這個欄位，所以，我們需要把時分秒去掉只保留年月日即可，採用date_format(x,'%Y-%m-%d')函數就可以對這個日期進行格式化成我們希望要的格式。SQL語句如下：
select *,weekday(create_timestamp) as weekday,date_format(date_add(create_timestamp, interval - (weekday(create_timestamp) + 1) day),'%Y-%m-%d') as mondayfrom test;
結果如下圖所示：
最後，我們就可以使用聚合函數進行統計每周的訂單數了，SQL語句如下：
select monday, count(1) from (select *, weekday(create_timestamp) as weekday, date_format(date_add(create_timestamp, interval - (weekday(create_timestamp) + 1) day),'%Y-%m-%d') as monday from test) as xgroup by mondayorder by monday;
最後的統計結果如下圖所示，這就是我們希望要的結果了。
總結
最後梳理一下思路：根據每一行的創建時間計算出這個時間屬於對應周第幾天，然後用這個創建時間再減去這個的得到的第幾天，就可以把每一個創建時間轉換為每周的周一。這樣就可以把所有屬於同一個周的訂單的創建時間，轉換為了每一個周的周一。最後基於這個每周的周一就可以統計得到最後的每周的訂單數目。
在MySQL中，按周的統計雖然不能直接實現，但是我們可以根據它現有個各種日期類型的函數，稍微轉換變通一下，就可以實現我們平時的需求。所以，在遇到類似的這樣的需求的時候，不要著急，按部就班分析一下，就可以基於現有的函數慢慢的一步步實現最後希望達到的效果。
這個解題的思路不僅僅可以適用於MySQL資料庫，向Oracle資料庫、SQLserver資料庫、postgresql資料庫都可以根據這個思路來實現按周去統計數據。