MySQL的DATE_FORMAT函數(shù)的使用
假設某寶為鼓勵大家雙12買買買,獎勵雙十一那天訂單最多的兩位用戶:分別是用戶1:“剁手皇帝陳哈哈” 和 用戶2:“觸手怪劉大莉” 一人一萬元;
需求1:讓你通過MySQL訂單表,統(tǒng)計一下雙11那天,這兩位每小時的購買訂單成交數(shù);你會怎么寫這條SQL呢?
記得幾年前我剛接觸MySQl,年少輕狂,在得知不用考慮效率的情況下,我寫了個接口循環(huán)二十四遍,發(fā)送24條SQL去查(捂臉),由于那個接口,被技術經理嘲諷~~表示他寫的SQL比我吃的米都多。雖然我們山東人基本不吃米飯,但我還是羞愧不已。。
然后經理通過調用一個DATE_FORMAT函數(shù)分組查詢處理一下,就ok了,效率是我的幾十倍吧。從那時起,我暗自計劃要深入研究SQL技巧。
第二天,由于和朋友開黑打了兩把王者,計劃延后了幾年~
在MySQL中對于處理時間字段,有專門封裝的DATE_FORMAT函數(shù),可以說,DATE_FORMAT函數(shù)可以基本滿足任何時間字段的處理需求。
DATE_FORMAT(date,format) 函數(shù)
參數(shù)解析:
1、date:代表具體時間字段,也可以為now()查詢當前時間;
2、format:DATE_FORMAT將傳來的Date類型數(shù)據(jù)轉為自己需要的格式,如%Y-%m-%d %H:%i:%s會將傳來的Time數(shù)據(jù)轉為"yyyy-MM-dd HH:mm:ss"格式
%Y-%m-%d %H:%i:%s 與 yyyy-MM-dd HH:mm:ss 相對應,也是最常用的格式,這里舉幾個簡單的栗子如下;
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s'); -- 結果:2020-12-07 22:18:58 SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i'); -- 結果:2020-12-07 22:18 SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H'); -- 結果:2020-12-07 22 SELECT DATE_FORMAT(NOW(),'%Y-%m-%d'); -- 結果:2020-12-07 SELECT DATE_FORMAT(NOW(),'%H:%i:%s'); -- 結果:22:18:58 SELECT DATE_FORMAT(NOW(),'%H'); -- 結果:22
對于上面的需求1,用DATE_FORMAT函數(shù)的查詢方式如下:
SELECT name as '用戶名',DATE_FORMAT(createTime,'%Y-%m-%d %H') as '時間/小時',count(*) as '訂單量' from t_order where DATE_FORMAT(createTime,'%Y-%m-%d') = '2020-11-11' GROUP BY DATE_FORMAT(createTime,'%Y-%m-%d %H');
查詢結果:
mysql> SELECT name as '用戶名',DATE_FORMAT(createTime,'%Y-%m-%d %H') as '時間/小時',count(*) as '訂單量' from t_order where DATE_FORMAT(createTime,'%Y-%m-%d') = '2020-11-11' GROUP BY DATE_FORMAT(createTime,'%Y-%m-%d %H'); +------------------+---------------+-----------+ | 用戶名 | 時間/小時 | 訂單量 | +------------------+---------------+-----------+ | 剁手皇帝陳哈哈 | 2020-11-11 00 | 0 | | 剁手皇帝陳哈哈 | 2020-11-11 01 | 10 | | 剁手皇帝陳哈哈 | 2020-11-11 02 | 6 | | 剁手皇帝陳哈哈 | 2020-11-11 03 | 3 | | 剁手皇帝陳哈哈 | 2020-11-11 04 | 0 | | 剁手皇帝陳哈哈 | 2020-11-11 05 | 0 | | 剁手皇帝陳哈哈 | 2020-11-11 06 | 0 | | 剁手皇帝陳哈哈 | 2020-11-11 07 | 0 | | 剁手皇帝陳哈哈 | 2020-11-11 08 | 0 | | 剁手皇帝陳哈哈 | 2020-11-11 09 | 0 | | 剁手皇帝陳哈哈 | 2020-11-11 10 | 0 | | 剁手皇帝陳哈哈 | 2020-11-11 11 | 0 | | 剁手皇帝陳哈哈 | 2020-11-11 12 | 12 | | 剁手皇帝陳哈哈 | 2020-11-11 13 | 6 | | 剁手皇帝陳哈哈 | 2020-11-11 14 | 3 | | 剁手皇帝陳哈哈 | 2020-11-11 15 | 7 | | 剁手皇帝陳哈哈 | 2020-11-11 16 | 2 | | 剁手皇帝陳哈哈 | 2020-11-11 17 | 3 | | 剁手皇帝陳哈哈 | 2020-11-11 18 | 11 | | 剁手皇帝陳哈哈 | 2020-11-11 19 | 2 | | 剁手皇帝陳哈哈 | 2020-11-11 20 | 1 | | 剁手皇帝陳哈哈 | 2020-11-11 21 | 3 | | 剁手皇帝陳哈哈 | 2020-11-11 22 | 2 | | 剁手皇帝陳哈哈 | 2020-11-11 23 | 0 | +------------------+---------------+-----------+ 24 rows in set (0.00 sec)
這里的GROUP BY DATE_FORMAT(createTime,'%Y-%m-%d %H')代表的是“年月日 + 小時”合在一起為單位,具體到的是某天的小時;
當然,也可以寫成以下兩種形式,都是按小時來劃分,但是…:
1、GROUP BY DATE_FORMAT(createTime,'%H')
2、GROUP BY HOUR(createTime)
需要注意的是,當where條件指定了某一天時,這三種的作用是相同的,但如果where條件沒指定某一天,就會大不相同,我們來看看查詢結果;
SELECT name as '用戶名',DATE_FORMAT(createTime,'%H') as '時間/小時',count(*) as '訂單量' from t_order GROUP BY DATE_FORMAT(createTime,'%H');
查詢結果
mysql> SELECT name as '用戶名',DATE_FORMAT(createTime,'%H') as '時間/小時',count(*) as '訂單量' from t_order GROUP BY DATE_FORMAT(createTime,'%H'); +-----------------+---------------+-----------+ | 用戶名 | 時間/小時 | 訂單量 | +-----------------+---------------+-----------+ | 觸手怪劉大莉 | 00 | 11 | | 觸手怪劉大莉 | 01 | 302 | | 觸手怪劉大莉 | 02 | 277 | | 觸手怪劉大莉 | 03 | 122 | | 觸手怪劉大莉 | 04 | 6 | | 觸手怪劉大莉 | 05 | 11 | | 觸手怪劉大莉 | 06 | 0 | | 觸手怪劉大莉 | 07 | 0 | | 觸手怪劉大莉 | 08 | 1 | | 觸手怪劉大莉 | 09 | 4 | | 觸手怪劉大莉 | 10 | 5 | | 觸手怪劉大莉 | 11 | 92 | | 觸手怪劉大莉 | 12 | 1937 | | 觸手怪劉大莉 | 13 | 1602 | | 觸手怪劉大莉 | 14 | 108 | | 觸手怪劉大莉 | 15 | 78 | | 觸手怪劉大莉 | 16 | 110 | | 觸手怪劉大莉 | 17 | 108 | | 觸手怪劉大莉 | 18 | 138 | | 觸手怪劉大莉 | 19 | 66 | | 觸手怪劉大莉 | 20 | 44 | | 觸手怪劉大莉 | 21 | 59 | | 觸手怪劉大莉 | 22 | 21 | | 觸手怪劉大莉 | 23 | 8 | +-----------------+---------------+-----------+ 24 rows in set (0.01 sec)
通過查詢結果可以看出,查出的數(shù)據(jù)是用戶歷史所有的訂單數(shù)在各小時的分布情況,DATE_FORMAT(createTime,'%H')代表的是是任意天的小時,也等價于GROUP BY HOUR(createTime)。
拿到這些數(shù)據(jù),相信寫推薦算法的同學就知道在哪個時間段給“觸手怪劉大莉”同學推廣告的效果最好了。
到此這篇關于MySQL的DATE_FORMAT函數(shù)的使用的文章就介紹到這了,更多相關MySQL DATE_FORMAT內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
相關文章
關于Mysql-connector-java驅動版本問題總結
這篇文章主要介紹了Mysql-connector-java驅動版本問題,本文給大家介紹的很詳細,通過原因說明問題小結個人建議給大家展示的很好,需要的朋友可以參考下2021-06-06MySQL 8.0.18使用clone plugin重建MGR的實現(xiàn)
這篇文章主要介紹了MySQL 8.0.18使用clone plugin重建MGR的實現(xiàn),文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2019-12-12解決MySql版本問題sql_mode=only_full_group_by
這篇文章主要介紹了解決MySql版本問題sql_mode=only_full_group_by,具有很好的參考價值,希望對大家有所幫助。如有錯誤或未考慮完全的地方,望不吝賜教2022-11-11Mysql中批量替換某個字段的部分數(shù)據(jù)(推薦)
這篇文章主要介紹了Mysql中批量替換某個字段的部分數(shù)據(jù),通過實例代碼給大家介紹的非常詳細,具有一定的參考借鑒價值,需要的朋友可以參考下2020-02-02MySQL DATE_ADD和ADDDATE函數(shù)實現(xiàn)向日期添加指定時間間隔
這篇文章主要介紹了MySQL DATE_ADD和ADDDATE函數(shù)實現(xiàn)向日期添加指定時間間隔,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2021-01-01