1

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 about the site for people interested in the history and origins of science and mathematics

CREATE TABLE #Numbers ( n int NOT NULL );
INSERT INTO #Numbers
SELECT TOP 250 ROW_NUMBER() OVER (Order By Id) FROM Posts;

CREATE TABLE #Titles (Title varchar(250) NOT NULL, Closed bit NOT NULL );

INSERT INTO #Titles (Title, Closed)
SELECT TOP 700000 -- 25%
       ' ' + LOWER(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(CAST(P.Title as varchar(250)), ',', ' '), '?', ''), '*',''), '/', ''), ':', ''), '"', ''), '(', ''), ')', ''), '''', ''),'`',' '), '<', ''),'  ',' ')) + ' ', 
       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' AND PH.PostHistoryTypeId = 10
WHERE P.Title IS NOT NULL
   AND P.PostTypeId = 1
ORDER BY P.Id DESC; -- reverse chronological creation

CREATE TABLE #WordFrequency ( Word varchar(250) NOT NULL, TotalNum int NOT NULL, NumClosed int NOT NULL);

WITH TitleWords (Word, Closed) AS (
      SELECT 
             SubString(Title, n + 1 , CHARINDEX(' ', Title, n + 1) - n ),
             Closed
      FROM #Titles T
         INNER JOIN #Numbers N ON SUBSTRING(T.Title, N.n, 1) = ' '
      WHERE N.n < LEN(T.Title) - 1
)
INSERT INTO #WordFrequency(Word, TotalNum, NumClosed)
SELECT TW.Word, 
       COUNT(*),
       SUM(CAST(Closed AS int))
FROM TitleWords TW
WHERE TW.Word <> ''
GROUP BY TW.Word;

SELECT * FROM
(SELECT TOP 10
       Reason = 'High Ratio'
     , Word
     , TotalNum
     , NumClosed
     , 1.0 * NumClosed/TotalNum AS Ratio
FROM #WordFrequency
WHERE TotalNum > 1  -- uncommon mispellings, really infrequently used words (2 mil posts)
ORDER BY 1.0 * NumClosed/TotalNum DESC, NumClosed DESC) A

UNION 

SELECT * FROM
(SELECT TOP 10
       Reason = 'High Occurrence'
     , Word
     , TotalNum
     , NumClosed
     , 1.0 * NumClosed/TotalNum AS Ratio
FROM #WordFrequency
WHERE 1.0 * NumClosed/TotalNum > .1
ORDER BY NumClosed DESC, 1.0 * NumClosed/TotalNum DESC) B;

  
DROP TABLE #WordFrequency;
DROP TABLE #Titles;
DROP TABLE #Numbers;

fork query

edited apr 28 12

created apr 27 12 Tarwn