Re: Ayuda subselect con limit

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.

> 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.
>

--
Emanuel Calvo Franco
ArPUG / AOSUG Member
Postgresql Support & Admin