当前位置：首页 > article >正文

使用SQL按每小时统计数据的方法

article 2024/11/28 21:45:59

在数据分析和报表生成中，按小时统计数据是一个常见的需求。无论是监控系统的运行状态，还是分析用户行为模式，小时级别的数据统计都能提供细致且有价值的信息。

需要知道

时间戳与时间格式

在SQL数据库中，时间数据通常以时间戳的形式存储。时间戳是一个表示特定时间点的整数，通常以毫秒或秒为单位。例如，1521008160000就是一个13位的毫秒级时间戳。

时间函数

MySQL提供了一系列时间函数，用于处理和转换时间数据。常用的函数包括：

FROM_UNIXTIME()：将Unix时间戳转换为日期时间格式。
DATE_FORMAT()：根据指定的格式显示日期时间数据。
HOUR()：从时间值中提取小时部分。

按小时统计数据的实现方法

简单的时间转换与分组

假设我们有一个名为dspreport的表，其中包含一个名为hourtime的列，存储的是毫秒级时间戳。我们可以使用以下SQL查询按小时分组统计数据：

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');

这个查询首先将毫秒级时间戳转换为秒级时间戳，然后使用FROM_UNIXTIME()函数将其转换为YYYY-MM-DD HH格式的字符串，最后按这个字符串分组并计数。

使用DATE_FORMAT()函数

另一种方法是使用DATE_FORMAT()函数，直接对时间戳进行格式化：

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()函数提供了更多的格式化选项，更加灵活。

示例

示例一：查询某个时间段内各个小时的访客人数

首先我们需要一个表来存储访客的访问记录。这个表至少需要包含两个字段：visit_time（访问时间）和ip（访客的IP地址）。例如：

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

接着写出按小时查询访客人数的sql：

SELECT
  HOUR(visit_time) AS hour,
  COUNT(DISTINCT ip) AS visitor_count
FROM
  visitor_logs
WHERE
  visit_time BETWEEN '开始时间' AND '结束时间'
GROUP BY
  HOUR(visit_time);

如果想要不区分是否为独立访客，只想要得到被访问次数的话，可以将COUNT (DISTINCT ip)改为COUNT(*)
需要注意的是：如果使用的是 PostgreSQL ，可能会因为 PostgreSQL 没有内置的 HOUR 函数而收到错误提示： “function hour(timestamp without time zone) does not exist”，可以换成 EXTRACT 函数来获取时间戳中的小时部分

示例二：查询每小时内新建对话次数

部分数据库表如下图所示：
在这里插入图片描述
我们按照上面的方法，编写sql来查询每小时内新建对话数：

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

运行该sql得出以下结果：
在这里插入图片描述
但是与数据库中的数据做比较后，很容易发现问题：
其中在不同日期但同一时间的数据被归纳到了一起。
为了解决这个问题，我们需要在原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

在这里插入图片描述
之后我们再创建一个结构体来接收得到的数据，就可以啦

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}]