如何用 SQL 查询统计指定时间内记录数量超过指定值的 item_ID？

如何统计指定时间内记录超过指定值

本文介绍了一种 sql 查询，用于统计指定时间内记录数量超过指定值的场景。

问题

例如，假设我们有一个如下所示的记录表：

海螺语音

海螺AI推出的AI语音生成工具，支持多种语种、情绪和效果。

下载

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

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;