mysql - 如何跨表减去并仅使用 SQL 连接差异最小的行？

Question

SQL 进行中

    SELECT race_id, runner_id, end_time , end_lat, end_lng, (prev_lat-end_lng/prev_lng-runner_lng) as slope,
CASE WHEN slope > 0 THEN direction = 'POSITIVE'
WHEN slope < 0 THEN direction = 'NEGATIVE'
ELSE '0'
    FROM race_info_table ri
    LEFT JOIN 
    (SELECT TOP 1 runner_id, runner_lat AS prev_lat, runner_lng AS prev_lng, occurred_at_10s, 
    datediff(second, occurred_at_10s,ri.end_time) as last_location 
    FROM runner_location_table rl
    ORDER BY datediff(second, occurred_at_10s,ri.end_time) ASC)
    ON ri.runner_id == rl.runner_id

上述查询的问题 - 我特别不知道如何将比赛信息数据与最接近的上一个跑步者信息时间相结合。上面的查询不起作用，因为 date diff 部分失败，因为它不知道 ri.end_time 是什么。

问题：如何判断跑步者是在坡度的正向还是负向奔跑？

如何回答这个问题：

1. 计算每个跑步者的跑步者位置表和race_info 表中的时间差。
2. 找出 end_time 和 occurred_at_10s 时间戳中的最短时间差。
3. 使用 runner_id 将跑步者最近 10 秒前的位置数据从 runner_location_table 加入到 race_info_table。
4. 现在新表应该有这些列：start_lng、start_lat、end_lng、end_lat、race_id、runner_id、end_time、start_time、runner_lat_100_prev、runner_lng_100_prev。
   5. 使用 runner_lat_100_prev、runner_lng_100_prev、end_lng 和 end_lat，计算斜率。
   6. 使用 case 语句添加一个新列，如果斜率为正，我们有一个称为方向的新列，如果斜率为负，则表示正或负。

输入数据

race_info_table
 - start_lng DOUBLE
 - start_lat DOUBLE
 - end_lng DOUBLE
 - end lat DOUBLE
 - race_id BIGINT
 - runner_id BIGINT
 - end_time TIMESTAMP
 - start_time TIMESTAMP

runner_location_table (recorded every 100 seconds)
 - runner_id BIGINT​, Unique identifier for a runner
 - runner_lat DOUBLE​, Latitude of runner location
 - runner_lng DOUBLE​, Longitude of runner location
 - occurred_at_10s TIMESTAMP​, UTC timestamp that increases in 100 second increments.

创建表的代码

CREATE TABLE runner_locations_table (runner_id BIGINT,
runner_lat DOUBLE, 
runner_lng DOUBLE, 
occurred_at_10s TIMESTAMP);

CREATE TABLE race_info_table (runner_id BIGINT,
race_id BIGINT,
start_lat DOUBLE,
start_lng DOUBLE,
start_time TIMESTAMP,
end_lat DOUBLE,
end_lng DOUBLE,
end_time TIMESTAMP);

标签： mysqlsql

Answer 1

这个问题最初被标记为“sql-server”。此答案仅适用于 SQL SERVER。

我想你只是想要outer apply：

SELECT race_id, runner_id, end_time, end_lat, end_lng 
FROM race_info_table ri OUTER APPY
     (SELECT TOP 1 driver_id, driver_lat AS prev_lat, driver_lng AS prev_lng, occurred_at_10s, 
datediff(second, occurred_at_10s,ri.end_time) as last_location 
      FROM runner_location_table rl
      WHERE ri.runner_id = rl.runner_id
      ORDER BY datediff(second, occurred_at_10s, ri.end_time) ASC
     ) rl;