mysql 分组统计后取每组最新的一条数据

原创

mob64ca12e77061 2024-09-01 05:05:54 ©著作权

©著作权归作者所有：来自51CTO博客作者mob64ca12e77061的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL 分组统计后取每组最新的一条数据

在数据分析和管理中，常常需要对数据进行分组统计。在这一过程中，有时我们需要从每个组中提取最新的记录。本文将详细介绍如何使用 MySQL 执行这一操作，并提供相应的代码示例。

问题背景

假设我们有一个用户活动记录表 user_activity，它的结构如下：

id	user_id	activity	created_at
1	1	login	2023-09-20 10:00:00
2	1	logout	2023-09-20 12:00:00
3	2	login	2023-09-21 09:00:00
4	2	logout	2023-09-21 10:00:00
5	1	login	2023-09-22 08:00:00

我们需要统计每个用户的最新活动记录，并返回每组的最新活动信息。

SQL 查询的实现

我们可以通过使用子查询来实现。下面的 SQL 语句展示了如何从 user_activity 表中获取每个用户的最新活动：

SELECT ua.*
FROM user_activity ua
JOIN (
    SELECT user_id, MAX(created_at) AS latest_activity
    FROM user_activity
    GROUP BY user_id
) latest ON ua.user_id = latest.user_id AND ua.created_at = latest.latest_activity;

解析代码

子查询: SELECT user_id, MAX(created_at) AS latest_activity FROM user_activity GROUP BY user_id
- 这个子查询按 user_id 分组，找出每个用户的最新活动时间。
连接操作: JOIN
- 将子查询结果与原表 user_activity 连接，条件是 user_id 和 created_at 都要匹配。
结果输出: SELECT ua.*
- 最终输出包含所有字段的最新活动记录。

结果示例

执行上述查询后，可能会得到如下结果：

id	user_id	activity	created_at
5	1	login	2023-09-22 08:00:00
4	2	logout	2023-09-21 10:00:00

使用场景

这个查询的使用场景非常广泛，例如：

用户行为分析，找出最后一次登录或退出的时间。
生成用户活动报告，提取用户最新的成交记录。

最后的思考

通过这种方式，我们可以高效地从大量数据中提取出有价值的信息。在实际应用中，可以进一步优化查询性能，比如增加索引，特别是在数据量庞大时。

序列图示例

我们可以使用序列图来更好地理解这个过程：

sequenceDiagram
    participant A as User Activity Table
    participant B as Subquery
    participant C as Final Result

    A->>B: Group by user_id and get MAX(created_at)
    B->>A: Return latest_activity
    A->>C: Join on user_id and created_at
    C->>A: Get final result