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


![clouds-1842317_960_720.jpg](https://images.hive.blog/DQmXtmawvQJ2vvE7YhBDiLZZAx8RGfGo8gQVbmrpbVGYj9p/clouds-1842317_960_720.jpg)
(图源 ：[pixabay](https://pixabay.com/photos/clouds-male-man-person-skateboard-1842317/))

可惜这些优秀的作者们，在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人在中文社区发帖呢（当然也包括之前发现的僵尸网络用户）
![image.png](https://images.hive.blog/DQmT4k6rpEELKTQDB6Sat93PMFWeYRHnSxa6mFdUC39dSoL/image.png)

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

修正后的查询：
```
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个）：
![image.png](https://images.hive.blog/DQmc5HPhDYeu4jNXsfiuWaYgK1Z8ioTqPEoWmUvfDbYjnKE/image.png)

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

所以修改过滤条件为下边这个样子：
```
HAVING COUNT(*) >= 2  
	AND MAX(CASE WHEN created > DATEADD(YEAR, -1, GETDATE()) THEN 1 ELSE 0 END) = 0
```
部分返回结果如下（可见有177个失踪人口，呜呜呜）
![1744859760055.png](https://images.hive.blog/DQmavgiKBLoFmsZYJtKjw3ytv6dxfM2yWigpCzAJyCQ296Y/1744859760055.png)


我们还知道可以通过如下语句查询和计算用户的有效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;
```

嗯，这回结果更丰满了
![1744860623388.png](https://images.hive.blog/DQmTCCU9ixHJhyf5VeFVikH9HzvyvDoDUbvE4wXNHdpdNtf/1744860623388.png)

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

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


![1744871376815.png](https://images.hive.blog/DQmSoZE6F5ZUgKLfHNo75oKK3Yu4tsr3KbDacNAmqdGKiBW/1744871376815.png)

![1744871442819.png](https://images.hive.blog/DQmRWQUnFcrA5ikD6qFzpjZ8vLcrKKnTFdSiqre1CkuWhoz/1744871442819.png)

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


比如通过以下语句过滤出失踪超过30天以上的用户，快来看看里边有没有你呀？
```
HAVING COUNT(*) > 3  
    AND MAX(CASE WHEN c.created > DATEADD(DAY, -30, GETDATE()) THEN 1 ELSE 0 END) = 0
```


![1744871824784.png](https://images.hive.blog/DQmYM7wcA9wLp4f6c1prd7HQRBxqy7pbJ4TyQBNGfFC1PvL/1744871824784.png)

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

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

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