首页 / 更多教程 / 一条 SQL 统计大V涨粉排行榜
一条 SQL 统计大V涨粉排行榜
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了一条 SQL 统计大V涨粉排行榜，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6914字，纯文字阅读大概需要10分钟。
内容图文
古时的风筝第 70 篇原创文章
今天来说一个最近遇到的小需求，不是什么高大上的需求，当然也就谈不上什么技术深度。其实，大多数时候，我们的日常开发就是由这些不起眼的小需求组成，而且今天要说的其实就是纯用一条 SQL 实现的，而这类 SQL 还是比较典型的。
需求说明
是这样的，有一个某站的数据抓取程序(当然是合法抓取啦)，每天会抓取一次固定的几百个头部大V 的主要信息，主要就是粉丝数量，存储到一张 MySQL 表中，然后统计 7 日增粉趋势排行榜 TOP 10，也就是 7 天内增加粉丝数量最多的10个大V。
表结构如下，例如，对于 account_id 为 111 的用户，每天抓取的信息都会在表中形成一行记录，记录当天时间和粉丝数。
id account_id follower create_time
1 111 900 2020-04-30
2 111 800 2020-04-29
3 111 700 2020-04-28
CREATE TABLE `v_follower` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`account_id` varchar(20) DEFAULT NULL,
`follower` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_mid` (`account_id`) USING BTREE,
KEY `index_create_time` (`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=14672 DEFAULT CHARSET=utf8最后通过计算，形成下面的这样一个排行榜单，展示 7 天内增粉数量排名前 10 的大V 信息。
因为对实时性要求不高，这个功能用两个定时服务完成。
第一个定时任务，抓取每个大 V 的最新粉丝数
每天晚上 22 点运行，动态抓取几百个头部大 V 的最新粉丝数，然后存到表中持久化。按 500 个大V计算，每天新增 500 条记录，一年才 18万条，可以放心使用 MySQL 单表。
第二个定时任务，统计增粉 TOP 10 用户
每天凌晨之前运行，比如 23:55 分，统计出增粉前10的大V用户，然后以 List 形式存到 Redis 中。本文要说的重点就是这个统计排行的 SQL 语句。
这个 SQL 怎么写
看到这儿的同学先别着急往下看，先想想你是不是能很快的把这条 SQL 写出来。我把测试表放到了百度网盘，想要试着写一下的同学可以下载
下来，在公众号内回复「练习」即可获取下载链接。
先来分析一下，其实方法显而易见，既然要做 7 天增粉计算，那就把当天的粉丝数和7天前的粉丝数相减就可以了嘛。
没错，思路就是这么一个思路，但是这里面有几个问题。
-
首先，这不是算一个用户的增粉数，而是批量的计算几百个用户。如果一个用户当然好说了。
- 这 7 天并不一定是严格意义上的 7 天，有可能这个大V用户是最近几天的新用户，但是来势汹汹，3、4天粉丝数就达到了头部大V的数量级。还有可能是第一个定时任务出现问题，导致7天内有记录为空。
如果是统计一个大V 的增粉情况应该怎么写呢。
有的同学说，先查询出当天的粉丝数，然后查询出7天前的粉丝数，然后在 Java 中计算一下就好了。先不说这种做法效率怎么样，那首先不满足上面说的第 2 个问题，如果 7 天前没有记录怎么办。再者，我们希望只用 SQL 实现。
很多同学心里想的是，这好说啊，于是三下五除二的打开电脑，打开 MySQL 客户端，然后顺利的敲下 select，然后就没有然后了。
说一下思路，和上面说的思路都是一致的:
-
求出当天或者指定日期的粉丝数，如果所求日期无记录，则往前推，直到找到7天内最晚有记录的粉丝数；
-
求出 7 天前，如果7天前无数据，则往后推，直到找到 7天内最晚有记录的粉丝数；
- 两个粉丝数相减就是涨粉量；
总结出来一个概括公式就是：max(create_time) 的粉丝数 -min(create_time) 的粉丝数，create_time 的区间是 7 天内。
第一步，求出 account_id = ‘546195‘ 且创建日期是 2020-04-08 或者这一天与 7天之前的日期之间最晚日期的记录。由于 create_time 字段是 datetime 类型，所以使用了 DATE_FORMAT 函数做格式转换，其实是不建议在字段上做函数计算的，对效率影响很大。
SELECT
a.*
FROM
v_follower a
RIGHT JOIN (
SELECT
account_id,
max(create_time) create_time
FROM
v_follower a
WHERE
DATE_FORMAT(create_time, ‘%Y-%m-%d‘) >= DATE_ADD(‘2020-04-08‘, INTERVAL - 1 WEEK)
AND account_id = ‘546195‘
) b ON a.account_id = b.account_id
AND a.create_time = b.create_time;先获取 7 天内最大日期，用函数 max(create_time)，之后自连接取得粉丝数量字段。
第二步，获取 7 天之前的粉丝数，如果7天前无数据，则取7天内最早的记录，比如 2020-04-01 这一天的记录没有，那就取 2020-04-02 的记录，以此类推。下面是取 7 天前数据的 SQL 语句。
SELECT
a.*
FROM
v_follower a
RIGHT JOIN (
SELECT
account_id,
min(create_time) create_time
FROM
v_follower a
WHERE
DATE_FORMAT(create_time, ‘%Y-%m-%d‘) >= DATE_ADD(‘2020-04-08‘, INTERVAL - 1 WEEK)
AND DATE_FORMAT(create_time, ‘%Y-%m-%d‘) <= ‘2020-04-08‘
AND account_id = ‘546195‘
) b ON a.account_id = b.account_id
AND a.create_time = b.create_time;先取得 7 天内最小日期，用函数 min(create_time)，然后自连接查询出粉丝数字段。
第三步，将这两个查询 join 起来，变成一条记录，然后两个不同时间点的 follower 字段做个减法就可以了。
SELECT
x.*, y.follower follower_7day_ago,
x.follower - y.follower inc
FROM
(
SELECT
a.*
FROM
v_follower a
RIGHT JOIN (
SELECT
account_id,
max(create_time) create_time
FROM
v_follower a
WHERE
DATE_FORMAT(create_time, ‘%Y-%m-%d‘) >= DATE_ADD(‘2020-04-08‘, INTERVAL - 1 WEEK)
AND account_id = ‘546195‘
) b ON a.account_id = b.account_id
AND a.create_time = b.create_time
) x
LEFT JOIN (
SELECT
a.*
FROM
v_follower a
RIGHT JOIN (
SELECT
account_id,
min(create_time) create_time
FROM
v_follower a
WHERE
DATE_FORMAT(create_time, ‘%Y-%m-%d‘) >= DATE_ADD(‘2020-04-08‘, INTERVAL - 1 WEEK)
AND DATE_FORMAT(create_time, ‘%Y-%m-%d‘) <= ‘2020-04-08‘
AND account_id = ‘546195‘
) b ON a.account_id = b.account_id
AND a.create_time = b.create_time
) y ON x.account_id = y.account_id运行之后，查询出的结果如图所示
破壁
一个大V用户的增粉已经顺利查出来了，那么扩展开来，所有大V 的增粉记录也就不在话下了，首先将 account_id 条件去掉，然后按照 account_id 进行分组，分组之后就像是处理单个大V那样了，之后把查出的记录按粉丝数排序倒序取前 10 条。大功告成。
SELECT
z.*
FROM
(
SELECT
x.*, y.follower follower_7day_ago,
x.follower - y.follower inc
FROM
(
SELECT
a.*
FROM
v_follower a
RIGHT JOIN (
SELECT
account_id,
max(create_time) create_time
FROM
v_follower a
WHERE
DATE_FORMAT(create_time, ‘%Y-%m-%d‘) >= DATE_ADD(‘2020-04-08‘, INTERVAL - 1 WEEK)
GROUP BY
account_id
) b ON a.account_id = b.account_id
AND a.create_time = b.create_time
) x
LEFT JOIN (
SELECT
a.*
FROM
v_follower a
RIGHT JOIN (
SELECT
account_id,
min(create_time) create_time
FROM
v_follower a
WHERE
DATE_FORMAT(create_time, ‘%Y-%m-%d‘) >= DATE_ADD(‘2020-04-08‘, INTERVAL - 1 WEEK)
AND DATE_FORMAT(create_time, ‘%Y-%m-%d‘) <= ‘2020-04-08‘
GROUP BY
account_id
) b ON a.account_id = b.account_id
AND a.create_time = b.create_time
) y ON x.account_id = y.account_id
) z
ORDER BY
z.inc DESC
LIMIT 0,
10最后输出结果如下：
最后将结果存到 redis 中，每天只统计一次即可。
其他的
这么多子查询、连接查询，即使行数不多，查询速度也会很慢的，如果到了几十万那就更慢了。所以在 account_id 列和 create_time 列都加了索引，以免产生慢查询。
例子中的记录最后日期是 2020-04-08，所以如果有同学想要做测试的话，也最好以这天或者之前的日期为最新日期，要不然差不多数据，看不出结果。
需要的同学可以在公众号内回复「练习」获取测试表。
还可以读：
隔离做的好，数据操作没烦恼[MySQL]
做开发也要掌握的 mysql 优化思路
公众号：古时的风筝， 一个不只有技术的技术公众号。
我是风筝，一个主业 Java，同时也擅长 Python、React 的斜杠开发者。你可选择现在就关注我，或者看看历史文章再关注也不迟。
技术交流还可以加群或者直接加我微信。
一条 SQL 统计大V涨粉排行榜
标签：开发者 持久化 where 定时任务 打开 粉丝 时间 选择 结构
本文系统来源：https://blog.51cto.com/14909424/2524929
内容总结
以上是互联网集市为您收集整理的一条 SQL 统计大V涨粉排行榜全部内容，希望文章能够帮你解决一条 SQL 统计大V涨粉排行榜所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。