最为一名伪技术博主，最近输出的内容竟然没有几篇和技术相关的，我深表惭愧。恰好这两天发现了一点好玩的东西，分享给大家。
(图源 ：pixabay)
话说，在查询HIVESQL的Comments表时，发现其中竟然保存了每篇文章的投票信息，咦，这就有意思了，比如我们可以通过这个查询哪篇文章得票数最多？或者说哪篇文章被踩最多等等。
补充：其实我们同样可以通过TxVotes来做相关查询，但是这不是今天要说的重点。
JSON信息表示的Active_votes
当我尝试通过Comments表的Active_votes来进行查询时，却发现问题来了，Active_votes并不是作为一个表存在，而是一组JSON数据，如何查询成了大问题。
随便找了一篇文章的投票数据，大致是这个样子：
[{"voter":"predictor2100","weight":461329,"rshares":"241536059946","percent":7900,"reputation":"3033366683403","time":"2017-07-09T05:48:09"},{"voter":"davleo1119","weight":32146,"rshares":1033014798,"percent":10000,"reputation":"51488332594","time":"2017-07-03T07:42:03"}]
上述数据，包含了针对一篇文章的两个用户投票信息，里边包含了voter、weight、rshares、percent、reputation、time等信息。
可见，数据的组织上和普通的数据表也无甚太大区别，只不过数据表我们可以直接通过字段来查询，但是Active_votes则是一个符合JSON格式的大字符串。
为了更好的描述问题，我们先来执行一个普通查询：
SELECT TOP 2 *
FROM Comments
WHERE Author = 'oflyhigh'
AND YEAR(Created) = 2023
AND Depth = 0
ORDER BY Created DESC;
上述SQL语句会选出我2023年两篇最新的文章。
我们来看一下第一篇文章的返回的Active_votes（部分）：
通过OPENJSON统计JSON信息的条目
现在问题来了，我想在返回结果的同时，同时看一下我得到了多少投票，那么查询语句该如何修改呢？换句话说，如何统计Active_votes部分的条目数量呢？
答案是使用OPENJSON转化JSON内容，然后就可以像操作数据表一样进行操作了，以下SQL会在返回其它数据的同时，返回得票数：
SELECT TOP 2 Author, Title, Permlink, Created,
(
SELECT COUNT(*)
FROM OPENJSON(active_votes)
) AS Votes
FROM Comments
WHERE Author = 'oflyhigh'
AND YEAR(Created) = 2023
AND Depth = 0
ORDER BY Created DESC;
返回结果如下：
对照一下https://hive.blog/ 上我们文章的得票数，发现通过SQL计算出来的，与HIVE.BLOG上显示的完全一致。
通过OPENJSON条件查询JSON信息
在查看Comments数据表结构时，发现其中有一个Net_votes字段，简单来讲，这代表一个帖子的净得票（我理解是weight>0的投票）。
将净得票加入上述查询，我们会得到如下结果：
可以看到第二篇文章的净得票与得票数并不相同！那么如何从Active_votes得出净得票数目呢？如果是普通的数据表，我们可以直接给查询加上条件，但是对一组JSON条目，我们又该如何进行条件查询呢？
以下是MicroSoft关于OPENJSON的官方语法信息：
OPENJSON( jsonExpression [ , path ] ) [ <with_clause> ]
<with_clause> ::= WITH ( { colName type [ column_path ] [ AS JSON ] } [ ,...n ] )
根据上述语法，我们写出如下查询SQL:
SELECT TOP 2
Author,
Title,
Permlink,
Created,
Net_votes,
(
SELECT COUNT(*)
FROM OPENJSON(active_votes) WITH
(
weight BIGINT '$.weight'
)
WHERE weight > 0
) AS Votes
FROM Comments
WHERE Author = 'oflyhigh'
AND YEAR(Created) = 2023
AND Depth = 0
ORDER BY Created DESC;
查询结果如下，Net_Votes与我们计算出来的Votes一致啦。
（至于第一个帖子怎么多了一票？答案是我撰文期间有人帮我点了个赞，我就不重新截图啦）。
补充：其实Net_votes计算不单单是去掉零权重的票，还要减去DownVotes等，不过这个不是今天学习的重点，暂且忽略。
课后作业
回到我们文章开头说的问题：
我们可以通过这个查询哪篇文章得票数最多？或者说那哪文章被踩最多？
有了上边学习的基础，实现这两个功能应该是轻而易举的小事啦？那么这两个SQL就交给HIVE上的朋友们来写吧。（友情提示，pencent值为负，表示DownVote！）
以下是O哥2023年被踩得最狠的两个帖子：
当然了，踩得狠不狠，不能单看人数，踩的比重（percent），还要看踩你的人是否有足够的有效HP。具体如何写出高效又优雅的查询，就留给小伙伴们自己探究喽。
好了，今天就先学到这里，你，学废了嘛？