key word/phrase search against similarity Matrix in SQLite

Question

I have a table of documents with the schema:

CREATE TABLE Frequency (
  docid VARCHAR(255),
  term VARCHAR(255),
  count int,
PRIMARY KEY(docid, term));

To find the similarity raw scores for all documents I would use:

SELECT a.term, b.term, sum(a.count * b.count) 
FROM Frequency a, Frequency b
Where a.term = b.term

I am not sure why this works, but it did on test data to do D*DT, where DT is transpose of D.

I now need to compute the query/text string similarity for terms something like "congress gun laws"

I believe this involves unions and group by, but all my query attempts fail e.g.,:

SELECT *
FROM Frequency a, Frequency b, Frequency c
Where a.term = b.term 
UNION
SELECT  a.docid, 'congress' as term, 1 as count 
UNION
SELECT  b.docid , 'gun' as term, 1 as count
UNION 
SELECT  c.docid , 'laws' as term, 1 as count 
Group by docid;

I am new to this kind of SQL and would appreciate a narrative as I am trying to understand What I am doing as well.

Please explain why the first query works and how I could approach the second.

Answer 1

To put it simply, what we really want to do here is to add the new tuples to the table, and then compare this new table to the old one using the matrix transpose operation you mentioned above. What you would need is to 'mark' these new keywords so that you could use them for a conditional in your query. So this

SELECT b.docid, b.term, SUM(a.count * b.count) 
FROM (SELECT * FROM Frequency
      UNION
      SELECT  'q' as docid, 'congress' as term, 1 as count 
      UNION
      SELECT  'q' as docid, 'gun' as term, 1 as count
      UNION 
      SELECT  'q' as docid, 'laws' as term, 1 as count 
     ) a, Frequency b
WHERE a.term = b.term 
AND a.docid = 'q'
GROUP BY b.docid, b.term
ORDER BY SUM(a.count * b.count);

would give you the list of docids with the term and their respective similarity scores.