如何在SQLite中选取100组同视频随机行对（每组来自不同视频）

需求说明 #

需要从video_segments表中选取100组行对，满足以下要求：

• 每组行对包含同一video_id的2个片段
• 任意两组行对的video_id不重复
• 结果具备随机性，每次查询返回不同的片段组合

表结构 #

CREATE TABLE video_segments (
    video_id TEXT,
    segment_num INTEGER,
    data BLOB
);

尝试过的无效语句 #

你之前的查询无法保证每个选中的video_id恰好返回2个片段，可能出现某个video_id返回多组或不足2组的情况：

WITH CTE AS (
  SELECT video_id
  FROM video_segments
  GROUP BY video_id
  HAVING COUNT(*) >= 2
)
SELECT *
FROM video_segments
WHERE video_id IN (
  SELECT video_id
  FROM CTE
  ORDER BY RANDOM()
  LIMIT 100
)
ORDER BY RANDOM()
LIMIT 200;

示例数据 #

video_id|segment_num|data|
foo      0            <bin>
foo      1            <bin>
foo      2            <bin>
foo      3            <bin>
bar      0            <bin>
bar      1            <bin>
baz      0            <bin>
baz      1            <bin>
baz      2            <bin>

有效结果示例（选取3组行对） #

foo 0 <bin>
foo 2 <bin>
bar 0 <bin>
bar 1 <bin>
baz 0 <bin>
baz 2 <bin>

最优查询语句 #

以下语句可以严格满足需求，同时保证随机性和性能：

WITH eligible_videos AS (
  -- 筛选出有至少2个片段的video_id，随机选100个不重复的
  SELECT video_id
  FROM video_segments
  GROUP BY video_id
  HAVING COUNT(*) >= 2
  ORDER BY RANDOM()
  LIMIT 100
),
ranked_segments AS (
  -- 给每个选中video_id的片段随机排序并标记行号
  SELECT 
    vs.*,
    ROW_NUMBER() OVER (PARTITION BY vs.video_id ORDER BY RANDOM()) AS rnk
  FROM video_segments vs
  JOIN eligible_videos ev ON vs.video_id = ev.video_id
)
-- 取出每个video_id的前2个随机片段
SELECT video_id, segment_num, data
FROM ranked_segments
WHERE rnk <= 2
ORDER BY video_id, rnk;

语句逻辑说明 #

1. eligible_videos CTE：先过滤出所有拥有至少2个片段的video_id，通过ORDER BY RANDOM()随机排序后取前100个，确保这100个video_id都符合条件且互不重复。
2. ranked_segments CTE：关联选中的100个video_id对应的所有片段，使用窗口函数ROW_NUMBER()按video_id分组并随机排序，给每个片段标记行号。
3. 最终查询：筛选出每个video_id下行号≤2的记录，确保每个video_id恰好返回2个随机片段，总共200行对应100组行对。

优势 #

• 严格保证每个选中的video_id贡献恰好2个片段，不会出现多取或少取的情况
• 两层随机机制（选video_id随机、选片段随机）确保结果的随机性
• 先筛选目标video_id再关联片段，相比全表随机排序更高效

内容的提问来源于stack exchange，提问作者Foobar