Вопрос по sql, postgresql – ОШИБКА: подзапрос в FROM не может ссылаться на другие отношения того же уровня запроса

4

Я работаю с PostgreSQL 9 и хочу найти ближайшего соседа внутри таблицыRP для всех кортежей вRQ, сравнивая даты (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 в подзапросе, похоже, проблема. Как я могу избежать этой ошибки? Как я могу получить доступ из подзапросаRQ?

Этот запросlateral join, Я надеюсь, что у Postgresql это тоже скоро будет, SQL Server и DB2already have it Michael Buen
Кстати, былquestion with almost identical title a while ago, Подобная проблема, может быть интересна. Оказывается, я тоже ответил на это. Erwin Brandstetter

Ваш Ответ

2   ответа
5

Update:

LATERAL объединения позволяют это и были введены в Postgres 9.3. Подробности:

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

Причина в сообщении об ошибке. Один из элементовFROM список не может ссылаться на другой элементFROM список на том же уровне. Это не видно для пира на том же уровне. Вы можете решить это сcorrelated subquery:

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

Очевидно, вам все равно, какая строка изRP вы выбираете из множества одинаково близких рядов, поэтому я делаю то же самое.

Тем не менее, выражение подзапроса вSELECT список можно только вернутьone колонка. Если вы хотите более одного или всех столбцов из таблицыRP, используйте что-то вроде этой конструкции подзапроса:
Я предполагаю существование первичного ключаid в обеих таблицах.

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

Коррелированные подзапросы печально известны дляbad performance, Этот вид запроса - при этом очевидно вычисляя, что вы хотите - будетsuck в частности, потому что выражениеrp.t - rq.t не может использовать индекс. Производительность резко ухудшится с большими таблицами.

Этот переписанный запрос должен быть в состоянии использоватьindex on RP.t, который должен выполнить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;

Обратите внимание на использованиеparentheses with composite types! Здесь нет лишних. Подробнее об этом в руководствеВот а такжеВот.

Протестировано с PostgreSQL 9.1.Демо на sqlfiddle.

0

без индекса, все равно будут выполнять перекрестное соединение. Итак, еще один способ выражения запроса:

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 и возьмите накопленную сумму. В результате все значения rq между двумя значениями rp имеют одинаковый индекс rp.

Ближайшее значение к данному значению rq имеет индекс rp, равный значению rq или еще один. Для вычисления 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

Преимущество этого подхода состоит в том, что нет перекрестного соединения и коррелированных подзапросов.

Error: User Rate Limit Exceeded

Похожие вопросы