You are viewing a single comment's thread from:

RE: ENGAGE > Big Macs / LIFESTYLE Trade / Win some Tokens

in #engagement • 6 years ago

No worries :)

I use @steemsql and this dirty query:





declare @comment_length int declare @comment_count int SET @comment_length = (select sum(len(body)) from comments where depth > 0 and author = 'sumatranate') SET @comment_count = (select count(*) from comments where depth > 0 and author = 'sumatranate') SELECT @comment_length, '|',@comment_count, '|', @comment_length/@comment_count

6 years ago in #engagement by
$0.00
    Reply 4
    Sort:  
  • Trending
    • [-]
     6 years ago  

    This is really dirty query 😏
    You're making 2 table scans to compute length and count where it could be done in 1

    $0.00
      Reply
      [-]
       6 years ago  

      Sorry :)

      Is this better?

      select
      sum(case when depth = 0 then 1 else 0 end) Post_count,
      sum(case when depth > 0 then 1 else 0 end) Comment_count,
      sum(case when depth > 0 then len(body) else 0 end) Comment_Length,
      sum(case when depth > 0 then 1 else 0 end)/sum(case when depth = 0 then 1 else 0 end) as Comments_Per_post,
      sum(case when depth > 0 then len(body) else 0 end)/sum(case when depth > 0 then 1 else 0 end) as Comm_len_avg
      from comments where author = 'arcange'

      !ENGAGE 100

      [-]
       6 years ago  

      :(

      Sent you some directly :)

      $0.00
        Reply
        [-]
         6 years ago  

        If's way better! You can even use the new IIF() function to make it more readable:

        select
   sum(IIF(depth = 0, 1,0) AS Postcount, 
   sum(IIF(depth > 0, 1, 0)AS Commentcount,
   sum(IIF(depth > 0, len(body), 0) AS CommentLength, 
   sum(IIF(depth > 0, 1, 0))/sum(IF(depth = 0, 1, 0)) AS CommentsPerpost, 
   sum(IIF(depth > 0, len(body), 0))/sum(IIF(depth > 0, 1,0) AS Commlen_avg
from comments 
where author = 'arcange'
```<div class="pull-right"><sub><a href="/steemreply/@arcange/introducing-steemreply-stay-in-touch-with-your-steem-network">Posted with <img src="http://steemreply.com/logo-comment.png"/></a></sub></div>
        $0.00
          Reply