每隔5分钟计算一次数据库中的条目数 [英] Count how many entries are in a database at 5 minute intervals

问题描述

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

I have multiple Raspberry Pi's collecting wifi beacons from mobile devices and save them in a mySQL DB. I have created a view in the DB. Each entry in the DB has the mobile device mac address, the pi id, rssi, location and a timestamp

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

I have created a view from multiple tables that looks like this.

+----------+--------------+------+-----+---------+-------+
| 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分钟间隔内看到了多少个数据包/信标.我一直在尝试这样的事情.

For each Pi/location I want to count how many packets/beacons were seen during each 5 minute interval. I have been trying something like this.

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 |
+---------------------+----------+----------+

推荐答案

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

这篇关于每隔5分钟计算一次数据库中的条目数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！