如何使用MySQL查詢一年中每月的記錄數

2022-09-14 22:12:46

先說結論

SELECT
    tmp.value1 AS `value1`,
    MONTH(SUBSTRING_INDEX(tmp.ct, ',', 1)) AS `month`,
    LENGTH(tmp.ct) - LENGTH(
REPLACE
    (tmp.ct, ',', '')
) + 1 AS `cnt`
FROM
    (
    SELECT
        id,
        value1,
        GROUP_CONCAT(date_value) AS ct
    FROM
        test_year_record
    GROUP BY
        value1,
        INTERVAL(
            date_value,
            DATE(CONCAT('2022', '-01-01')),
            DATE(CONCAT('2022', '-02-01')),
            DATE(CONCAT('2022', '-03-01')),
            DATE(CONCAT('2022', '-04-01')),
            DATE(CONCAT('2022', '-05-01')),
            DATE(CONCAT('2022', '-06-01')),
            DATE(CONCAT('2022', '-07-01')),
            DATE(CONCAT('2022', '-08-01')),
            DATE(CONCAT('2022', '-09-01')),
            DATE(CONCAT('2022', '-10-01')),
            DATE(CONCAT('2022', '-11-01')),
            DATE(CONCAT('2022', '-12-01')),
            DATE(CONCAT('2023', '-01-01'))
        )
) AS tmp
JOIN test_year_record AS ot
ON
    ot.id = tmp.id
WHERE
    ot.value1 = 1 AND YEAR(SUBSTRING_INDEX(tmp.ct, ',', 1)) = '2022'

注：以’2022’為例，上面結論中使用了CONCAT方法進行字串拼接，方便了年份替換，可以直接替換置對應的ORM的引數等。

查詢結果

思路及SQL解釋

這個問題可以劃分為如下幾個子問題，我們可以挨個分析解決：

1. 如何以月份劃分

對於一個月份的資料可以如下判斷：

date_value >= DATE_ADD(date_value, INTERVAL - DAY(date_value) + 1 DAY)
 AND
data_value <= LAST_DAY(data_value)

解釋一下：

DATE_ADD(date_value, INTERVAL - DAY(date_value) + 1 DAY)：data_value所在月的第一天，原理為在data_value的基礎上加上-DAY(data_value)天數再+1，當然也可以使用DATE_SUB或者去YEAR和MONTH資訊再進行拼接；

LAST_DAY(date_value)：data_value所在月的最後一天。

一個月的解決了，那麼多個月的無非就手寫幾個範圍就可以了（x

當然不能手寫這些範圍，一方面是很麻煩而且不好看，另一方面是會給mysql帶來過多的計算量。

那麼如何給12月進行劃分呢：

INTERVAL() 函數可以解決我們的問題：

INTERVAL( N , n 1 , n 2 , ⋯ , n 3 N,n_1,n_2,cdots,n_3 N,n1,n2,⋯,n3)，其中 N N N為帶判斷是資料，後面的 n 1 ∼ n n n_1 sim n_n n1∼nn分別為各個間斷點，這個函數的返回值如下，當 N < n 1 N < n1 N<n1返回0，當 n 1 ≤ N < n 2 n_1 leq N < n_2 n1≤N<n2時返回1，當 n 2 ≤ N < n 3 n_2 leq N < n_3 n2≤N<n3時返回2，…，以此類推。

據此，我們可以給一年做一個分段：

 INTERVAL(
            date_value,
            DATE(CONCAT('2022', '-01-01')), # 一月
            DATE(CONCAT('2022', '-02-01')), # 二月
            DATE(CONCAT('2022', '-03-01')), # 三月
            DATE(CONCAT('2022', '-04-01')), # 四月
            DATE(CONCAT('2022', '-05-01')), # 五月
            DATE(CONCAT('2022', '-06-01')), # 六月
            DATE(CONCAT('2022', '-07-01')), # 七月
            DATE(CONCAT('2022', '-08-01')), # 八月
            DATE(CONCAT('2022', '-09-01')), # 九月
            DATE(CONCAT('2022', '-10-01')), # 十月
            DATE(CONCAT('2022', '-11-01')), # 十一月
            DATE(CONCAT('2022', '-12-01')), # 十二月
            DATE(CONCAT('2023', '-01-01')) # 次年一月，防止次年的資料記錄進當年12月中
        )

注：這裡其實還有個問題，就是結果會返回去年的資料（0），可以像我一樣在外查詢裡面進行一個年份判斷，也可以交給java等檢測。

2.獲取每月資料

可以使用GROUP BY子句，以INTERVAL的值進行分組（為了保證屬於同一個value1的資料，還需要以value1進行分組）。

注：GROUP BY 子句中含有多個引數時，將會是多條這些資料都一樣的記錄分為一組。

僅僅是做了分組是不夠的，我們還需要GROUP_CONCAT()函數來獲取一個分組中的資料集。

執行完當前這步，可以獲取的結果如下：

3.統計每月資料

在ct這一列中，我們獲取的資料是有規律的，比如一個日期中會有兩個"-"、兩個日期之間以",“分隔。

這裡我們選擇以”,"為標誌，統計出有多少個分隔符，再+1就得到了資料的數量。

至於實現方式，可以使用如下方式：

即

LENGTH(tmp.ct) - LENGTH(REPLACE(tmp.ct, ',', '')) + 1

4.統計值與月份相對應

取得GROUP_CONCAT獲取的第一個日期即可代表這一整個資料所在的月份。

可以使用SUBSTRING_INDEX()函數，它有三個引數，第一個引數為待片取的字串、第二個引數為分隔符、第三個引數為第幾個擷取到第幾個分隔符。

如此一來：

SUBSTRING_INDEX(tmp.ct, ',', 1)

便可以取到該日期，再使用MONTH函數即可獲取對應的月份。

5.總體整合

我這裡是使用了一次子查詢，子查詢獲取對應的分組及GROUP_CONCAT資料，再交由外查詢進行處理。

結語

這裡給出的方案僅僅是一種方案，也許存在著其他更快更好的解決方案但我沒有想到，在複雜問題面前一步一步獲取小資料是我習慣，這也就使得很可能出現多個巢狀著的子查詢。

到此這篇關於如何使用MySQL查詢一年中每月的記錄數的文章就介紹到這了,更多相關MySQL查詢每月記錄數內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！