Oracle如何随机取两表数据填充access_history并生成有序随机时间？

Got it, let's work through your two test data generation needs for Oracle step by step. Since you're okay with using CONNECT BY LEVEL <= 10000 and don't mind DBMS_RANDOM overhead, here's how to solve each requirement:

To pick a random location_id from the locations table for each row in access_history, you can use a correlated subquery that sorts the locations table randomly and selects one row. This works reliably for both single-row inserts and bulk generation.

For bulk inserts (like your 10000-row goal), embed this directly in your main query:

-- Random location_id for each row (Oracle 12c+)
(SELECT location_id FROM locations ORDER BY DBMS_RANDOM.VALUE FETCH FIRST 1 ROW ONLY)

If you're on an older Oracle version that doesn't support FETCH FIRST, use this equivalent:

(SELECT location_id FROM locations WHERE ROWNUM <= 1 ORDER BY DBMS_RANDOM.VALUE)

The key here is to ensure that for the same employee_id, each subsequent record's access_date is 10 minutes to 5 hours later than the previous one. We'll use window functions to accumulate random time intervals per employee:

1. Assign a row number to each record per employee (to establish the order of accesses).
2. Start with a random base date (e.g., matching your example's June 2020 timeframe).
3. Accumulate random intervals (10 minutes to 300 minutes = 5 hours) using a running sum window function.

Full Bulk Insert Query (Combines Both Requirements) #

This query generates 10000 test records, meets both your random location and sequential date rules, and matches your table structure:

INSERT INTO access_history (employee_id, card_num, location_id, access_date)
WITH base_records AS (
    -- Generate 10000 rows, each linked to a random employee
    SELECT
        e.employee_id,
        e.card_num,
        -- Random location from the locations table
        (SELECT location_id FROM locations ORDER BY DBMS_RANDOM.VALUE FETCH FIRST 1 ROW ONLY) AS location_id,
        -- Assign a unique row number per employee to order access dates
        ROW_NUMBER() OVER (PARTITION BY e.employee_id ORDER BY DBMS_RANDOM.VALUE) AS access_order
    FROM
        employees e
        -- Cross join to generate 10000 total rows
        CROSS JOIN (SELECT LEVEL FROM dual CONNECT BY LEVEL <= 10000) row_gen
    -- Shuffle rows to randomize employee distribution
    ORDER BY DBMS_RANDOM.VALUE
),
sequential_dates AS (
    -- Build sequential access dates for each employee
    SELECT
        employee_id,
        card_num,
        location_id,
        -- Start with a random date in June 2020 (matches your example)
        TO_DATE('06/21/2020', 'MM/DD/YYYY') + DBMS_RANDOM.VALUE(0, 2) +
        -- Accumulate random intervals (10 mins to 5 hrs = 10-300 mins)
        SUM(NUMTODSINTERVAL(DBMS_RANDOM.VALUE(10, 300), 'MINUTE'))
            OVER (PARTITION BY employee_id ORDER BY access_order) AS access_date
    FROM
        base_records
)
SELECT employee_id, card_num, location_id, access_date
FROM sequential_dates;

Query Breakdown: #

• base_records CTE: Creates 10000 rows, each paired with a random employee (shuffled with DBMS_RANDOM.VALUE) and random location. The access_order row number ensures we can sequence dates per employee.
• sequential_dates CTE: Starts with a random date in June 2020 (adjust the base date if needed), then uses a running sum to add random 10min-5hr intervals for each subsequent record of the same employee. This guarantees each access date is later than the previous one for the same employee.
• Verify the results with your sample query:

  SELECT * FROM access_history ORDER BY employee_id, access_date;
  

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