Re: Ayuda subselect con limit

Emanuel Calvo Franco escribió:
> 2009/1/21 Linos <info(at)linos(dot)es>:
>> Emanuel Calvo Franco escribió:
>>> 2009/1/20 Linos <info(at)linos(dot)es>:
>> gracias por echarme un cable emanuel, si te he entendido bien tendria
>> que usar lo que me pegas en el mail como la subquery para el left join, no?
>> si es asi no me funciona porque si intento referenciar en la subquery con la
>> que hago el left join una de las columnas que solicito en el select me da
>> postgresql este mensaje.
>>
>> HINT: There is an entry for table "lin", but it cannot be referenced from
>> this part of the query.
>>
>
> Completaste la subquery? fijate que faltan campos. De ultima repetilos
> en la última.
> No debería tirar este error. Además... a cual de las consultas se lo aplicaste?

se lo aplique a la consulta que tiene un left join, si se lo pongo a la que usa
subquerys por columnas me da este error:

ERROR: subquery must return only one column

Te pego aqui los dos tests completos q hecho con el texto que enviaste, esta vez
sin quitar tablas para q sea exactamente como yo las ejecuto.

----------------------------- la que usa la subquery como columna
SELECT lin.id_ticket,
lin.linea_id,
mo.referencia,
art.talla,
lin.pvp_teorico,
lin.pvp_real,
(select * from (SELECT ofe.oferta_id, ofe.nombre
FROM schema.oferta AS ofe
JOIN t109.modelo_oferta AS tie_ofe ON tie_ofe.id_oferta =
ofe.oferta_id
WHERE tie_ofe.id_modelo = mo.modelo_id
ORDER BY prioridad DESC) as tabla_oferta limit 1) as tabla
FROM t109.ticket_cabecera AS cab
JOIN t109.ticket_linea AS lin ON lin.id_ticket = cab.ticket_id
JOIN schema.articulo AS art ON art.articulo_id = lin.id_articulo
JOIN schema.modelo AS mo ON mo.modelo_id = art.id_modelo
WHERE lin.modificado_manual IS TRUE
AND lin.id_oferta IS NULL
ORDER BY lin.id_ticket,
lin.linea_id;

ERROR: subquery must return only one column

-------------------------------- la del left join
SELECT lin.id_ticket,
lin.linea_id,
mo.referencia,
art.talla,
lin.pvp_teorico,
lin.pvp_real,
sub.oferta_id,
sub.nombre,
CASE WHEN sub.tipo_oferta = 'DTO' THEN lin.pvp_teorico * (1.00 -
(sub.dto / 100))
WHEN sub.tipo_oferta = 'PRECIO_FIJO' THEN sub.precio_fijo
ELSE NULL
END
FROM t109.ticket_cabecera AS cab
JOIN t109.ticket_linea AS lin ON lin.id_ticket = cab.ticket_id
JOIN schema.articulo AS art ON art.articulo_id = lin.id_articulo
JOIN schema.modelo AS mo ON mo.modelo_id = art.id_modelo
LEFT JOIN (select * from (SELECT ofe.oferta_id
FROM schema.oferta AS ofe
JOIN t109.modelo_oferta AS tie_ofe ON tie_ofe.id_oferta =
ofe.oferta_id
WHERE tie_ofe.id_modelo = mo.modelo_id
ORDER BY prioridad DESC) as tabla_oferta limit 1)
AS sub ON sub.id_modelo = mo.modelo_id
WHERE lin.modificado_manual IS TRUE
AND lin.id_oferta IS NULL
ORDER BY lin.id_ticket,
lin.linea_id;

ERROR: invalid reference to FROM-clause entry for table "mo"
LINE 20: WHERE tie_ofe.id_modelo = mo.modelo_id
^
HINT: There is an entry for table "mo", but it cannot be referenced from this
part of the query.

