Emanuel Calvo Franco escribió:
2009/1/20 Linos <[email protected]>:
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
--
TIP 3: Si encontraste la respuesta a tu problema, publícala, otros te lo
agradecerán
