Pchełki SQL: rzadkie dwójki, ciąg dalszy

Ostatnio szukaliśmy w polskim słowniku rzadkich kombinacji dwuliterowych. Zastosowana metoda była prawdopodobnie jedną z możliwie najgorszych, dlatego dziś nadgryziemy zagadnienie z nieco innej strony, przy okazji odświeżając sobie składnię polecenia CROSS APPLY.

Przy kombinacjach dwuliterowych teoretycznych kombinacji było ledwie tysiąc z kawałkiem. Przy większej liczbie znaków mamy wykładniczy wzrost ilości danych do przemielenia, dlatego podejście z wygenerowaniem wszystkich możliwych kombinacji, a następnie wyszukiwanie ich w słowniku, mija się z celem.

Zamiast tego wygenerujemy sobie po prostu listę wszystkich sekwencji n-znakowych, które faktycznie istnieją w słowniku, a następnie je zdeduplikujemy i zapiszemy do osobnej tabelki, żeby się im potem bezwstydnie przyglądać.

Zaczniemy od napisania funkcji, która dla zadanego tekstu wejściowego oraz wartości n wygeneruje wszystkie unikalne n-literowe sekwencje, które w tym tekście są:

create function getNComb(@txt nvarchar(15), @n tinyint)
returns @t table(s nvarchar(15) not null)
as begin
	declare @s nvarchar(15), @i tinyint = 1, @seg nvarchar(15)
	while @i <= len(@txt) - @n + 1 begin
		SET @seg = substring(@txt, @i, @n)
		if not exists(select * from @t where s = @seg)
			insert @t select @seg
		set @i += 1
	end
	return 
end

Sprawdźmy, czy działa:

select * from getNComb('tartak', 2)

Wynik:

s
--
ta
ar
rt
ak

Jak widać ciąg 'ta' pojawił się tylko raz, chociaż w słowie 'tartak' występuje dwukrotnie.

Skoro działa, wygenerujmy teraz wszystkie segmenty dwuliterowe z całego słownika:

select s, count(*) c 
into seg2
from slowa sl
cross apply dbo.getNComb(sl.slowo, 2)
group by s

Tym razem zapytanie wymieszało się w czasie "tylko" około czterech minut, czyli kilkakrotnie szybciej, niż poprzednim razem.

Powtórzmy operację dla segmentów trzy- i czteroliterowych:

select s, count(*) c 
into seg3
from slowa sl
cross apply dbo.getNComb(sl.slowo, 3)
group by s
select s, count(*) c 
into seg4
from slowa sl
cross apply dbo.getNComb(sl.slowo, 4)
group by s

Mamy teraz kompletne zestawienia wszystkich występujących w słowniku podciągów 2-, 3- oraz 4-literowych wraz z ich popularnością w kolumnie c.

Ich analizę być może przeprowadzę w osobnym wpisie, teraz natomiast omówię operator CROSS APPLY.

Jest to tak naprawdę odpowiednik operatora INNER JOIN (lub w skrócie JOIN), tylko zamiast tabeli na drugim końcu połączenia mamy funkcję tabelaryczną (czyli taką funkcję, która zwraca rekordy)

Funkcja ta zostanie wywołana dokładnie jeden raz dla każdego rekordu z tabeli głównej. Jeżeli funkcja nie zwróci żadnego rekordu (odpowiednik sytuacji, w której JOIN nie znajduje dopasowania), operator CROSS APPLY wyeliminuje cały rekord z wyników. Jeżeli natomiast funkcja zwróci więcej niż jeden rekord, wówczas operator ten odpowiednio zwielokrotni rekord z tabeli głównej. Jedyna istotna różnica w składni między operatorami JOIN oraz CROSS APPLY jest taka, że przy JOIN musimy podać warunek dopasowania ("ON ..."), a przy CROSS APPLY - nie.

3 komentarze

    1. Obawiam się, że faktycznie to zrobię; ostatnio trochę gonię w piętkę z nowymi tematami, a takie “analizy” to doskonały zapychacz 😉

  1. oj późno trafiłem na taką stronę.
    A może bardziej elegancko i wydajniej?

    create function getNComb2 (@txt nvarchar(15), @n tinyint)
    returns table
    as
       return
          (with
             qr as (select x = 1 union all select x+1 from qr where x<len(@txt)- @n + 1)
          select distinct
             s = substring(@txt, x, @n)
          from qr
       )

Skomentuj teo Anuluj pisanie odpowiedzi

Twój adres e-mail nie zostanie opublikowany.