具有“复”子集结构的随机选择

Question

这是我在整个StackExchange世界中的第一个问题。

我不确定这种问题是否完全适合在这里或在DBA元部分，所以可以自由地“标记”或移动这个问题。

在我的应用程序中，我必须对我的数据进行随机选择。结构如下(请参阅未定义)：

• 4 task (例如吃，睡，.)
• 10 mood (例如平静、兴奋、快乐、.)
• 10 place (例如卧室、蝙蝠房、.)

只有几个组合在这些项目之间是有意义的。我决定使用一个链接表来总结使用来自其他表的外键的所有合适的组合(参见上面的db<>fiddle )。我希望只有链接表中的组合才能被选择。

执行的步骤如下：

1. 随机挑选1 task (例如吃)
2. 检索包含该任务的所有组合(例如，关于Eat的所有组合)
3. 随机挑选1 mood
4. 从步骤2列表中检索包含步骤3 mood的所有组合
5. 随机挑选1 place
6. 找到随机选择的组合

备注

每个随机选择(针对每个级别)都应该从包含“唯一”项的列表中执行。例如，如果一个具有同一个mood si的组合超过一个的列表显然被接受，那么最多的mood更有可能被选中。

所有这些步骤都是为了避免针对不同任务的有效组合的“数目”会影响被选择的概率。例如，如果选择是简单地选择链接表中的一行，而对于Eat任务，我得到了20个组合，对于睡眠任务，则更有可能选择一个Eat任务。

注意:我使用的是MariaDB 10.3 (x64)

真题

我完全是DBs的初学者(你肯定注意到了)。

执行这种“数据库端”操作有意义吗？

我认为使用最终使用我DB中的这些数据的应用程序来做这样的事情要容易得多。相反，我开始考虑完全使用具有性能目的的数据库来执行此选择的想法。

关于随机选择的一行，我读过：数据采样:有效查找随机行的技术，这是对Rick的博客从表中获取随机行的重写，这里还有几个关于DBA的问题。

我正在尝试编写我的查询，以便以一种聪明的方式执行操作。代码还未完成，可能是最终任务的30-40%。没有使用MySQL的经验，我每天都会发现新的特性，所以我从零开始了几次。在这一阶段，我更希望把重点放在上面的问题上。显然，一些关于编写此类查询的最佳策略的建议是值得欢迎的。

create sql security invoker view filtered_table as
select *
from link_table
join ( 
    select task_id as id
    from task
    order by rand()
    limit 1 
) as random
on link_table.task_id = random.id;
select * from filtered_table;

set @min := (
    select min(subset_id)
    from filtered_table
);
set @max := (
    select max(subset_id)
    from filtered_table
);
select @min, @max;

PS。抱歉，我经常吃意大利面和比萨所以.¯_(ツ)_/

Answer 1

执行的步骤如下：

• 随机挑选一项任务(例如吃)
• 检索包含该任务的所有组合(例如，关于Eat的所有组合)
• 从步骤2中检索的组合列表中随机选择1种情绪
• 从包含步骤3情绪的步骤2列表中检索所有组合
• 从剩余的组合中随机挑选一个位置
• 找到随机选择的组合

WITH 
cte1 AS (SELECT * 
         FROM task 
         ORDER BY RAND() LIMIT 1),
cte2 AS (SELECT mood.* 
         FROM link_table 
         JOIN mood USING (mood_id)
         JOIN cte1 USING (task_id)
         ORDER BY RAND() LIMIT 1)
SELECT cte1.*, cte2.*, place.*
FROM link_table
JOIN cte1 USING (task_id)
JOIN cte2 USING (mood_id)
JOIN place USING (place_id)
ORDER BY RAND() LIMIT 1

小提琴

PS。查询可能不会返回任何记录。

PPS。还有更简单的选择：

WITH cte AS (SELECT * 
             FROM link_table
             ORDER BY RAND() LIMIT 1)
SELECT task.*, mood.*, place.*
FROM cte
JOIN task USING (task_id)
JOIN mood USING (mood_id)
JOIN place USING (place_id)

小提琴

如果link_table不是空的，这个变体永远不会返回零记录。

但!对于第一次和第二次查询，每个单独的记录返回的机会不同.