mysql - 不同行中的开始日期和结束日期

Question

我将事件记录存储在单个表中，开始和结束日期时间记录为单独的记录。

存储记录的表。

CREATE TABLE `avl_data` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`imei_number` bigint(20) unsigned NOT NULL DEFAULT '0',
`latitude` double NOT NULL DEFAULT '0',
`longitude` double NOT NULL DEFAULT '0',
`report_id` tinyint(4) NOT NULL DEFAULT '0',
`rtc_date` datetime NOT NULL,
`ibutton_id` varchar(25) DEFAULT 'N/A',
`odometer` bigint(20) NOT NULL DEFAULT '0',
`speed` smallint(6) NOT NULL DEFAULT '0',
`vin_number` varchar(255) DEFAULT 'N/A',
`date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`time_report` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `imei_number` (`imei_number`),
KEY `imei_rtc` (`imei_number`,`rtc_date`),
CONSTRAINT `avl_data_ibfk_1` FOREIGN KEY (`imei_number`) REFERENCES `assets` (`imei_number`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=64916466 DEFAULT CHARSET=utf8 |`

这是我到目前为止所尝试过的。

    select concat(ass.label_1, " ", ass.label_2, " ", ass.label_3)                                         as "Vehicle",
       @start := case
                   when a.report_id = 103 then convert_tz(a.rtc_date, 'UTC', 'Asia/Kuwait') end as "Start",
       @end := case
                 when a.report_id = 104 then convert_tz(a.rtc_date, 'UTC', 'Asia/Kuwait') end   as "End",
       TIMEDIFF(@start, @end)                                                                   as 'Duration',
       a.speed                                                                                  as 'Speed',
       a.latitude                                                                               as 'Latitude',
       a.longitude                                                                              as 'Longitude'
from avl_data a
       inner join assets ass on a.imei_number = ass.imei_number
where a.imei_number = 356158069811103
  and rtc_date >= '2018-10-01 00:00:00'
  and rtc_date <= '2018-10-31 23:59:59'
  and a.report_id in (103, 104)
order by a.rtc_date asc;

分别生成开始和事件记录，第一个记录是事件开始，第二个记录是事件停止。

+-------------------------------+---------------------+---------------------+----------+-------+-----------+-----------+
| Vehicle                       | Start               | End                 | Duration | Speed | Latitude  | Longitude |
+-------------------------------+---------------------+---------------------+----------+-------+-----------+-----------+
| Mitsubishi Outlander 14/74080 | 2018-10-01 08:29:26 | NULL                | NULL     |   128 | 29.045856 | 48.113764 |
| Mitsubishi Outlander 14/74080 | NULL                | 2018-10-01 08:30:17 | NULL     |   114 | 29.031169 | 48.121516 |
|

理想情况下我想要单行，即

+-------------------------------+---------------------+---------------------+----------+-------+-----------+-----------+
| Vehicle                       | Start               | End                 | Duration | Speed | Latitude  | Longitude |
+-------------------------------+---------------------+---------------------+----------+-------+-----------+-----------+
| Mitsubishi Outlander 14/74080 | 2018-10-01 08:29:26 | 2018-10-01 08:30:17 | 00:01:17 |   128 | 29.045856 | 48.113764 | 

感谢@Thorsten，这对我很有用，引导功能非常完美。

select a.report_id as "ID",
       any_value(case when a.report_id = 103 then concat(ass.label_1, ' ', ass.label_2, ' ', ass.label_3) end) as "Vehicle",
       any_value(case when a.report_id = 103 then convert_tz(a.rtc_date, 'UTC', 'Asia/Kuwait') end) as "Start",
       any_value(case when a.report_id = 103 then lead(convert_tz(a.rtc_date, 'UTC', 'Asia/Kuwait')) over () end) as "End",
       any_value(case when a.report_id = 103 then SEC_TO_TIME(TIMESTAMPDIFF(SECOND , convert_tz(a.rtc_date, 'UTC', 'Asia/Kuwait'), lead(convert_tz(a.rtc_date, 'UTC', 'Asia/Kuwait')) over ())) end) as "Duration",
       any_value(case when a.report_id = 103 then a.speed end) as "Speed",
       any_value(case when a.report_id = 103 then a.latitude end) as "Latitude",
       any_value(case when a.report_id = 103 then a.longitude end) as "Longitude"
from avl_data a
       join assets ass on a.imei_number = ass.imei_number
where a.imei_number = 356158069811103
  and a.rtc_date >= '2018-10-01 00:00:00'
  and a.rtc_date <= '2018-10-31 23:59:59'
  and a.report_id in (103, 104); 

结果，尽管有办法删除现在的空行吗？

