使用 MySQL 5.5 比较后续行之间的时间

Question

这是我在MySQL 5.5.1数据库社区版本上tusers表

mysql> SELECT * FROM `tusers`;
+------------+------------+----------+-----+
| tIDUSER    | tDate      | tHour    | tID |
+------------+------------+----------+-----+
| Controneri | 2022-01-06 | 07:54:42 |   1 |
| Controneri | 2022-01-06 | 07:43:38 |   2 |
| Controneri | 2022-01-06 | 07:13:09 |   3 |
| Controneri | 2022-01-06 | 06:31:52 |   4 |
| Controneri | 2022-01-06 | 06:13:12 |   5 |
+------------+------------+----------+-----+
5 rows in set (0.13 sec)

我只需要从表中选择tusers这些行

+------------+------------+----------+-----+
| tIDUSER    | tDate      | tHour    | tID |
+------------+------------+----------+-----+
| Controneri | 2022-01-06 | 07:43:38 |   2 |
| Controneri | 2022-01-06 | 06:13:12 |   5 |
+------------+------------+----------+-----+

因为与上一行相比，同一用户Controneri在一小时内重复其他行。

每个用户对网页的访问都存储在表上的日期和时间。 但是我只需要提取第一次访问，并排除一小时时间跨度内的重复访问。 在此示例中，用户在 1 月 6 日Controneri他登录了 5 次。但是有效的访问是在06:13:12和07:43:38，因为在06：13：12访问之后，在07:13:12之前还有其他访问，即在一小时结束之前与06:13:12小时(06:31:52和07:13:09，第4行和第3行)相比。

我尝试过但没有成功。

我的表结构和下面Select querydb-fiddle.com，它提供了MySQL 5

有什么建议吗？

-- ----------------------------
-- Table structure for tusers
-- ----------------------------
DROP TABLE IF EXISTS `tusers`;
CREATE TABLE `tusers`  (
`tIDUSER` varchar(255) NULL DEFAULT NULL,
`tDate` date NULL DEFAULT NULL,
`tHour` time NULL DEFAULT NULL,
`tID` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`tID`) USING BTREE
) ENGINE = InnoDB;
-- ----------------------------
-- Records of tusers
-- ----------------------------
INSERT INTO `tusers` VALUES ('Controneri', '2022-01-06', '07:54:42', 1);
INSERT INTO `tusers` VALUES ('Controneri', '2022-01-06', '07:43:38', 2);
INSERT INTO `tusers` VALUES ('Controneri', '2022-01-06', '07:13:09', 3);
INSERT INTO `tusers` VALUES ('Controneri', '2022-01-06', '06:31:52', 4);
INSERT INTO `tusers` VALUES ('Controneri', '2022-01-06', '06:13:12', 5);
SELECT
a.tID,
a.tDate,
a.tHour,
a.tIDUSER,
TIMEDIFF( a.tHour, b.tHour ) AS tDif 
FROM
`tusers` a
JOIN `tusers` b ON 
a.tDate = b.tDate 
AND a.tIDUSER = b.tIDUSER 
AND a.tID > b.tID 
WHERE
( TIMEDIFF( a.tHour, b.tHour ) BETWEEN '00:00:00' AND '01:00:00' ) 
ORDER BY
a.tIDUSER,
a.tDate,
a.tHour ASC;

Answer 1

对于 MySQL 5.5，您可以通过跟踪用户变量中的先前值来实现此目的 -

SELECT tIDUSER, tDate, tHour, tID
FROM (
SELECT
tusers.*,
IF(@prev_date_time IS NULL OR @prev_user <> tIDUSER OR @prev_date_time + INTERVAL 1 HOUR < TIMESTAMP(tDate, tHour), @prev_date_time := TIMESTAMP(tDate, tHour), NULL) AS `show`,
@prev_user := tIDUSER
FROM tusers, (SELECT @prev_date_time := NULL, @prev_user := NULL) n
ORDER BY tIDUSER ASC, tDate ASC, tHour ASC
) t
WHERE `show` IS NOT NULL
ORDER BY tIDUSER ASC, tDate ASC, tHour ASC;

这是一个数据库<>小提琴。感谢粘性位，因为我冒昧地从他们的数据库<>小提琴中"借用"。

MySQL 5.6 手册指出 -

但是，涉及用户的表达式的计算顺序 变量未定义。

而在后来的版本中扩展为——

涉及用户变量的表达式的计算顺序为 定义。例如，不能保证 SELECT @a，@a：=@a+1 首先评估@a，然后执行分配。

MySQL 5.7 手册还指出 -

