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

in cn •  2 years ago  (edited)

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

Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE STEEM!
Sort Order:  

WOW NICE LIKE IT
I am voting for you, you too have voted, so good that we can go ahead with one another.

Your posts are very informative but its sad i have to wait once in a while to get them in english. Most of the time i just copy your headlines to google translate to see what it means. Finally after 10 days , I am reading one of your blogs again.

// 稍后同步到我的中文博客和英文计算
机博客。
Shāo hòu tóngbù dào wǒ de zhōngwén bókè hé yīngwén jì suàn
jī bókè.

// later sync to my Chinese blog and English calculations
Machine blog.

I guess you know what i mean now..Thanks for the language conversions you are doing. Regards,
Anadi

Thanks for your suggestions. I'll try my best to make my posts bilingual..

Thank you.

你也开始玩简单的了~~

  ·  2 years ago (edited)

我觉得简单哈哈哈,我今天工作用到了api接口,就是在前端用ajax,刚好前几天你提过我当时就试了一下,今天直接用上了。你真是我的明灯哈哈哈

啥 API? 怎么用的?

  ·  2 years ago (edited)

手机验证码呀,要在前台让用户填手机号以后,发送请求到api文件,然后获得响应再对比验证码对不对。api我也自己写的,啦啦啦。其实对你来说可简单了哈哈

哈哈再给你几次机会了,我都看了呢,因为没有我,我就默默的点了个赞然后离去了哈哈哈

今天 排名上有你啊

天哪,我眼神不好。。。。我要仔细看看去哈哈哈哈

I am Cornholio! You will co-operate with my bunghole!