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

Наш резидент эксперт по базам данных сообщает нам, что таблицы индексов неоценимы . Я не совсем понимаю, почему. Вот таблица чисел:

ИСПОЛЬЗОВАТЬ модель
ИДТИ

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 и почему мы должны иметь их?

101 голос | спросил Jeff Atwood 25 Jam1000000amWed, 25 Jan 2012 04:45:27 +040012 2012, 04:45:27

4 ответа


74

Я видел много применений, когда вам нужно проецировать «отсутствующие данные». Например. у вас есть временной ряд (например, журнал доступа), и вы хотите показывать количество обращений в день за последние 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 не подразумевают определенный порядок выполнения

ответил Remus Rusanu 25 Jam1000000amWed, 25 Jan 2012 04:58:31 +040012 2012, 04:58:31
45

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

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

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

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

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

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

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

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

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

ответил Aaron Bertrand 25 Jpm1000000pmWed, 25 Jan 2012 19:12:45 +040012 2012, 19:12:45
25

Вот отличный пример, который я использовал недавно из 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

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

ответил JNK 25 Jam1000000amWed, 25 Jan 2012 04:55:18 +040012 2012, 04:55:18
11

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

ответил A-K 26 Jam1000000amThu, 26 Jan 2012 00:28:32 +040012 2012, 00:28:32

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

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

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