Pragmatic approach to k-means clusters with PostgreSQL

Question

I am looking for a super simple and very pragmatic approach to k-means clustering of questions using PostgreSQL database alone.

While I am fully aware that this method may not yield meaningful results if my assumptions are off, it may be a good and first attempt at categorizing my data.

Imagine a small online forum, where users are free to ask brief questions about ANY topic they want but without the need to categorize them, and other users need to be notified about a new question if it matches the topics of interest to which they have previously subscribed.

My plan is to first break down each incoming question into lexemes using to_tsvector but in all honesty I am a bit lost about what to do afterwards.

Even assuming I have correctly identified the k categories to which the question may be matched, how would I go about deciding whether a questions should fall into one (or more) category?

Answer 1

you could use the ranking returned from the text search as a cut-off to decide if the question matches a topic. For each category you could hold a document with relevant terms and search the query text against these terms.

Btw, there's a nice extension called madlib (it's more of a utility package) - it contains many useful features/algorithms including topic analysis & clustering. Take a look at madlib documentation

Answer 2

Basket-Of-Words approach:

1. Take the unique words and their number in the dataset and look through the top X words. Search for the most important words that might mark a cluster.
2. Put the chosen words into columns with their number per question as values in each row.
3. Cluster on all word columns that you chose. In order to cluster, have a look at the Sisense Tech Talk Multi-dimensional Clustering Using K-Means in Postgres SQL.

It is using PostgreSQL database alone, and though the code is not short, it is simple and pragmatic in that there is an example that you simply and pragmatically can adapt to your needs. The example leads to such a cluster result, I guess that is what you are looking for:

4. Use the assigned cluster_id to check the working of the chosen words. Improve iteratively. That would be the pragmatic manual approach.

Alternatively, at a given set of important words and their numbers, you might also do TF-IDF in SQL by step-wise calculating:

• Term frequency
• Inverse document frequency
• Term frequency–Inverse document frequency