也可以在语句中为用户变量赋值 除了设置。(此功能在 MySQL 8.0 中已弃用，并且 可能会在后续版本中删除。进行分配时 这样，赋值运算符必须是 ：= 而不是 =，因为 后者被视为比较运算符 = 在语句中 其他 比设置：

尽管有上述警告，但这种方法已广泛使用多年。 您的里程可能会有所不同。

我怀疑这会在较大的结果集中表现不佳，但请尝试一下。

Answer 2

根据OP在评论中的要求，这是一个使用递归CTE的查询，该查询将在MySQL版本8及更高版本中提供。

WITH RECURSIVE
cte1
AS
(
SELECT tusers.tiduser,
tusers.tdate,
tusers.thour,
tusers.tid,
addtime(tusers.tdate, tusers.thour) AS sane_timestamp_representation,
row_number() OVER (PARTITION BY tusers.tiduser
ORDER BY addtime(tusers.tdate, tusers.thour) ASC) AS rn
FROM tusers
),
cte2
AS
(
SELECT cte1.tiduser,
cte1.tdate,
cte1.thour,
cte1.tid,
cte1.sane_timestamp_representation,
0 AS n
FROM cte1
UNION ALL
SELECT cte1.tiduser,
cte1.tdate,
cte1.thour,
cte1.tid,
cte1.sane_timestamp_representation,
cte2.n + 1 AS n
FROM cte2
INNER JOIN cte1
ON cte2.tiduser = cte1.tiduser
AND cte1.sane_timestamp_representation > adddate(cte2.sane_timestamp_representation, INTERVAL 1 HOUR)
),
cte3
AS
(
SELECT cte2.tiduser,
cte2.tdate,
cte2.thour,
cte2.tid,
cte2.sane_timestamp_representation,
row_number() OVER (PARTITION BY cte2.tiduser,
cte2.n
ORDER BY cte2.sane_timestamp_representation ASC) rn
FROM cte2
)
SELECT cte3.tiduser,
cte3.tdate,
cte3.thour,
cte3.tid
FROM cte3
WHERE cte3.rn = 1
ORDER BY cte3.tiduser ASC,
cte3.sane_timestamp_representation ASC;

数据库<>小提琴

1. 在cte1中，我们首先将时间戳的日期和时间部分结合在一起(将它们保存为两个不同的列并不是最聪明的想法;当必须跨越日期边界时，它会变得一团糟)。我们还根据时间戳按每个用户的升序分配row_number()rn。 从现在开始，cte1充当我们的"基表"。

阿拉伯数字。 现在在cte2递归性发生了。作为锚点，我们从cte1cte1.rn = 1的位置获取所有行。这些是具有该用户最小时间戳的用户的记录。我们还添加了一些数字n。对于这些初始锚点行，我们将n设置为0.n将充当哪些行不能相互覆盖的指示器。所有具有n + xforx > 1的行都不能被任何具有n(每个用户)的行所覆盖。

在递归步骤中，我们联接每个用户过去一小时cte1的所有记录。由于结果集中已有的记录(每个用户)无法涵盖这些记录，因此它们已经过了一个小时，因此我们将n + 1分配给它们n。

3.cte3添加了另一个row_number()rn，按时间戳按user和n升序对记录进行排序。那些rn为1的用户不会被用户的任何先前记录覆盖，因为所有其他具有相同或更大n的时间戳都较大，而那些n较小的用户不会按照我们构建的n覆盖它们。因此，我们可以从cte3rn = 1中选择这些记录并获得最终结果。

不过有一个很大的警告：

中间结果集将快速增长！您可以尝试从没有WHERE子句的cte3中进行选择，并亲自查看。因此，虽然这表明理论上可以做到，但即使对于中等数据集，也可能不切实际。所需的资源可能会很快超过最大值。
(好吧，由于带有递归CTE的AFAIK SQL是图灵完备的，并且问题似乎可以很好地计算，很明显无论如何都可以完成。但我认为，看到一个如何做到这一点的例子仍然很有趣。

也许可以优化。我相信，关键是限制递归步骤中的连接行。我们实际上只需要加入超过一小时的最旧记录，这将是下一个感兴趣的记录。这也会使最终SELECT中的cte3和WHERE变得不必要(除非投影摆脱帮助程序列)。但我没有找到这样做的方法。 至少在递归步骤中，不允许或实现递归 CTE 的LIMIT和窗口函数。但如果有人想出这样的优化，我很想看到它！

哦，两列中愚蠢的时间戳表示，首先需要放在一起，这也将使时间戳上的索引使用变得不可能。所以这是限制性能的另一个因素。