sql 统计出5秒内记录个数超过5个的值

Question

比如有下个这样一个记录表
ID 是自增的，item_id 是关联外键，tiem 是记录的时间（单位秒，假设从1秒开始）

ID	item_ID	time
1	1	1
2	2	2
3	2	2
4	2	3
5	2	3
6	1	4
7	1	7
8	1	7
9	1	8
10	2	8
11	1	8
12	1	9
13	2	11

我想统计出5秒内记录个数超过5个的 item_id ，比如上表的查询结果应该是

item_ID
1

因为从第4秒开始的5秒内有6个 item_id = 1 的记录，请问下 sql 的语句应该怎么写？

Answer 1

https://tableconvert.com/
http://mysql.jsrun.net/

CREATE DATABASE test;
use test;
CREATE TABLE tableName
(
     ID     varchar(300),
     item_ID     varchar(300),
     time     varchar(255)
);

INSERT INTO tableName ( ID , item_ID , time )
VALUES
    ('1', '1', '1'),
    ('2', '2', '2'),
    ('3', '2', '2'),
    ('4', '2', '3'),
    ('5', '2', '3'),
    ('6', '1', '4'),
    ('7', '1', '7'),
    ('8', '1', '7'),
    ('9', '1', '8'),
    ('10', '2', '8'),
    ('11', '1', '8'),
    ('12', '1', '9'),
    ('13', '2', '11');
SELECT DISTINCT c.item_ID
FROM 
    (SELECT 
        (SELECT b.item_ID
        FROM tableName b
        WHERE b.item_ID = a.item_ID
                AND b.time
            BETWEEN a.time
                AND (a.time + 5)
        GROUP BY  b.item_ID
        HAVING count(b.item_ID) >5 ) item_ID
        FROM tableName a
        ORDER BY  a.time) c
    WHERE c.item_ID is NOT null;

Answer 2

你这个5s很多玩法，如果固定是从1-5,6-10这种，查询很好做，首先第一次按照时间计算在第几个5s，然后外面套一层做分组就行了；
就怕他是随机分布的5s ，比如数据集中出现在9s一次,11s5次，5s内连续出现6次，此时你别说用sql了，用代码处理都要考虑考虑

Answer 3

SELECT T.ITEM_ID
  FROM (SELECT ITEM_ID, TIME FROM TEST) T,
       (SELECT DISTINCT ITEM_ID, TIME AS BEGIN_TIME, TIME+5 AS END_TIME
          FROM TEST) S
 WHERE T.TIME >= S.BEGIN_TIME
   AND T.TIME <= S.END_TIME
   AND T.ITEM_ID = S.ITEM_ID
 GROUP BY BEGIN_TIME, END_TIME, T.ITEM_ID
HAVING COUNT(*)>5;