时间戳

首页 > 解决方案 > 以开始和结束代码匹配的条件计算两行之间的时间差

mysql - 以开始和结束代码匹配的条件计算两行之间的时间差

问题描述

我有如下数据：

uid ,access_code , access_date
(1,'200', '2017-08-06 06:59:04'),
(1,'204', '2017-08-06 21:11:21'),
(1,'200', '2017-09-30 21:33:45'),
(1,'510', '2017-09-30 22:11:40'),
(1,'200', '2017-10-28 07:09:44'),
(1,'200', '2017-10-28 07:12:21'),
(1,'510', '2017-10-28 07:12:51'),
(1,'510', '2017-10-28 07:13:20')

我想得到成对的记录，即 1 行 access_code=200 和下一个 access_code=510 或 204 并计算时间差。预期结果如下：

(1,'2017-08-06 06:59:04','2017-08-06 21:11:21',14:12:17)
(1,'2017-09-30 21:33:45','2017-09-30 22:11:40',00:37:55)
(1,'2017-10-28 07:09:44','2017-10-28 07:12:51',00:03:07)
(1,'2017-10-28 07:12:21','2017-10-28 07:13:20',00:00:59)

我有下面的 sql 查询，但只有在每个 access_code=200 后面跟着 204 或 510 时，该查询才能处理。如果有两个 200 然后后面跟着两个 510 或 204，那么它将不起作用。

select uid,start,stop, TIMEDIFF(stop, start) AS duration FROM (
select uid,access_date,access_code,@start AS start,
        @start := IF(access_code = '200', access_date, NULL) AS prev_start,
        @stop  := IF(access_code = '510' or access_code = '204' ,  access_date, NULL) AS stop,
        @uid  := uid AS guid from Table1  order by uid,access_date) as tmp,
        (SELECT @start:=NULL, @stop:=NULL, @uid:=NULL) AS vars
WHERE  start IS not  NULL AND stop  is not NULL order by duration desc;

SQL 演示在这里http://www.sqlfiddle.com/#!9/f272ec/1

谢谢你。

标签： mysql

解决方案

我想得到成对的记录，即 1 行 access_code=200 和下一个 access_code=510 或 204 并计算时间差。预期结果如下：

如果我理解正确，您想选择给定 uid 的行之间的时间差，其中 access_code 为 200，但下一个实例uid具有 a204或510access_code，然后获取它们之间的时间差。

这并不漂亮，但如果我理解你想要什么，那么这个草率的查询将处理它并排除后续请求也是 200 access_code 的情况。

select 
    a.uid,
    a.access_date as `startDate`,
    (select access_code from Table1 b where b.uid=a.uid and b.access_date > a.access_date order by access_date asc limit 1) as following_code,
    (select b.access_date from Table1 b where b.uid=a.uid and b.access_date > a.access_date order by access_date asc limit 1) as `stopDate`,
    timediff(
        (select b.access_date from Table1 b where b.uid=a.uid and b.access_date > a.access_date order by access_date asc limit 1),
        a.access_date
    ) as duration
from Table1 a
where 
    (select access_code from Table1 b where b.uid=a.uid and b.access_date > a.access_date order by access_date asc limit 1)
    and a.access_code = 200
    having following_code = 204
    or following_code = 510

结果：

"uid"   "startDate" "following_code"    "stopDate"  "duration"
"1" "2017-08-06 06:59:04"   "204"   "2017-08-06 21:11:21"   "14:12:17"
"1" "2017-09-30 21:33:45"   "510"   "2017-09-30 22:11:40"   "00:37:55"
"1" "2017-10-28 07:12:21"   "510"   "2017-10-28 07:12:51"   "00:00:30"

推荐阅读