当前位置:Gxlcms > 数据库问题 > 一条 SQL 统计大V涨粉排行榜

一条 SQL 统计大V涨粉排行榜

时间:2021-07-01 10:21:17 帮助过:32人阅读

技术图片

古时的风筝第 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天前的粉丝数相减就可以了嘛。

没错,思路就是这么一个思路,但是这里面有几个问题。

  1. 首先,这不是算一个用户的增粉数,而是批量的计算几百个用户。如果一个用户当然好说了。

  2. 这 7 天并不一定是严格意义上的 7 天,有可能这个大V用户是最近几天的新用户,但是来势汹汹,3、4天粉丝数就达到了头部大V的数量级。还有可能是第一个定时任务出现问题,导致7天内有记录为空。

如果是统计一个大V 的增粉情况应该怎么写呢。

有的同学说,先查询出当天的粉丝数,然后查询出7天前的粉丝数,然后在 Java 中计算一下就好了。先不说这种做法效率怎么样,那首先不满足上面说的第 2 个问题,如果 7 天前没有记录怎么办。再者,我们希望只用 SQL 实现。

很多同学心里想的是,这好说啊,于是三下五除二的打开电脑,打开 MySQL 客户端,然后顺利的敲下 select,然后就没有然后了。

说一下思路,和上面说的思路都是一致的:

  1. 求出当天或者指定日期的粉丝数,如果所求日期无记录,则往前推,直到找到7天内最晚有记录的粉丝数;

  2. 求出 7 天前,如果7天前无数据,则往后推,直到找到 7天内最晚有记录的粉丝数;

  3. 两个粉丝数相减就是涨粉量;

总结出来一个概括公式就是: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   定时任务   打开   粉丝   时间   选择   结构   

人气教程排行