Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Генерация уникальных случайных кодов  [new]
mezzanine
Member

Откуда:
Сообщений: 157
Добрый день.
Вопрос конечно не совсем подходит к форуму, но написать скорей всего прийдется на T-SQL.
Нужно генерировать уникальный случайный код, длина которого от 1 .. 20, каждый знак это значение из 0-9 и a-z.
Код должен быть уникальный в пределах класса, где класс это длина кода:
класс ХХХ - 1NM, 5VF ....
класс ХХХXX - 1NMGH, 5VFJU ....
т.е. всего 20 классов од Х до ХХХХХХХХХХХХХХХХХХХХ
Количество уникальных кодов в класе не меньше 1.000.0000 а дальше без ограничений (конечно если позволит размер класса).

Пока на ум приходит решение на основе таблицы (класс | код | использован). В таблицу будут вставляться случыйный код с проверкой его уникальности по (класс | код). Но скорость генерации будет страшно низкая.
Может кто сталкивался с подобной проблеммой.
17 сен 17, 18:19    [20801708]     Ответить | Цитировать Сообщить модератору
 Re: Генерация уникальных случайных кодов  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36695
mezzanine
Но скорость генерации будет страшно низкая.
А сколько миллионов в секунду надо нагенерить?
17 сен 17, 19:14    [20801815]     Ответить | Цитировать Сообщить модератору
 Re: Генерация уникальных случайных кодов  [new]
mezzanine
Member

Откуда:
Сообщений: 157
Больше всего планируют генерировать пачками по 100.000 но и 5 минут на эту операцию будет приемлимым временем. По началу будет довольно шустро. Но чем больше использованых записей в классе, тем дольше следующие пачки.
17 сен 17, 19:51    [20801870]     Ответить | Цитировать Сообщить модератору
 Re: Генерация уникальных случайных кодов  [new]
LoopN
Guest
100тыс. за несколько сек

/*
create table t
(
	class tinyint NOT NULL,
	val varchar(20) NOT NULL 
	PRIMARY KEY(class,val)
)


*/

declare @count_gen int = 100000
declare @class int=4

--------------
declare @VAL char(36)='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
declare @rezul table (val varchar(20))
declare @exists decimal(38,0)=(select count(*) from t where class=@class)

set @count_gen = case 
					when  @count_gen>power(cast(36 as decimal(38,0)),@class) -@exists
					then power(cast(36 as decimal(38,0)),@class)-@exists  
					else @count_gen 
				end

while @count_gen > 0
begin
	insert into t(class,val)
	output inserted.val into @rezul(val)
	select top (@count_gen) @class,z.val 
	from
	(
		select distinct z.val from
		(
			select  
				LEFT(substring(@VAL,1+ASCII(substring(Gen,1,1))%36,1) +
				substring(@VAL,1+ASCII(substring(Gen,2,1))%36,1) +
				substring(@VAL,1+ASCII(substring(Gen,3,1))%36,1) +
				substring(@VAL,1+ASCII(substring(Gen,4,1))%36,1) +
				substring(@VAL,1+ASCII(substring(Gen,5,1))%36,1) +
				substring(@VAL,1+ASCII(substring(Gen,6,1))%36,1) +
				substring(@VAL,1+ASCII(substring(Gen,7,1))%36,1) +
				substring(@VAL,1+ASCII(substring(Gen,8,1))%36,1) +
				substring(@VAL,1+ASCII(substring(Gen,9,1))%36,1) +
				substring(@VAL,1+ASCII(substring(Gen,10,1))%36,1) +
				substring(@VAL,1+ASCII(substring(Gen,11,1))%36,1) +
				substring(@VAL,1+ASCII(substring(Gen,12,1))%36,1) +
				substring(@VAL,1+ASCII(substring(Gen,13,1))%36,1) +
				substring(@VAL,1+ASCII(substring(Gen,14,1))%36,1) +
				substring(@VAL,1+ASCII(substring(Gen,15,1))%36,1) +
				substring(@VAL,1+ASCII(substring(Gen,16,1))%36,1) +
				substring(@VAL,1+ASCII(substring(Gen,17,1))%36,1) +
				substring(@VAL,1+ASCII(substring(Gen,18,1))%36,1) +
				substring(@VAL,1+ASCII(substring(Gen,19,1))%36,1) +
				substring(@VAL,1+ASCII(substring(Gen,20,1))%36,1) ,@class) val
			from
			(
				select cast(HashBytes('SHA1',cast(NEWID() as char(36))) as char(20)) Gen
				from master.dbo.spt_values v 
			) z
		) z
		left join  t
			on t.class=@class and t.val=z.val
		where t.class is null
	) z
	set @count_gen = @count_gen-@@ROWCOUNT
