0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for those who create, solve, and study puzzles

set nocount on;

declare @title nvarchar(4000), @postId int, @nc int;
declare @words table (postid int, word nvarchar(4000), nc int);
declare @word nvarchar(4000), @to int, @i int  = 0, @sampled int = 0;

declare crs cursor read_only forward_only for
select top(100000) lower(p.Title), p.Id, 
   case when h.PostId is null then 0 else 1 end as nc
from Posts p
left outer join (
  select PostId 
  from PostHistory 
  where Comment = '3' and PostHistoryTypeId = 10) h
on p.Id = h.PostId
where p.PostTypeId = 1
order by p.Id;

open crs;

fetch next from crs into @title, @postId, @nc;
while 0 = @@fetch_status
begin
  -- Analyze a NC closed title 
  -- or 10% of other titles
  --
  if 1 = @nc or rand() > 0.90 
  begin
    --raiserror (N'At title: %s', 0,1, @title);
    while 1=1
    begin
      -- discard everything before first a-z
      set @to = patindex(N'[a-z]%', @title);
      
      if (0 = @to)
      begin
         --raiserror (N'No more words in %s', 0,3, @title);
         break;
      end
      
      set @title = substring(@title, @to, 8000);
      
      -- find next non a-z
      set @to = patindex(N'%[^a-z]%', @title);
      if @to = 0
      begin
         -- this is the last word in the title
        set @to = len(@title);
      end
      
      set @word = substring(@title, 1, @to-1);
      
      -- ignore anything less than 3 chars
      if len(@word) > 3
      begin
        insert into @words (postid, word, nc)
        values  (@postId, @word, @nc);
      end
      
      set @title = substring(@title, @to+1, 8000);
      --raiserror (N'Found word: ''%s'' Left: ''%s''', 0,1, @word, @title);
    end
    set @sampled += 1;
  end
  fetch next from crs into @title, @postId, @nc;
  set @i += 1;
  if 0 =  @i % 1000
  begin
    raiserror (N'Processed %d titles (sampled %d). At postId: %d', 0, 2, 
      @i, @sampled, @postId);
  end
end
raiserror (N'Exit at %d titles (sampled %d). At postId: %d', 0, 2, 
      @i, @sampled, @postId);

close crs;
deallocate crs;

select word, count(*) as cnt, nc
from @words 
group by nc, word
order by cnt desc;

view current version

edited apr 28 12

created apr 27 12 remusrusanu