Alright, let's figure out how to calculate the time each F_ID spends in a specific L_CAT using Oracle 11g SQL. I'll walk you through the thought process first, then share reproducible code you can test directly.

First, let's break down what we need to do:

• Group consecutive records of the same F_ID and L_CAT together—these represent a continuous stay in that category.
• For each group, capture the start time (the first record's timestamp) and end time (the timestamp when the F_ID switches to a different L_CAT, or current time if it's still in that category).
• Calculate the time difference between start and end to get the duration.

Oracle 11g supports analytical functions like LAG() and LEAD(), which are perfect for this scenario. LEAD() lets us look ahead to the next record's data, while LAG() helps us compare with the previous one to spot when the L_CAT changes.

First, let's create a sample table and insert test data to mimic your initial dataset:

CREATE TABLE LOCATION_HISTORY (
    F_ID VARCHAR2(20),
    L_CAT VARCHAR2(20),
    RECORD_TIME TIMESTAMP
);

INSERT INTO LOCATION_HISTORY VALUES ('F001', 'WAREHOUSE', TO_TIMESTAMP('2024-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO LOCATION_HISTORY VALUES ('F001', 'WAREHOUSE', TO_TIMESTAMP('2024-01-01 09:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO LOCATION_HISTORY VALUES ('F001', 'SHIPPING', TO_TIMESTAMP('2024-01-01 10:30:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO LOCATION_HISTORY VALUES ('F001', 'SHIPPING', TO_TIMESTAMP('2024-01-01 11:15:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO LOCATION_HISTORY VALUES ('F001', 'WAREHOUSE', TO_TIMESTAMP('2024-01-01 13:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO LOCATION_HISTORY VALUES ('F002', 'OFFICE', TO_TIMESTAMP('2024-01-01 09:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO LOCATION_HISTORY VALUES ('F002', 'OFFICE', TO_TIMESTAMP('2024-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS'));
COMMIT;

Now, here's the SQL query to compute the stay duration:

WITH ranked_data AS (
    SELECT 
        F_ID,
        L_CAT,
        RECORD_TIME,
        -- Get the next record's category and timestamp for this F_ID
        LEAD(L_CAT) OVER (PARTITION BY F_ID ORDER BY RECORD_TIME) NEXT_L_CAT,
        LEAD(RECORD_TIME) OVER (PARTITION BY F_ID ORDER BY RECORD_TIME) NEXT_RECORD_TIME
    FROM LOCATION_HISTORY
),
stay_intervals AS (
    SELECT 
        F_ID,
        L_CAT,
        RECORD_TIME AS START_TIME,
        -- Determine end time: next record's time if category changes, else current time
        CASE 
            WHEN NEXT_L_CAT != L_CAT OR NEXT_L_CAT IS NULL THEN 
                NVL(NEXT_RECORD_TIME, SYSDATE) 
            ELSE NULL 
        END AS END_TIME
    FROM ranked_data
    -- Only keep the first record of each consecutive L_CAT group
    WHERE LAG(L_CAT) OVER (PARTITION BY F_ID ORDER BY RECORD_TIME) != L_CAT 
       OR LAG(L_CAT) OVER (PARTITION BY F_ID ORDER BY RECORD_TIME) IS NULL
)
-- Calculate duration in hours (adjust units as needed)
SELECT 
    F_ID,
    L_CAT,
    START_TIME,
    END_TIME,
    ROUND((END_TIME - START_TIME) * 24, 2) AS DURATION_HOURS
FROM stay_intervals
ORDER BY F_ID, START_TIME;

• ranked_data CTE: Uses LEAD() to fetch the next record's category and timestamp for each F_ID (sorted by time). This tells us when the current stay in L_CAT ends.
• stay_intervals CTE: Filters out duplicate consecutive L_CAT records, keeping only the start of each stay. The end time is set to the next record's timestamp if the category changes, or SYSDATE if there's no next record (meaning the F_ID is still in that category).
• Final Calculation: Computes the duration in hours by subtracting start time from end time and converting days to hours (multiply by 24). You can adjust this to minutes (*24*60) or days if needed.

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