end 

select * from @rezul
17 сен 17, 20:52    [20801930]     Ответить | Цитировать Сообщить модератору
 Re: Генерация уникальных случайных кодов  [new]
LoopN
Guest
) z
	set @count_gen = @count_gen-@@ROWCOUNT

заменить на
	) z
	order by NEWID()
	set @count_gen = @count_gen-@@ROWCOUNT

так более случайно получается не мелких диапазонах
17 сен 17, 20:55    [20801935]     Ответить | Цитировать Сообщить модератору
 Re: Генерация уникальных случайных кодов  [new]
LoopN
Guest
Для оптимизации еще можно сделать след. логику:
если @@rowcount вернул меньше 300 строк то добить до 300 с помощью пустот из таблицы t.
Чтобы быстро находить пустоты надо ввести еще один столбец NextVallExists bit в таблице t который будет 1 если следующее число по порядку =+1 (1.AA 2.AB). Расчет NextVallExists проводить после каждой генерации исходя из таблицы @rezult.
17 сен 17, 21:13    [20801967]     Ответить | Цитировать Сообщить модератору
 Re: Генерация уникальных случайных кодов  [new]
LoopN
Guest
(select count(*) from t where class=@class)

вот это еще нужно заменить, т.к. будет долго считать. Сделать отдельную таблицу в которой будет содержаться общее количество для каждого класса. Или каждый класс разбить на отдельные партиции и с помощью системных таблиц быстро находить общее количество для каждого класса (select partition_id,rows from sys.partitions)
17 сен 17, 21:19    [20801982]     Ответить | Цитировать Сообщить модератору
 Re: Генерация уникальных случайных кодов  [new]
aleks222
Guest
mezzanine
Добрый день.
Вопрос конечно не совсем подходит к форуму, но написать скорей всего прийдется на T-SQL.
Нужно генерировать уникальный случайный код, длина которого от 1 .. 20, каждый знак это значение из 0-9 и a-z.
Код должен быть уникальный в пределах класса, где класс это длина кода:
класс ХХХ - 1NM, 5VF ....
класс ХХХXX - 1NMGH, 5VFJU ....
т.е. всего 20 классов од Х до ХХХХХХХХХХХХХХХХХХХХ
Количество уникальных кодов в класе не меньше 1.000.0000 а дальше без ограничений (конечно если позволит размер класса).

Пока на ум приходит решение на основе таблицы (класс | код | использован). В таблицу будут вставляться случыйный код с проверкой его уникальности по (класс | код). Но скорость генерации будет страшно низкая.
Может кто сталкивался с подобной проблеммой.


1. Самое интересное в этой выдуманной проблеме: нахера тредстартеру "случайные"?
Как только он осознает бессмысленность этого "требования" - все станет простым и быстрым.

2. Если ужо тредстартеру "случайные" нужны, кровь из носу. То предварительная генерация таблицы с запасом "на сто лет вперед" решает и эту надуманную проблему.
18 сен 17, 07:19    [20802326]     Ответить | Цитировать Сообщить модератору
 Re: Генерация уникальных случайных кодов  [new]
mezzanine
Member

Откуда:
Сообщений: 157
aleks222,

1. Будут использоваться разные класы, при длине кода 20 знаков будет трудно секвенцией дать хоть что-то похожее на случайность.
2. Как раз обсуждали этот вариант. Спасибо LoopN.
18 сен 17, 09:02    [20802400]     Ответить | Цитировать Сообщить модератору
 Re: Генерация уникальных случайных кодов  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
mezzanine, еще вот такой вариант.

declare @count_gen int = 100000;
declare @class int = 4;

