3

Sometimes I would like use word count to describe someone's contribution, so, I wrote a query (https://data.stackexchange.com/ell/query/1206589/word-count-a-user) to do the job:

DECLARE @total_count int, @post_id int, @post_body nvarchar(max);  

SET @total_count = 0;

DECLARE vendor_cursor CURSOR FOR
SELECT id, Body
FROM posts 
WHERE posts.OwnerUserId = 9872;

OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor   
INTO @post_id, @post_body
PRINT FORMATMESSAGE('  id   | count | total count')
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @count int;
    SELECT @count = (LEN(@post_body ) - LEN(replace(@post_body , ' ', '')))
    SELECT @total_count = @total_count + @count
    PRINT FORMATMESSAGE('%6d | %5d | %d', @post_id, @count, @total_count)
    FETCH NEXT FROM vendor_cursor
    INTO @post_id, @post_body
END

I guess this is not a question, neither a code review. Just a tool query sharing, and gather some feedback.

5

You can probably do that in one go, without the cursor and variables.

Use the STRING_SPLIT function, that returns a table from a text based on a split char.

Here is the simplified query.

SELECT id [Post Link]
     , count(*) [# words]
     , SUM (count(*)) OVER (ORDER BY Id) [total]
FROM posts 
cross apply string_split(posts.body,' ') words
WHERE posts.OwnerUserId = 9872
group by id
order by id

It is worth mentioning that the Body of the Posts table contains the rendered HTML of the post. As such it might have more "words" due to the added tags like <p>. To stay closer to the actual text that was posted, find the latest revision of the Post in the PostHistory table (PostHistoryTypeId in ( 2, 5, 8)) and then split the Text column. I leave that as an exercise for the reader

Remember that we have a site called CodeReview.se that are happy to take SQL query improvement/review requests. They also have a stackexchange tag for questions that are about SEDE.

2
  • Thank you so much. Btw, what does "words" do in the "cross apply string_split ..." line? – andrewngdl Mar 7 '20 at 8:55
  • @andrewngdl it is the alias for the table, just as I could do posts p then p would be the alias for the posts table, allowing me to do p.id. – rene Mar 7 '20 at 9:41

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .