STEEM SQL 系列之历史上的今天怎么实现的？SteemSQL Tutorial: How to Get Historic Posts of Today on SteemIt?

justyy (82)in #cn • 7 years ago (edited)

Thank you @arcange for creating STEEMSQL!

STEEM SQL Tutorial Series：

SteemSQL Tutorial: How to Get Authors Order By Potential Payout in Last 7 days?

@dapeng has some posts on "History of Today" which reveals some old posts that were published at the same time (but not this year apparently) on steemit. It can be achieved using the following SQL.

select top 10 *
from 
    Comments
where
    FORMAT(created,'MM-dd','en-us') = FORMAT(GetDate(),'MM-dd','en-us') and 
    FORMAT(created,'yyyy','en-us') <> FORMAT(GetDate(),'yyyy','en-us') and
    title <> ''
order by 
    total_payout_value desc

Let's explain this line by line.

select top 10 * Choosing the first 10 posts
from Comments Table Comments holds posts and comments
FORMAT(created,'MM-dd','en-us') = FORMAT(GetDate(),'MM-dd','en-us') The Month and Day should be the same as today
FORMAT(created,'yyyy','en-us') <> FORMAT(GetDate(),'yyyy','en-us') But it can't be this year.
title <> '' Filter out comments, which have empty titles.
order by total_payout_value desc Show the most earned posts first.

We could also add other conditions, such as categories = 'cn' if we are only interested in the CN posts.

Image Credit: Pixabay.com

感谢 @arcange 创造了 STEEMSQL!

STEEM SQL 系列：

STEEM SQL 系列之如何获取最近7天 CN 区用户发贴量，点赞数和估计收益值

@dapeng 之前搞了一个 “历史上的今天” 挖坟贴，也就是通过 SQL 查询在以前在同一天发表的帖子，听起来很玄乎？实际上就是以下SQL。我们先来看看：

select top 10 *
from 
    Comments
where
    FORMAT(created,'MM-dd','en-us') = FORMAT(GetDate(),'MM-dd','en-us') and 
    FORMAT(created,'yyyy','en-us') <> FORMAT(GetDate(),'yyyy','en-us') and
    title <> ''
order by 
    total_payout_value desc

我们来解释一下：

select top 10 * 选择前10条记录
from Comments 查询 STEEMSQL 里的 Comments 表
FORMAT(created,'MM-dd','en-us') = FORMAT(GetDate(),'MM-dd','en-us') 发表时间的月和日要和今天一样
FORMAT(created,'yyyy','en-us') <> FORMAT(GetDate(),'yyyy','en-us') 但是又不能是今年。
title <> '' 标题为空，也就是限制文章类型（评论的标题一般是空）
order by total_payout_value desc 按照收益排序

我们还可以其它条件，比如 categories = 'cn' 只查询第一个标签为 cn 的帖子。

CN 每日排行榜

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

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 激励我创作更多更好的内容。