>> Si puediera hacer eso seria perfecto por que el limit de la subquery me
>> daria el resultado correcto, el problema es q como no puedo hacer un where
>> dentro del left join indicando la referencia, la primera oferta que me da el
>> limit no coincide con el de esa referencia, respecto al explain te pego aqui
>> el de la query que tarda mas (la q usa un left join). Hay algunas tablas mas
>> que las que vienen en el mail inicial que envie (como explique en el primer
>> mail limpie parte de la query para que se viera el problema claramente), las
>> tablas implicadas son:
>>
>> lin ticket_linea
>> cab ticket_cabecera
>> art articulo
>> mo modelo
>> ofe oferta
>> tie_ofe t109.modelo_oferta
>> l_ofe oferta dentro del where de la subquery
>> l_tie_ofe t109.modelo_oferta dentro del where de la subquery
>>
>> Sort (cost=373239.03..373255.66 rows=6650 width=67) (actual
>> time=7364.919..7366.843 rows=10685 loops=1)
>> Sort Key: lin.id_ticket, lin.linea_id
>> Sort Method: quicksort Memory: 1532kB
>> -> Hash Join (cost=367952.80..372816.79 rows=6650 width=67) (actual
>> time=7230.655..7354.893 rows=10685 loops=1)
>> Hash Cond: (lin.id_ticket = cab.ticket_id)
>> -> Hash Left Join (cost=366683.70..371289.99 rows=6650 width=67)
>> (actual time=7207.268..7312.508 rows=10685 loops=1)
>> Hash Cond: (mo.modelo_id = tie_ofe.id_modelo)
>> -> Nested Loop (cost=1983.31..6539.39 rows=6650 width=37)
>> (actual time=45.763..126.466 rows=10685 loops=1)
>> -> Hash Join (cost=1983.31..4120.03 rows=6650 width=28)
>> (actual time=45.747..82.802 rows=10685 loops=1)
>> Hash Cond: (lin.id_articulo = art.articulo_id)
>> -> Seq Scan on ticket_linea lin
>> (cost=0.00..1987.09 rows=6650 width=26) (actual time=0.008..24.335
>> rows=10685 loops=1)
>> Filter: ((modificado_manual IS TRUE) AND
>> (id_oferta IS NULL))
>> -> Hash (cost=1179.25..1179.25 rows=64325
>> width=10) (actual time=45.707..45.707 rows=64328 loops=1)
>> -> Seq Scan on articulo art
>> (cost=0.00..1179.25 rows=64325 width=10) (actual time=0.005..21.227
>> rows=64328 loops=1)
>> -> Index Scan using modelo_pkey on modelo mo
>> (cost=0.00..0.35 rows=1 width=13) (actual time=0.002..0.003 rows=1
>> loops=10685)
>> Index Cond: (mo.modelo_id = art.id_modelo)
>> -> Hash (cost=364696.54..364696.54 rows=308 width=38) (actual
>> time=7161.484..7161.484 rows=48711 loops=1)
>> -> Hash Join (cost=4.70..364696.54 rows=308 width=38)
>> (actual time=0.272..7132.091 rows=48711 loops=1)
>> Hash Cond: (((subplan) = ofe.oferta_id) AND
>> (tie_ofe.id_oferta = ofe.oferta_id))
>> -> Seq Scan on modelo_oferta tie_ofe
>> (cost=0.00..889.99 rows=61599 width=8) (actual time=0.004..14.465 rows=61212
>> loops=1)
>> -> Hash (cost=3.08..3.08 rows=108 width=34)
>> (actual time=0.105..0.105 rows=108 loops=1)
>> -> Seq Scan on oferta ofe (cost=0.00..3.08
>> rows=108 width=34) (actual time=0.004..0.053 rows=108 loops=1)
>> SubPlan
>> -> Limit (cost=11.79..11.80 rows=1 width=8)
>> (actual time=0.063..0.064 rows=1 loops=109923)
>> -> Sort (cost=11.79..11.80 rows=1
>> width=8) (actual time=0.063..0.063 rows=1 loops=109923)
>> Sort Key: l_ofe.prioridad,
>> l_ofe.oferta_id
>> Sort Method: quicksort Memory: 17kB
>> -> Hash Join (cost=8.29..11.78
>> rows=1 width=8) (actual time=0.043..0.059 rows=2 loops=109923)
>> Hash Cond: (l_ofe.oferta_id =
>> l_tie_ofe.id_oferta)
>> -> Seq Scan on oferta l_ofe
>> (cost=0.00..3.08 rows=108 width=8) (actual time=0.001..0.024 rows=108
>> loops=109923)
>> -> Hash (cost=8.27..8.27
>> rows=1 width=4) (actual time=0.005..0.005 rows=2 loops=109923)
>> -> Index Scan Backward
>> using modelo_oferta_pkey on modelo_oferta l_tie_ofe (cost=0.00..8.27 rows=1
>> width=4) (actual time=0.003..0.004 rows=2 loops=109923)
>> Index Cond: ($0 =
>> id_modelo)
>> -> Hash (cost=812.38..812.38 rows=36538 width=4) (actual
>> time=23.366..23.366 rows=36547 loops=1)
>> -> Seq Scan on ticket_cabecera cab (cost=0.00..812.38
>> rows=36538 width=4) (actual time=0.006..11.571 rows=36547 loops=1)
>> Total runtime: 7369.879 ms
>>
>>
>>
>
> De cual de las consultas es este analyze?
> Version de Postgres?
> Sistema operativo?

