最为一名伪技术博主，最近输出的内容竟然没有几篇和技术相关的，我深表惭愧。恰好这两天发现了一点好玩的东西，分享给大家。

(图源：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。具体如何写出高效又优雅的查询，就留给小伙伴们自己探究喽。

好了，今天就先学到这里，你，学废了嘛？

每天进步一点点：当SQL遇到JSON

JSON信息表示的Active_votes

通过OPENJSON统计JSON信息的条目

通过OPENJSON条件查询JSON信息

课后作业

相关链接