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

需要知道

時間戳與時間格式

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

時間函數(shù)

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

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

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

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

假設(shè)我們有一個名為dspreport的表，其中包含一個名為hourtime的列，存儲的是毫秒級時間戳。我們可以使用以下SQL查詢按小時分組統(tǒng)計數(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');

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

使用DATE_FORMAT()函數(shù)

另一種方法是使用DATE_FORMAT()函數(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');

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

示例

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

首先我們需要一個表來存儲訪客的訪問記錄。這個表至少需要包含兩個字段：visit_time（訪問時間）和ip（訪客的IP地址）。例如：

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

接著寫出按小時查詢訪客人數(shù)的sql：

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

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

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

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

我們按照上面的方法，編寫sql來查詢每小時內(nèi)新建對話數(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ù)庫中的數(shù)據(jù)做比較后，很容易發(fā)現(xiàn)問題：

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

為了解決這個問題，我們需要在原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

之后我們再創(chuàng)建一個結(jié)構(gòu)體來接收得到的數(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可以是提前定義或從前端獲取等

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

[{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實現(xiàn)按每小時統(tǒng)計數(shù)據(jù)的文章就介紹到這了,更多相關(guān)SQL按小時統(tǒng)計數(shù)據(jù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論