+-----+-------------------------------+---------------------+---------------------+----------+-------+-----------+-----------+
| ID  | Vehicle                       | Start               | End                 | Duration | Speed | Latitude  | Longitude |
+-----+-------------------------------+---------------------+---------------------+----------+-------+-----------+-----------+
| 103 | Mitsubishi Outlander 14/74080 | 2018-10-01 08:29:26 | 2018-10-01 08:30:17 | 00:00:51 |   128 | 29.045856 | 48.113764 |
| 104 | NULL                          | NULL                | NULL                | NULL     |  NULL |      NULL |      NULL |
| 103 | Mitsubishi Outlander 14/74080 | 2018-10-01 08:43:45 | 2018-10-01 08:44:14 | 00:00:29 |   136 | 29.067756 | 48.110384 |
| 104 | NULL                          | NULL                | NULL                | NULL     |  NULL |      NULL |      NULL |
| 103 | Mitsubishi Outlander 14/74080 | 2018-10-01 09:31:36 | 2018-10-01 09:31:44 | 00:00:08 |   135 | 29.056563 | 48.108851 |
| 104 | NULL                          | NULL                | NULL                | NULL     |  NULL |      NULL |      NULL |
| 103 | Mitsubishi Outlander 14/74080 | 2018-10-01 09:32:02 | 2018-10-01 09:33:54 | 00:01:52 |   149 | 29.048803 | 48.112581 |
| 104 | NULL                          | NULL                | NULL                | NULL     |  NULL |      NULL |      NULL |
| 103 | Mitsubishi Outlander 14/74080 | 2018-10-01 09:41:57 | 2018-10-01 09:42:35 | 00:00:38 |   131 | 29.036886 | 48.108733 |
| 104 | NULL                          | NULL                | NULL                | NULL     |  NULL |      NULL |      NULL |
| 103 | Mitsubishi Outlander 14/74080 | 2018-10-01 19:48:09 | 2018-10-01 19:48:20 | 00:00:11 |   126 | 29.034386 | 48.119706 |
| 104 | NULL                          | NULL                | NULL                | NULL     |  NULL |      NULL |      NULL |

Answer 1

您可以使用聚合从两行中获取一行。由于您需要来自 report_id = 103 的一些值和来自 report_id = 104 的一些值，因此您可以使用 CASE WHEN 来获取一个值或另一个值。由于只有 1 行 103 行和 1 行 104 行，因此您的聚合函数是具有 ANY_VALUE 的伪聚合。

select
  any_value(case when a.report_id = 103 then concat(ass.label_1, ' ', ass.label_2, ' ', ass.label_3) end) as "Vehicle",
  any_value(case when a.report_id = 103 then convert_tz(a.rtc_date, 'UTC', 'Asia/Kuwait') end) as "Start",
  any_value(case when a.report_id = 104 then convert_tz(a.rtc_date, 'UTC', 'Asia/Kuwait') end) as "End",
  TIMEDIFF(
    any_value(case when a.report_id = 103 then convert_tz(a.rtc_date, 'UTC', 'Asia/Kuwait') end),
    any_value(case when a.report_id = 104 then convert_tz(a.rtc_date, 'UTC', 'Asia/Kuwait') end)
  ) as "Duration",
  any_value(case when a.report_id = 103 then a.speed end) as "Speed",
  any_value(case when a.report_id = 103 then a.latitude end) as "Latitude",
  any_value(case when a.report_id = 103 then a.longitude end) as "Longitude"
from avl_data a
join assets ass on a.imei_number = ass.imei_number
where a.imei_number = 356158069811103
  and rtc_date >= date '2018-10-01'
  and rtc_date < date '2018-11-01'
  and a.report_id in (103, 104);

另一种选择是联接:

select
  a103."Vehicle",
  a103."Start",
  a104."End",
  TIMEDIFF(a103."Start", a104."End") as "Duration",
  a103."Latitude",
  a103."Longitude",
from
(
  select
    concat(ass.label_1, ' ', ass.label_2, ' ', ass.label_3) as "Vehicle",
    convert_tz(a.rtc_date, 'UTC', 'Asia/Kuwait') end as "Start",
    speed as "Speed",
    latitude as "Latitude",
    longitude as "Longitude"
  from avl_data a
  join assets ass on a.imei_number = ass.imei_number
  where a.imei_number = 356158069811103
    and rtc_date >= '2018-10-01 00:00:00'
    and rtc_date <= '2018-10-31 23:59:59'
    and a.report_id = 103
) a103
cross join
(
  select convert_tz(a.rtc_date, 'UTC', 'Asia/Kuwait' as "End"
  from avl_data a
  join assets ass on a.imei_number = ass.imei_number
  where a.imei_number = 356158069811103
    and rtc_date >= '2018-10-01 00:00:00'
    and rtc_date <= '2018-10-31 23:59:59'
    and a.report_id = 104
) a104;