0

Please login or register to vote for this query.

(click on this box to dismiss)

This is a newer query to return the list of words that have the highest ratio of being used in 'closed as non-constructive' vs all posts. Although I've tried two different methods of splitting, the sheer amount of data and my rustiness with SQL optimization is causing all the methods I have tried to timeout, even when doing a pre-query for just words in closed posts. Currently running most recent 25% of posts and sorting by ratio desc, so words with largest chance of being closed.

Q&A for amateur radio enthusiasts

CREATE TABLE #Titles (PostId bigint NOT NULL, Title nvarchar(250) NOT NULL, Closed bit NOT NULL );

INSERT INTO #Titles (PostId, Title, Closed)
SELECT TOP 100 
       P.Id, 
       LOWER(Replace(Replace(Replace(Replace(Replace(Replace(P.Title, ',', ' '), '?', ''), '  ',' '), '/', ''), ':', ''), '"', '')), 
       CASE WHEN PH.id IS NOT NULL THEN 1 ELSE 0 END
FROM Posts P
   LEFT JOIN PostHistory PH ON PH.PostId = P.Id AND PH.Comment = '3'
WHERE P.Title IS NOT NULL;

CREATE TABLE #WordFrequency ( Word nvarchar(250) NOT NULL, NumNotClosed int NOT NULL, NumClosed int NOT NULL);

WITH TitleWords (PostId, Word, Rem, Closed) AS (
   SELECT T.PostId, 
          SUBSTRING(T.Title, 1, CHARINDEX(' ', T.Title + ' ')), 
          SUBSTRING(T.Title, CHARINDEX(' ', T.Title + ' ') + 1, LEN(T.Title)),
          T.Closed
   FROM #Titles T
   
   UNION ALL
   
   SELECT PostId, 
          SUBSTRING(Rem, 1, CHARINDEX(' ', Rem + ' ')), 
          SUBSTRING(Rem, CHARINDEX(' ', Rem + ' ') + 1, LEN(Rem)),
          Closed
   FROM TitleWords
   WHERE LEN(Rem) > 0
)
INSERT INTO #WordFrequency
SELECT TW.Word, 
       SUM(CASE WHEN Closed = 1 Then 0 ELSE 1 END), 
       SUM(CASE WHEN Closed = 1 Then 1 ELSE 0 END)
FROM TitleWords TW
GROUP BY TW.Word;

SELECT Word
     , NumClosed
     , NumNotClosed
     , NumClosed/(NumNotClosed+1) AS Freqish
FROM #WordFrequency
ORDER BY NumClosed/(NumNotClosed+1) DESC;

DROP TABLE #WordFrequency;
DROP TABLE #Titles;

view current version

edited apr 27 12

created apr 27 12 Tarwn