Como fazer um subquery Postgresql na cláusula select com join in da cláusula como o servidor SQL?
estou a tentar escrever a seguinte pergunta no 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
isto certamente funcionaria no servidor SQL da Microsoft, mas não funciona de todo no postegresql. Li um pouco a sua documentação e parece que posso reescrevê-la como: [[4]}
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
But that returns the following error on postegresql:"subquery in from cannot refer to other relations of same query level". Então, estou preso. Alguém sabe como posso conseguir isso?
Obrigado.4 answers
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)
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)
Complementando @Bob Jarvis e @dmikam resposta, os Postgres não executam um bom plano quando não se usa LATERAL, abaixo de uma simulação, em ambos os casos os resultados da pesquisa são os mesmos, mas o custo é muito diferente
Estrutura da tabela
CREATE TABLE ITEMS (
N INTEGER NOT NULL,
S TEXT NOT NULL
);
CREATE INDEX N_INDEX ON ITEMS(N);
INSERT INTO ITEMS
SELECT
(random()*1000000)::integer AS n,
md5(random()::text) AS s
FROM
generate_series(1,10000000);
Realizar JOIN com GROUP BY em subquery Sem 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);
Os resultados
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)
Utilizar 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);
Resultados
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)
PostgreSQL 10.3 (Debian 10.3-1.pgdg90+1)
Eu sei que isto é antigo, mas desde o Postgresql 9.3 existe uma opção para usar uma palavra-chave "LATERAL" para usar subqueries relacionadas dentro das junções, de modo que a consulta da pergunta pareceria como:
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