如何构造带有偏差的 MySQL 查询，其中包含多次出现的项目 - how to construct mysql query with bias with items that appear multiple times

how to construct mysql query with bias with items that appear multiple times

我想构造一个查询来随机显示 ITEM 表中的项目，但要根据项目类别在首选项表中存在的次数（更大的偏差）。类别必须自动添加到查询中。

ITEM TABLE
Itemname   Category Id
'item1', '20081'
'item2 ', '15032'
'items3', '20081'
'item4', '20081'
PREFERENCE TABLE
Userid, Categoryname, Categoryid
'79', 'Everything Else', '15032'
'146', 'Antiques', '20081'
'79', 'Antiques', '20081'
'79', 'Antiques', '20081'
'79', 'Antiques', '20081'

简单来说就是这样

SELECT * FROM `ex`.`item` where category_id=20081 or category_id=79 /*there rest to be added automatically and also with the bias depending on the count in preference  */order by rand();

SELECT i.*
FROM item i
JOIN (SELECT Categoryid, COUNT(*) cat_count
      FROM preference
      WHERE Categoryid IN (20081, 79)
      GROUP BY Categoryid) p
ON i.Categoryid = p.Categoryid
ORDER BY cat_count*RAND() DESC

SELECT  *,
        (
        SELECT  COUNT(*)
        FROM    preference
        WHERE   (p.userid, p.categoryid) = (79, i.categoryid)
        ) AS pref
FROM    item i
LEFT JOIN
        preference p
ON      (p.userid, p.categoryid) = (79, i.categoryid)
WHERE   category_id IN (20081, 79)
ORDER BY
        RAND() + 1 - POW(0.7, pref)
LIMIT 25

对于无偏项目，排序值将在 [0， 1）内均匀随机，对于 1 个首选项，[0.3， 1.3）对于 2 个首选项，在 [0.51， 1.51）内均匀随机，依此类推。

你可能想出一个更复杂的偏见公式