how to get the each bus , return the number of passenger boarding it, each bus should provide the un-CodePudding

with abc as 
(
select x.bus_id,x.pasenger_id,x.destination,x.origin,case when X.pasenger_time <=  X.bus_time then 1 else 0 end flag
from (
select b."id" as Bus_id,a."id" as  pasenger_id,
b.ORIGIN,
b.destination ,TO_CHAR ( TO_DATE ( a."time", 'HH24:MI' ), 'HH:MI AM') as pasenger_time,
TO_CHAR ( TO_DATE ( b."time", 'HH24:MI' ), 'HH:MI AM') as bus_time
from buses b cross join
passengers a  WHERE 
b.ORIGIN=a.ORIGIN and
b.destination=a.destination 
--where 
--a."id"=b."id" and 
-- TO_CHAR ( TO_DATE ( b."time", 'HH24:MI' ), 'HH:MI AM') <= TO_CHAR ( TO_DATE ( a."time", 'HH24:MI' ), 'HH:MI AM')
order by pasenger_id asc,a."time") X  
) 
select * from abc where flag='1';


select b."id" as buses_id ,b."time"as buses_time  ,b.origin as buses_origin ,b.destination as buses_destination, 
 p."id" as passengers_id ,p."time"as passengers_time  ,p.origin as passengers_origin ,p.destination as passengers_destination
from buses b cross join  
passengers p where 
b.origin=p.origin and
b.destination=p.destination and 
TO_CHAR ( TO_DATE ( p."time", 'HH24:MI' ), 'HH:MI AM') <= TO_CHAR ( TO_DATE ( b."time", 'HH24:MI' ), 'HH:MI AM');



select b."id" as buses_id ,b."time" as buses_time  ,b.origin as buses_origin ,b.destination as buses_destination, 
 p."id" as passengers_id ,p."time"as passengers_time  ,p.origin as passengers_origin ,p.destination as passengers_destination
from buses b , passengers p where 
b.origin=p.origin and
b.destination=p.destination and 
TO_CHAR ( TO_DATE ( p."time", 'HH24:MI' ), 'HH:MI AM') <= TO_CHAR ( TO_DATE ( b."time", 'HH24:MI' ), 'HH:MI AM');

Desirable Results

Table 1:

Buses

id  ORIGIN  DESTINATION time
10  Warsaw  Berlin  10:55
20  Berlin  Paris   6:20
21  Berlin  Paris   14:00
22  Berlin  Paris   21:40
30  Paris   Madrid  13:30

Table 2:

Passenger

id  ORIGIN  DESTINATION time
1   Paris   Madrid  13:30
2   Paris   Madrid  13:31
10  Warsaw  Paris   10:00
11  Warsaw  Berlin  22:31
40  Berlin  Paris   6:15
41  Berlin  Paris   6:50
42  Berlin  Paris   7:12
43  Berlin  Paris   12:03
44  Berlin  Paris   20:00

CodePudding user response：

We use lag to determine which bus each passenger went on when we connect the tables.

select   b.id
        ,count(p.id)
from     (
          select *
                ,lag(time) over(partition by origin, destination order by time) as pre_bus_time
          from   Buses
         ) b left join Passenger p on p.origin  = b.origin  and p.destination = b.destination and p.time <= b.time and p.time >= coalesce(b.pre_bus_time, '00:00:00')
group by b.id
order by b.id

id	count
10	0
20	1
21	3
22	1
30	1

Fiddle

CodePudding user response：

I already found the way to do it

WITH P_bus
AS
(SELECT  PASSENGERS.P_ID,BUSES.B_ID,PASSENGERS."time" as p_time,BUSES."time" as b_time,
PASSENGERS.ORIGIN,PASSENGERS.destination,
TO_DATE ( BUSES."time", 'HH24:MI' )- TO_DATE (PASSENGERS."time", 'HH24:MI' ) as time_diff
FROM PASSENGERS
CROSS JOIN BUSES
WHERE PASSENGERS.ORIGIN=BUSES.ORIGIN
AND  PASSENGERS.DESTINATION=BUSES.DESTINATION
ORDER BY PASSENGERS.P_ID) 
SELECT B.B_ID,COUNT(Y.B_ID) FROM BUSES B
LEFT OUTER JOIN (
select x.*,RANK() OVER(PARTITION BY x.P_ID ORDER BY x.b_time) AS RANK_BUS  
from (select *  FROM P_bus  where  time_diff>=0) x )  Y
ON B.B_ID=Y.B_ID
AND Y.RANK_BUS=1
GROUP BY B.B_ID
order by  B.B_ID

;