[sql] SQL Server와 같은 절에서 조인을 사용하여 select 절에서 Postgresql 하위 쿼리를 수행하는 방법은 무엇입니까?

Program / 글쓴이

postgresql에 다음 쿼리를 작성하려고합니다.

select name, author_id, count(1),
    (select count(1)
    from names as n2
    where n2.id = n1.id
        and t2.author_id = t1.author_id
    )
from names as n1
group by name, author_id

이것은 확실히 Microsoft SQL Server에서 작동하지만 postegresql에서는 전혀 작동하지 않습니다. 나는 그 문서를 조금 읽었고 그것을 다음과 같이 다시 쓸 수있을 것 같다 :

select name, author_id, count(1), total
from names as n1, (select count(1) as total
    from names as n2
    where n2.id = n1.id
        and n2.author_id = t1.author_id
    ) as total
group by name, author_id

그러나 postegresql에 다음과 같은 오류를 반환합니다. “FROM의 하위 쿼리는 동일한 쿼리 수준의 다른 관계를 참조 할 수 없습니다.” 그래서 나는 붙어 있습니다. 내가 어떻게 이룰 수 있는지 아는 사람 있나요?

감사

답변

귀하의 의도를 완벽하게 이해하고 있는지는 모르겠지만 다음과 같은 내용이 귀하가 원하는 것에 가깝습니다.

select n1.name, n1.author_id, count_1, total_count
  from (select id, name, author_id, count(1) as count_1
          from names
          group by id, name, author_id) n1
inner join (select id, author_id, count(1) as total_count
              from names
              group by id, author_id) n2
  on (n2.id = n1.id and n2.author_id = n1.author_id)

불행히도 이것은 첫 번째 하위 쿼리를 id, name 및 author_id별로 그룹화해야한다는 요구 사항을 추가합니다. 두 번째 하위 쿼리에 가입 할 수있는 ID가 있어야하므로이 문제를 해결하는 방법을 모르겠습니다. 아마도 다른 누군가가 더 나은 해결책을 제시 할 것입니다.

공유하고 즐기십시오.

답변

@Bob Jarvis 및 @dmikam 답변을 보완 하면 Postgres는 시뮬레이션 아래에서 LATERAL을 사용하지 않을 때 좋은 계획을 수행하지 못합니다. 두 경우 모두 쿼리 데이터 결과는 동일하지만 비용은 매우 다릅니다

테이블 구조

CREATE TABLE ITEMS (
    N INTEGER NOT NULL,
    S TEXT NOT NULL
);

INSERT INTO ITEMS
  SELECT
    (random()*1000000)::integer AS n,
    md5(random()::text) AS s
  FROM
    generate_series(1,1000000);

CREATE INDEX N_INDEX ON ITEMS(N);

수행 JOIN과 GROUP BY하위 쿼리에서하지 않고LATERAL

EXPLAIN
SELECT
    I.*
FROM ITEMS I
INNER JOIN (
    SELECT
        COUNT(1), n
    FROM ITEMS
    GROUP BY N
) I2 ON I2.N = I.N
WHERE I.N IN (243477, 997947);

결과

Merge Join  (cost=0.87..637500.40 rows=23 width=37)
  Merge Cond: (i.n = items.n)
  ->  Index Scan using n_index on items i  (cost=0.43..101.28 rows=23 width=37)
        Index Cond: (n = ANY ('{243477,997947}'::integer[]))
  ->  GroupAggregate  (cost=0.43..626631.11 rows=861418 width=12)
        Group Key: items.n
        ->  Index Only Scan using n_index on items  (cost=0.43..593016.93 rows=10000000 width=4)

사용 LATERAL

EXPLAIN
SELECT
    I.*
FROM ITEMS I
INNER JOIN LATERAL (
    SELECT
        COUNT(1), n
    FROM ITEMS
    WHERE N = I.N
    GROUP BY N
) I2 ON 1=1 --I2.N = I.N
WHERE I.N IN (243477, 997947);

결과

Nested Loop  (cost=9.49..1319.97 rows=276 width=37)
  ->  Bitmap Heap Scan on items i  (cost=9.06..100.20 rows=23 width=37)
        Recheck Cond: (n = ANY ('{243477,997947}'::integer[]))
        ->  Bitmap Index Scan on n_index  (cost=0.00..9.05 rows=23 width=0)
              Index Cond: (n = ANY ('{243477,997947}'::integer[]))
  ->  GroupAggregate  (cost=0.43..52.79 rows=12 width=12)
        Group Key: items.n
        ->  Index Only Scan using n_index on items  (cost=0.43..52.64 rows=12 width=4)
              Index Cond: (n = i.n)

내 Postgres 버전은 PostgreSQL 10.3 (Debian 10.3-1.pgdg90+1)

답변

나는 이것이 오래 되었다는 것을 알고 있지만 Postgresql 9.3 부터 JOINS 내에서 RELATED 하위 쿼리를 사용하기 위해 키워드 “LATERAL”을 사용하는 옵션이 있으므로 질문의 쿼리는 다음과 같습니다.

SELECT
    name, author_id, count(*), t.total
FROM
    names as n1
    INNER JOIN LATERAL (
        SELECT
            count(*) as total
        FROM
            names as n2
        WHERE
            n2.id = n1.id
            AND n2.author_id = n1.author_id
    ) as t ON 1=1
GROUP BY
    n1.name, n1.author_id

답변

위의 의견에 게시 된대로 Bob Jarvis 답변을 기반으로 필요한 최종 SQL의 형식화 된 버전으로 여기에 대답하고 있습니다.

select n1.name, n1.author_id, cast(count_1 as numeric)/total_count
  from (select id, name, author_id, count(1) as count_1
          from names
          group by id, name, author_id) n1
inner join (select author_id, count(1) as total_count
              from names
              group by author_id) n2
  on (n2.author_id = n1.author_id)

답변

select n1.name, n1.author_id, cast(count_1 as numeric)/total_count
  from (select id, name, author_id, count(1) as count_1
          from names
          group by id, name, author_id) n1
inner join (select distinct(author_id), count(1) as total_count
              from names) n2
  on (n2.author_id = n1.author_id)
Where true

distinct더 많은 조인 그룹 성능이 느리기 때문에 더 많은 내부 조인 인 경우 사용 됩니다.

답변