Re: Ayuda subselect con limit

Emanuel Calvo Franco escribió:
> 2009/1/20 Linos <info(at)linos(dot)es>:
>> Hola,
>> les explico lo que quiero hacer y las dos maneras que he encontrado
>> de hacerlo (pero ninguna me convence, creo q se me debe escapar algo), tengo
>> una tabla donde grabo las lineas de un ticket, en estas lineas guardo el
>> pvp_teorico por un lado y su pvp con las ofertas aplicadas por otro, la
>> tabla de ofertas tiene una prioridad para cada oferta y otra tabla con las
>> relaciones entre las referencias de los articulos y las ofertas en la que
>> está ese articulo.
>>
>> tabla ticket_linea(id_ticket, linea_id, referencia, pvp_teorico, pvp_real,
>> id_oferta, modificado_manual)
>> tabla oferta(oferta_id, nombre, tipo_oferta, dto, precio_fijo, prioridad)
>> tabla modelo_oferta(id_oferta, referencia)
>>
>> Lo que yo quiero sacar es para cada linea de ticket el precio por el que
>> deberia haberse vendido segun la oferta con mas prioridad, no todas las
>> lineas tienen oferta aplicada algunas lo tienen a null, si no fuera porque
>> no quiero repetir cada linea sino sacar solamente una linea por cada linea
>> de ticket con la oferta de mayor prioridad un simple left join me valia pero
>> dado que el limit 1 dentro del join me jode el resultado (porque no me deja
>> aplicar el WHERE referenciando una columna externa a la subquery dentro de
>> ella si no solamente fuera en la condicion del join donde el limit 1 ya me
>> habria jodido el resultado salvo que casualmente para esa referencia la
>> mejor oferta fuera la mejor de todas. Poniendo un ejemplo para que me
>> entiendan mejor:
>>
>
>
>
>
>> SELECT lin.id_ticket,
>> lin.linea_id,
>> lin.referencia,
>> 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_linea AS lin
>> LEFT JOIN (SELECT ofe.oferta_id,
>> ofe.nombre,
>> ofe.prioridad,
>> ofe.tipo_oferta,
>> ofe.dto,
>> ofe.precio_fijo,
>> tie_ofe.referencia
>> FROM schema.oferta AS ofe
>> JOIN t109.modelo_oferta AS tie_ofe ON tie_ofe.id_oferta =
>> ofe.oferta_id
>> ORDER BY ofe.prioridad DESC, ofe.oferta_id DESC LIMIT 1)
>> AS sub ON sub.referencia = lin.referencia
>> WHERE lin.modificado_manual IS TRUE
>> AND lin.id_oferta IS NULL
>> ORDER BY lin.id_ticket,
>> lin.linea_id;
>>
>> Esta query al hacer el limit antes de poder filtrar por lin.referencia casi
>> nunca coincide con la oferta mas prioritaria para esa referencia, encontre
>> dos soluciones para este problema, una es utilizar esta sintaxis:
>>
>> SELECT lin.id_ticket,
>> lin.linea_id,
>> lin.referencia,
>> lin.pvp_teorico,
>> lin.pvp_real,
>> (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.referencia = lin.referencia
>> ORDER BY prioridad DESC LIMIT 1),
>> (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.referencia = lin.referencia
>> ORDER BY prioridad DESC LIMIT 1)
>> FROM t109.ticket_linea AS lin
>> WHERE lin.modificado_manual IS TRUE
>> AND lin.id_oferta IS NULL
>> ORDER BY lin.id_ticket,
>> lin.linea_id;
>>
>> Pero utilizar para cada columna una subquery me parece un poco retrogrado,
>> ahi si me permite integrar el where en la subquery. Otra es la q estoy
>> utilizando ahora mismo:
>>
>> SELECT lin.id_ticket,
>> lin.linea_id,
>> lin.referencia,
>> 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_linea AS lin
>> LEFT JOIN (SELECT ofe.oferta_id,
>> ofe.nombre,
>> ofe.prioridad,
>> ofe.tipo_oferta,
>> ofe.dto,
>> ofe.precio_fijo,
>> tie_ofe.referencia
>> 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 whe_ofe.oferta_id
>> FROM schema.oferta AS whe_ofe
>> JOIN t109.modelo_oferta AS whe_tie_ofe ON
>> whe_tie_ofe.id_oferta = whe_ofe.oferta_id
>> AND tie_ofe.referencia = whe_tie_ofe.referencia
>> ORDER BY whe_ofe.prioridad DESC, whe_ofe.oferta_id
>> DESC LIMIT 1)
>> ) AS sub ON sub.referencia = lin.referencia
>> WHERE lin.modificado_manual IS TRUE
>> AND lin.id_oferta IS NULL
>> ORDER BY lin.id_ticket,
>> lin.linea_id;
>>
>> Aunque me gusta mas, porque es un solo left join al fin y al cabo, es mas
>> lenta que la solucion que utiliza subqueries por columnas, asi que ninguna
>> de las dos me gusta demasiado. Las querys quizan tengan algun error
>> sintactico, no hagan caso si ven algun error, las tengo probadas y funcionan
>> tal como describo, las he limpiado de mucha mas informacion que llevan las
>> originales para ir directamente al grano pero creo q explican la idea. Me
>> podrian recomendar una manera mejor de hacer lo que quiero? Gracias
>>
>> Un saludo,
>> Miguel Angel.
>> --
>> TIP 1: para suscribirte y desuscribirte, visita
>> http://archives.postgresql.org/pgsql-es-ayuda
>>
>
> (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.referencia = lin.referencia
> ORDER BY prioridad DESC) as tabla_oferta limit 1) as tabla
>
> Es mas rebuscado pero te asegura que no interfiera en la prioridad.
>
> Podrías pegar los explain analyze? De esta manera se puede ver donde
> esta el cuello de botella de las querys.
>

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

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