2

Please login or register to vote for this query.

(click on this box to dismiss)

Less than performant query that takes all the words (sort of) that show up in post titles that have been closed a not constructive (I think) and selects the 10 that occur the most frequently. Added a filter for common words, but what it probably needs to do is analyze words that were and were not included in closed posts, then return only the top 10 that had no instances of non-constructive (or below a threshold?) Replace the "TOP 100 PERCENT" with a much smaller number, 1000 took about 40s when I ran it, 100 was about 300ms

Q&A for linguists, teachers, and students wanting to discuss the finer points of the Latin language

CREATE TABLE #Titles (PostId bigint, Title nvarchar(250) );

INSERT INTO #Titles (PostId, Title)
SELECT TOP 100 PERCENT P.Id, LOWER(Replace(Replace(Replace(Replace(Replace(P.Title, ',', ' '), '?', ''), '  ',' '), '/', ''), ':', ''))
FROM Posts P
   INNER JOIN PostHistory PH ON PH.PostId = P.Id
WHERE PH.Comment = '3';

CREATE TABLE #StuffToIgnore ( Word varchar(250) );
INSERT INTO #StuffToIgnore
VALUES ('it')
       , ('for')
       , ('to')
       , ('is')
       , ('a')
       , ('the')
       , ('and')
       , ('of')
       , ('are')
       , ('as')
       , ('or')
       , ('i')
       --, ('what')
       --, ('how')
       ;       

WITH TitleWords (PostId, Word, Rem) AS (
   SELECT PostId, SUBSTRING(Title, 1, CHARINDEX(' ', Title + ' ')), SUBSTRING(Title, CHARINDEX(' ', Title + ' ') + 1, LEN(Title))
   FROM #Titles T
   
   UNION ALL
   
   SELECT PostId, SUBSTRING(Rem, 1, CHARINDEX(' ', Rem + ' ')), SUBSTRING(Rem, CHARINDEX(' ', Rem + ' ') + 1, LEN(Rem))
   FROM TitleWords
   WHERE LEN(Rem) > 0
)
SELECT TOP 10 TW.Word, COUNT(PostId) AS NumOccurred
FROM TitleWords TW
   LEFT JOIN #StuffToIgnore STI ON STI.Word = TW.Word
WHERE STI.Word IS NULL
GROUP BY TW.Word
ORDER BY COUNT(PostId) DESC;

DROP TABLE #Titles;
DROP TABLE #StuffToIgnore;

fork query

edited apr 27 12

created apr 27 12 Tarwn