sql—使用mysql查询定期统计具有时间范围的记录

4uqofj5v 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(345)

给定一个mysql表来跟踪一个访问者在一个位置花了多长时间，一天中每隔5分钟计算一次总访问者的合适查询是什么？

+-----------------+------------------+------+-----+---------+-------+
| Field           | Type             | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+-------+
| end_timestamp   | int(10) unsigned | NO   | PRI | NULL    |       |
| start_timestamp | int(10) unsigned | NO   | PRI | NULL    |       |
| visitor_id      | int(10) unsigned | NO   | PRI | NULL    |       |
| location_id     | int(10) unsigned | NO   | PRI | NULL    |       |
+-----------------+------------------+------+-----+---------+-------+

e、 g.结果可能如下：

+---------------------+-------------------+
| Timestamp           | COUNT(visitor_id) |
+---------------------+-------------------+
| 2020-01-01 00:00:00 | 45                |
| 2020-01-01 00:05:00 | 49                |
| 2020-01-01 00:10:00 | 37                |
...

这是我目前正在计算的post query，但希望通过将其作为数据库查询的一部分来将部分工作转移到mysql服务器上。

sql mysql Date count recursive-query

来源：https://stackoverflow.com/questions/62033330/using-a-mysql-query-to-count-records-with-time-ranges-at-regular-intervals

1条答案

按热度按时间

7gs2gvoe1#

如果您运行的是mysql 8.0，那么可以使用递归查询来生成间隔，然后使用 left join ，最后聚合。
下面的查询为您提供了当天所需的信息（您可以更改 current_date 根据需要安排其他日期）：

with all_ts as (
    select current_date ts
    union all
    select ts + interval 5 minute 
    from all_ts 
    where ts < current_date + interval 1 day
)
select a.ts, count(t.visitor_id) no_visitors
from all_ts a
left join mytable t
    on  t.start_timestamp >= a.ts
    and t.end_timestamp   <  a.ts 
group by a.ts

如果将日期存储为unix时间戳，则可以更改 left join 具体如下：

left join mytable t
    on  t.start_timestamp >= unix_timestamp(a.ts)
    and t.end_timestamp   <  unix_timestamp(a.ts)

赞(0）回复(0）举报 2021-07-26

我来回答

sql—使用mysql查询定期统计具有时间范围的记录

1条答案

相关问题

热门标签

最新问答