如何基于crash表每行随机选取traffic_flow表10行并插入新表？

嘿，我来帮你搞定这个需求！首先得明确咱们的匹配逻辑：事故记录和流量数据得是同一天、同一个检测器的，这样选出来的流量数据才和事故场景相关，对吧？下面针对不同主流数据库给出具体的实现方案，你可以直接套用到自己的场景里：

1. MySQL 版本 #

MySQL里我们可以用ROW_NUMBER()窗口函数，给每个事故对应的流量数据随机排序，然后取前10条插入新表：

-- 先创建存放结果的新表（字段按需调整）
CREATE TABLE crash_traffic_sample (
    crash_date DATE,
    crash_time TIME,
    crash_detector_id INT, -- 对应crash表的检测器ID
    traffic_id INT, -- 对应traffic_flow的自增ID
    traffic_date DATE,
    traffic_time TIME,
    traffic_detector_id INT,
    -- 替换成你实际需要的流量参数字段
    flow_volume INT,
    average_speed DECIMAL(10,2)
);

-- 插入随机匹配的数据
INSERT INTO crash_traffic_sample
SELECT 
    c.crash_date,
    c.time AS crash_time,
    c.detector_id AS crash_detector_id,
    t.id AS traffic_id,
    t.date AS traffic_date,
    t.time AS traffic_time,
    t.detector_id AS traffic_detector_id,
    t.flow_volume,
    t.average_speed
FROM (
    SELECT 
        c.*,
        t.*,
        -- 按每个事故记录分组，随机排序后给每条流量数据编号
        ROW_NUMBER() OVER (PARTITION BY c.crash_date, c.time, c.detector_id ORDER BY RAND()) AS rn
    FROM crash c
    -- 关联同日期、同检测器的流量数据
    JOIN traffic_flow t 
        ON c.crash_date = t.date 
        AND c.detector_id = t.detector_id
) AS combined_data
-- 只取每个事故对应的前10条随机流量数据
WHERE rn <= 10;

2. PostgreSQL 版本 #

PostgreSQL的逻辑和MySQL差不多，只是随机函数换成RANDOM()：

-- 创建新表
CREATE TABLE crash_traffic_sample (
    crash_date DATE,
    crash_time TIME,
    crash_detector_id INT,
    traffic_id INT,
    traffic_date DATE,
    traffic_time TIME,
    traffic_detector_id INT,
    flow_volume INT,
    average_speed NUMERIC(10,2)
);

-- 插入数据
INSERT INTO crash_traffic_sample
SELECT 
    c.crash_date,
    c.time AS crash_time,
    c.detector_id AS crash_detector_id,
    t.id AS traffic_id,
    t.date AS traffic_date,
    t.time AS traffic_time,
    t.detector_id AS traffic_detector_id,
    t.flow_volume,
    t.average_speed
FROM (
    SELECT 
        c.*,
        t.*,
        ROW_NUMBER() OVER (PARTITION BY c.crash_date, c.time, c.detector_id ORDER BY RANDOM()) AS rn
    FROM crash c
    JOIN traffic_flow t 
        ON c.crash_date = t.date 
        AND c.detector_id = t.detector_id
) AS combined_data
WHERE rn <= 10;

3. SQL Server 版本 #

SQL Server用NEWID()来生成随机排序的依据，实现逻辑一致：

-- 创建新表
CREATE TABLE crash_traffic_sample (
    crash_date DATE,
    crash_time TIME,
    crash_detector_id INT,
    traffic_id INT,
    traffic_date DATE,
    traffic_time TIME,
    traffic_detector_id INT,
    flow_volume INT,
    average_speed DECIMAL(10,2)
);

-- 插入数据
INSERT INTO crash_traffic_sample
SELECT 
    c.crash_date,
    c.time AS crash_time,
    c.detector_id AS crash_detector_id,
    t.id AS traffic_id,
    t.date AS traffic_date,
    t.time AS traffic_time,
    t.detector_id AS traffic_detector_id,
    t.flow_volume,
    t.average_speed
FROM (
    SELECT 
        c.*,
        t.*,
        ROW_NUMBER() OVER (PARTITION BY c.crash_date, c.time, c.detector_id ORDER BY NEWID()) AS rn
    FROM crash c
    JOIN traffic_flow t 
        ON c.crash_date = t.date 
        AND c.detector_id = t.detector_id
) AS combined_data
WHERE rn <= 10;

几个关键提醒： #

• 如果你的crash表有唯一主键（比如crash_id），那PARTITION BY用主键会更高效，比如改成PARTITION BY c.crash_id。
• 一定要替换示例里的flow_volume、average_speed这些字段为你实际需要的交通流量参数，新表的结构也可以根据自己的需求增减字段。
• 如果某个事故对应的同日期同检测器流量数据不足10条，那会把所有可用的都插入进去，不会报错。

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