I have many bodies of text, and for each of them, I want to extract all unigrams, bigrams, and trigrams (words, not characters) and insert the counts and ngram lengths into another table.
Right now I am thinking of unnesting a regexp-splitted body of text using WITH ORDINALITY, and then using multiple subqueries for the bigrams and trigrams, but that requires ordering . However, I think this might be an inefficient way of going about it, since this sort of positional data should normally be accessed by index.
I am currently implementing this in Python, and a huge bottleneck is the dictionary insertion and searching of dictionaries/sets for stopwords.
Here is a very basic example:
Input:
This is a small, small sentence.
Output
ngram | count | length
-------------------------------------
this | 1 | 1
is | 1 | 1
a | 1 | 1
small | 2 | 1
sentence | 1 | 1
this is | 1 | 2
is a | 1 | 2
a small | 1 | 2
small small | 1 | 2
small sentence | 1 | 2
this is a | 1 | 3
is a small | 1 | 3
a small small | 1 | 3
small small sentence | 1 | 3
Stripping the punctuation/handling lowercase is not an issue here, but getting the proper counts is important.
As an preliminary or intermediate step, I would also be removing stopwords which, in this case, are this, a, and is.
ngram | count | length
--------------------------------------
small | 2 | 1
sentence | 1 | 1
small small | 1 | 2
small sentence | 1 | 2
small small sentence | 1 | 3
In the above example