title: BM25 implementation in SQL Server
body: <p>I've hit a stump, while trying to implement the BM25 algorithm in SQL Server 2008 R2. I know that SQL Server includes the Full-Text Search option, which already implements a variant of BM25, but I would like to do some tests and since the FTS procedures are non-editable (as far as I know), I've decided to implement it myself. </p>
  
  <p>I have two tables, TF (term frequency) and DF (document frequency) with the following structures:</p>
  
  <p><strong>TF</strong></p>
  
  <p>*Note: the weight column denotes importance of the word (it is usually 1)</p>
  
  <pre><code>ID | Term | DocumentID | Count | Weight*
  </code></pre>
  
  <p><strong>DF</strong></p>
  
  <pre><code>ID | Term | Count
  </code></pre>
  
  <p>The TF table contains the relationship between a term and a document; that is, the frequency of the term in a document. The DF table contains information about how many documents contain a term. Using these two tables I would now like to compute BM25 similarity values between two documents (one document acts as a query) according to the formula in the <a href="http://en.wikipedia.org/wiki/Okapi_BM25" rel="nofollow noreferrer">Wikipedia article</a>. The tables TF and DF translate to functions f(q, D) and n(q) respectively:</p>
  
  <p><img src="https://i.stack.imgur.com/qjfAF.png" alt="enter image description here"></p>
  
  <p><img src="https://i.stack.imgur.com/PYC6k.png" alt="enter image description here"></p>
  
  <p>I would like the result to be in this format:</p>
  
  <pre><code>DocumentA_ID | DocumentB_ID | BM25_Value
  </code></pre>
  
  <p>Here is some code that I have up to now:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[BM25]
  -- default parameters k =&gt; [1.2 - 2.0], b =&gt; [0.0 - 1.0]
      @K1 FLOAT = 1.2,
      @B FLOAT = 0.75
  AS
  BEGIN
      SET NOCOUNT ON;
  
      DECLARE @N FLOAT;
      DECLARE @AVGDL FLOAT;
  
      -- number of all documents
      SELECT @N = CAST(COUNT(*) AS FLOAT)
      FROM FullDocument;
  
      -- average length of documents (in words)
      SELECT @AVGDL = AVG(Length) 
      FROM (
          SELECT  DocumentID, 
                  CAST(COUNT(*) AS FLOAT) AS Length 
          FROM TF 
          GROUP BY DocumentID 
      ) A;
  
      -- BM25 implementation  
  --  SELECT  TF.Term, 
  --          TF.DocumentID, 
  --          C.DocumentLength, 
  --          LOG((@N - DF.Count + 0.5)/(DF.Count + 0.5)) * (TF.Count * (@K1 + 1))/(TF.Count + @K1 * (1 - @B + @B * (C.DocumentLength / @AVGDL))) AS BM25, 
  --          Weight
  --  FROM TF 
  --  INNER JOIN DF ON TF.Term = DF.Term
  --  INNER JOIN (
  --      SELECT  DocumentID, 
  --              COUNT(*) AS DocumentLength 
  --      FROM TF 
  --      GROUP BY DocumentID
  --  ) C ON TF.DocumentID = C.DocumentID
  END
  </code></pre>
  
  <p>I'm having trouble constructing the query in the last section (BM25 implementation) to get the desired result format. Any help would be greatly appreciated.</p>