Naive bayes calculation in sql

I want to use naive bayes to classify documents into a relatively large number of classes. I'm looking to confirm whether an mention of an entity name in an article really is that entity, on the basis of whether that article is similar to articles where that entity has been correctly verified.

Say, we find the text "General Motors" in an article. We have a set of data that contains articles and the correct entities mentioned within in. So, if we have found "General Motors" mentioned in a new article, should it fall into that class of articles in the prior data that contained a known genuine mention "General Motors" vs. the class of articles which did not mention that entity?

(I'm not creating a class for every entity and trying to classify every new article into every possible class. I already have a heuristic method for finding plausible mentions of entity names, and I just want to verify the plausibility of the limited number of entity name mentions per article that the method already detects.)

Given that the number of potential classes and articles was quite large and naive bayes is relatively simple, I wanted to do the whole thing in sql, but I'm having trouble with the scoring query...

Here's what I have so far:

CREATE TABLE `each_entity_word` (
  `word` varchar(20) NOT NULL,
  `entity_id` int(10) unsigned NOT NULL,
  `word_count` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (`word`, `entity_id`)
);

CREATE TABLE `each_entity_sum` (
  `entity_id` int(10) unsigned NOT NULL DEFAULT '0',
  `word_count_sum` int(10) unsigned DEFAULT NULL,
  `doc_count` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (`entity_id`)
);

CREATE TABLE `total_entity_word` (
  `word` varchar(20) NOT NULL,
  `word_count` int(10) unsigned NOT NULL,
  PRIMARY KEY (`word`)
);

CREATE TABLE `total_entity_sum` (
  `word_count_sum` bigint(20) unsigned NOT NULL,
  `doc_count` int(10) unsigned NOT NULL,
  `pkey` enum('singleton') NOT NULL DEFAULT 's


                    
                    
                        
                        sql
                        
                        mysql
                        
                        machine-learning
                        
                        nlp
                        
                        bayesian
                        
                    
                    
                        
                        
                        
                        
                        
                        Edit
                        
                        
                        
                            
                        
                        
                        Report

Here is a blog post detailing what you are looking for: http://nuncupatively.blogspot.com/2011/07/naive-bayes-in-sql.html

I have coded up many versions of NB classifiers in SQL. The answers above advocating changing analysis packages were not scalable to my large data and processing time requirements. I had a table with a row for each word/class combination (nrows = words * classes) and a coefficient column. I had another table with a column for document_id and word. I just joined these tables together on word, grouped by document, and summed the coefficients and then adjusted the sums for the class probability. This left me with a table of document_id, class, score. I then just picked the min score (since I was doing a complement naive bayes approach, which I found worked better in a multi-class situation).

As a side note, I found many transformations/algorithm modifications improved my holdout predictions a great deal. They are described in the work of Jason Rennie on "Tackling the Poor Assumptions of Naive Bayes Text Classifiers" and summarized here: http://www.ist.temple.edu/~vucetic/cis526fall2007/liang.ppt

answered 2011-02-22T17:54:24.997

Naive bayes calculation in sql

1 Answer

Your Answer