子查询中的Oracle“无效标识符”

Question

当子查询引用外部查询中的列时，我在将视图从PostgreSQL转换为Oracle时遇到了问题。

这个问题在这里似乎已经讨论过好几次了，但是我一直无法让任何一个修复程序来处理我的特定查询。

该查询的目的是获取移动设备的最后记录位置，并以公里为单位获取距离其最近的检查点/地理边界的距离，并引用3个独立的表:设备、device_locations和检查点。

SELECT 
    d.id,
    dl.latitude AS last_latitude,
    dl.longitude AS last_longitude,
    (SELECT * /* Get closest 'checkpoint' to the last device position by calculating the Great-circle distance */
    FROM (
        SELECT
            6371 * acos(cos(dl.latitude / (180/acos(-1))) * cos(checkpoints.latitude / (180/acos(-1))) * cos((checkpoints.longitude / (180/acos(-1))) - (dl.longitude / (180/acos(-1)))) + sin(dl.latitude  / (180/acos(-1))) * sin(checkpoints.latitude / (180/acos(-1)))) AS distance
        FROM checkpoints
        ORDER BY distance)
    WHERE ROWNUM = 1) AS distance_to_checkpoint
FROM devices d
LEFT JOIN ( /* Get the last position of the device */
    SELECT l.id,
        l.time,
        l.latitude,
        l.longitude,
        l.accuracy
    FROM device_locations l
    WHERE l.ROWID IN (SELECT MAX(ROWID) FROM device_locations GROUP BY id) 
    ORDER BY l.id, l.time DESC) dl 
ON dl.id = d.id;

我已经坚持了一段时间了，希望有人能让我走上正确的道路，谢谢。

Answer 1

这是对我另一个答案的跟进。为了获得距离最小的checkpoints记录，您可以加入表并再次使用window函数来选择最佳记录。例如：

select
  device_id,
  last_latitude,
  last_longitude,
  checkpoint_latitude,
  checkpoint_longitude,
  distance
from
(
  select
    device_id,
    last_latitude,
    last_longitude,
    checkpoint_latitude,
    checkpoint_longitude,
    distance,
    min(distance) over (partition by device_id) as min_distance
  from
  (
    select
      d.id as device_id,
      dl.latitude as last_latitude,
      dl.longitude as last_longitude,
      cp.latitude as checkpoint_latitude,
      cp.longitude as checkpoint_longitude,
      6371 *
      acos(cos(dl.latitude / (180/acos(-1))) *
           cos(cp.latitude / (180/acos(-1))) *
           cos((cp.longitude / (180/acos(-1))) - (dl.longitude / (180/acos(-1))))
           + 
           sin(dl.latitude / (180/acos(-1))) *
           sin(cp.latitude / (180/acos(-1)))
          ) as distance
    from devices d
    left join 
    (
      select 
        id as device_id, latitude, longitude, time,
        max(time) over (partition by id) as max_time
      from device_locations
    ) dl on dl.device_id = d.id and dl.time = dl.max_time
    cross join checkpoints cp
  )
)
where (distance = min_distance) or (distance is null and min_distance is null);

这样的查询在Oracle12c中可以用CROSS APPLY和OUTER APPLY编写。

Answer 2

我认为有两个问题：

1. 最后选择列后的额外逗号：AS distance_to_checkpoint,
2. 外部选择列引用内部表device_locations l，而不是派生表dl -示例：l.latitude应该是dl.latitude。

Answer 3

首先:查询不会得到最后的设备位置。它获得的记录具有最高的ROWID每ID，这可能恰好是最近的条目，但根本没有保证是。

那么，您很可能在范围上有问题。不幸的是，名称只有一个层次是有效的，这是一个恼人的限制。dl.latitude等在子查询中可能无效，因为它实际上是子查询中的子查询。无论如何，您想得到的是最小的距离，您可以很容易地通过MIN获得。

子查询中的ORDER BY在标准SQL中是多余的。甲骨文为他们的ROWNUM技术做了一个例外，但我不会利用这一点。(正如前面提到的，获得最小值甚至是笨拙的。)无论如何，外部连接中的ORDER BY是多余的。

我就是这样处理这个问题的：

select
  d.id as device_id,
  dl.latitude as last_latitude,
  dl.longitude as last_longitude,
  (
    select min(6371 *
               acos(cos(dl.latitude / (180/acos(-1))) *
                    cos(cp.latitude / (180/acos(-1))) *
                    cos((cp.longitude / (180/acos(-1))) - (dl.longitude / (180/acos(-1))))
                    + 
                    sin(dl.latitude / (180/acos(-1))) *
                    sin(cp.latitude / (180/acos(-1)))
                   )
               )
    from checkpoints cp
  ) as distance
from devices d
left join 
(
  select 
    id as device_id, latitude, longitude, time,
    max(time) over (partition by id) as max_time
  from device_locations
) dl on dl.device_id = d.id and dl.time = dl.max_time;