android - 从每个类别中至少选择一个？

Question

SQLFiddle Link

我有一个包含大量测试/考试问题的 SQLite 数据库。每个问题属于一个问题类别。

我的表格是这样的:

目标
我想要做的是选择 5 个随机问题，但结果必须包含至少每个类别中的一个。目标是从每个类别中选择一组随机问题。

例如，输出可能是问题 ID 1、2、5、7、8，或 2、3、6、7、8 或 8、6、3、1、7。

ORDER BY category_id, RANDOM()
我可以通过执行下面的 SQL 从 SQLite 中获取随机问题列表，但是我如何确保结果包含我的每个类别中的问题？

基本上，我正在寻找类似 this 的内容, SQLite 版本。

我只想获得 5 个结果，但每个类别都有一个(或更多)结果，所有类别都在结果集中表示。

赏金
添加了赏金，因为我很好奇是否可以仅在 SQLite 中完成此操作。我可以在 SQLite+Java 中做到这一点，但是有没有办法只在 SQLite 中做到这一点？ :)

SQLFiddle Link

Answer 1

答案的关键是结果中有两种问题:对于每个类别，一个问题必须约束来自该类别；以及一些遗留问题。

首先，约束问题:我们只从每个类别中选择一条记录:

SELECT id, category_id, question_text, 1 AS constrained, max(random()) AS r
FROM so_questions
GROUP BY category_id

(此查询依赖于 SQLite 3.7.11(Jelly Bean 或更高版本)中引入的功能:在查询 SELECT a, max(b) 中，a< 的值 保证来自具有最大 b 值的记录。)

我们还必须得到非约束问题(过滤掉已经在约束集中的重复问题将在下一步中进行):

SELECT id, category_id, question_text, 0 AS constrained, random() AS r
FROM so_questions

当我们将这两个查询与 UNION 合并，然后按 id 分组时，我们将所有重复项放在一起。然后选择 max(constrained) 确保对于有重复的组，只保留受约束的问题(而所有其他问题每组只有一个记录)。

最后，ORDER BY 子句确保约束问题首先出现，然后是一些随机的其他问题:

SELECT *, max(constrained)
FROM (SELECT id, category_id, question_text, 1 AS constrained, max(random()) AS r
      FROM so_questions
      GROUP BY category_id
      UNION ALL
      SELECT id, category_id, question_text, 0 AS constrained, random() AS r
      FROM so_questions)
GROUP BY id
ORDER BY constrained DESC, r
LIMIT 5

---

对于早期的 SQLite/Android 版本，我还没有找到不使用临时表的解决方案(因为约束问题的子查询必须多次使用，但由于 random() 而不会保持不变):

BEGIN TRANSACTION;

CREATE TEMPORARY TABLE constrained AS
SELECT (SELECT id
        FROM so_questions
        WHERE category_id = cats.category_id
        ORDER BY random()
        LIMIT 1) AS id
FROM (SELECT DISTINCT category_id
      FROM so_questions) AS cats;

SELECT ids.id, category_id, question_text
FROM (SELECT id
      FROM (SELECT id, 1 AS c
            FROM constrained
            UNION ALL
            SELECT id, 0 AS c
            FROM so_questions
            WHERE id NOT IN (SELECT id FROM constrained))
      ORDER BY c DESC, random()
      LIMIT 5) AS ids
JOIN so_questions ON ids.id = so_questions.id;

DROP TABLE constrained;
COMMIT TRANSACTION;