时间戳

首页 > 解决方案 > 什么样的连接可以合并单个 SQL 表中的记录？

mysql - 什么样的连接可以合并单个 SQL 表中的记录？

问题描述

我需要在按时间相关的表中查找并合并记录。该表记录了网站中的用户活动（活动开始时间和活动结束时间）。

我正在尝试将同一用户的其他活动一小时内的任何活动合并为一个记录。因此，如果一条记录的开始时间是在同一用户之前的活动结束后 55 分钟，我会将其合并为一条记录。

我尝试了各种自我加入来实现这一点，但结果从来都不是完美的。

在两个步骤中，我尝试了这个：

  1. 首先更新updated_at（活动结束），让彼此一个小时内的所有记录都有一个共同的updated_at时间戳，也就是组中最新的。

  2. 删除组中所有后面的记录，这样只剩下最早的记录，现在有最早的created_at和最新的updated_at

-- 首先为一个用户的所有活动设置一个共同的结束时间（updated_at），间隔不到一小时

UPDATE users_activity
SET updated_at = (SELECT a.LatestEnd  FROM (SELECT
    UA1.id,
    MAX(UA2.updated_at) AS LatestEnd
FROM users_activity UA1, users_activity UA2
    WHERE
    UA1.id <> UA2.id
        AND UA1.user_id = UA2.user_id
        AND UA1.created_at > DATE_SUB(UA2.updated_at,INTERVAL 1 HOUR)
        AND UA1.created_at < UA2.updated_at
        ) a)
WHERE
    users_activity.id IN (SELECT b.id  FROM (SELECT
    UA1.id
    FROM users_activity UA1, users_activity UA2
    WHERE
    UA1.id <> UA2.id
        AND UA1.user_id = UA2.user_id
        AND UA1.created_at > DATE_SUB(UA2.updated_at,INTERVAL 1 HOUR)
        AND UA1.created_at < UA2.updated_at
        ) b);
-- next delete all the later records in the group, leaving only the earliest
DELETE FROM users_activity 
WHERE
    users_activity.id IN (SELECT * FROM (SELECT d.id FROM users_activity d 
INNER JOIN
(SELECT
    COUNT(CONCAT(user_id,'_',updated_at)) AS Duplicates,
    CONCAT(user_id,'_',updated_at) AS UserVisitEnd,
    id,
    user_id,
    MAX(created_at) AS LatestStart
FROM users_activity
    GROUP BY UserVisitEnd
    HAVING Duplicates > 1) a on a.LatestStart = d.created_at AND a.user_id = d.user_id) as AllDupes);

如果数据是这样的：

|id  |user_id|created_at         |updated_at
|5788|1222   |2019-06-06 08:55:28|2019-06-06 09:30:41
|5787|3555   |2019-06-06 08:40:04|2019-06-06 11:07:21
|5786|1222   |2019-06-06 07:11:03|2019-06-06 08:01:29
|5785|7999   |2019-06-05 18:11:03|2019-05-01 18:17:44
|5784|3555   |2019-06-04 16:53:32|2019-06-04 16:58:19
|5783|9222   |2019-04-01 15:21:32|2019-04-01 16:53:32
|5782|1222   |2019-03-29 14:02:09|2019-03-29 15:51:07
|5774|1222   |2019-03-29 13:38:43|2019-03-29 13:50:43
|5773|7999   |2018-09-23 17:38:35|2018-09-23 17:40:35

我应该得到这个结果： 

|id  |user_id|created_at         |updated_at
|5787|3555   |2019-06-06 08:40:04|2019-06-06 11:07:21
|5786|1222   |2019-06-06 07:11:03|2019-06-06 09:30:41
|5785|7999   |2019-06-05 18:11:03|2019-05-01 18:17:44
|5784|3555   |2019-06-04 16:53:32|2019-06-04 16:58:19
|5783|9222   |2019-04-01 15:21:32|2019-04-01 16:53:32
|5774|1222   |2019-03-29 13:38:43|2019-03-29 15:51:07
|5773|7999   |2018-09-23 17:38:35|2018-09-23 17:40:35

合并数据示例

新信息。此查询将为我提供包含我需要的信息的结果：要更新和合并的会话 ID。但是，当每一行的更新可能会改变其他行所需的更新时，如何进行大规模更新呢？

SELECT b.id, b.user_id, b.created_at, b.updated_at, b.UpdatedAtOfSessionToMerge, b.IDofSessionToMerge FROM (SELECT
UA1.id,
UA1.user_id,
UA1.created_at,
UA1.updated_at,
UA2.updated_at AS UpdatedAtOfSessionToMerge,
UA2.id AS IDofSessionToMerge
FROM users_activity UA1, users_activity UA2
WHERE
UA1.id <> UA2.id
    AND UA1.user_id = UA2.user_id
    AND UA1.created_at > DATE_SUB(UA2.updated_at,INTERVAL 1 HOUR)
    AND UA1.updated_at < UA2.updated_at
    AND UA1.created_at < UA2.updated_at
    ) b order by b.user_id;

标签： mysqlsql

解决方案

您可以根据参数对日期进行分组。此外，如果可以的话，订购数据在未来的处理速度方面总是好的。它还使您的查询结果更好。

SELECT min(ID) as ID, User_ID, Min(Created_At) Created_At, Max(Updated_At) as Updated_At

FROM Table GROUP BY User_ID, ORDER BY User_ID;

检查以下链接以在 MySQL 中格式化日期

推荐阅读