hql取满足条件最新一条记录_一条 SQL 统计大V涨粉排行榜

最新推荐文章于 2022-07-27 15:27:49 发布

weixin_39552204

最新推荐文章于 2022-07-27 15:27:49 发布

阅读量132

点赞数

古时的风筝第 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 的斜杠开发者。你可选择现在就关注我，或者看看历史文章再关注也不迟。

技术交流还可以加群或者直接加我微信。

【没写过慢查询，不足以谈编程!】

weixin_39552204

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
hql取满足条件最新一条记录_一条 SQL 统计大V涨粉排行榜

古时的风筝第70篇原创文章今天来说一个最近遇到的小需求，不是什么高大上的需求，当然也就谈不上什么技术深度。其实，大多数时候，我们的日常开发就是由这些不起眼的小需求组成，而且今天要说的其实就是纯用一条 SQL 实现的，而这类 SQL 还是比较典型的。需求说明是这样的，有一个某站的数据抓取程序(当然是合法抓取啦)，每天会抓取一次固定的几百个头部大V 的主要信息，主要就是粉丝数量，存储...
复制链接

扫一扫