mysql - 在给定登机和下机时间的情况下查找每小时载客量

Question

我有下表的一家巴士公司的运输记录:

CREATE TABLE ride_txn(
passenger_no int(11) pk,
txn_time timestamp,
action varchar(10)
)

其中操作可以是“登机”或“下机”。

假设我有 2 行 passenger_no。 100，他下午 1 点 30 分登机，4 点 30 分下车。

passenger_no    txn_time    action
100             13:30:00    Board
100             16:30:00    Deboard

我可以编写一个 sql 查询来检索他在公共(public)汽车上的时间吗？我在每个小时开始时数数，所以他在下午 2 点、下午 3 点和下午 4 点在公共(public)汽车上。换句话说，我想得到类似的东西

passenger_no    hour_in_bus
100             2
100             3
100             4

Answer 1

这是一个开始:

select
    passenger_no, hr
from
    ride_txn rt,
    (
        select  0 hr union all select  1 union all select  2 union all
        select  3    union all select  4 union all select  5 union all
        select  6    union all select  7 union all select  8 union all 
        select  9    union all select 10 union all select 11 union all
        select 12    union all select 13 union all select 14 union all
        select 15    union all select 16 union all select 17 union all
        select 18    union all select 19 union all select 20 union all
        select 21    union all select 22 union all select 23
    ) hrs
where
    action = 'Board' and
    hrs.hr between
        hour(txn_time) /* could add 3599 seconds to only count top of the hour */
        and
        (
            select min(txt_time) from ride_txn rt2
            where 
                    rt2.passenger_no = rt.passenger_no
                and rt2.txt_time > rt.txt_time and action = 'Deboard'
        )

我不得不假设事件会正确配对，而且“下船”时间将大于“登船”时间。所以没有什么事情会跨越午夜，并且会在一天之内发生。不过，它会在一天内处理多对。

我不确定 hour() 是否真的是一个 MySQL 函数，但我相信您可以找到等效的函数。我还假设它返回一个从 0 到 23 的数字。