首页 文章

SQL大表中的随机行（带where子句）

提问于
浏览 次
3

我有一个人们可以在汽车上投票的网站 . 向用户展示了4辆汽车，他/她可以对他们最喜欢的汽车进行投票 .

表 cars 具有重要的列：

car_id   int(10) (not auto_increment, so has gaps)
views    int(7)
points   int(7)
car_type int(1) (value = 1, 2 or 3)

目前，我为所有car_types使用映射表，其中PK具有无间隙的PK . 我选择映射表的最大ID并创建4个随机数（PHP），从映射中选择那些行并获取相应的car_id . 这些数字我用来从 cars 表中选择汽车 .

The problem 之后添加到数据库中的汽车获得与之前添加的汽车相同点的机会更少 .

My question 是如何显示4辆具有相同数量的点（随机）的车辆，这些点数由最少数量的视图（视图asc）排序 . 重要的注意事项：

  • 选择应仅查询 at least 1点的汽车 .

  • 数据库将包含超过30M的汽车，它更容易:) .

  • 当70％的汽车有1分，20％有2分，10％有3分，比随机分数应该选择汽车70％的时间，1分20％，2分，10％，3分 .

  • 该查询将用于向访客显示4辆车，我们都知道用户不耐烦所以查询越快越好:)

  • 我可以（如果需要）使用映射表，它在PK中没有间隙（正如我现在所做的那样） .
    将显示

  • 某个car_type内的汽车 only . 例如，4辆车的2型（家用车），因为我不想同时展示跑车和家用车 .

If you know 解决上述问题的另一种解决方案，我愿意接受各种解决方案（PHP / SQL） .

Bounty because 这是我感谢帮助我的人并确保我非常感谢你的帮助的方式 .

UPDATE:

谢谢你到目前为止的所有答案！你的答案很好 . 我在过去的几个小时里确实考虑了很多，我开始意识到数据库实际上从来没有为这样的事情构建（显示随机数据），它是为了显示精确和准确的数据而快速访问 . 具有30M或更多行的's why selects on PK'仍然非常快 . 这就是为什么我在考虑用PHP做所有随机的东西 . 因此，我在PHP中生成40个随机数，并从右侧车型的映射表中选择那40行 . 使用 IN 进行此选择非常快（如0.0006秒） . 在这个选择之后我得到了40个car_ids，我也从汽车表中选择 IN . 我循环汽车并将它们放在一个数组中并做一些自定义排序（基于点和视图） . 在此之后，我从40辆汽车中的所有点中选择一个随机数，并从距离这个点数最近并且视图最少的阵列中抓取汽车 . 这样，PHP可以处理随机性和视图部分以及查询，因为您要求的精确数据非常快（每个0.0006秒） .

php mysql sql database

