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

iyr7buue 于 2021-06-19 发布在 Mysql

关注(0)|答案(2)|浏览(303)

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

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 |

sql mysql

来源：https://stackoverflow.com/questions/53315223/start-and-end-dates-in-different-rows

2条答案

按热度按时间

o75abkj41#

这两行之间有公用键吗？
如果是这样的话，那么只需将avl\ U数据表的两个示例连接在一起
像这样的

select convert_tz(aStart.rtc_date, 'UTC', tzone) as "Start", 
    convert_tz(aEnd.rtc_date, 'UTC', tzone) as "End",
from avl_data aStart
inner join avl_data aEnd on aStart.Key = aEnd.Key;

其中key是公共密钥。
如果您没有一个公共键，并且依赖一行接一行，那么您有一个主键序列，那么类似的方法可能会起作用

select convert_tz(aStart.rtc_date, 'UTC', tzone) as "Start", 
    convert_tz(aEnd.rtc_date, 'UTC', tzone) as "End",
from avl_data aStart
inner join avl_data aEnd on aStart.reportId + 1 = aEnd.reportId;

但是，我不建议这样做，因为对于整个表来说，一个id比前一个id大一个可能不是100%可靠的。
像这样的东西可能会给你答案，但它会有额外的结果，因为它会有一个开始与结束以及

select aStart.imei_number as "Imei",
        convert_tz(aStart.rtc_date, 'UTC', tzone) as "Start", 
        convert_tz(MIN(aEnd.rtc_date), 'UTC', tzone) as "End"
from avl_data aStart
inner join avl_data aEnd on aStart.imei_number = aEnd.imei_number
                    and  aStart.rtc_date < aEnd.rtc_date
group by aStart.imei_number, aStart.rtc_date, aStart.report_id
order by aStart.imei_number, aStart.rtc_date;

您可以将其用作子查询，并用排名值ref Package 它https://stackoverflow.com/a/1895127/3805124 然后删除偶数，因为它们应该是以结束记录开始的多余的。

赞(0）回复(0）举报 2021-06-20

vlf7wbxs2#

可以使用聚合从两行中获取一行。当您需要一些来自report\u id=103的值和一些来自report\u id=104的值时，您可以使用 CASE WHEN 得到一个或另一个值。由于只有一个103行和一个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;

赞(0）回复(0）举报 2021-06-19

我来回答

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

2条答案

相关问题

热门标签

最新问答