Re: Ayuda subselect con limit

Emanuel Calvo Franco escribió:
> 2009/1/21 Linos <info(at)linos(dot)es>:
>> 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
>
> En este where estas comparando con el campo de una tabla que no esta en el
> JOIN.
> En todo caso utiliza schema.modelo.modelo_id.

da exactamente el mismo error, de hecho me devuelve como nombre de la columna
que da el error el alias que le tengo puesto aunque la referencie con su nombre
completo como tu me indicas. Solo me deja referenciar desde la subquery del left
join lo que este dentro de la propia subquery con lo cual no puedo filtar con
los valores de la query externa que es lo que me interesa.

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

********** Error **********

ERROR: invalid reference to FROM-clause entry for table "modelo"
SQL state: 42P01
Hint: There is an entry for table "mo", but it cannot be referenced from this
part of the query.
Character: 804

>> 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
>> ^
>
> Esta bien, lo que pasa que estas utilizando el alias mo que no esta
> definido en la
> subquery
>
>> 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.
>>
>
>
>