Re: Ayuda subselect con limit

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.

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