MySQL如何按周統計表中資料

---

微信搜尋“coder-home”或掃一掃下面的二維碼，關注公眾號，第一時間瞭解更多幹貨分享，還有各類視訊教程資源。掃描它，帶走我

---

背景

今天演算法的同事過來問我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執行多次即可產生多條測試資料*/
insert into 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 weekday
from test limit 10;

實驗結果如下所示：

然後我們在基於這個得到的整形的周幾，用日期相加函式data_add()把每一個訂單建立時間改為每一週的週一。使用如下的SQL語句來實現：

select 
	*,
	weekday(create_timestamp) as weekday,
	date_add(create_timestamp, interval - (weekday(create_timestamp)) day) as monday
from test limit 10;

結果截圖如下：

此時我們已經得到每個訂單建立時間所對應的週一這個日期，但是在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 monday
from test limit 10;

結果如下圖所示：

最後，我們就可以使用聚合函式進行統計每週的訂單數了，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 x 
group by monday 
order by monday;

最後的統計結果如下圖所示，這就是我們希望要的結果了。

總結

最後梳理一下思路：根據每一行的建立時間計算出這個時間屬於對應周第幾天，然後用這個建立時間再減去這個的得到的第幾天，就可以把每一個建立時間轉換為每週的週一。這樣就可以把所有屬於同一個周的訂單的建立時間，轉換為了每一個周的週一。最後基於這個每週的週一就可以統計得到最後的每週的訂單數目。

在MySQL中，按周的統計雖然不能直接實現，但是我們可以根據它現有個各種日期型別的函式，稍微轉換變通一下，就可以實現我們平時的需求。所以，在遇到類似的這樣的需求的時候，不要著急，按部就班分析一下，就可以基於現有的函式慢慢的一步步實現最後希望達到的效果。

這個解題的思路不僅僅可以適用於MySQL資料庫，向Oracle資料庫、SQLserver資料庫、postgresql資料庫都可以根據這個思路來實現按周去統計資料。

---

微信搜尋“coder-home”或掃一掃下面的二維碼，關注公眾號，第一時間瞭解更多幹貨分享，還有各類視訊教程資源。掃描它，帶走我

---