mysql - 在特定时间间隔的随机结果(根据列数据)

Question

我在 MySQL 中有这 2 个表
- “tag_name”包含唯一的 tag_name_id每个单独创建的标签，以及各自的 tag
- “标签”，其中还包含一个独特的 tags_id ，一个timecode (时刻)和 group_id (相同标签可以有不同的group_id)

现在，我想做的是随机获取 timecode在前 10 秒的某个位置 ( timecode <= 10 )。之后，根据提取的时间码，尝试选择所有相距 3 秒或以上的结果。 (所有小于3秒的结果必须被丢弃)。

示例:
如果我的数据库中有这些结果:

2,3,4,4,6,13,14,17,18,18,21,25,28,28,etc (timecodes)  

我想随机抓取前 10 个中的一个(假设我选择了 4 个)，然后我想开始根据该时刻对它们进行随机排序。 (“随机”，因为我想在出现相同时间码瞬间时更改顺序，即:“4,4”，因为它们关联到不同的标签，所以我希望它们在它们之间“切换”，这样我就可以选择不同的标签每次)
所以结果查询是这样的:4,13,17,21,25,28,etc

我已经有了这个返回随机数的查询，今天早上我一直在尝试在选择中进行选择，因为我认为答案就在那里，但我无法检索到我想要的结果，而且我也找不到检索距离为 3 的结果的方法...

SELECT tag_name.tag, ROUND(avg(timecode)) as timecode, group_id 
FROM tags
INNER JOIN tag_name
ON tag_name.tag_name_id = tags.tag_name_id
WHERE tags.filename = 'filename.mp4' AND timecode <= 10
GROUP BY  group_id, tag_name.tag
ORDER BY RAND()
LIMIT 1

Here是 SQLFiddle

Answer 1

所以我们有一个时间码表，为了便于论证，它可能看起来像这样......

CREATE TABLE timecodes(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,timecode INT NOT NULL);
INSERT INTO timecodes (timecode) VALUES (2),(3),(4),(4),(6),(13),(14),(17),(18),(18),(21),(25),(28),(28);

 SELECT * FROM timecodes;
 +----+----------+
 | id | timecode |
 +----+----------+
 |  1 |        2 |
 |  2 |        3 |
 |  3 |        4 |
 |  4 |        4 |
 |  5 |        6 |
 |  6 |       13 |
 |  7 |       14 |
 |  8 |       17 |
 |  9 |       18 |
 | 10 |       18 |
 | 11 |       21 |
 | 12 |       25 |
 | 13 |       28 |
 | 14 |       28 |
 +----+----------+

现在，这个问题分为两个部分。第一部分涉及从前 n 个结果中获取随机结果。这样做的一种方法(尽管可能不是最快的方法)是这样的......

 SELECT @seed := x.timecode 
   FROM timecodes x 
   JOIN timecodes y 
     ON y.id <= x.id 
  GROUP 
     BY x.id 
 HAVING COUNT(*) <= 5 
  ORDER 
     BY RAND() 
  LIMIT 1;

+---------------------+
| @seed := x.timecode |
+---------------------+
|                   4 |
+---------------------+

此查询生成一个种子(在本例中为“4”)，可以将其重新用于后续查询，例如；

SELECT @seed := MIN(y.timecode)
  FROM timecodes x
  JOIN timecodes y 
    ON y.timecode >= x.timecode + 3
 WHERE x.timecode = @seed;

1st iteration
+--------------------------+
| @seed := MIN(y.timecode) |
+--------------------------+
|                       13 |
+--------------------------+

2nd iteration    
+--------------------------+
| @seed := MIN(y.timecode) |
+--------------------------+
|                       17 |
+--------------------------+

3rd iteration    
+--------------------------+
| @seed := MIN(y.timecode) |
+--------------------------+
|                       21 |
+--------------------------+

4th iteration    
+--------------------------+
| @seed := MIN(y.timecode) |
+--------------------------+
|                       25 |
+--------------------------+

5th iteration    
+--------------------------+
| @seed := MIN(y.timecode) |
+--------------------------+
|                       28 |
+--------------------------+

6th iteration    
+--------------------------+
| @seed := MIN(y.timecode) |
+--------------------------+
|                     NULL |
+--------------------------+

这可以包含在一个存储过程中，或者一些应用程序级别的代码中，上面写着“然后做第一件事，当@seed不为空时，做第二件事”——但这超出了我的工资等级。