select top (@count_gen) 
      ( select  c as [text()]
        from  ( select  top (@class) char(number) as c
                from    master..spt_values sv
                where   sv.Type = 'P' and (sv.number between 48 and 57 or number between 97 and 122)
                order   by sv1.Name, sv2.Name, newid()) as t2
        for xml path(''))
from    master..spt_values as sv1
        cross join master..spt_values as sv2
18 сен 17, 11:56    [20802954]     Ответить | Цитировать Сообщить модератору
 Re: Генерация уникальных случайных кодов  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4257
/******
 *  generate random PIN word with next criteria:
 *  length between 6 and 9 symbols
 *  word must have: number, alphobetic symbols in lower and upper case
 * INPUT PARAMETERS:
 *
 *    @@pattern INT        - Pin Code Patterns
 *    a - Alpha (a-z) case insensitive compare
 *    A - mixed case Alpha (A-Z, a-z)
 *    N - Numeric  (0-9)
 *    m- AlphaNumeric (0-9, a-z) case insensitive compare
 *    M- Mixed case Alpha Numeric (0-9,  A-Z, a-z)
 *  Usage: SELECT dbo.fn_gen_pin( 'MMMMMMMMMMMMMMMM' ) UNION ALL SELECT dbo.fn_gen_pin( 'AAAAAAAAAAAAAAAA' ) UNION ALL SELECT dbo.fn_gen_pin( 'NNNNNNNNN' )
           SELECT dbo.fn_gen_pin( 'mmmmmmm' )  
 *
 *  Return: user pin code, for example: cv90Fy21
 */
CREATE FUNCTION [dbo].[fn_gen_pin]( @pattern varchar(32) )
RETURNS varchar(16)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @result varchar(16)
    IF @pattern IS NULL OR LEN(@pattern) < 6 OR LEN(@pattern) > 16
        RETURN 'Error';

    WITH cte AS
    (
        SELECT TOP (60) c1 AS sym FROM
        (
            SELECT c1, 1 AS code FROM
                 ( SELECT TOP 24 c1 FROM ( VALUES ('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('J'),('K'),('L'),('M'),('N'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z')) AS T1(c1)
                    ORDER BY (SELECT rndOrder FROM dbo.vw_orderby) )j
            UNION ALL
            SELECT c2, 2 AS code FROM
                 ( SELECT TOP 23 c2 FROM ( VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('j'),('k'),('m'),('n'),('p'),('q'),('r'),('s'),('t'),('u'),('v'),('w'),('x'),('y'),('z')) AS T2(c2)
                    ORDER BY (SELECT rndOrder FROM dbo.vw_orderby) )k
            UNION ALL
            SELECT c3, 4 AS code FROM
                 ( SELECT TOP 23 c3 FROM ( VALUES ('0'), ('1'), ('2'), ('3'),('4'), ('5'), ('6'), ('7'), ('8'), ('9')) AS T3(c3)
                    ORDER BY (SELECT rndOrder FROM dbo.vw_orderby) )l
        )x WHERE code 
        & ( CASE 
                WHEN ASCII(LEFT( @pattern, 1 )) = ASCII('a') THEN  1
                WHEN ASCII(LEFT( @pattern, 1 )) = ASCII('A') THEN (1 | 2)
                WHEN ASCII(LEFT( @pattern, 1 )) = ASCII('N') THEN  4
                WHEN ASCII(LEFT( @pattern, 1 )) = ASCII('m') THEN (4 | 2)
                WHEN ASCII(LEFT( @pattern, 1 )) = ASCII('M') THEN (1 | 2 | 4) END
        ) = code 
        ORDER BY (SELECT rndOrder FROM dbo.vw_orderby)
    )
    SELECT @result = LEFT( result, LEN(@pattern) ) FROM
    (
        SELECT 
        (
            SELECT sym AS [text()] FROM
            (
                SELECT TOP 16 sym FROM
                (
                    SELECT TOP 6 sym FROM
                    (
                        SELECT TOP 1 sym, (select 1 + CAST(rndVal AS int) % 3 from dbo.vw_rnd) AS ord FROM cte WHERE UNICODE(sym) BETWEEN 97 AND 122
                        UNION ALL
                        SELECT TOP 1 sym, (select 1 + CAST(rndVal AS int) % 3 from dbo.vw_rnd) AS ord FROM cte WHERE UNICODE(sym) BETWEEN 65 AND 90
                        UNION ALL
                        SELECT TOP 1 sym, (select 1 + CAST(rndVal AS int) % 3 from dbo.vw_rnd) AS ord FROM cte WHERE UNICODE(sym) BETWEEN 48 AND 57
                        UNION ALL
                        SELECT TOP 1 sym, (select 3 + CAST(rndVal AS int) % 3 from dbo.vw_rnd) AS ord FROM cte WHERE UNICODE(sym) BETWEEN 97 AND 122
                        UNION ALL
                        SELECT TOP 1 sym, (select 3 + CAST(rndVal AS int) % 3 from dbo.vw_rnd) AS ord FROM cte WHERE UNICODE(sym) BETWEEN 65 AND 90
                        UNION ALL
                        SELECT TOP 1 sym, (select 3 + CAST(rndVal AS int) % 3 from dbo.vw_rnd) AS ord FROM cte WHERE UNICODE(sym) BETWEEN 48 AND 57
                    )sts ORDER BY ord ASC 
                    UNION ALL
                    SELECT sym FROM cte
                )z 
            ) AS T2 FOR XML PATH('') 
        ) AS result
    )x
    RETURN @result;
