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

Double-Jin發表於2019-04-04

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

先貼完整的效果
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的話程式碼量很多,所以了新建日期表這個方案,如果有好的方案希望大家貼上來,多多交流。

本作品採用《CC 協議》,轉載必須註明作者和本文連結
未經允許禁止轉載 -- 苦力小林,

相關文章