【LeetCode 2142. 每辆车的乘客人数 I】【LeetCode 2153. 每辆车的乘客人数 II】乘客到站等车，车到站载客。[MySQL 用户变量/Pandas]面向过程编程完成
题目地址
https://leetcode.cn/problems/the-number-of-passengers-in-each-bus-i/description/
https://leetcode.cn/problems/the-number-of-passengers-in-each-bus-ii/description/
思路
将所有关键时刻作为tick。（同一时刻车和人同时到，默认人在车前到）
之后按照tick升序，使用MySQL用户变量编程完成计算逻辑。
输出结果。
代码
题目1
MySQL代码
with ticks as(
select arrival_time as tick,
bus_id,
null as passenger_id,
"bus" as mytype
from Buses
union
select arrival_time as tick,
null as bus_id,
passenger_id,
"passenger" as mytype
from Passengers
)
,
t1 as(
select * ,
row_number() over(order by tick asc, field(mytype, "passenger","bus")) as rn
from ticks
)
,
t2 AS (
SELECT
#------------只需要修改里面的逻辑就行。注意语句是“顺序执行”的-------------------------------
*,
-- 此次事件，可以运走多少人
(case when mytype="bus" then @station_passenger_number
when mytype="passenger" then 0
end) AS passengers_cnt,
-- 此次事件后，站台人数
(case when mytype="bus" then @station_passenger_number := 0
when mytype="passenger" then @station_passenger_number := @station_passenger_number + 1 end )
#-------------只需要修改里面的逻辑就行。注意语句是“顺序执行”的------------------------------
FROM
(SELECT @station_passenger_number := 0 ) vars,
(SELECT * FROM t1 ORDER BY rn asc ) ordered_ticks
)
select bus_id, cast(passengers_cnt as unsigned int) as passengers_cnt
from t2
where mytype="bus"
order by bus_id asc
等效pandas代码
import pandas as pd
def count_passengers_in_bus(buses: pd.DataFrame, passengers: pd.DataFrame) -> pd.DataFrame:
# Create a ticks DataFrame by concatenating buses and passengers
ticks = pd.concat([
buses.rename(columns={'arrival_time': 'tick'}).assign(mytype='bus'),
passengers.rename(columns={'arrival_time': 'tick'}).assign(mytype='passenger'),
])
# Sort the DataFrame and reset the index to simulate row_number()
ticks = ticks.sort_values(by=['tick', 'mytype'], ascending=[True, False])
ticks = ticks.reset_index(drop=True)
# Initialize station passenger number
station_passenger_number = 0
# Function to apply on each row to calculate passengers_cnt and updated_station_passenger_number
def process_row(row, station_passenger_number):
if row['mytype'] == 'bus':
passengers_on_bus = station_passenger_number
station_passenger_number -= passengers_on_bus
elif row['mytype'] == 'passenger':
passengers_on_bus = 0
station_passenger_number += 1
return passengers_on_bus, station_passenger_number
# Apply the function to each row
result_list = []
for index, row in ticks.iterrows():
passengers_cnt, station_passenger_number = process_row(row, station_passenger_number)
result_list.append((row['bus_id'], passengers_cnt))
# Create a result DataFrame
result_df = pd.DataFrame(result_list, columns=['bus_id', 'passengers_cnt'])
# Filter out non-bus rows and order by bus_id
result_df = result_df.dropna(subset=['bus_id']).sort_values(by='bus_id')
# Convert passengers_cnt to unsigned int
result_df['passengers_cnt'] = result_df['passengers_cnt'].astype('uint')
return result_df
题目2
MySQL代码
with ticks as(
select arrival_time as tick,
bus_id,
null as passenger_id,
"bus" as mytype,
capacity as capacity
from Buses
union
select arrival_time as tick,
null as bus_id,
passenger_id,
"passenger" as mytype,
null as capacity
from Passengers
)
,
t1 as(
select * ,
row_number() over(order by tick asc, field(mytype, "passenger","bus")) as rn
from ticks
)
,
t2 AS (
SELECT
#------------只需要修改里面的逻辑就行。注意语句是“顺序执行”的-------------------------------
*,
-- 此次事件，可以运走多少人
(case when mytype="bus" then (
case when capacity>=@station_passenger_number then @station_passenger_number
else capacity end
)
when mytype="passenger" then 0
end) AS passengers_cnt,
-- 此次事件后，站台人数
(case when mytype="bus" then(
case when capacity>=@station_passenger_number then @station_passenger_number := 0
else @station_passenger_number := @station_passenger_number-capacity end
)
when mytype="passenger" then @station_passenger_number := @station_passenger_number + 1 end ) updated_station_passenger_number
#-------------只需要修改里面的逻辑就行。注意语句是“顺序执行”的------------------------------
FROM
(SELECT @station_passenger_number := 0 ) vars,
(SELECT * FROM t1 ORDER BY rn asc ) ordered_ticks
)
select bus_id , cast(passengers_cnt as unsigned int) as passengers_cnt
from t2
where mytype="bus"
order by bus_id asc
等效pandas代码
import pandas as pd
def number_of_passengers(buses: pd.DataFrame, passengers: pd.DataFrame) -> pd.DataFrame:
# Create a ticks DataFrame by concatenating buses and passengers
ticks = pd.concat([
buses.rename(columns={'arrival_time': 'tick'}).assign(mytype='bus'),
passengers.rename(columns={'arrival_time': 'tick'}).assign(mytype='passenger', capacity=pd.NA),
])
# Sort the DataFrame and reset the index to simulate row_number()
ticks = ticks.sort_values(by=['tick', 'mytype'], ascending=[True, False])
ticks = ticks.reset_index(drop=True)
# Initialize station passenger number
station_passenger_number = 0
# Function to apply on each row to calculate passengers_cnt and updated_station_passenger_number
def process_row(row, station_passenger_number):
if row['mytype'] == 'bus':
passengers_on_bus = min(row['capacity'], station_passenger_number)
station_passenger_number -= passengers_on_bus
elif row['mytype'] == 'passenger':
passengers_on_bus = 0
station_passenger_number += 1
return passengers_on_bus, station_passenger_number
# Apply the function to each row
result_list = []
for index, row in ticks.iterrows():
passengers_cnt, station_passenger_number = process_row(row, station_passenger_number)
result_list.append((row['bus_id'], passengers_cnt))
# Create a result DataFrame
result_df = pd.DataFrame(result_list, columns=['bus_id', 'passengers_cnt'])
# Filter out non-bus rows and order by bus_id
result_df = result_df.dropna(subset=['bus_id']).sort_values(by='bus_id')
# Convert passengers_cnt to unsigned int
result_df['passengers_cnt'] = result_df['passengers_cnt'].astype('uint')
return result_df
