在R语言中清洗起讫点（OD）数据：求移除返程行程的方法

Hey there! Let's work through this problem together. You've got a dataset of trip records with thousands of entries, and you need to remove all those return trips that head back to a customer's original starting point—got it. Here's how you can approach this, depending on your exact setup:

I'll assume your trips table has these key fields (adjust if yours are different):

• customer_id: Unique ID for each customer
• start_loc: Starting location of the trip
• end_loc: Ending location of the trip
• trip_timestamp: Timestamp or date to order trips chronologically (critical for identifying which trips are returns)

If "return trip" means any trip that ends at the exact starting location of the customer's very first trip, here's how to do it:

First, identify each customer's initial starting location and their first trip time (to avoid accidentally deleting the first trip itself):

WITH customer_initial_details AS (
    SELECT
        customer_id,
        FIRST_VALUE(start_loc) OVER (PARTITION BY customer_id ORDER BY trip_timestamp) AS initial_start,
        MIN(trip_timestamp) OVER (PARTITION BY customer_id) AS first_trip_time
    FROM trips
)

Then delete the return trips (double-check with a SELECT first to confirm!):

DELETE FROM trips
USING customer_initial_details
WHERE
    trips.customer_id = customer_initial_details.customer_id
    AND trips.end_loc = customer_initial_details.initial_start
    AND trips.trip_timestamp != customer_initial_details.first_trip_time;

For MySQL Users (Older Versions Without CTE Support) #

Use a subquery instead:

DELETE t
FROM trips t
JOIN (
    SELECT
        customer_id,
        start_loc AS initial_start,
        MIN(trip_timestamp) AS first_trip_time
    FROM trips
    GROUP BY customer_id
) ci ON t.customer_id = ci.customer_id
WHERE
    t.end_loc = ci.initial_start
    AND t.trip_timestamp != ci.first_trip_time;

If "return trip" specifically means a direct backtrack (e.g., a customer travels from A to B, then later B to A), use this approach to target those paired trips:

First, identify all return trip IDs:

WITH return_trip_ids AS (
    SELECT
        t2.trip_id
    FROM trips t1
    JOIN trips t2 ON
        t1.customer_id = t2.customer_id
        AND t1.start_loc = t2.end_loc
        AND t1.end_loc = t2.start_loc
        AND t2.trip_timestamp > t1.trip_timestamp -- Ensure return happens after the original trip
)

Then delete them (again, test with SELECT first!):

DELETE FROM trips
WHERE trip_id IN (SELECT trip_id FROM return_trip_ids);

Before running any DELETE command:

• Backup your data to avoid accidental data loss.
• Replace DELETE with SELECT * in the queries above to preview exactly which records will be removed. This lets you confirm you're targeting only the return trips you want to delete.

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