Как преобразовать первые 100 миллионов положительных целых чисел в строки?

Это немного отвлекает от реальной проблемы. Если предоставление контекста помогает, генерация этих данных может быть полезна для способов тестирования производительности для обработки строк, для генерации строк, которые должны иметь некоторую операцию, применяемую к ним внутри курсора, или для создания уникальных имен анонимных имен для чувствительных данных. Меня просто интересуют эффективные способы генерации данных в SQL Server, пожалуйста, не спрашивайте, почему мне нужно генерировать эти данные.

Я попытаюсь начать с некоторого формального определения. Строка включена в серию, если она состоит только из заглавных букв из A - Z. Первый член серии - «A». Серия состоит из всех допустимых строк, отсортированных по длине первого и типичного алфавитного порядка второго. Если строки были в таблице в столбце с именем STRING_COL, порядок может быть определен в T-SQL как ORDER BY LEN(STRING_COL) ASC, STRING_COL ASC

Чтобы дать менее формальное определение, обратите внимание на алфавитные заголовки столбцов в excel. Серия такая же картина. Посмотрите, как вы можете преобразовать целое число в базовое число 26:

  

1 -> A, 2 -> B, 3 -> C, ..., 25 -> Y, 26 -> Z, 27 -> AA, 28 -> AB, ...

Аналогия не совсем совершенна, потому что «А» ведет себя иначе, чем 0 в базе десять. Ниже приведена таблица выбранных значений, которая, мы надеемся, сделает ее более понятной:

╔════════════╦════════╗
║ ROW_NUMBER ║ STRING ║
╠════════════╬════════╣
║          1 ║ A      ║
║          2 ║ B      ║
║         25 ║ Y      ║
║         26 ║ Z      ║
║         27 ║ AA     ║
║         28 ║ AB     ║
║         51 ║ AY     ║
║         52 ║ AZ     ║
║         53 ║ BA     ║
║         54 ║ BB     ║
║      18278 ║ ZZZ    ║
║      18279 ║ AAAA   ║
║     475253 ║ ZZZY   ║
║     475254 ║ ZZZZ   ║
║     475255 ║ AAAAA  ║
║  100000000 ║ HJUNYV ║
╚════════════╩════════╝

Цель состоит в том, чтобы написать запрос SELECT, который возвращает первые 100000000 строк в порядке, указанном выше. Я выполнил свое тестирование, выполнив запросы в SSMS с отбрасыванием результирующего набора в отличие от сохранения его в таблице:

 отменить набор результатов

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

Каковы некоторые методы эффективного создания набора данных, описанного выше? Мартин Смит указал, что хранимая процедура CLR, вероятно, не является хорошим подходом из-за накладных расходов на обработку так много строк.

12 голосов | спросил Joe Obbish 11 Maypm17 2017, 15:22:51

1 ответ


-1

ok, вот мой последний скрипт.

Нет цикла, нет рекурсивного.

Он работает только для 6 символов

Самый большой недостаток - это около 22 минут за 1,00,000,000

На этот раз мой сценарий очень короткий.

SET NoCount on

declare @z int=26
declare @start [email protected]+1 
declare @MaxLimit int=10000000

SELECT TOP (@MaxLimit) IDENTITY(int,1,1) AS N
    INTO NumbersTest1
    FROM     master.dbo.spt_values x1   
   CROSS JOIN (SELECT number FROM master.dbo.spt_values WHERE [type] = 'P' AND number < 500) x2
            CROSS JOIN (SELECT number FROM master.dbo.spt_values WHERE [type] = 'P' AND number < 500) x3
    WHERE   x1.number < 219
ALTER TABLE NumbersTest1 ADD CONSTRAINT PK_NumbersTest1 PRIMARY KEY CLUSTERED (N)


select N, strCol from NumbersTest1
cross apply
(
select 
case when IntCol6>0 then  char((IntCol6%@z)+64) else '' end 
+case when IntCol5=0 then 'Z' else isnull(char(IntCol5+64),'') end 
+case when IntCol4=0 then 'Z' else isnull(char(IntCol4+64),'') end 
+case when IntCol3=0 then 'Z' else isnull(char(IntCol3+64),'') end 
+case when IntCol2=0 then 'Z' else isnull(char(IntCol2+64),'') end 
+case when IntCol1=0 then 'Z' else isnull(char(IntCol1+64),'') end strCol
from
(
select  IntCol1,IntCol2,IntCol3,IntCol4
,case when IntCol5>0 then  IntCol5%@z else null end IntCol5

,case when IntCol5/@z>0 and  IntCol5%@z=0 then  IntCol5/@z-1 
when IntCol5/@z>0 then IntCol5/@z
else null end IntCol6
from
(
select IntCol1,IntCol2,IntCol3
,case when IntCol4>0 then  IntCol4%@z else null end IntCol4

,case when IntCol4/@z>0 and  IntCol4%@z=0 then  IntCol4/@z-1 
when IntCol4/@z>0 then IntCol4/@z
else null end IntCol5
from
(
select IntCol1,IntCol2
,case when IntCol3>0 then  IntCol3%@z else null end IntCol3
,case when IntCol3/@z>0 and  IntCol3%@z=0 then  IntCol3/@z-1 
when IntCol3/@z>0 then IntCol3/@z
else null end IntCol4

from
(
select IntCol1
,case when IntCol2>0 then  IntCol2%@z else null end IntCol2
,case when IntCol2/@z>0 and  IntCol2%@z=0 then  IntCol2/@z-1 
when IntCol2/@z>0 then IntCol2/@z
else null end IntCol3

from
(
select case when N>0 then N%@z else null end IntCol1
,case when N%@z=0 and  (N/@z)>1 then (N/@z)-1 else  (N/@z) end IntCol2 

)Lv2
)Lv3
)Lv4
)Lv5
)LV6

)ca

DROP TABLE NumbersTest1
ответил KumarHarsh 12 Maypm17 2017, 14:04:18

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132