3

Please login or register to vote for this query.

(click on this box to dismiss)

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

set nocount on;

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

-- with no right to declare functions, must use an iterative cursor solution
--
declare crs cursor read_only forward_only for
select top(1000000) 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
 into #nc_words
  from @words 
  where nc = 1
  group by nc, word;
create unique clustered index nc_words_word 
  on #nc_words(word);

select word, count(*) as cnt
  into #population_words
  from @words 
  where nc = 0
  group by nc, word;
create unique clustered index population_words_word 
  on #population_words(word);

select nc.word, nc.cnt as nc_count, g.cnt as good_count
from #nc_words nc
left outer join #population_words g
  on nc.word = g.word
where nc.word not in (
select top (10) percent pw.word
  from #population_words pw
  order by pw.cnt desc)
order by nc.cnt desc;

fork query

edited apr 30 12

created apr 27 12 remusrusanu