Este analyze es de la consulta que yo uso con el left join que completa es esta:

SELECT lin.id_ticket,
lin.linea_id,
mo.referencia,
art.talla,
lin.pvp_teorico,
lin.pvp_real,
sub.oferta_id,
sub.nombre,
CASE WHEN sub.tipo_oferta = 'DTO' THEN lin.pvp_teorico * (1.00 -
(sub.dto / 100))
WHEN sub.tipo_oferta = 'PRECIO_FIJO' THEN sub.precio_fijo
ELSE NULL
END
FROM t109.ticket_cabecera AS cab
JOIN t109.ticket_linea AS lin ON lin.id_ticket = cab.ticket_id
JOIN schema.articulo AS art ON art.articulo_id = lin.id_articulo
JOIN schema.modelo AS mo ON mo.modelo_id = art.id_modelo
LEFT JOIN (SELECT ofe.oferta_id,
ofe.nombre,
ofe.prioridad,
ofe.tipo_oferta,
ofe.dto,
ofe.precio_fijo,
tie_ofe.id_modelo
FROM schema.oferta AS ofe
JOIN t109.modelo_oferta AS tie_ofe ON tie_ofe.id_oferta =
ofe.oferta_id
WHERE ofe.oferta_id = (SELECT l_ofe.oferta_id
FROM schema.oferta AS l_ofe
JOIN t109.modelo_oferta AS l_tie_ofe ON
l_tie_ofe.id_oferta = l_ofe.oferta_id
AND tie_ofe.id_modelo = l_tie_ofe.id_modelo
ORDER BY l_ofe.prioridad DESC, l_ofe.oferta_id DESC LIMIT 1)
) AS sub ON sub.id_modelo = mo.modelo_id
WHERE lin.modificado_manual IS TRUE
AND lin.id_oferta IS NULL
ORDER BY lin.id_ticket,
lin.linea_id;

Tiene el case y algunas columnas q no le pido a la de las subquerys en el select
porque al final como no me gustaba como iba a quedar termine de definir lo que
necesitaba en esta query y abandone la otra, imaginate con todas esas columnas
que necesito como habria quedado usando una subquery para cada columna en el
select. Utilizo SO Linux (Arch Linux kernel 2.6.28) y Postgresql 8.3.5

Un saludo,
Miguel Angel.