3808. 寻找情绪一致的用户

题目描述

表：reactions

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| user_id      | int     |
| content_id   | int     |
| reaction     | varchar |
+--------------+---------+
(user_id, content_id) 是这张表的主键（值互不相同）。
每一行代表用户对某条内容的反应。

根据以下要求编写一个解决方案，以识别 情绪一致的用户：

为每个用户统计他们发送的总反应次数。
仅包含 至少对 5 个不同内容项 作出反应的用户。
如果用户至少 60% 的反应属于 同一种类型，则认为其 情绪一致。

返回结果表按 reaction_ratio 降序排序，然后按 user_id 升序排序。

注意：

reaction_ratio 应该舍入到 2 位小数

结果格式如下所示。

示例：

输入：

reactions 表：

+---------+------------+----------+
| user_id | content_id | reaction |
+---------+------------+----------+
| 1       | 101        | like     |
| 1       | 102        | like     |
| 1       | 103        | like     |
| 1       | 104        | wow      |
| 1       | 105        | like     |
| 2       | 201        | like     |
| 2       | 202        | wow      |
| 2       | 203        | sad      |
| 2       | 204        | like     |
| 2       | 205        | wow      |
| 3       | 301        | love     |
| 3       | 302        | love     |
| 3       | 303        | love     |
| 3       | 304        | love     |
| 3       | 305        | love     |
+---------+------------+----------+

输出：

+---------+-------------------+----------------+
| user_id | dominant_reaction | reaction_ratio |
+---------+-------------------+----------------+
| 3       | love              | 1.00           |
| 1       | like              | 0.80           |
+---------+-------------------+----------------+

解释：

用户 1：
- 总反应数 = 5
- 'like' 出现了 4 次
- reaction_ratio = 4 / 5 = 0.80
- 满足 60% 一致的要求
用户 2：
- 总反应数 = 5
- 出现最多的反应只出现了 2 次
- reaction_ratio = 2 / 5 = 0.40
- 不满足一致的要求
用户 3：
- 总反应数 = 5
- 'love' 出现了 5 次
- reaction_ratio = 5 / 5 = 1.00
- 满足一致的要求

结果表按 reaction_ratio 降序排序，然后按 user_id 升序排序。

解法

方法一：分组统计 + 连接查询

思考

需按用户统计反应次数、主导反应及其占比，并筛掉反应不足 \(5\) 次或占比低于 \(0.60\) 的用户。直接在原表上多次扫描会重复聚合。

同一用户的多种反应要同时参与「最大次数」与「总次数」的计算，再回表取出达到最大次数的反应类型。

为此先按 \((\textit{user\_id},\textit{reaction})\) 分组得到次数表 \(t\)，再在 \(t\) 上按用户聚合出最大次数与比例，筛入 \(s\)。

最后用 \(s\) 与 \(t\) 按用户连接、保留次数等于最大值的行，并按比例与编号排序。分组后再连接，避免在明细上反复过滤。

我们首先统计每个用户对每种反应的计数，记录在临时表 \(t\) 中。然后，我们在临时表 \(t\) 的基础上，统计每个用户的最大反应计数和总反应计数，计算出反应比例，并筛选出满足条件的用户，记录在临时表 \(s\) 中。最后，我们将临时表 \(s\) 和 \(t\) 进行连接查询，找出每个用户的主导反应，并按要求排序输出结果。

MySQLPandas

# Write your MySQL query statement below
WITH
    t AS (
        SELECT
            user_id,
            reaction,
            COUNT(1) cnt
        FROM reactions
        GROUP BY 1, 2
    ),
    s AS (
        SELECT
            user_id,
            MAX(cnt) mx_cnt,
            ROUND(MAX(cnt) / SUM(cnt), 2) reaction_ratio
        FROM t
        GROUP BY 1
        HAVING reaction_ratio >= 0.60 AND SUM(cnt) >= 5
    )
SELECT user_id, reaction dominant_reaction, reaction_ratio
FROM
    s
    JOIN t USING (user_id)
WHERE cnt = mx_cnt
ORDER BY 3 DESC, 1;

import pandas as pd
from decimal import Decimal, ROUND_HALF_UP


def find_emotionally_consistent_users(reactions: pd.DataFrame) -> pd.DataFrame:
    t = reactions.groupby(["user_id", "reaction"]).size().reset_index(name="cnt")

    s = (
        t.groupby("user_id")
        .agg(mx_cnt=("cnt", "max"), total_cnt=("cnt", "sum"))
        .reset_index()
    )

    s["reaction_ratio"] = (
        s["mx_cnt"]
        .div(s["total_cnt"])
        .apply(
            lambda x: float(
                Decimal(str(x)).quantize(Decimal("0.00"), rounding=ROUND_HALF_UP)
            )
        )
    )

    s = s[(s["reaction_ratio"] >= 0.60) & (s["total_cnt"] >= 5)]

    merged = pd.merge(
        s[["user_id", "mx_cnt", "reaction_ratio"]],
        t,
        left_on=["user_id", "mx_cnt"],
        right_on=["user_id", "cnt"],
    )

    result = (
        merged[["user_id", "reaction", "reaction_ratio"]]
        .rename(columns={"reaction": "dominant_reaction"})
        .sort_values(by=["reaction_ratio", "user_id"], ascending=[False, True])
        .reset_index(drop=True)
    )

    return result

3808. 寻找情绪一致的用户

题目描述

解法

方法一：分组统计 + 连接查询

评论