3089. 查找突发行为 🔒

题目描述

表：Posts

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| post_id     | int     |
| user_id     | int     |
| post_date   | date    |
+-------------+---------+
post_id 是这张表的主键（有不同值的列）。
这张表的每一行包含 post_id，user_id 和 post_date。

编写一个解决方案来找到在 2024 年 2 月期间在发帖行为中表现出 突发行为 的用户。突发行为 指用户在 2024 年 2 月 存在一个 连续 7 天 的时段中发帖频率是其平均每周发帖频率的 至少两倍。

注意： 在你的统计中只包含 2 月 1 日到 2 月 28 日，也就是说你应该把 2 月记为正好 4 周。

返回结果表，以 user_id 升序排序。

结果格式如下所示。

示例：

输入：

Posts 表：

+---------+---------+------------+
| post_id | user_id | post_date  |
+---------+---------+------------+
| 1       | 1       | 2024-02-27 |
| 2       | 5       | 2024-02-06 |
| 3       | 3       | 2024-02-25 |
| 4       | 3       | 2024-02-14 |
| 5       | 3       | 2024-02-06 |
| 6       | 2       | 2024-02-25 |
+---------+---------+------------+

输出：

+---------+----------------+------------------+
| user_id | max_7day_posts | avg_weekly_posts |
+---------+----------------+------------------+
| 1       | 1              | 0.2500           |
| 2       | 1              | 0.2500           |
| 5       | 1              | 0.2500           |
+---------+----------------+------------------+

解释：

用户 1：2 月份只发布了 1 个帖子，平均每周发布 0.25 个帖子，任何 7 天期间最多发布 1 个帖子。
用户 2：也只发了 1 个帖子，与用户 1 相同的平均和最大 7 天发帖频率。
用户 5：与用户 1 和用户 2 一样，用户 5 在整个 2 月份只发布了 1 篇文章，得到相同的平均和最大 7 天发帖频率。
用户 3：虽然用户 3 发布的帖子比其他用户多（3 篇），但他在连续 7 天期间中没有达到每周平均发帖频率的两倍，因此没有在输出中列出。

注意： 输出表以 user_id 升序排序。

解法

方法一：自连接 + 分组统计

我们可以使用自连接，将表 Posts 与自身连接，连接条件是 p1.user_id = p2.user_id 且 p2.post_date 在 p1.post_date 和 p1.post_date 后 6 天之间，然后我们将连接结果按照 p1.user_id 和 p1.post_id 分组，即可统计出每个用户在每天的 7 天内的发帖数量，我们将这个结果保存在表 P 中。

接着我们统计出每个用户在 2024 年 2 月份的每周平均发帖数量，保存在表 T 中。注意，我们需要查找 post_date 在 2024-02-01 和 2024-02-28 之间的记录，将记录按照 user_id 分组，然后统计每个用户的发帖数量，最后除以 4 即可得到每周平均发帖数量，我们将这个结果保存在表 T 中。

最后，我们将表 P 和表 T 连接，连接条件是 P.user_id = T.user_id，然后按照 user_id 分组，统计出每个用户在 7 天内的最大发帖数量，最后筛选出满足条件 max_7day_posts >= avg_weekly_posts * 2 的记录，即可得到结果。注意，我们需要按照 user_id 升序排序。

MySQLPython3

# Write your MySQL query statement below
WITH
    P AS (
        SELECT p1.user_id AS user_id, COUNT(1) AS cnt
        FROM
            Posts AS p1
            JOIN Posts AS p2
                ON p1.user_id = p2.user_id
                AND p2.post_date BETWEEN p1.post_date AND DATE_ADD(p1.post_date, INTERVAL 6 DAY)
        GROUP BY p1.user_id, p1.post_id
    ),
    T AS (
        SELECT user_id, COUNT(1) / 4 AS avg_weekly_posts
        FROM Posts
        WHERE post_date BETWEEN '2024-02-01' AND '2024-02-28'
        GROUP BY 1
    )
SELECT user_id, MAX(cnt) AS max_7day_posts, avg_weekly_posts
FROM
    P
    JOIN T USING (user_id)
GROUP BY 1
HAVING max_7day_posts >= avg_weekly_posts * 2
ORDER BY 1;

import pandas as pd


def find_bursty_behavior(posts: pd.DataFrame) -> pd.DataFrame:
    # 子查询 P
    p1 = pd.merge(
        posts, posts, on="user_id", suffixes=("_1", "_2")
    )  # 合并帖子表自身，根据用户ID
    p1 = p1[
        p1["post_date_2"].between(
            p1["post_date_1"], p1["post_date_1"] + pd.Timedelta(days=6)
        )
    ]  # 筛选出相邻 7 天内的帖子
    p1 = (
        p1.groupby(["user_id", "post_id_1"]).size().reset_index(name="cnt")
    )  # 统计每个用户在相邻 7 天内的帖子数

    # 子查询 T
    t = posts[
        (posts["post_date"] >= "2024-02-01") & (posts["post_date"] <= "2024-02-28")
    ]  # 筛选出 2024 年 2 月份的帖子
    t = (
        t.groupby("user_id").size().div(4).reset_index(name="avg_weekly_posts")
    )  # 计算每个用户平均每周的帖子数

    # 连接 P 和 T
    merged_df = pd.merge(p1, t, on="user_id", how="inner")  # 内连接 P 和 T

    # 过滤
    filtered_df = merged_df[
        merged_df["cnt"] >= merged_df["avg_weekly_posts"] * 2
    ]  # 过滤出满足条件的行

    # 聚合
    result_df = (
        filtered_df.groupby("user_id")
        .agg({"cnt": "max", "avg_weekly_posts": "first"})
        .reset_index()
    )  # 对满足条件的行按用户ID聚合
    result_df.columns = ["user_id", "max_7day_posts", "avg_weekly_posts"]  # 重命名列名

    # 排序
    result_df.sort_values(by="user_id", inplace=True)  # 按用户ID排序

    return result_df

3089. 查找突发行为 🔒

题目描述

解法

方法一：自连接 + 分组统计

评论