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

xpil - 2018/01/22 - Pchełki /Pchełki SQL /

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.

2
Dodaj komentarz

avatar
1 Comment threads
1 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
2 Comment authors
xpilRzast Recent comment authors
  Subscribe  
najnowszy najstarszy oceniany
Powiadom o
Rzast
Gość
Rzast

Ich analizę być może przeprowadzę w osobnym wpisie […]

Przeprowadź! 🙂

%d bloggers like this: