Dlaczego tabele liczb są „nieocenione”?

Nasz ekspert w dziedzinie bazy danych mówi nam, że tabele liczb są nieocenione . Nie bardzo rozumiem dlaczego. Oto tabela liczb:

USE Model
GO

CREATE TABLE Numbers
(
    Number INT NOT NULL,
    CONSTRAINT PK_Numbers 
        PRIMARY KEY CLUSTERED (Number)
        WITH FILLFACTOR = 100
)

INSERT INTO Numbers
SELECT
    (a.Number * 256) + b.Number AS Number
FROM 
    (
        SELECT number
        FROM master..spt_values
        WHERE 
            type = 'P'
            AND number <= 255
    ) a (Number),
    (
        SELECT number
        FROM master..spt_values
        WHERE 
            type = 'P'
            AND number <= 255
    ) b (Number)
GO

Uzasadnieniem dla posta na blogu jest:

Tabele liczb są naprawdę nieocenione. Używam ich cały czas do manipulacji ciągami, symulacji funkcji okna, zapełniania tabel testowych dużą ilością danych, eliminacji logiki kursora i wielu innych zadań, które bez nich byłyby niezwykle trudne.

Ale nie rozumiem dokładnie, jakie są te zastosowania - czy możesz podać kilka przekonujących, konkretnych przykładów, w których „tablica liczb” oszczędza mnóstwo pracy w SQL Server - i dlaczego powinniśmy je mieć?

Widziałem wiele zastosowań, kiedy trzeba wyświetlać „brakujące dane”. Na przykład. masz szereg czasowy (na przykład dziennik dostępu) i chcesz pokazać liczbę trafień dziennie w ciągu ostatnich 30 dni (panel kontrolny analizy). Jeśli to zrobisz select count(...) from ... group by day, będziesz liczyć za każdy dzień, ale wynik będzie miał tylko wiersz dla każdego dnia, w którym faktycznie miałeś co najmniej jeden dostęp. Z drugiej strony, jeśli najpierw rzutujesz tabelę dni ze swojej tabeli liczb ( select dateadd(day, -number, today) as day from numbers), a następnie opuściłeś łączenie z liczbami (lub aplikacją zewnętrzną, cokolwiek ci się podoba), otrzymasz wynik, który ma 0 dla liczby dni nie miał dostępu. To tylko przykład. Oczywiście można argumentować, że warstwa prezentacji na desce rozdzielczej poradzi sobie z brakującymi dniami i po prostu pokaże 0, ale niektóre narzędzia (np. SSRS) po prostu nie będą w stanie sobie z tym poradzić.

Inne przykłady, które widziałem, wykorzystywały podobne triki szeregów czasowych (data / godzina +/- liczba) do wykonywania wszelkiego rodzaju obliczeń w oknie. Ogólnie rzecz biorąc, ilekroć w imperatywnym języku użyłbyś pętli for ze znaną liczbą iteracji, deklaratywna i ustawiona natura SQL może użyć sztuczki opartej na tabeli liczb.

BTW, odczuwam potrzebę zwrócenia uwagi na fakt, że chociaż przy użyciu tabeli liczb wydaje się to bezwzględnie konieczne do wykonania procedury, nie popadaj w błąd, zakładając, że jest to konieczne. Podam przykład:

int x;
for (int i=0;i<1000000;++i)
  x = i;
printf("%d",x);

Ten program wyświetli 999999, co jest prawie całkowicie gwarantowane.

Spróbujmy to samo w SQL Server, używając tabeli liczb. Najpierw utwórz tabelę zawierającą 1 000 000 liczb:

create table numbers (number int not null primary key);
go

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

set nocount on;
begin transaction
while @i < 1000
begin
    set @j = 0;
    while @j < 1000
    begin
        insert into numbers (number) 
            values (@j*1000+@i);
        set @j += 1;
    end
    commit;
    raiserror (N'Inserted %d*1000', 0, 0, @i)
    begin transaction;
    set @i += 1;
end
commit
go

Teraz zróbmy „pętlę for”:

declare @x int;
select @x = number 
from numbers with(nolock);
select @x as [@x];

Wynik to:

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

Jeśli masz teraz moment WTF (w końcu number to klastrowany klucz podstawowy!), Sztuczka nazywa się skanowaniem kolejności alokacji, a ja nie wstawiłem @j*1000+@iprzypadkowo ... Mógłbyś również zaryzykować i powiedzieć, że wynik jest taki, ponieważ równoległość, która czasem może być poprawną odpowiedzią.

Pod tym mostem jest wiele trolli i wspomniałem o niektórych w zwarciu operatora logicznego On SQL Server, a funkcje T-SQL nie oznaczają określonej kolejności wykonywania

