列车时刻表搜索算法：隔日车次的新增/停运状态判定问题

Got it, let's work through this problem step by step. First, let's recap the rules to make sure we're aligned:

• A trip is new if there's no matching trip running 2 days before it (T-2)
• A trip is ended if there's no matching trip scheduled to run 2 days after it (T+2)
• A "matching trip" means same travel direction (same start/end cities) and departure time within ±60 minutes of the original trip

First, let's cover key pre-processing steps, then jump to practical implementations using SQL (great for database datasets) and Python/Pandas (good for CSV/flat files).

Key Pre-Processing Steps #

1. Filter relevant trips: Only keep trips between Paris (P) and Berlin (B) (both directions: P→B and B→P)
2. Extract date/time components: Split dep_datetime into a pure date (for T-2/T+2 checks) and a time component (for the ±60 minute window)
3. Calculate T-2 and T+2 dates: For each trip, compute the date 2 days before and after its departure date

Implementation 1: SQL (For Database Datasets) #

If your data is stored in a SQL database, this query will compute the status for each trip:

WITH trip_metadata AS (
    SELECT
        trip_id,
        start_city_id,
        end_city_id,
        dep_datetime,
        -- Extract pure date and time from departure datetime
        DATE(dep_datetime) AS trip_date,
        TIME(dep_datetime) AS dep_time,
        -- Calculate T-2 and T+2 dates
        DATE_SUB(DATE(dep_datetime), INTERVAL 2 DAY) AS t_minus_2_date,
        DATE_ADD(DATE(dep_datetime), INTERVAL 2 DAY) AS t_plus_2_date
    FROM trips
    -- Filter to only Paris-Berlin/Berlin-Paris trips
    WHERE (start_city_id = 'P' AND end_city_id = 'B') 
       OR (start_city_id = 'B' AND end_city_id = 'P')
)
SELECT
    tm.trip_id,
    tm.start_city_id,
    tm.end_city_id,
    tm.dep_datetime,
    -- Determine if trip is "new" (no matching T-2 trip)
    CASE
        WHEN NOT EXISTS (
            SELECT 1
            FROM trip_metadata tm2
            WHERE tm2.start_city_id = tm.start_city_id
              AND tm2.end_city_id = tm.end_city_id
              AND tm2.trip_date = tm.t_minus_2_date
              AND TIMESTAMPDIFF(MINUTE, tm2.dep_time, tm.dep_time) BETWEEN -60 AND 60
        ) THEN 'new'
        ELSE NULL
    END AS status_new,
    -- Determine if trip is "ended" (no matching T+2 trip)
    CASE
        WHEN NOT EXISTS (
            SELECT 1
            FROM trip_metadata tm2
            WHERE tm2.start_city_id = tm.start_city_id
              AND tm2.end_city_id = tm.end_city_id
              AND tm2.trip_date = tm.t_plus_2_date
              AND TIMESTAMPDIFF(MINUTE, tm2.dep_time, tm.dep_time) BETWEEN -60 AND 60
        ) THEN 'ended'
        ELSE NULL
    END AS status_ended,
    -- Combine into a single overall status field (optional)
    CONCAT_WS(', ', 
        CASE WHEN status_new IS NOT NULL THEN status_new END,
        CASE WHEN status_ended IS NOT NULL THEN status_ended END
    ) AS overall_status
FROM trip_metadata tm;

How This Works: #

• The CTE trip_metadata adds all the date/time values we need for checks
• The NOT EXISTS clauses look for matching trips on T-2 and T+2:
  • Same travel direction
  • Exact date match (T-2 or T+2)
  • Departure time difference within 60 minutes (before or after)
• We output separate status_new/status_ended fields plus a combined overall_status for convenience

Implementation 2: Python/Pandas (For CSV/Flat Files) #

If you're working with a CSV or local file, here's a Pandas script to compute the status:

import pandas as pd

# Load your dataset (replace 'trips.csv' with your file path)
df = pd.read_csv('trips.csv', parse_dates=['dep_datetime'])

# Filter to only Paris-Berlin/Berlin-Paris trips
df = df[((df['start_city_id'] == 'P') & (df['end_city_id'] == 'B')) | 
        ((df['start_city_id'] == 'B') & (df['end_city_id'] == 'P'))]

# Extract date and time components
df['trip_date'] = df['dep_datetime'].dt.date
df['dep_time'] = df['dep_datetime'].dt.time

# Calculate T-2 and T+2 dates
df['t_minus_2_date'] = (df['dep_datetime'] - pd.Timedelta(days=2)).dt.date
df['t_plus_2_date'] = (df['dep_datetime'] + pd.Timedelta(days=2)).dt.date

# Helper function to check for matching trips on a target date
def has_matching_trip(row, target_date_col):
    target_date = row[target_date_col]
    # Filter trips with same direction and target date
    candidates = df[(df['start_city_id'] == row['start_city_id']) &
                    (df['end_city_id'] == row['end_city_id']) &
                    (df['trip_date'] == target_date)]
    if candidates.empty:
        return False
    # Calculate time difference in minutes
    time_diff = abs(pd.to_datetime(candidates['dep_time'].astype(str)) - 
                    pd.to_datetime(row['dep_time'].astype(str))).dt.total_seconds() / 60
    # Return True if any candidate is within ±60 minutes
    return any(time_diff <= 60)

# Apply helper function to determine status
df['is_new'] = df.apply(lambda x: not has_matching_trip(x, 't_minus_2_date'), axis=1)
df['is_ended'] = df.apply(lambda x: not has_matching_trip(x, 't_plus_2_date'), axis=1)

# Map boolean flags to status labels
df['status'] = df.apply(lambda x: ', '.join(filter(None, [
    'new' if x['is_new'] else None,
    'ended' if x['is_ended'] else None
])), axis=1)

# Show the final result (adjust columns as needed)
print(df[['trip_id', 'start_city_id', 'end_city_id', 'dep_datetime', 'status']])

How This Works: #

• We first load and filter the data to only relevant trips
• We split the departure datetime into date and time parts for easier checks
• The has_matching_trip function checks if any trip on the target date meets the direction and time window criteria
• We apply this function to flag "new" and "ended" trips, then combine into a single status field

Important Edge Cases to Consider #

• Cross-month trips: If your dataset only covers one month, trips in the first 2 days of the month will have T-2 dates in the previous month (which won't be in your data). These will be incorrectly marked as "new" unless you include the last 2 days of the previous month in your dataset. Similarly, trips in the last 2 days of the month will have T+2 dates in the next month—include the first 2 days of the next month if you want accurate "ended" statuses.
• Multiple matching trips: If there are multiple trips on T-2 that fit the time window, the logic still works (we just need at least one match to not be "new").
• Exact time matches: The ±60 minute window includes exact matches (0 minutes difference), which aligns with typical scheduling logic.

内容的提问来源于stack exchange，提问作者Duner Oberm