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

heqichang
  • 16

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

IDitem_IDtime
111
222
322
423
523
614
717
817
918
1028
1118
1219
13211

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

item_ID
1

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

回复
阅读 601
3 个回答

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;

image.png

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

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;
你知道吗?

宣传栏