Znalazłem tabelę liczb całkiem przydatną w różnych sytuacjach.

W Dlaczego warto rozważyć użycie tabeli liczb pomocniczych? , napisany w 2004 roku, pokazuję kilka przykładów:

• Przetwarzanie ciągu
• Znajdowanie luk w tożsamości
• Generowanie zakresów dat (np. Zapełnianie tabeli kalendarza, co może być również nieocenione)
• Generowanie przedziałów czasu
• Generowanie zakresów adresów IP

W Złe nawyki, aby kopać: używając pętli do zapełniania dużych tabel , pokazuję, jak można wykorzystać tabelę liczb, aby w krótkim czasie wstawiać wiele wierszy (w przeciwieństwie do kolanowego podejścia przy użyciu pętli while).

Podczas przetwarzania listy liczb całkowitych: moje podejście i Więcej na temat dzielenia list: niestandardowe ograniczniki, zapobieganie duplikacjom i utrzymywanie porządku , pokazuję, jak używać tabeli liczb do dzielenia łańcucha (np. Zestawu wartości oddzielonych przecinkami) i zapewnienia wydajności porównania tej i innych metod. Więcej informacji na temat dzielenia i innych operacji na łańcuchach:

• Podziel ciągi znaków we właściwy sposób - lub następny najlepszy sposób
• Podział ciągów: kontynuacja
• Porównywanie metod dzielenia / łączenia łańcuchów
• Usuwanie duplikatów z ciągów znaków w programie SQL Server
• Niespodzianki dotyczące wydajności i założenia: STRING_SPLIT ()

I SQL Server tabeli numerów, Poradnik - Część 1 , daję jakieś tło na temat koncepcji i mieć w przyszłości stanowisk w sklepie do konkretnych zastosowań szczegółów.

Istnieje wiele innych zastosowań, to tylko kilka, które wyróżniły się na tyle, że mogłem o nich napisać.

I podobnie jak @ gbn, mam kilka odpowiedzi na temat przepełnienia stosu i na tej stronie, które również używają tabeli liczb.

Na koniec mam serię postów na blogu o generowaniu zestawów bez zapętlania się, które częściowo pokazują przewagę wydajności wynikającą z używania tabeli liczb w porównaniu z większością innych metod (pomijając dziwne wartości odstające Remusa):

• Wygeneruj zestaw bez pętli - 1
• Wygeneruj zestaw bez pętli - 2
• Wygeneruj zestaw bez pętli - 3

Oto świetny przykład, którego ostatnio użyłem od Adama Machanica:

CREATE FUNCTION dbo.GetSubstringCount
(
    @InputString TEXT, 
    @SubString VARCHAR(200),
    @NoisePattern VARCHAR(20)
)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    RETURN 
    (
        SELECT COUNT(*)
        FROM dbo.Numbers N
        WHERE
            SUBSTRING(@InputString, N.Number, LEN(@SubString)) = @SubString
            AND PATINDEX(@NoisePattern, SUBSTRING(@InputString, N.Number + LEN(@SubString), 1)) = 0
            AND 0 = 
                CASE 
                    WHEN @NoisePattern = '' THEN 0
                    ELSE PATINDEX(@NoisePattern, SUBSTRING(@InputString, N.Number - 1, 1))
                END
    )
END

Użyłem czegoś innego podobnego do a, CTEaby znaleźć konkretną instancję podłańcucha (tj. „Znajdź trzecią potok w tym ciągu”) do pracy z skorelowanymi danymi rozdzielonymi:

declare @TargetStr varchar(8000), 
@SearchedStr varchar(8000), 
@Occurrence int
set @TargetStr='a'
set @SearchedStr='abbabba'
set @Occurrence=3;

WITH Occurrences AS (
SELECT Number,
       ROW_NUMBER() OVER(ORDER BY Number) AS Occurrence
FROM master.dbo.spt_values
WHERE Number BETWEEN 1 AND LEN(@SearchedStr) AND type='P'
  AND SUBSTRING(@SearchedStr,Number,LEN(@TargetStr))=@TargetStr)
SELECT Number
FROM Occurrences
WHERE Occurrence=@Occurrence

Jeśli nie masz tabeli liczb, alternatywą jest użycie pętli. Zasadniczo tabela liczb pozwala wykonywać iteracje oparte na zestawie, bez kursorów i pętli.

Używałbym tabeli liczb, ilekroć potrzebuję równoważnika SQL Enumerable.Range. Na przykład użyłem go w odpowiedzi na tej stronie: obliczając liczbę permutacji