Случайный выбор в SQL Server

У меня есть два набора, и для каждого значения в первом наборе я хочу применить несколько случайных значений из второго. Подход, который я выбрал, использует выбор из первого с перекрестным применением из второго. Упрощенный MWE выглядит следующим образом:

DROP TABLE IF EXISTS #S;
CREATE TABLE #S (c CHAR(1));
INSERT INTO #S VALUES ('A'), ('B');
DROP TABLE IF EXISTS #T;
WITH idGen(id) AS (
    SELECT 1 
    UNION ALL 
    SELECT id + 1 FROM idGen WHERE id < 1000
) 
SELECT id INTO #T FROM idGen OPTION(MAXRECURSION 0);
DROP TABLE IF EXISTS #R;
SELECT c, id INTO #R FROM #S
CROSS APPLY (
    SELECT id, ROW_NUMBER() OVER (
        /* 
        -- this gives 100% overlap
        PARTITION BY c
        ORDER BY RAND(CHECKSUM(NEWID()))
        */
        -- this gives the expected ~10% overlap
        ORDER BY RAND(CHECKSUM(NEWID()) + CHECKSUM(c))
    ) AS R
    FROM #T 
) t
WHERE t.R <= 100;
SELECT COUNT(*) AS PercentOverlap -- ~10%
FROM #R rA JOIN #R rB
ON rB.id = rA.id AND rB.c = 'B'
WHERE rA.c = 'A';

Хотя это решение работает, мне интересно, почему переход на (прокомментированный) метод разделения не работает? Кроме того, есть ли какие-либо предостережения при использовании этого решения, поскольку добавление двух контрольных сумм кажется грязным?

В реальной задаче также есть счетчик в первом наборе, содержащий количество случайных значений для выбора из второго набора, который заменяет статические 100 в приведенном выше примере. Однако использование фиксированных 100 упростило проверку ожидаемого перекрытия.

почему вы не можете добиться случайного порядка с помощью ... ORDER BY NEWID ()

— 25.06.2018 16:39

sql sql-server tsql random

25.06.2018 14:43

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Функция RAND() - это постоянная времени выполнения в SQL Server. Это означает, что обычно оценивается один раз для запроса. Когда вы передаете значение в RAND, это значение служит начальным значением.

Вам нужно изучить план выполнения, и вы увидите, куда оптимизатор ставит оценку функций. В случае, который не дает ожидаемого результата, скорее всего, оптимизатор оптимизировал его слишком агрессивно и вынес всю «случайность» за пределы цикла.

Кроме того, нет смысла оборачивать NEWID() в CHECKSUM() и в RAND(). Достаточно простого NEWID(). Или, что еще лучше, функция, которая предназначена для получения случайного числа, например CRYPT_GEN_RANDOM()

Любая версия вашего запроса выглядит немного странно. Я бы написал это так:

SELECT c, id INTO #R 
FROM #S
CROSS APPLY 
(
    SELECT TOP(100) -- or #S.SomeField instead of 100
        id
    FROM #T 
    ORDER BY CRYPT_GEN_RANDOM(4) -- generate 4 random bytes, usually it is enough
) AS t
;

Это дает 100 случайных строк из #T для каждой строки из #S.

На самом деле, приведенный выше запрос не подходит. Оптимизатор снова видит, что внутренний запрос (внутри CROSS APPLY) не зависит от внешнего запроса, и оптимизирует его. Конечным результатом является то, что случайные строки выбираются только один раз.

Нам нужно что-то, чтобы оптимизатор выполнял внутренний запрос для каждой строки из #S. Один из способов был бы примерно таким:

SELECT c, id INTO #R 
FROM #S
CROSS APPLY 
(
    SELECT TOP(100) -- or #S.SomeField instead of 100
        id
    FROM #T 
    ORDER BY CRYPT_GEN_RANDOM(4) + CHECKSUM(c)
) AS t
;

Что-то во внутреннем запросе для ссылки на строку из внешнего запроса. Если вместо постоянного TOP(#S.SomeField) поставить TOP(100), то + CHECKSUM(c) не нужен.

Это план первого варианта. Вы можете видеть, что #T сканируется один раз (читается 1000 строк).

Query 1