mysql count在命中0之前连续值为1的数目

pbossiut 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(344)

我有一堆每天每分钟都插入一行的记录。每行包含一个datetime时间戳（time）、一个值（0或1）和一个外键spotid。总共有100个点。
我需要看看从顶部开始，每个地点的“2017-01-01 00:00:00”和“2017-01-01 00:05:00”（5小时）两个时间戳之间的值1保持了多长时间。如果值为0，则计数器停止并移动到下一个spotid。
例如，这是我当前使用的查询，从理论上讲，最终查询应该如何通过它：

SELECT time, value, spotID FROM `records` 
WHERE time BETWEEN '2017-01-01 00:00:00' AND '2017-01-01 05:00:00'
ORDER BY `spotID`, `time` DESC

+------------------------+----------------+-------------+
|    time (datetime)     | value (0 or 1) | spotID (fk) |
+------------------------+----------------+-------------+
| '2017-01-01 05:59:00'  |              1 |           1 |
| '2017-01-01 05:58:00'  |              1 |           1 |
| '2017-01-01 05:57:00'  |              1 |           1 |
| ...consecutive 1's...                                 |
| '2017-01-01 05:30:00'  |              1 |           1 |
| '2017-01-01 05:29:00'  |              0 |           1 |
| ...we hit 0, count 1's and move onto spotID 2...      |
| '2017-01-01 05:59:00'  |              1 |           2 |
| '2017-01-01 05:58:00'  |              1 |           2 |
| '2017-01-01 05:57:00'  |              1 |           2 |
| '2017-01-01 05:56:00'  |              1 |           2 |
| ...consecutive 1's...                                 |
| '2017-01-01 03:42:00'  |              1 |           2 |
| '2017-01-01 03:41:00'  |              0 |           2 |
| ...we hit 0, count 1's and move onto spotID 3...      |
| '2017-01-01 05:59:00'  |              0 |           3 |
| ...we hit 0, count 1's and move onto spotID 4...      |
+------------------------+----------------+-------------+

最后的查询应该显示（如果我的数学是正确的）：

+--------+---------+
| spotID | minutes |
+--------+---------+
|     1  |      29 |
|     2  |     137 |
|     3  |       0 |
|    ..  |      .. |
|   100  |      .. |
+--------+---------+

我只是不知道如何通过上面的查询并计算连续1的总和，直到到达第一个0为止（忽略后面的任何1，只移动到下一个spotid）。
编辑：
这是我当前的查询：

SELECT a.spotID, SUM(a.value) as minutes
FROM (

    SELECT sr.time, sr.value, sr.spotID FROM `records` as sr
    WHERE sr.time BETWEEN '2017-01-01 00:00:00' AND '2017-01-01 05:00:00'
    ORDER BY sr.spotID, sr.time DESC

) a

GROUP BY a.spotID

它非常接近，但是，我仍然不知道如何让它排除值为0之后的任何行。我想知道我是否需要把一个箱子放在一起？

mysql count break

来源：https://stackoverflow.com/questions/51276124/mysql-count-number-of-consecutive-values-of-1-until-0-is-hit

2条答案

按热度按时间

wrrgggsh1#

如果每分钟插入一行，为什么不计算每个spotid的行数呢。

SELECT time,a.spotID,minutes FROM records as a
INNER JOIN (
  SELECT spotID,COUNT(spotID) as minutes FROM records
  WHERE time > [MIN TIME] AND time < [MAX TIME]
  GROUP BY id
  ) b ON a.spotID = b.spotID
WHERE time > [MIN TIME] AND time < [MAX TIME]
ORDER BY time DESC

这样地？不确定它是否会运行，因为我只是在使用SQLFiddle。

赞(0）回复(0）举报 2021-06-21

iq3niunx2#

我会分两步完成。
我是这样解决的：
创建数据：

DROP TABLE IF EXISTS bleach;
CREATE TABLE `bleach` (`time` DATETIME, `value` INT, spotID INT);

INSERT INTO bleach (`time`, `value`,spotID) SELECT '2017-01-01 05:59:00',1,1;
INSERT INTO bleach (`time`, `value`,spotID) SELECT '2017-01-01 05:58:00',1,1;
INSERT INTO bleach (`time`, `value`,spotID) SELECT '2017-01-01 05:57:00',1,1;
INSERT INTO bleach (`time`, `value`,spotID) SELECT '2017-01-01 05:30:00',1,1;
INSERT INTO bleach (`time`, `value`,spotID) SELECT '2017-01-01 05:29:00',0,1;

创建临时表并基于spotid插入

DROP TABLE IF EXISTS bleach2;
CREATE TABLE bleach2 (min_time DATETIME, max_time DATETIME, spotid INT, minutes TIME);

INSERT INTO bleach2 (min_time, max_time, spotID) SELECT MIN(`time`), MAX(`time`), spotID FROM bleach WHERE `value`=1 GROUP BY spotID;

计算时差：

UPDATE bleach2 SET minutes=TIMEDIFF(min_time,max_time);

输出

min_time             max_time             spotid     minutes

2017-01-01 05:30:00 2017-01-01 05:59:00    1        -00:29:00

赞(0）回复(0）举报 2021-06-21

我来回答

mysql count在命中0之前连续值为1的数目

2条答案

相关问题

热门标签

最新问答