如何在同一查询中获取各车手跨表总耗时及每日时间数据？

当然可行！这是数据分析里很常见的「明细+聚合」需求，我们可以通过两种常用方式实现，下面结合例子具体说明：

方案一：使用窗口函数（推荐，简洁高效） #

如果你的数据库支持窗口函数（比如MySQL 8.0+、PostgreSQL、SQL Server等），这是最便捷的方式。核心思路是先把所有日期表合并成一个数据集，再用窗口函数计算每位车手的总耗时，让每一行明细都带上对应的总耗时。

示例代码：

WITH daily_driver_data AS (
    -- 合并所有按日期划分的表，同时标记对应的日期
    SELECT 
        driver_id,
        driver_name,
        '2024-05-20' AS race_date,
        start_time,
        arrive_time,
        -- 根据你的数据库调整耗时计算方式，这里以MySQL为例
        TIMESTAMPDIFF(SECOND, start_time, arrive_time) AS daily_spent_time
    FROM race_data_20240520
    UNION ALL
    SELECT 
        driver_id,
        driver_name,
        '2024-05-21' AS race_date,
        start_time,
        arrive_time,
        TIMESTAMPDIFF(SECOND, start_time, arrive_time) AS daily_spent_time
    FROM race_data_20240521
    -- 继续添加其他日期的表，注意用UNION ALL（比UNION高效，因为不做去重）
)
SELECT 
    driver_id,
    driver_name,
    race_date,
    start_time,
    arrive_time,
    daily_spent_time,
    -- 窗口函数：按车手ID分组计算总耗时
    SUM(daily_spent_time) OVER (PARTITION BY driver_id) AS total_spent_time
FROM daily_driver_data
ORDER BY driver_id, race_date;

方案二：聚合子查询关联（兼容老版本数据库） #

如果你的数据库不支持窗口函数（比如MySQL 5.x），可以先单独计算总耗时，再通过关联查询把总耗时拼到每一行明细上。

示例代码：

WITH daily_driver_data AS (
    -- 同样先合并所有日期表
    SELECT 
        driver_id,
        driver_name,
        '2024-05-20' AS race_date,
        start_time,
        arrive_time,
        TIMESTAMPDIFF(SECOND, start_time, arrive_time) AS daily_spent_time
    FROM race_data_20240520
    UNION ALL
    SELECT 
        driver_id,
        driver_name,
        '2024-05-21' AS race_date,
        start_time,
        arrive_time,
        TIMESTAMPDIFF(SECOND, start_time, arrive_time) AS daily_spent_time
    FROM race_data_20240521
    -- 继续添加其他日期的表
),
driver_total_data AS (
    -- 单独计算每位车手的总耗时
    SELECT 
        driver_id,
        SUM(daily_spent_time) AS total_spent_time
    FROM daily_driver_data
    GROUP BY driver_id
)
-- 关联明细和总耗时数据
SELECT 
    d.driver_id,
    d.driver_name,
    d.race_date,
    d.start_time,
    d.arrive_time,
    d.daily_spent_time,
    t.total_spent_time
FROM daily_driver_data d
JOIN driver_total_data t ON d.driver_id = t.driver_id
ORDER BY d.driver_id, d.race_date;

注意事项 #

• 所有日期表的字段结构必须完全一致（字段名、数据类型都要匹配），否则UNION ALL会报错
• 计算spent_time的函数需要根据数据库调整：
  • PostgreSQL：EXTRACT(EPOCH FROM (arrive_time - start_time))
  • SQL Server：DATEDIFF(SECOND, start_time, arrive_time)
  • Oracle：(arrive_time - start_time) * 86400（转换为秒）
• 如果日期表数量极多，手动写UNION ALL太繁琐，可以考虑用动态SQL生成查询（比如存储过程），但要注意防范SQL注入风险

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