END

GO
20 сен 17, 20:01    [20810210]     Ответить | Цитировать Сообщить модератору
 Re: Генерация уникальных случайных кодов  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4257
забыл добавить

CREATE VIEW [dbo].[vw_rnd]
WITH SCHEMABINDING
AS
SELECT CAST(round(rand() * 10 + 1, 0) AS INT) AS rndVal

GO
20 сен 17, 20:03    [20810214]     Ответить | Цитировать Сообщить модератору
 Re: Генерация уникальных случайных кодов  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4257
И еще

CREATE VIEW [dbo].[vw_orderby]
WITH SCHEMABINDING
AS
SELECT ABS(CAST(round(rand() * 100000000 + 1, 0) AS BIGINT) ^ CHECKSUM(CAST(getdate() AS BINARY(19))) ^ CHECKSUM(newidVal)) AS rndOrder
FROM dbo.vw_NEWID, dbo.vw_rnd

GO
20 сен 17, 20:05    [20810216]     Ответить | Цитировать Сообщить модератору
 Re: Генерация уникальных случайных кодов  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4257
CREATE VIEW [dbo].[vw_NEWID]
WITH SCHEMABINDING 
AS
  SELECT CAST(CAST(NEWID() AS varchar(36)) AS varchar(36)) AS newidVal

GO
20 сен 17, 20:06    [20810218]     Ответить | Цитировать Сообщить модератору
 Re: Генерация уникальных случайных кодов  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36695
2Lepsik
+
/*
* ...
*  word must have: number, alphAbetic symbols in lower and upper case
*/
21 сен 17, 01:22    [20810702]     Ответить | Цитировать Сообщить модератору
 Re: Генерация уникальных случайных кодов  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4257
Гавриленко Сергей Алексеевич,

m- AlphaNumeric (0-9, a-z) case insensitive compare

SELECT dbo.fn_gen_pin( 'mmmmmmm' )
23 сен 17, 05:50    [20817693]     Ответить | Цитировать Сообщить модератору
 Re: Генерация уникальных случайных кодов  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4257
опять не то, вот верно

SELECT dbo.fn_gen_pin( 'MMMMMMMMMMMMM' )

0HsQ9uUtkNT2M
23 сен 17, 05:54    [20817694]     Ответить | Цитировать Сообщить модератору
 Re: Генерация уникальных случайных кодов  [new]
aleks222
Guest
Lepsik
опять не то, вот верно

SELECT dbo.fn_gen_pin( 'MMMMMMMMMMMMM' )

0HsQ9uUtkNT2M


Штатный RAND() справится.

ALTER FUNCTION [dbo].[BigIntToStr]( @i bigint, @Digits varchar(1024) )
RETURNS varchar(64)
as
begin
    declare @base int, @result varchar(64);

    set @base = LEN(@Digits);

    with digits as (select D = SUBSTRING( @Digits, @i%@base + 1, 1 ), i = @i/@base, n = 1
                    UNION ALL
                    select D = SUBSTRING( @Digits, i%@base+1, 1 ), i = i/@base, n = n + 1 
                    FROM digits WHERE i > 0
    )
    select @result = (select D + ''  FROM digits ORDER BY n ASC for xml path('') );

