Почему таблицы с цифрами «неоценимы»?

Я видел много применений, когда вам нужно проецировать «отсутствующие данные». Например. у вас есть временной ряд (например, журнал доступа), и вы хотите показывать количество обращений в день за последние 30 дней (подумайте о панели аналитики). Если вы выполните select count (...) из ... group by day, вы получите счет за каждый день, но результат будет иметь только строку за каждый день, когда вы на самом деле имели хотя бы один доступ. С другой стороны, если вы сначала спроектируете таблицу дней из вашей таблицы чисел (выберите dateadd (день, -number, today) в качестве дня из числа), а затем вы оставите соединение со счетами (или внешними примените, что бы вы ни предложили), тогда вы получите результат, который имеет 0 для подсчета за те дни, когда у вас не было доступа. Это всего лишь один пример. Конечно, можно утверждать, что уровень представления вашей панели инструментов может обрабатывать недостающие дни и просто показывать 0 вместо этого, но некоторые инструменты (например, SSRS) просто не смогут справиться с этим.

Другие примеры, которые я видел, использовали аналогичные трюки временных рядов (дата /время +/- число) для выполнения всех видов расчетов окон. В общем, всякий раз, когда на императивном языке вы используете цикл for с известным числом итераций, декларативный и заданный характер SQL может использовать трюк, основанный на таблице чисел.

Кстати, я чувствую необходимость вызывать тот факт, что даже при использовании таблицы чисел он чувствует себя как императивное процедурное выполнение, не попадает в заблуждение, предполагая, что он is императив. Позвольте мне привести пример:

int x;
для (int i = 0; i <1000000; ++ i)
  x = i;
Е ( "% d", х);

Эта программа выведет 999999, что в значительной степени гарантировано.

Давайте попробуем сделать то же самое в SQL, используя таблицу чисел. Сначала создайте таблицу из числа 1M:

создать номера таблиц (число int не нулевой первичный ключ);
идти

declare @i int = 0
    , @j int = 0;

установить nocount;
начать транзакцию
в то время как @i <1000
начать
    set @j = 0;
    тогда как @j <1000
    начать
        вставлять в числа (число)
            значения (@ j * 1000 + @ i);
        set @j + = 1;
    конец
    совершить;
    raiserror (N'Inserted% d * 1000 ', 0, 0, @i)
    начать транзакцию;
    set @i + = 1;
конец
совершить
идти

Теперь давайте сделаем цикл for:

declare @x int;
выберите @x = число
из чисел с (nolock);
выберите @x как [@x];

Результат:

@x
-----------
88698

Если у вас теперь есть момент WTF (ведь number кластеризованный первичный ключ!), трюк называется сканирование заказа на размещение , и я не вставлял @ j * 1000 + @ i случайно ... Вы могли бы также угадать догадку и сказать результат, потому что parallelism и что иногда может быть правильным ответом.

Есть много троллей под этим мостом , и я упомянул некоторых в В SQL-булевом коротком замыкании оператора и Функции T-SQL не подразумевают определенный порядок выполнения

Я нашел таблицу чисел весьма полезной в различных ситуациях.

В Почему мне следует использовать таблицу вспомогательных номеров? , написанную в 2004 году, я приведу несколько примеров:

• Разбор строки
• Поиск недостатков идентификации
• Создание диапазонов дат (например, заполнение таблицы календаря, которая также может быть неоценимой)
• Создание временных интервалов
• Создание диапазонов IP-адресов

В Плохие привычки пинать: использование циклов для заполнения больших таблиц , я покажу, как таблицу чисел можно использовать для короткой работы по вставке большого количества строк (в отличие от подхода, использующего коленный рефлекс цикл while).

В Обработка списка целые числа: мой подход и Подробнее о разбиении списков: пользовательские разделители, предотвращение дубликатов и порядок заказа , я покажу, как использовать таблицу чисел для разбиения строки (например, набор значений, разделенных запятыми) и обеспечить сопоставление производительности между этим и другими методами. Дополнительная информация о разделении и другой обработке строк:

• Разделите строки правильным способом или следующим лучшим способом
• Разделение строк: продолжение
• Сравнение методов разделения строк /конкатенации
• Удаление дубликатов строк из SQL Server
• Сюрпризы и предположения производительности: STRING_SPLIT ()

И в Таблица номеров SQL Server, Объяснение - Часть 1 , я подробно расскажу о концепции и будущим публикациям, чтобы подробно описывать конкретные приложения.

Есть много других применений, это всего лишь несколько, которые выделялись мне достаточно, чтобы писать о них.

И как @gbn, я несколько ответы на переполнение стека и на этом сайт , который также использует таблицу чисел.

Наконец, у меня есть серия сообщений в блогах о генерации множеств без циклов, которые частично показывают преимущество производительности использования таблицы чисел по сравнению с большинством других методов (причудливый отброс Remus в сторону):

• Создать набор без циклов - 1
• Создать набор без циклов - 2
• Создать набор без циклов - 3

Вот отличный пример, который я использовал недавно из Adam Machanic:

CREATE FUNCTION dbo.GetSubstringCount
(
    @InputString TEXT,
    @SubString VARCHAR (200),
    @NoisePattern VARCHAR (20)
)
ВОЗВРАЩАЕТ INT
С СХЕМЫ
В ВИДЕ
НАЧАТЬ
    ВЕРНУТЬ
    (
        SELECT COUNT (*)
        FROM dbo.Numbers N
        ГДЕ
            SUBSTRING (@InputString, N.Number, LEN (@SubString)) = @SubString
            И PATINDEX (@NoisePattern, SUBSTRING (@InputString, N.Number + LEN (@SubString), 1)) = 0
            И 0 =
                ДЕЛО
                    WHEN @NoisePattern = '' THEN 0
                    ELSE PATINDEX (@NoisePattern, SUBSTRING (@InputString, N.Number - 1, 1))
                КОНЕЦ
    )
КОНЕЦ

Я использовал что-то еще похожее с CTE, чтобы найти конкретный экземпляр подстроки (т. е. «Найти третий канал в этой строке») для работы с коррелированными данными с разделителями:

объявить @TargetStr varchar (8000),
@SearchedStr varchar (8000),
@Occurrence int
set @ TargetStr = 'a'
set @ SearchedStr = 'abbabba'
set @ Occurrence = 3;

С появлением AS (
SELECT Number,
       ROW_NUMBER () OVER (ORDER BY Number) AS Occurrence
FROM master.dbo.spt_values
WHERE Number BENWEEN 1 AND LEN (@SearchedStr) AND type = 'P'
  И SUBSTRING (@ SearchedStr, Number, LEN (@TargetStr)) = @ TargetStr)
Номер SELECT
FROM Occurrences
WHERE Occurrence = @ Occurrence

Если у вас нет таблицы чисел, альтернативой является использование какого-либо цикла. В принципе, таблица чисел позволяет вам выполнять итерацию на основе набора без курсоров или циклов.

Я бы использовал таблицу чисел всякий раз, когда мне нужен SQL-эквивалент Enumerable.Range. Например, я просто использовал его в ответе на этом сайте: вычисление числа перестановок