7 回答

  • 1

    我想给出一个具体的答案，但我需要帮助才能理解你的思考过程......

    你从写作开始：

    我有一个网站，人们可以投票（...）他们最喜欢的汽车 . 问题是后来添加到数据库中的汽车获得与之前添加的汽车相同点的机会较少 .

    但是你继续写下：

    当70％的汽车有1分，20％有2分，10％有3分，比随机分数应该选择汽车70％的时间，1分20％，2分，10％，3分 .

    对我来说，根据第一句话，后一个规范没有多大意义 .

    Imho，你真正想要的是让用户有相同数量的机会投票购买每辆车 . 或者更确切地说，投票支持每辆车相对于彼此的汽车 .

    如果您认为（汽车）变量是独立的，那么您需要计算一个选择出现的次数，而不是投票的次数，并相应地调整您的决策过程 . 这是一个数学问题，它不是那么难看，然后它可以被转化为SQL，无论好坏 - 我冒昧地认为它可能会更糟 .

    如果你像我一样假设他们不是独立的，你还需要考虑相关性 - 并存储他们彼此相遇的次数 . 因为，有一个非常微小的机会，你不会喜欢这个梅赛德斯，而不是塔塔，新凯或那个AvtoVAZ . 但考虑到梅赛德斯，宝马，保时捷和法拉利之间的选择，决定可能并不那么明确 .

    换一种说法，您的规格根本没有回答问题 .

    我现在乞求同意两小时前发布的答案：选择它们是非常随意的，如果没有额外的代码，你会感到满意......

    作为旁注，如果您的ID确实没有间隙，请在php或其他任何内容中生成四个ID，并使用 in() 语句获取它们 . 你不会比那更有效率 .

    回复于
  • 4

    我不确定你是否可以这样做，但是如果你忘记了'随机'并创建了一个不同的公式来模拟它呢？我的建议是在 cars table 中创建 date 类型的一列 lastViewed ，因此在更新 views 列期间，它还会使用当前日期更新 lastViewed

    然后可以通过以下方式完成选择查询：

    select * from cars where points=?, car_type=? order by views desc, lastViewed limit 4

    此sql将始终返回'随机' results for the visitor based in the low views and the latest date it was viewed. The cool part of this solution is that it will give priority for the one that haven'已查看一段时间 . 因此，当插入新的 car 时， lastViewed 的默认值可以是类似于1900年的日期 .

    回复于
  • 2

    Yo可以编写一个执行以下操作的商店程序：

    （不要采用与大多数伪代码一样正确的语法）

    首先选择要点：

    SELECT @varpoints = points FROM cars ORDER BY RAND() LIMIT 1

    这样我们就可以获得points列的随机值 .

    将该值存储在var中并执行类似的操作（伪代码）：

    WHILE (SELECT COUNT(car_id) FROM cars WHERE points = @varpoints ORDER BY views ASC) > 4
{
     SET @varpoints = @varpoints - 1;
}

    现在只检索具有所需结果的SQL：

    SELECT car_id FROM cars WHERE points = @varpoints ORDER BY views ASC

    这应该做的工作 .

    这将采用随机点值并对具有该值的汽车进行查询 . 如果它没有得到至少4，它将减去1并再试一次 . 如果存在每个点少于4辆汽车的机会，某种尝试捕获会很好 .

    回复于
  • 1

    所以，看起来你的主要问题是速度 . 在这种情况下，你可以做一些预处理，比方说 - 有一个像队列一样使用的表，包含4辆车的组，准备好显示 . 当然，这将区分最后一刻观看/投票的汽车，但您可以定期刷新此队列 .

    • 当70％的汽车有1分，20％有2分，10％有3分，比随机分数应该选择汽车70％的时间，1分20％，2分，10％，3分 .

    如果您选择它们是非常随机的，那么无需额外代码即可满足 .

    回复于
  • 0

    确保 car_type 和 points 列上有组合索引 . 首先，获取您感兴趣的行总数：

    $condition = "car_type=? AND points > 0";
$q_count = "SELECT count(*) FROM cars WHERE {$condition}";
$r_count = mysql_query($q_count);
$car_count = mysql_result($r_count, 0, 0);

    $car_count 保存我们可以生成的最大整数来检索汽车 . 我们将在随机数生成器中使用它 . 现在 SELECT 您感兴趣的所有行：

    $q_cars = "SELECT car_id FROM cars WHERE {$condition}";
$r_cars = mysql_query($q_cars);
$car_ids = array();
for($i = 0; $i < 4; ++$i)
{
    $random_row = rand(0, $car_count);
    $car_ids[] = mysql_result($r_cars, $random_row, 0);
}

    我假设从随机数发生器正态分布 . 不检查空表或重复记录，因为在30M记录中，概率非常低 . 您可能需要考虑调整表架构 . 30M记录表查询速度慢， ORDER BY RAND() 非常糟糕，使用 LIMIT 和 OFFSET .

    回复于
  • 0

    我想你可以在引用http://www.kahunaburger.com/2008/10/13/selecting-random-weighted-records-from-mysql/的帖子的vanilla sql中执行此操作，我通过此链接跟踪：MySQL: Select Random Entry, but Weight Towards Certain Entries

    问题的难点在于概率和帖子提出了同样的问题 . 解决方案在评论中给出为 ORDER BY -LOG(1.0 - RAND()) / weighting .

    SELECT * FROM cars
WHERE points >= 1
    AND car_type = ROUND((RAND() * 2) + 1)
ORDER BY -LOG(1.0 - RAND()) / 70
LIMIT 4;

    希望这可以帮助 .

    回复于
  • 1

    Oracle使用Rownum来表示没有间隙的虚假ID .

    Select rownum, c.* from Cars where points > 1

    将为您提供具有订购ID的结果集 .

    使用该提示，您可以使用以下mysqlCode referred here模拟相同的事情

    SELECT @rownum:=@rownum+1 rownum, c.* 
  FROM (SELECT @rownum:=0) r, Cars c where points > 1;

    现在具有与oracle中类似的预期结果集，您可以使用子选择选择任意4行 . 假设你知道有多个点的汽车行数 .

    select * from 
 (the above select)
where rownum in (Random[0], Random[1], Random[2], Random[3])

    或者您想要构建查询的任何方式 .

    这不会在mysql和php之间移动整个数据，但它会给mysql带来一些压力 .

    回复于

相关问题