記錄一下資料統計時遇到的問題和解決方案
先貼完整的效果
問題一 GroupBy資料不是每天連續的
統計圖是關於某段時間內,每年/每月/每週的微博數量的折線圖。資料為連續不斷的,但現實的情況中資料庫裡的資料不可能是連續的,情況如下
資料中4號 ~ 7號的時間段沒有返回,我們理想中的返回格式是補全沒有的日期,然後在這個日期對應的數量欄位填0。
在網上google了一下,解決方案是新建一張自期表做主表,左聯要統計的表,
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(datelist date); -- 生成一個儲存日期的表,datalist是欄位名
-- 這裡是生成並插入日期資料
INSERT INTO calendar(datelist) SELECT
adddate(
( -- 這裡的起始日期,你可以換成當前日期
DATE_FORMAT("2016-1-1", '%Y-%m-%d')
),
numlist.id
) AS `date`
FROM
(
SELECT
n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000+ n10000.i * 10000 AS id
FROM
num n1
CROSS JOIN num AS n10
CROSS JOIN num AS n100
CROSS JOIN num AS n1000
CROSS JOIN num AS n10000
) AS numlist;
執行sql語句後,請刪除num表
這個時間我們拿到了日期表,用日期表做主表,左聯一下就能解決問題一
問題二 使用了left join,where是針對左表,但左表是日期表,那如何做業務表上的條件限制
原因分析:
資料庫在透過連線兩張或多張表來返回記錄時,都會生成一張中間的臨時表,然後再將這張臨時表返回給使用者;
where條件是在臨時表生成好後,再對臨時表進行過濾的條件;
因此:where 條件加上,已經沒有left join的含義(必須返回左邊表的記錄)了,條件不為真的就全部過濾掉。
解決方案是把限制條件放在on後面
select a.*,b.*
from table1 a
left join table2 b on b.X=a.X and XXX
結論:
where後面:是先連線然生成臨時查詢結果,然後再篩選
on後面:先根據條件過濾篩選,再連 生成臨時查詢結果
結語
水平有限,關於補0的那個問題其實還想到查詢出來用php迴圈補全日期和補0,但是業務上有很多處要用到這個,用php的話程式碼量很多,所以了新建日期表這個方案,如果有好的方案希望大家貼上來,多多交流。
本作品採用《CC 協議》,轉載必須註明作者和本文連結