错误:FROM中的子查询无法引用相同查询级别的其他关系 [英] ERROR: subquery in FROM cannot refer to other relations of same query level

问题描述

我正在使用PostgreSQL 9，我想在表RP中找到RQ中所有元组的最近邻居，比较日期(t)，但出现此错误:

I'm working with PostgreSQL 9 and I want to find the nearest neighbor inside table RP for all tuples in RQ, comparing the dates (t), but I get this error:

错误:FROM中的子查询无法引用同一查询的其他关系 级别

ERROR: subquery in FROM cannot refer to other relations of same query level

使用此查询:

SELECT *
FROM RQ, (SELECT * FROM RP ORDER BY ABS(RP.t - RQ.t) LIMIT 1) AS RA

子查询中的

RQ.t似乎是问题所在.如何避免此错误?如何从子查询访问RQ?

RQ.t in subquery seems to be the problem. How can I avoid this error? How can I get access from subquery to RQ?

推荐答案

更新:

LATERAL联接允许这样做，并且在Postgres 9.3中引入.详细信息:

Update:

LATERAL joins allow that and were introduced with Postgres 9.3. Details:

• 什么是和PostgreSQL中的子查询之间有什么区别?

原因在错误消息中. FROM列表中的一个元素不能引用同一级别的FROM列表中的另一个元素.对于同一级别的对等方，它是不可见的. 您可以使用相关子查询来解决此问题:

The reason is in the error message. One element of the FROM list cannot refer to another element of the FROM list on the same level. It is not visible for a peer on the same level. You could solve this with a correlated subquery:

SELECT *, (SELECT t FROM rp ORDER BY abs(rp.t - rq.t) LIMIT 1) AS ra
FROM   rq

很显然，您不在乎从一组同样接近的行中选择RP中的哪一行，所以我也这样做.

Obviously, you don't care which row from RP you pick from a set of equally close rows, so I do the same.

但是，在SELECT列表中的子查询表达式只能返回 one 列.如果要从表RP中获取多个列，请使用类似于此子查询构造的内容:
我假设两个表中都存在主键id.

However, a subquery expression in the SELECT list can only return one column. If you want more than one or all columns from the table RP, use something like this subquery construct:
I assume the existence of a primary key id in both tables.

SELECT id, t, (ra).*
FROM (
    SELECT *, (SELECT rp FROM rp ORDER BY abs(rp.t - rq.t) LIMIT 1) AS ra
    FROM   rq
    ) x;

相关的子查询因性能不佳而臭名昭著.这种查询-虽然显然在计算所需的内容-尤其会吸吮，因为表达式rp.t - rq.t不能使用索引.如果使用更大的表，性能将急剧下降.

Correlated subqueries are infamous for bad performance. This kind of query - while obviously computing what you want - will suck in particular, because the expression rp.t - rq.t cannot use an index. Performance will deteriorate drastically with bigger tables.

此重新编写的查询应该能够利用RP.t 上的索引，对于大表，该索引应该能够更快地执行 ..

This rewritten query should be able to utilize an index on RP.t, which should perform much faster with big tables.

WITH x AS (
    SELECT * 
         ,(SELECT t
           FROM   rp
           WHERE  rp.t <  rq.t
           ORDER  BY rp.t DESC
           LIMIT  1) AS t_pre

         ,(SELECT t
           FROM   rp
           WHERE  rp.t >= rq.t
           ORDER  BY rp.t
           LIMIT  1) AS t_post
    FROM   rq
    )
SELECT id, t
      ,CASE WHEN (t_post - t) < (t - t_pre)
            THEN t_post
            ELSE COALESCE(t_pre, t_post) END AS ra
FROM   x;

同样，如果要整个行:

WITH x AS (
    SELECT * 
         ,(SELECT rp
           FROM   rp
           WHERE  rp.t <  rq.t
           ORDER  BY rp.t DESC
           LIMIT  1) AS t_pre

         ,(SELECT rp
           FROM   rp
           WHERE  rp.t >= rq.t
           ORDER  BY rp.t
           LIMIT  1) AS t_post
    FROM   rq
    ), y AS (
    SELECT id, t
          ,CASE WHEN ((t_post).t - t) < (t - (t_pre).t)
                THEN t_post
                ELSE COALESCE(t_pre, t_post) END AS ra
    FROM   x
    )
SELECT id AS rq_id, t AS rq_t, (ra).*
FROM   y 
ORDER  BY 2;

请注意带复合类型的括号的使用！这里没有括号是多余的.有关详细信息，请参见手册此处，以及此处.

Note the use of parentheses with composite types! No paren is redundant here. More about that in the manual here and here.

在PostgreSQL 9.1上测试. 在sqlfiddle上进行演示.

Tested with PostgreSQL 9.1. Demo on sqlfiddle.

这篇关于错误:FROM中的子查询无法引用相同查询级别的其他关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！