sql 語言 groupBy 分組統計時間段解決方案

Double-Jin發表於2019-04-04
SQL

記錄一下資料統計時遇到的問題和解決方案

先貼完整的效果
sql語言groupBy分組統計時間段相關解釋方案

問題一 GroupBy資料不是每天連續的

統計圖是關於某段時間內，每年/每月/每週的微博數量的折線圖。資料為連續不斷的，但現實的情況中資料庫裡的資料不可能是連續的，情況如下

sql語言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表
這個時間我們拿到了日期表，用日期表做主表，左聯一下就能解決問題一

sql語言groupBy分組統計時間段相關解釋方案

問題二 使用了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的話程式碼量很多，所以了新建日期表這個方案，如果有好的方案希望大家貼上來，多多交流。

相關文章