调查一下中文社区“失踪”人口

话说中文社区曾有几个HIVE友的文章我很喜欢看，怎么说呢，有些“文章”的感觉，言之有物、文字驾驭功夫了得、情感也比较细腻。

(图源：pixabay)

可惜这些优秀的作者们，在HIVE上已经消失了好长时间，甚至微信群中都极少见到他/她们的精彩发言，甚是遗憾。

我不由想起网络上经常见到的一个梗：

先生当初退出文坛，我是坚决反对的，也曾苦苦挽留过。先生惜字如金，行文如刀，如剑，如号角，如灯盏……都是那么让人振聋发聩，耳目一新。不曾想先生决绝的离去，背影如同流星划过天际，短暂却耀眼，但这却让中国文坛倒退了三十年！

那么这些年，中文社区都流失了哪些人口呢？我突然想到，或许可以用HiveSQL来分析一下，正好这两天刚刚完成一键建立到HiveSQL转发服务的脚本，玩得正上瘾呢。

首先，如何定义中文社区的人口呢？我将其粗略定义为：

最近3年有在'hive-105017'发表文章（即主贴）的用户

根据这个定义我们可以写出如下查询

SELECT author
FROM comments
WHERE category = 'hive-105017'
  AND depth = 0
  AND created > DATEADD(YEAR, -3, GETDATE()) 
GROUP BY author

其中depth = 0，表示我们只查询发表文章（主贴）的用户
DATEADD(YEAR, -3, GETDATE()) ，会得到3年前的当前时间。

查询后我们得到类似如下的结果，原来这三年曾经有750人在中文社区发帖呢（当然也包括之前发现的僵尸网络用户）

没错，上述查询返回结果中包含了只发一两篇主贴的僵尸网络用户。当然了，发一两篇的不一定一定是僵尸网络用户，但是不活跃用户没跑了。所以我们过滤掉这部分中户（僵尸网络以及不活跃用户）

修正后的查询：

SELECT author, COUNT(*) AS posts
FROM comments
WHERE category = 'hive-105017'
  AND depth = 0
  AND created > DATEADD(YEAR, -3, GETDATE())
GROUP BY author
HAVING COUNT(*) >= 2  
ORDER BY author;

部分返回结果如下（可见这部分用户有334个）：

接下来，我们要从这334个用户中找出来失踪人口，我们暂定为最近一年没有在中文社区发帖的用户。

所以修改过滤条件为下边这个样子：

HAVING COUNT(*) >= 2  
    AND MAX(CASE WHEN created > DATEADD(YEAR, -1, GETDATE()) THEN 1 ELSE 0 END) = 0

部分返回结果如下（可见有177个失踪人口，呜呜呜）

我们还知道可以通过如下语句查询和计算用户的有效HP

SELECT Name, (Vesting_shares + Received_vesting_shares - Delegated_vesting_shares) * Hive_per_vest AS E_HP 
FROM Accounts, DynamicGlobalProperties  
WHERE name = 'xxx'

综合之前的查询，我们给查询结果添加上有效HP(E_HP)显示

SELECT c.author, 
       COUNT(*) AS posts,
       (a.Vesting_shares + a.Received_vesting_shares - a.Delegated_vesting_shares) * d.Hive_per_vest AS E_HP
FROM comments c
JOIN Accounts a ON c.author = a.Name
JOIN DynamicGlobalProperties d ON 1 = 1
WHERE c.category = 'hive-105017'
  AND c.depth = 0
  AND c.created > DATEADD(YEAR, -3, GETDATE())
GROUP BY c.author, a.Vesting_shares, a.Received_vesting_shares, a.Delegated_vesting_shares, d.Hive_per_vest
HAVING COUNT(*) >= 2  
    AND MAX(CASE WHEN c.created > DATEADD(YEAR, -1, GETDATE()) THEN 1 ELSE 0 END) = 0
ORDER BY c.author;

嗯，这回结果更丰满了

其中需要解释的是GROUP BY c.author, a.Vesting_shares, a.Received_vesting_shares, a.Delegated_vesting_shares, d.Hive_per_vest因为后边的字段要在计算字段在计算 E_HP用到，所以需要加在这里。

再加上排名，以及最后在CN区发帖过去了多少天，再加上一些格式上的调整（对了，把最近三年发帖数改成>3，用以屏蔽掉更多僵尸网络用户等），最终筛选出来如下87个失踪超一年的人口。

我们还可以通过修改SQL，来查询失踪超过半年，或者2个月，或者30天以上的人口。

比如通过以下语句过滤出失踪超过30天以上的用户，快来看看里边有没有你呀？

HAVING COUNT(*) > 3  
    AND MAX(CASE WHEN c.created > DATEADD(DAY, -30, GETDATE()) THEN 1 ELSE 0 END) = 0

需要补充说明的是，以上失踪人口登记表仅仅针对中文社区哦。

有些用户可能还活跃在HIVE上，比如在其它社区（或者没使用社区功能，仅使用标签）发帖，那么也会被列入失踪人口哦。

所以，问题来了，你在“失踪”人口之列嘛？你为啥玩起了失踪？什么时候再回到中文社区大家庭呢？