| comments | difficulty | edit_url | tags | |
|---|---|---|---|---|
true |
中等 |
|
表：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 升序排序。
# 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