在數(shù)據(jù)分析和報(bào)表生成中，按小時(shí)統(tǒng)計(jì)數(shù)據(jù)是一個(gè)常見(jiàn)的需求。無(wú)論是監(jiān)控系統(tǒng)的運(yùn)行狀態(tài)，還是分析用戶行為模式，小時(shí)級(jí)別的數(shù)據(jù)統(tǒng)計(jì)都能提供細(xì)致且有價(jià)值的信息。

需要知道

時(shí)間戳與時(shí)間格式

在SQL數(shù)據(jù)庫(kù)中，時(shí)間數(shù)據(jù)通常以時(shí)間戳的形式存儲(chǔ)。時(shí)間戳是一個(gè)表示特定時(shí)間點(diǎn)的整數(shù)，通常以毫秒或秒為單位。例如，1521008160000就是一個(gè)13位的毫秒級(jí)時(shí)間戳。

時(shí)間函數(shù)

MySQL提供了一系列時(shí)間函數(shù)，用于處理和轉(zhuǎn)換時(shí)間數(shù)據(jù)。常用的函數(shù)包括：

• FROM_UNIXTIME()：將Unix時(shí)間戳轉(zhuǎn)換為日期時(shí)間格式。
• DATE_FORMAT()：根據(jù)指定的格式顯示日期時(shí)間數(shù)據(jù)。
• HOUR()：從時(shí)間值中提取小時(shí)部分。

按小時(shí)統(tǒng)計(jì)數(shù)據(jù)的實(shí)現(xiàn)方法

簡(jiǎn)單的時(shí)間轉(zhuǎn)換與分組

假設(shè)我們有一個(gè)名為dspreport的表，其中包含一個(gè)名為hourtime的列，存儲(chǔ)的是毫秒級(jí)時(shí)間戳。我們可以使用以下SQL查詢按小時(shí)分組統(tǒng)計(jì)數(shù)據(jù)：

SELECT 
    FROM_UNIXTIME(hourtime / 1000, '%Y-%m-%d %H') AS hour,
    COUNT(*) AS count
FROM 
    dspreport
GROUP BY 
    FROM_UNIXTIME(hourtime / 1000, '%Y-%m-%d %H');

這個(gè)查詢首先將毫秒級(jí)時(shí)間戳轉(zhuǎn)換為秒級(jí)時(shí)間戳，然后使用FROM_UNIXTIME()函數(shù)將其轉(zhuǎn)換為YYYY-MM-DD HH格式的字符串，最后按這個(gè)字符串分組并計(jì)數(shù)。

使用DATE_FORMAT()函數(shù)

另一種方法是使用DATE_FORMAT()函數(shù)，直接對(duì)時(shí)間戳進(jìn)行格式化：

SELECT 
    DATE_FORMAT(FROM_UNIXTIME(hourtime / 1000), '%Y-%m-%d %H') AS hour,
    COUNT(*) AS count
FROM 
    dspreport
GROUP BY 
    DATE_FORMAT(FROM_UNIXTIME(hourtime / 1000), '%Y-%m-%d %H');

這種方法與第一種方法類(lèi)似，但DATE_FORMAT()函數(shù)提供了更多的格式化選項(xiàng)，更加靈活。

示例

示例一：查詢某個(gè)時(shí)間段內(nèi)各個(gè)小時(shí)的訪客人數(shù)

首先我們需要一個(gè)表來(lái)存儲(chǔ)訪客的訪問(wèn)記錄。這個(gè)表至少需要包含兩個(gè)字段：visit_time（訪問(wèn)時(shí)間）和ip（訪客的IP地址）。例如：

CREATE TABLE visitor_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ip VARCHAR(50) NOT NULL,
  visit_time DATETIME NOT NULL
);

接著寫(xiě)出按小時(shí)查詢?cè)L客人數(shù)的sql：

SELECT
  HOUR(visit_time) AS hour,
  COUNT(DISTINCT ip) AS visitor_count
FROM
  visitor_logs
WHERE
  visit_time BETWEEN '開(kāi)始時(shí)間' AND '結(jié)束時(shí)間'
GROUP BY
  HOUR(visit_time);

如果想要不區(qū)分是否為獨(dú)立訪客，只想要得到被訪問(wèn)次數(shù)的話，可以將COUNT (DISTINCT ip)改為COUNT(*)
需要注意的是：如果使用的是 PostgreSQL ，可能會(huì)因?yàn)?PostgreSQL 沒(méi)有內(nèi)置的 HOUR 函數(shù)而收到錯(cuò)誤提示： “function hour(timestamp without time zone) does not exist”，可以換成 EXTRACT 函數(shù)來(lái)獲取時(shí)間戳中的小時(shí)部分

示例二：查詢每小時(shí)內(nèi)新建對(duì)話次數(shù)

部分?jǐn)?shù)據(jù)庫(kù)表如下圖所示：

我們按照上面的方法，編寫(xiě)sql來(lái)查詢每小時(shí)內(nèi)新建對(duì)話數(shù)：

SELECT EXTRACT(HOUR FROM created_at) as hour, COUNT(*) as conversation_count 
FROM conversations 
WHERE created_at BETWEEN '2024-11-19 07:03:28.09' AND '2024-11-26 07:03:28.09' 
GROUP BY EXTRACT(HOUR FROM created_at) 
ORDER BY hour

運(yùn)行該sql得出以下結(jié)果：

但是與數(shù)據(jù)庫(kù)中的數(shù)據(jù)做比較后，很容易發(fā)現(xiàn)問(wèn)題：

其中在不同日期但同一時(shí)間的數(shù)據(jù)被歸納到了一起。

為了解決這個(gè)問(wèn)題，我們需要在原SQL上做一些“升級(jí)”

SELECT DATE(created_at) as date, EXTRACT(HOUR FROM created_at) as hour, COUNT(*) as conversation_count 
FROM conversations 
WHERE created_at BETWEEN '2024-11-19 07:03:28.09' AND '2024-11-26 07:03:28.09' 
GROUP BY DATE(created_at), EXTRACT(HOUR FROM created_at) 
ORDER BY date, hour

之后我們?cè)賱?chuàng)建一個(gè)結(jié)構(gòu)體來(lái)接收得到的數(shù)據(jù)，就可以啦

type ChartData []struct {
    Date  time.Time `gorm:"column:date"`
    Hour  int       `gorm:"column:hour"`
    Count int       `gorm:"column:count"`
}

???????sql := "SELECT DATE(created_at) as date, EXTRACT(HOUR FROM created_at) as hour, COUNT(*) as conversation_count FROM conversations WHERE created_at BETWEEN '2024-11-19 07:03:28.09' AND '2024-11-26 07:03:28.09' GROUP BY DATE(created_at),EXTRACT(HOUR FROM created_at) ORDER BY date, hour"
datas := ChartData{}
err := db.Raw(sql,StartTime,EndTime)//這里StartTime和EndTime可以是提前定義或從前端獲取等

最終我們可以得到一個(gè)這樣的東西：

[{2024-11-20 00:00:00 +0000 UTC 8 2} 
{2024-11-21 00:00:00 +0000 UTC 8 1} 
{2024-11-21 00:00:00 +0000 UTC 9 2} 
{2024-11-21 00:00:00 +0000 UTC 10 4} 
{2024-11-22 00:00:00 +0000 UTC 9 1}]、

到此這篇關(guān)于使用SQL實(shí)現(xiàn)按每小時(shí)統(tǒng)計(jì)數(shù)據(jù)的文章就介紹到這了,更多相關(guān)SQL按小時(shí)統(tǒng)計(jì)數(shù)據(jù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論