STEEM SQL 系列之 如何获取最近7天 CN 区用户发贴量,点赞数和估计收益值 SteemSQL Tutorial: How to Get Authors Order By Potential Payout in Last 7 days?

I will start the tutorial series of getting STEEM data via sql.steemdata.com. The tool we are using here is LinqPad and today, I am going to show you how to get the list of authors in the last 7 days who have published posts on the first tag 'cn'. The results are sorted by total pending payout.

You can also contact me @justyy if you want to learn a particular SQL but you don't know how to write it, which then may be included in the next posts.

SQL很简单,我认为是 Sexy Query (查询) Language 语言。这个语言很强大,主要用于操作数据库,现在比较流行的有 MSSQL, MYSQL, SQL SERVER, ORACLE 等。

我们用 LinqPad 来查询 steemsql.com。这个系列每次会讲一个语句,如果你觉得你想知道,但是不清楚怎么写的,很欢迎告诉我,我将会整理到下一系列。

基础准备工作

下载 LinqPad (免费版就够用了)。然后新建数据库连接:数据库地址是sql.steemsql.com 用户名是 steemit 密码是 steemit

这里不重复贴图了,详细可以看 @joythewanderer 的帖子关于如何添加链接

获取最近7天 CN 区用户发贴量,点赞数和估计收益值

新建 SQL 查询语句,输入以下:

select top 30 
   author, 
   count(author) as cnt, 
   sum(net_votes) as votes, 
   sum(pending_payout_value) as pending_payout_value 
from 
   Comments 
where 
   title<>'' and 
   dirty='False' and 
   category='cn' and 
   parent_author=''  and datediff(hour, created, GETDATE()) between 0 and 7*24 
group by 
   author 
order by 
  pending_payout_value desc

Run the SQL to fetch the top 30 authors, using the LinqPad:
显示结果如下:

  • 这里 top 30 就是取前30个结果
  • 按 估计收益值从大到小排序:order by pending_payout_value desc
  • 限制 CN 社区: category='cn'
  • 好的帖子 e.g 不被踩过的帖子:dirty='False
  • 是主贴(并不是评论)parent_author=''title <> '' 标题不为空,两个条件一结合比较严格。我发现像 @minnowbooster 的回复也是有带标题的。
  • 时间是过去7天:datediff(hour, created, GETDATE()) between 0 and 7*24
  • 把所有按 author 的帖子分组,取数量,点赞数 还有潜在收益。

前三甲: @oflyhigh @rivalhw @tumutanzi,大腿还有地么?

另:我会今晚把这个排名加到我的 每日榜单里,多提提意见。我想弄一个 有心的机器人 (至少 half human, half bot),让你们都爱上我,哈哈。

更新:今日榜单已经加上

Originally published at https://steemit.com Thank you for reading my post, feel free to Follow, Upvote, Reply, ReSteem (repost) @justyy which motivates me to create more quality posts.

原创 https://Steemit.com 首发。感谢阅读,如有可能,欢迎Follow, Upvote, Reply, ReSteem (repost) @justyy 激励我创作更多更好的内容。

// Later, it will be reposted to my blogs: justyy.com, helloacm.com and codingforspeed.com 稍后同步到我的中文博客和英文计算机博客

近期热贴

Recent Popular Posts


Tags: #cn #cn-programming #steem-dev #steem-sql #steemit

H2
H3
H4
3 columns
2 columns
1 column
13 Comments