如何从历史数据中检索行程？

afdcj2ne 于 2021-06-26 发布在 Hive

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

我有下表 mytable 在Hive中：

id    radar_id     car_id     datetime
1     A21          123        2017-03-08 17:31:19.0
2     A21          555        2017-03-08 17:32:00.0
3     A21          777        2017-03-08 17:33:00.0
4     B15          123        2017-03-08 17:35:22.0
5     B15          555        2017-03-08 17:34:05.0
5     B15          777        2017-03-08 20:50:12.0
6     A21          123        2017-03-09 11:00:00.0
7     C11          123        2017-03-09 11:10:00.0
8     A21          123        2017-03-09 11:12:00.0
9     A21          555        2017-03-09 11:12:10.0
10    B15          123        2017-03-09 11:14:00.0
11    C11          555        2017-03-09 11:20:00.0

我想知道汽车通过雷达的路线 A21 以及 B15 在同一次旅行中。例如，如果同一个项目的日期不同 car_id ，那就不一样了。基本上，我想考虑雷达之间的最大时差 A21 以及 B15 对于同一辆车应该是30分钟。如果它更大，那么旅行就不一样了，比如
car_id 777 .
我的最终目标是计算每天的平均出行次数（不是唯一的，所以如果同一辆车经过同一条路线2次，那么应该计算2次）。
预期结果如下：

radar_start   radar_end       avg_tripscount_per_day
A21           B15             1.5

日期 2017-03-08 两个雷达之间有两个行程 A21 以及 B15 （汽车 777 由于30分钟的限制而不被考虑），而在 2017-03-09 只有一次旅行。平均每天2+1=1.5次。
我怎样才能得到这个结果？基本上，我不知道如何在查询中引入30分钟限制，以及如何按时间分组乘车 radar_start 以及 radar_end .
谢谢。
更新：
旅行在开始之日登记。
如果车是被雷达触发的 A21 在 2017-03-08 23:55 通过雷达 B15 在 2017-03-09 00:15 ，则应视为当日登记的同一行程 2017-03-08 .
万一 ids 6和8是同一辆车 123 路过 A21 两次，然后变成 B15 ( id 10). 最后一次与 id 应考虑第8条。所以， 8-10 . 因此，最接近于 B15 . 解释是一辆汽车经过 A21 两次，第二次转到 B15 .

sql Hive hiveql

来源：https://stackoverflow.com/questions/45481288/how-to-retrieve-trips-from-historical-data

2条答案

按热度按时间

aoyhnmkz1#

我错过了你用的 Hive 所以开始为 SQL-Server ，但也许对你有帮助。尝试以下操作：
查询

select radar_start, 
       radar_end, 
       convert(decimal(6,3), count(*)) / convert(decimal(6,3), count(distinct dt)) as avg_tripscount_per_day
from (
    select 
        t1.radar_id as radar_start,
        t2.radar_id as radar_end,
        convert(date, t1.[datetime]) dt,
        row_number() over (partition by t1.radar_id, t1.car_id, convert(date, t1.[datetime]) order by t1.[datetime] desc) rn1,
        row_number() over (partition by t2.radar_id, t2.car_id, convert(date, t2.[datetime]) order by t2.[datetime] desc) rn2
    from trips as t1
    join trips as t2 on t1.car_id = t2.car_id 
        and datediff(minute,t1.[datetime], t2.[datetime]) between 0 and 30
        and t1.radar_id = 'A21' 
        and t2.radar_id = 'B15'
)x
where rn1 = 1 and rn2 = 1
group by radar_start, radar_end

输出

radar_start radar_end   avg_tripscount_per_day
A21         B15         1.5000000000

样本数据

create table trips
(
    id int,
    radar_id char(3),
    car_id int,
    [datetime] datetime
)
insert into trips values
(1,'A21',123,'2017-03-08 17:31:19.0'),
(2,'A21',555,'2017-03-08 17:32:00.0'),
(3,'A21',777,'2017-03-08 17:33:00.0'),
(4,'B15',123,'2017-03-08 17:35:22.0'),
(5,'B15',555,'2017-03-08 17:34:05.0'),
(5,'B15',777,'2017-03-08 20:50:12.0'),

(6,'A21',123,'2017-03-09 11:00:00.0'),
(7,'C11',123,'2017-03-09 11:10:00.0'),
(8,'A21',123,'2017-03-09 11:12:00.0'),

(9,'A21',555,'2017-03-09 11:12:10.0'),
(8,'B15',123,'2017-03-09 11:14:00.0'),
(9,'C11',555,'2017-03-09 11:20:00.0')

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

ijnw1ujt2#

select  count(*) / count(distinct to_date(datetime))    as trips_per_day

from   (select  radar_id
               ,datetime
               ,lead(radar_id) over w  as next_radar_id
               ,lead(datetime) over w  as next_datetime                    

        from    mytable

        where   radar_id in ('A21','B15')

        window  w as 
                (
                    partition by  car_id
                    order by      datetime
                )
        ) t

where   radar_id        = 'A21'
    and next_radar_id   = 'B15'
    and datetime + interval '30' minutes >= next_datetime
;

+----------------+
| trips_per_day  |
+----------------+
| 1.5            |
+----------------+

附笔
如果您的版本不支持间隔，则最后的代码记录可以替换为- and to_unix_timestamp(datetime) + 30*60 > to_unix_timestamp(next_datetime)

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

我来回答

如何从历史数据中检索行程？

2条答案

相关问题

热门标签

最新问答