每隔5分钟计算一次数据库中的条目数

admin

每隔5分钟计算一次数据库中的条目数

sql

我有多个Raspberry Pi从移动设备收集wifi信标，并将它们保存在mySQL
DB中。我已经在数据库中创建了一个视图。数据库中的每个条目都有移动设备的mac地址，pi id，rssi，位置和时间戳

我已经从多个看起来像这样的表中创建了一个视图。

+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| pi_id    | varchar(64)  | NO   |     | NULL    |       |
| name     | varchar(127) | NO   |     | NULL    |       |
| location | varchar(255) | NO   |     | NULL    |       |
| mac_id   | varchar(200) | NO   |     | NULL    |       |
| rssi     | int(11)      | NO   |     | NULL    |       |  
| datetime | datetime     | NO   |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

对于每个Pi /位置，我想统计在每5分钟间隔内看到了多少个数据包/信标。我一直在尝试这样的事情。

SELECT datetime , location, count(*) 
FROM packet_locations
GROUP BY DATE(DATE_SUB(datetime, INTERVAL 5 MINUTE)), location;

我想要这样的输出：

+---------------------+----------+----------+
| datetime            | location | count(*) |
+---------------------+----------+----------+
| 2016-01-26 00:00:00 | Pi1      |      44  |
| 2016-01-26 00:00:00 | Pi2      |      66  |
| 2016-01-26 00:05:00 | Pi1      |      100 |
| 2016-01-26 00:05:00 | Pi2      |      101 |
| 2016-01-26 00:10:00 | Pi1      |      128 |
| 2016-01-26 00:10:00 | Pi2      |      128 |
+---------------------+----------+----------+

阅读 134

2021-06-07

共1个答案

admin

SELECT concat( date_format(datetime,'%Y-%m-%d %k:')
             , lpad(floor(minute(datetime)/5)*5,2,'0')
             , ':00'
             ) datetime
     , location
     , count(1)
  FROM packet_locations
  GROUP BY date(datetime)
         , hour(datetime)
         , floor(minute(datetime)/5)
         , location

2021-06-07