desde ¿Cómo hacer una subconsulta de Postgresql en la cláusula de selección con join from from cláusula como SQL Server?



postgresql español (4)

Estoy tratando de escribir la siguiente consulta en 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

Sin duda, esto funcionaría en Microsoft SQL Server pero no funciona en postegresql. Leí un poco su documentación y parece que podría reescribirla como:

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

Pero eso devuelve el siguiente error en postegresql: "subconsulta en FROM no puede referirse a otras relaciones del mismo nivel de consulta". Así que estoy atascado. ¿Alguien sabe cómo puedo lograr eso?

Gracias


Answer #1

Sé que esto es antiguo, pero desde Postgresql 9.3 hay una opción para usar una palabra clave "LATERAL" para usar subconsultas relacionadas dentro de UNIONES, por lo que la consulta de la pregunta se vería así:

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

Answer #2

No estoy seguro de entender su intención a la perfección, pero quizás lo siguiente sea similar a lo que desea:

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)

Desafortunadamente, esto agrega el requisito de agrupar la primera subconsulta por id, así como nombre y author_id, que no creo que se desee. Sin embargo, no estoy seguro de cómo solucionarlo, ya que necesita tener una identificación disponible para unirse a la segunda subconsulta. Quizás alguien más encuentre una mejor solución.

Comparte y Disfruta.


Answer #3

Complementando @Bob Jarvis y @dmikam respuesta, Postgres no realiza un buen plan cuando no utiliza LATERAL, debajo de una simulación, en ambos casos los resultados de los datos de consulta son los mismos, pero el costo es muy diferente

Estructura de la tabla

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);

Realización de JOIN con GROUP BY en subconsulta sin 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);

Los 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)

Usando 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)

Mi versión de Postgres es PostgreSQL 10.3 (Debian 10.3-1.pgdg90+1)


Answer #4

Solo estoy respondiendo aquí con la versión formateada del sql final que necesitaba en base a la respuesta de Bob Jarvis tal como se publicó en mi comentario anterior:

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)




subquery