计算与每个唯一cRt的前一行在MySQL中的日期差异
计算与每个唯一cRt的前一行在MySQL中的日期差异
提问于 2020-04-26 17:19:43
我有一张列为cDate的桌子。
我想要计算具有相同cRt值的两个连续记录之间的时间差。
例如：
+---------------+----------+---------------------+-----------------------------+----------+
| cNode | cEl | cDate | cEvent | cRt |
+---------------+----------+---------------------+-----------------------------+----------+
| SD20-2-100102 | SD100102 | 2020-04-13 11:34:35 | POSITIVE REMOTE CONTROL SN | SD100102 |
| SD20-2-100102 | SD100102 | 2020-04-13 07:15:14 | NEGATIVE REMOTE CONTROL SN | SD100102 |
+---------------+----------+---------------------+-----------------------------+----------+SD100102 cRt的时差是259个。
mysql>
SELECT
ROUND(
time_to_sec(
(
TIMEDIFF(
'2020-04-13 11:34:35',
'2020-04-13 07:15:14'
)
)
) / 60
) AS td;
+-----+
| td |
+-----+
| 259 |
+-----+
1 row in set表：
+---------------+----------+---------------------+-----------------------------+----------+
| cNode | cEl | cDate | cEvent | cRt |
+---------------+----------+---------------------+-----------------------------+----------+
| SD20-2-100102 | SD100102 | 2020-04-13 11:34:35 | POSITIVE REMOTE CONTROL SN | SD100102 |
| SD20-2-100102 | SD100102 | 2020-04-13 07:15:14 | NEGATIVE REMOTE CONTROL SN | SD100102 |
| SD40-2-103423 | SD103423 | 2020-04-06 06:21:50 | POSITIVE REMOTE CONTROL SN | SD103423 |
| SD40-2-103423 | SD103423 | 2020-04-06 06:20:21 | NEGATIVE REMOTE CONTROL SN | SD103423 |
| SD20-2-103484 | SD103484 | 2020-04-23 15:19:37 | POSITIVE REMOTE CONTROL SN | SD103484 |
| SD20-2-103484 | SD103484 | 2020-04-23 15:17:53 | NEGATIVE REMOTE CONTROL SN | SD103484 |
| SD20-2-103484 | SD103484 | 2020-04-21 14:29:50 | POSITIVE REMOTE CONTROL SN | SD103484 |
| SD20-2-103484 | SD103484 | 2020-04-21 14:19:50 | NEGATIVE REMOTE CONTROL SN | SD103484 |
| SD20-2-103484 | SD103484 | 2020-02-13 07:33:00 | POSITIVE REMOTE CONTROL SN | SD103484 |
| SD20-2-103484 | SD103484 | 2020-02-13 07:31:55 | NEGATIVE REMOTE CONTROL SN | SD103484 |
| SD20-2-103484 | SD103484 | 2020-01-13 15:57:16 | POSITIVE REMOTE CONTROL SN | SD103484 |
| SD20-2-103484 | SD103484 | 2020-01-12 00:10:50 | NEGATIVE REMOTE CONTROL SN | SD103484 |
| SD40-2-103565 | SD103565 | 2020-04-07 12:20:48 | POSITIVE REMOTE CONTROL SN | SD103565 |
| SD40-2-103565 | SD103565 | 2020-04-07 11:59:47 | NEGATIVE REMOTE CONTROL SN | SD103565 |
| SD40-2-103565 | SD103565 | 2020-03-05 11:46:03 | POSITIVE REMOTE CONTROL SN | SD103565 |
| SD40-2-103565 | SD103565 | 2020-03-05 09:13:46 | NEGATIVE REMOTE CONTROL SN | SD103565 |
| SD40-2-103565 | SD103565 | 2020-02-18 12:30:21 | POSITIVE REMOTE CONTROL SN | SD103565 |
| SD40-2-103565 | SD103565 | 2020-02-18 12:29:12 | NEGATIVE REMOTE CONTROL SN | SD103565 |
| SD40-2-103565 | SD103565 | 2020-02-14 07:52:28 | POSITIVE REMOTE CONTROL SN | SD103565 |
| SD40-2-103565 | SD103565 | 2020-02-14 07:50:42 | NEGATIVE REMOTE CONTROL SN | SD103565 |
+---------------+----------+---------------------+-----------------------------+----------+谢谢。
回答 1
Stack Overflow用户
回答已采纳
发布于 2020-04-26 17:30:29
在8.0之前的MySql版本中，没有窗口函数就无法执行此操作。
一种方法是，每一行都要在当前cDate之前获得同一个cRt的最大cRt：
SELECT t.*,
ROUND(time_to_sec((TIMEDIFF(
t.cDate,
(SELECT MAX(cDate) FROM tablename WHERE cRt = t.cRt AND cDate < t.cDate)
))) / 60) AS td
FROM tablename t见演示。
如果您可以使用LAG()窗口函数：
SELECT *,
ROUND(time_to_sec((TIMEDIFF(
cDate,
LAG(cDate) OVER (PARTITION BY cRt ORDER BY cDate)
))) / 60) AS td
FROM tablename 见演示。
结果：
| cNode | cEl | cDate | cEvent | cRt | td |
| ------------- | -------- | ------------------- | -------------------------- | -------- | ----- |
| SD20-2-100102 | SD100102 | 2020-04-13 11:34:35 | POSITIVE REMOTE CONTROL SN | SD100102 | 259 |
| SD20-2-100102 | SD100102 | 2020-04-13 07:15:14 | NEGATIVE REMOTE CONTROL SN | SD100102 | |
| SD40-2-103423 | SD103423 | 2020-04-06 06:21:50 | POSITIVE REMOTE CONTROL SN | SD103423 | 1 |
| SD40-2-103423 | SD103423 | 2020-04-06 06:20:21 | NEGATIVE REMOTE CONTROL SN | SD103423 | |
| SD20-2-103484 | SD103484 | 2020-04-23 15:19:37 | POSITIVE REMOTE CONTROL SN | SD103484 | 2 |
| SD20-2-103484 | SD103484 | 2020-04-23 15:17:53 | NEGATIVE REMOTE CONTROL SN | SD103484 | 2928 |
| SD20-2-103484 | SD103484 | 2020-04-21 14:29:50 | POSITIVE REMOTE CONTROL SN | SD103484 | 10 |
| SD20-2-103484 | SD103484 | 2020-04-21 14:19:50 | NEGATIVE REMOTE CONTROL SN | SD103484 | 50340 |
| SD20-2-103484 | SD103484 | 2020-02-13 07:33:00 | POSITIVE REMOTE CONTROL SN | SD103484 | 1 |
| SD20-2-103484 | SD103484 | 2020-02-13 07:31:55 | NEGATIVE REMOTE CONTROL SN | SD103484 | 44135 |
| SD20-2-103484 | SD103484 | 2020-01-13 15:57:16 | POSITIVE REMOTE CONTROL SN | SD103484 | 2386 |
| SD20-2-103484 | SD103484 | 2020-01-12 00:10:50 | NEGATIVE REMOTE CONTROL SN | SD103484 | |
| SD40-2-103565 | SD103565 | 2020-04-07 12:20:48 | POSITIVE REMOTE CONTROL SN | SD103565 | 21 |
| SD40-2-103565 | SD103565 | 2020-04-07 11:59:47 | NEGATIVE REMOTE CONTROL SN | SD103565 | 47534 |
| SD40-2-103565 | SD103565 | 2020-03-05 11:46:03 | POSITIVE REMOTE CONTROL SN | SD103565 | 152 |
| SD40-2-103565 | SD103565 | 2020-03-05 09:13:46 | NEGATIVE REMOTE CONTROL SN | SD103565 | 22843 |
| SD40-2-103565 | SD103565 | 2020-02-18 12:30:21 | POSITIVE REMOTE CONTROL SN | SD103565 | 1 |
| SD40-2-103565 | SD103565 | 2020-02-18 12:29:12 | NEGATIVE REMOTE CONTROL SN | SD103565 | 6037 |
| SD40-2-103565 | SD103565 | 2020-02-14 07:52:28 | POSITIVE REMOTE CONTROL SN | SD103565 | 2 |
| SD40-2-103565 | SD103565 | 2020-02-14 07:50:42 | NEGATIVE REMOTE CONTROL SN | SD103565 | |页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/61444950
复制相关文章
相似问题
腾讯云开发者
