SQL Server와 같은 join in from 절을 사용하여 select 절에서 Postgresql 하위 쿼리를 수행하는 방법은 무엇입니까?

SQL Server와 같은 join in from 절을 사용하여 select 절에서 Postgresql 하위 쿼리를 수행하는 방법은 무엇입니까?

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는 시뮬레이션 아래에서 RATAL을 사용하지 않을 때 좋은 계획을 수행하지 않습니다. 두 경우 모두 쿼리 데이터 결과는 동일하지만 비용은 매우 다릅니다.

테이블 구조

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 이후로 JONS 내부에서 관련 하위 쿼리를 사용하기 위해 키워드 "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더 많은 내부 조인이 있으면 더 많은 조인 그룹 성능이 느리기 때문입니다.

언급URL : https://stackoverflow.com/questions/3004887/how-to-do-a-postgresql-subquery-in-select-clause-with-join-in-from-clause-like-s