SQL Machine Learning using matrix multiplication

Question

What is the easiest classification algorithm in SQL when my data looks like this?

A = 101000101110
B = 010101110010
C = 100101101000
B = 100101101101
C = 100010000001
A = 100010010101

These are binary vectors that indicate the presence of a certain attribute.

I was thinking about training some kind of regression using python that gives me a vector.

Then I would only need to multiple the vector with the binary vectors and I would bet some number indicating me what kind of label I have using a threshold method.

Are there any python algorithms that allow me to do that?

Answer 1

You have a few overlapping topics here:

1. Fitting a model to your data in Python
2. Storing model parameters to the database
3. Executing SQL queries to apply the model

I would start with a linear model, such as logistic regression or linear discriminant analysis.

You should think carefully how you structure the data in the database. This is more of a programming topic, but I would have a table with few columns and several rows to represent each item. This way you don't need to modify the schema if your data gets a new input feature.

Then as a final step you'll need to "just" write the SQL query, joining attribute data and model parameters and output. But this is off-topic on an AI forum.

Getting back to linear regression, you must re-arrange the binary representations into separate columns:

array([[1, 0, 1, 0, 0, 0, 1, 0, 1, 1, 1, 0],
       [0, 1, 0, 1, 0, 1, 1, 1, 0, 0, 1, 0],
       [1, 0, 0, 1, 0, 1, 1, 0, 1, 0, 0, 0],
       [1, 0, 0, 1, 0, 1, 1, 0, 1, 1, 0, 1],
       [1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1],
       [1, 0, 0, 0, 1, 0, 0, 1, 0, 1, 0, 1]])