sql中實現日期自動補全的方法:首先生成某個范圍的日歷表數據;然后通過這個日歷表連接查詢需要統計數據表的數據;最后得出這個范圍內完整的數據即可。
最近在開發統計訪問功能時,如果數據表里缺少某一天的數據,就會導致那天的記錄丟失,但顯示的時候還是需要那天的數據,沒有的話,可以默認為 0,大概想到了兩種解決辦法:
1. 將數據讀出來后,在程序循環添加缺少的數據,但是這對于統計數據范圍可變的需求,可能統計 7 天,30 天,60 天;通過程序來補全數據就顯得有點麻煩了。
2. 首先生成某個范圍的日歷表數據,再通過這個日歷表連接查詢需要統計數據表的數據,沒有的話,默認為 0;這樣就可以得出這個范圍內完整的數據了,所以這種方案,更適合這種需求的變化。
現在通過如下 sql 語句生成日歷表:
CREATE?TABLE?num?(i?INT);--?創建一個表用來儲存0-9的數字 INSERT?INTO?num?(i)?VALUES?(0),?(1),?(2),?(3),?(4),?(5),?(6),?(7),?(8),?(9);--?生成0-9的數字,方便以后計算時間 CREATE?TABLE??IF?NOT?EXISTS?Calendar(DATE?DATE);?--?生成一個存儲日期的表,date是字段名 --?這里是生成并插入日期數據 INSERT?INTO?calendar(DATE)?SELECT ????ADDDATE( ????????(???--?起始日期,從2017-1-1開始 ????????????DATE_FORMAT("2017-1-1",?'%Y-%m-%d') ????????), ????????numlist.id ????)?AS?`date` FROM ????( ????????SELECT ????????????n1.i?+?n10.i?*?10?+?n100.i?*?100?+?n1000.i?*?1000?AS?id ????????FROM ????????????num?n1 ????????CROSS?JOIN?num?AS?n10 ????????CROSS?JOIN?num?AS?n100 ????????CROSS?JOIN?num?AS?n1000 ????)?AS?numlist;
執行 sql 后生成 1W 條記錄,從2017-01-01~2044-05-18,這些數據已經足夠用很久了;也可以通過修改相對應的條件,生成更多的數據,這里就不詳細說明了,根據需求修改就行。
執行如下 sql 測試:
SELECT?COUNT(u.id)?AS?num,?c.date?FROM?calendar?AS?c LEFT?JOIN?users?AS?u?ON?c.date?=?u.`created_at` WHERE?c.date?BETWEEN?'2017-05-01'?AND?'2017-05-07' GROUP?BY?c.`date` ORDER?BY?c.`date`
得出如下結果:
? 版權聲明
文章版權歸作者所有,未經允許請勿轉載。
THE END