return @result;

end
go

-- Надеюсь догадаться передать строку нужных символов и вызвать несколько раз, если надо длинный результат не слишком сложно?

select dbo.BigIntToStr( 9223372036854775807*rand(), '0123456789abcdefghABCDE')

select dbo.BigIntToStr( 9223372036854775807*rand(), '0123456789абвгдеёжзиклмнопрсту' )


Сообщение было отредактировано: 15 ноя 17, 20:42
23 сен 17, 06:42    [20817699]     Ответить | Цитировать Сообщить модератору
 Re: Генерация уникальных случайных кодов  [new]
aleks222
Guest
Ну а для вящего быстродействия - можно inline-функцию забабахать

ALTER FUNCTION [dbo].[BigIntToStr]( @i bigint, @Digits varchar(1024) )
RETURNS TABLE 
AS
RETURN 
(
    with b as ( select base = len(@Digits) )
       , d as (select d = substring( @Digits, @i%base + 1, 1 ), i = @i/base, n = 1 from b
               UNION ALL
               select d = substring( @Digits,  i%base + 1, 1 ), i =  i/base, n = n + 1 
                 from d inner join b on 1 = 1 where i > 0
    )
    select str = ( select d + '' from d order by n asc for xml path('') )
)
23 сен 17, 07:01    [20817706]     Ответить | Цитировать Сообщить модератору
 Re: Генерация уникальных случайных кодов  [new]
Cammomile
Member

Откуда:
Сообщений: 1212
SELECT 
  LEFT(REPLACE(Newid(), '-',''), 3)  AS [3X]
, LEFT(REPLACE(Newid(), '-',''), 5)  AS [5X]
, LEFT(REPLACE(Newid(), '-',''), 12) AS [12X]
, LEFT(REPLACE(Newid(), '-',''), 20) AS [20X]
 
14 ноя 17, 10:09    [20951519]     Ответить | Цитировать Сообщить модератору
 Re: Генерация уникальных случайных кодов  [new]
Cammomile
Member

Откуда:
Сообщений: 1212
Фигню написал, прощенья прошу.
(невнимательно читал про уникальность)
14 ноя 17, 10:24    [20951610]     Ответить | Цитировать Сообщить модератору
 Re: Генерация уникальных случайных кодов  [new]
Cammomile
Member

Откуда:
Сообщений: 1212
Хотя, проверил тут на 4 миллионах кодов

SELECT COUNT(distinct [3x]) FROM Dbo.Codes  /*4000*/
SELECT COUNT(distinct [5x]) FROM Dbo.Codes /*1000000*/
SELECT COUNT(distinct [12x]) FROM Dbo.Codes /*4000000*/


То есть для длинных кодов вполне себе метод, вот для коротких неочень.
14 ноя 17, 10:27    [20951620]     Ответить | Цитировать Сообщить модератору
 Re: Генерация уникальных случайных кодов  [new]
Cammomile
Member

Откуда:
Сообщений: 1212
Кстати, про миллион уникальных кодов длиной 3. У нас 26 букв и 10 цифр. Это всего то 46656 комбинаций с повторениями.
14 ноя 17, 10:41    [20951671]     Ответить | Цитировать Сообщить модератору
 Re: Генерация уникальных случайных кодов  [new]
ГуЗы
Guest
Cammomile
Кстати, про миллион уникальных кодов длиной 3. У нас 26 букв и 10 цифр. Это всего то 46656 комбинаций с повторениями.

Чуть больше:
226920
14 ноя 17, 11:55    [20952062]     Ответить | Цитировать Сообщить модератору
 Re: Генерация уникальных случайных кодов  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30801
ГуЗы
Cammomile
Кстати, про миллион уникальных кодов длиной 3. У нас 26 букв и 10 цифр. Это всего то 46656 комбинаций с повторениями.

Чуть больше:
226920
46656
14 ноя 17, 22:27    [20954455]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить