MySQL изменяет порядок с помощью rand() на другие методы

Теперь я пытаюсь сделать случайный выбор из каждого сгруппированного массива столбцов, с шансами, за которыми следует вес каждой строки. Например, у меня есть таблица (DemoTable): http://sqlfiddle.com/#!9/23470/3/0

Name State Grade Weight
John NY 100 1
Liam NY 90 2
Olivia NY 90 3
Emma NY 80 4
James CA 10 1
Henry CA 20 1
Mia NJ 50 1
Ava NJ 30 4

Для State = 'NY' имеется четыре строки с массивом оценок: [100, 90, 90, 80] и вес [1, 2, 3, 4] соответственно. Таким образом, 80 имеет наибольший шанс быть выбранным, а 100 — наименьший в своей группе штатов. Я сделал запрос для него:

SELECT a.*,
(SELECT b.Grade FROM DemoTable b WHERE a.State = b.State 
ORDER BY RAND() * -b.Weight LIMIT 1) AS 'random_val' FROM DemoTable a;

и это сработало с результатом:

Name State Grade Weight random_val
John NY 100 1 80
Liam NY 90 2 80
Olivia NY 90 3 80
Emma NY 80 4 90
James CA 10 1 20
Henry CA 20 1 10
Mia NJ 50 1 30
Ava NJ 30 4 30

Тем не менее, я хотел бы знать, есть ли какой-либо другой метод, такой как соединение или объединение, вместо использования только порядка с помощью rand().
Есть ли другой способ изменить мой запрос MySQL, дающий тот же результат?
Я искал решение этой проблемы весь день, но не смог найти правильный способ сделать это; вот поэтому я и обратился сюда за помощью.
Я был бы искренне признателен, если бы я мог получить несколько советов.


mysql sql random join union
person Jay    schedule 22.06.2021    source источник
comment
@philipxy - О какой народной теории вы говорите? (В MySQL RAND() выполняется независимо для каждой строки, как показано в руководстве... dev.mysql.com/doc/refman/5.7/en/ )   -  person MatBailie    schedule 22.06.2021
comment
@Jay В MySQL8 я могу придумать несколько вариантов, должен быть MySQL 5.7 (которому почти шесть лет, и его заменила MySQL 8.0 три года назад).   -  person MatBailie    schedule 22.06.2021
comment
@MatBailie это не обязательно должен быть MySQL 5.7. Не могли бы вы дать несколько советов по использованию MySQL8?   -  person Jay    schedule 22.06.2021
comment
Показать желаемый результат. Должен ли он содержать только 3 строки, по одной строке на состояние?   -  person Akina    schedule 22.06.2021
comment
@Akina, это действительно показывает желаемые результаты; исходная таблица плюс один дополнительный столбец   -  person MatBailie    schedule 22.06.2021
comment
Я полагаю, вы хотите чтобы каждый Name в одном и том же State давал разные random_val?   -  person MatBailie    schedule 22.06.2021

Ответы (1)


arrow_upward
2
arrow_downward

Моя первая попытка использовать аналитические функции, хотя я подозреваю, что ваша быстрее работает с большими наборами данных...

WITH
  ranged AS
(
  SELECT
    *,
    SUM(weight) OVER (PARTITION BY state ORDER BY id) - weight   AS weight_range_lower,
    SUM(weight) OVER (PARTITION BY state ORDER BY id)            AS weight_range_upper,
    SUM(weight) OVER (PARTITION BY state            ) * rand()   AS rand_threshold
  FROM
    DemoTable
)
SELECT
  ranged.*,
  lookup.grade   AS random_grade
FROM
  ranged
INNER JOIN
  ranged  AS lookup
    ON  lookup.state               = ranged.state
    AND lookup.weight_range_lower <= ranged.rand_threshold
    AND lookup.weight_range_upper >  ranged.rand_threshold
ORDER BY
  ranged.id

Или, если вы хотите, чтобы всем членам одного и того же штата был присвоен один и тот же random_grade...

SELECT
  *,
  FIRST_VALUE(grade) OVER (PARTITION BY state ORDER BY weight * rand() DESC)
FROM
  DemoTable
ORDER BY
  id

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=133f9e86b013a477ac342d0295132dd5

person MatBailie    schedule 22.06.2021
comment
Большое спасибо за ваш ответ. Я не мог думать об использовании этого способа. Он отлично работает, давая мне тот же результат :) - person Jay; 22.06.2021