Почему таблицы с цифрами «неоценимы»?
Наш резидент эксперт по базам данных сообщает нам, что таблицы индексов неоценимы . Я не совсем понимаю, почему. Вот таблица чисел:
ИСПОЛЬЗОВАТЬ модель
ИДТИ
CREATE TABLE Numbers
(
Номер INT NOT NULL,
CONSTRAINT PK_Numbers
ПЕРВИЧНАЯ КЛАВИАТУРА (номер)
С FILLFACTOR = 100
)
INSERT INTO Numbers
ВЫБРАТЬ
(a.Number * 256) + b.Number Номер AS
ИЗ
(
Номер SELECT
FROM master..spt_values
ГДЕ
type = 'P'
И число <= 255
) число),
(
Номер SELECT
FROM master..spt_values
ГДЕ
type = 'P'
И число <= 255
) b (Число)
ИДТИ
В блоге, приведенное обоснование
Таблицы номеров поистине бесценны. Я использую их все время для манипуляции строками, имитируя функции окна, заполняя тестовые таблицы большим количеством данных, устраняя логику курсора и многие другие задачи, которые были бы невероятно трудными без них.
Но я не понимаю, что такое использование, в точности - можете ли вы представить некоторые убедительные конкретные примеры того, где «таблица чисел» экономит вам массу работы на SQL Server и почему мы должны иметь их?
4 ответа
Я видел много применений, когда вам нужно проецировать «отсутствующие данные». Например. у вас есть временной ряд (например, журнал доступа), и вы хотите показывать количество обращений в день за последние 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 в сторону):
Вот отличный пример, который я использовал недавно из 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. Например, я просто использовал его в ответе на этом сайте: вычисление числа перестановок