Генерация никогда не использовавшегося ранее случайного числа в SQL
Я использую приведенную ниже функцию для генерации случайного числа от 0 до 99999999999.
CREATE VIEW [dbo].[rndView]
AS
SELECT RAND() rndResult
GO
ALTER function [dbo].[RandomPass]()
RETURNS NUMERIC(18,0)
as
begin
DECLARE @RETURN NUMERIC(18,0)
DECLARE @Upper NUMERIC(18,0);
DECLARE @Lower NUMERIC(18,0);
DECLARE @Random float;
SELECT @Random = rndResult
FROM rndView
SET @Lower = 0
SET @Upper = 99999999999
set @RETURN= (ROUND(((@Upper - @Lower -1) * @Random + @Lower), 0))
return @RETURN
end;
Однако мне нужно убедиться, что возвращаемый номер никогда раньше не использовался в том же приложении. В .net я бы создал цикл while и продолжал цикл до тех пор, пока возвращаемое значение не будет найдено в таблице, в которой хранятся ранее использованные значения. Есть ли способ добиться того же результата непосредственно в SQL, в идеале без использования циклов? Если нет способа сделать это без циклов, я думаю, что было бы более эффективно сделать это в функции SQL, а не иметь цикл в .net, выполняющий ряд запросов запросов.
Почему бы просто не использовать Sequence? Какую пользу добавляет «случайность»?
@NickW это требование клиента
Отметьте свой вопрос с помощью базы данных, которую вы используете.
Ответы 2
Нет такой вещи, как случайное число без замены.
Вам нужно сохранить числа, которые уже использовались в таблице, которую я бы определил как:
create table used_random_numbers (
number decimal(11, 0) primary key
);
Затем, когда вы создадите новый номер, вставьте его в таблицу.
В части кода, которая генерирует число, используйте цикл while. В цикле while убедитесь, что номер не существует.
Теперь есть некоторые вещи, которые вы можете сделать, чтобы сделать это более эффективным по мере увеличения чисел, и способы, которые не требуют запоминания всех предыдущих значений.
Во-первых, возможно, UUID/GUID достаточно. Это отраслевой стандарт для «случайного» идентификатора, хотя в большинстве баз данных это строка HEX, а не число. Точный синтаксис зависит от базы данных.
Другой подход состоит в том, чтобы иметь 11-значное число. Первые или последние 10 цифр могут быть временем эпохи Unix (секунды с 1970-01-01) - либо явно, либо с помощью некоторого преобразования, поэтому значение «выглядит» случайным. Тогда дополнительная цифра будет случайной цифрой. Конечно, вы можете расширить это до минут или дней, чтобы у вас было больше случайных цифр.
Спасибо за подробный ответ. В основном так я и делаю. У меня есть таблица, в которой хранятся ранее использованные числа (на самом деле 3, которые объединены с помощью UNION), и каждое число, которое я генерирую, сверяется с этой таблицей, чтобы узнать, использовалось ли оно ранее. Я надеялся, что есть способ сделать это без зацикливания и создания нескольких запросов. Вроде как (псевдокод), SELECT RandomNumber WHERE RandomNumber NOT IN (SELECT ......)
@Оспрей. . . Я объяснил некоторые альтернативные методы.
Спасибо @Gordon, к сожалению, я не могу использовать уникальный идентификатор, потому что я ограничен 11 цифрами и не могу использовать время, потому что иногда возможные результаты ограничены числами, начинающимися с определенного двухзначного префикса. Это может варьироваться от пользователя к пользователю, а у некоторых пользователей его вообще нет.
Вам нужно будет сохранить используемые значения в таблице и рекурсивный запрос для генерации следующего значения.
Ответ зависит от используемой СУБД.
Ниже приведены два примера в PostgreSQL и MS SQL Server, которые решат вашу проблему.
PostgreSQL
Сначала создайте таблицу, в которой будут храниться ваши потребляемые идентификаторы:
CREATE TABLE consumed_ids (
id BIGINT PRIMARY KEY NOT NULL
);
PRIMARY KEY не является строго обязательным, но будет
- сгенерировать индекс, который ускорит следующий запрос;
- убедитесь, что два одинаковых идентификатора никогда не генерируются.
Затем используйте следующий запрос для получения нового идентификатора:
WITH RECURSIVE T AS (
SELECT 1 AS n, FLOOR(RANDOM() * 100000000000) AS v
UNION ALL
SELECT n + 1, FLOOR(RANDOM() * 100000000000)
FROM T
WHERE EXISTS(SELECT * FROM consumed_ids WHERE id = v)
)
INSERT INTO consumed_ids
SELECT v
FROM T
ORDER BY n DESC
LIMIT 1
RETURNING id;
Логика заключается в том, что пока (последний) сгенерированный идентификатор уже используется, мы генерируем новый идентификатор. Столбец n CTE предназначен только для получения последнего сгенерированного идентификатора в конце, но вы также можете использовать его для ограничения количества сгенерированных случайных чисел (например, сдаться, если n > 10).
(проверено с использованием PostgreSQL 12.4)
MS SQL-сервер
Сначала создайте таблицу, в которой будут храниться ваши потребляемые идентификаторы:
CREATE TABLE consumed_ids (
id BIGINT PRIMARY KEY NOT NULL
);
Затем используйте следующий запрос для получения нового идентификатора:
WITH T AS (
SELECT 1 AS n, FLOOR(RAND() * 100000000000) AS v
UNION ALL
SELECT n + 1, FLOOR(RAND() * 100000000000)
FROM T
WHERE EXISTS(SELECT * FROM consumed_ids WHERE id = v)
)
INSERT INTO consumed_ids (id)
OUTPUT Inserted.id
SELECT TOP 1 v
FROM T
ORDER BY n DESC;
(проверено с использованием MS SQL Server 2019).
Обратите внимание, однако, что MS SQL Server по умолчанию сдастся после 100 попыток.
functions в SQL не может изменить состояние базы данных, поэтому может не подойти для этого, поскольку вам нужна какая-то мутация для отслеживания истории.