Question

我与PostgreSQL 9合作,我想在 RP 表格中找到最近的邻居,以比较日期(t ),但我要找到这个错误:

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 in subquery似乎是个问题。我怎样才能避免这个错误? 我怎样才能从 RQ.t 进入 ?

Answer 1

Update:

LATERAL 合并允许加入,并采用Postgres 9.3. 。

What is the difference between LATERAL and a subquery in PostgreSQL?

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 中选择哪一行,从一组同样近的行中选择,所以我也这样做。

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;

相关子查询对“ 强势” 坏表现 < / 强” 来说是臭名昭著的。这种查询 — — 在计算您想要的东西的同时显然计算 — — 将特别 suck < / em >, 因为 < code>rp.t - rq.t 的表达方式不能使用索引。更大的表格会使性能急剧恶化。

此重写查询应该能够使用 RP.t 上的“ 强” 索引, 该索引应使用大表格更快的“ 强” 。
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; 请注意使用具有复合型号 < / 坚固> 的“ 强性外壳” 。这里没有多余的paren 。在手册< a href=" http://www. panggresql.org/docs/product/interactive/sql- 表达式.html#FIELD- SELECETION' rel="nreferrer" >这里和 < a href=" http://www. postergresql.org/docs/current/interactive/rowtyms.html#AEN7249" rel=" noreferr">。以 PostgreSQL 9.1. 测试。 < a href="http://sqlfiddle.com/#!1/fdea1/1, rel=“ noreferrer” > Demo on sqlfiddle.

Answer 2

相关子查询, 没有索引, 将进行交叉连接。因此, 表达查询的另一种方式是 :

select rp.*, min(abs(rp.t - rq.t))
from rp cross join
     rq
group by <rp.*> -- <== need to replace with all columns

还有另一种方法,它比较复杂。这需要使用累积总和。

这里就是这个想法。将所有 rp 和 rq 值合并在一起。现在, 用最接近 rp 值来列出它们。也就是说, 为 rp 创建一个 rp 标记, 并计算累积总和。因此, 两个 rq 值之间的所有rq 值都具有相同的rp 指数。

最接近给定的 rq 值的值有一个与rq 值或一个以上相同的 rp 指数。计算 rq_ index 时使用累积总和。

以下查询将这一点组合在一起:

with rqi as (select t.*, sum(isRQ) over (order by t) as rq_index
             from (select rq.t, 0 as isRP, <NULL for each rp column>
                   from rq
                   union all
                   select rq.t, 1 as isRP, rp.* 
                   from rp
                  ) t
            ) t
select rp.*,
       (case when abs(rqprev.t - rp.t) < abs(rqnext.t - rp.t)
             then abs(rqprev.t - rp.t)
             else abs(rqnext.t - rp.t)
        end) as closest_value
from (select *
      from t
      where isRP = 0
     ) rp join
     (select *
      from t
      where isRP = 1
     ) rqprev
     on rp.rp_index = rqprev.rp_index join
     (select *
      from t
      where isRP = 1
     ) rqnext
     on rp.rp_index+1 = rpnext.rq_index

这种办法的优点是,没有交叉加入,也没有相关的子节。

Update:

友情链接