uso de left join y sum

[cacr]

Amigos, tengo una duda...

Tiro la siguiente consulta:

Código:

# SELECT est_002, sum(agri04_005)
FROM agri04 LEFT OUTER JOIN public.estado USING (reg_001, est_001)
WHERE agri04_001 = '2006'
GROUP BY est_002
ORDER BY est_002;

Que me devuelve:

Código:

 est_002  |   sum    
----------+----------
 Mérida   | 29555.05
 Táchira  | 43560.38
 Trujillo | 18247.05
(3 filas)

Resulta, que cuando tiro la misma consulta para el año 2005, me devuelve un sólo registro...

¿al utilizar LEFT JOIN no debería traerme los 3 estados, aunque no tengan datos? o el SUM es tan especial que requiere un tratamiento especial?

[huesos52]

cacr

Habría que mirar que valores tienen las tablas y a que tabla pertenecen los campos que llamas en el select.

Cabe aclarar que cuando tienes valores nulos y haces un sum, solo sumará aquellos que tienen valores y omitirá los nulos.

Para evitar esto podrías probar algo así:

Código SQL:

SELECT est_002, SUM(COALESCE(agri04_005,0))
FROM agri04 LEFT OUTER JOIN public.estado USING (reg_001, est_001)
WHERE agri04_001 = '2006'
GROUP BY est_002
ORDER BY est_002;
 

saludos

[cacr]

Eu

Iniciado por huesos52

cacr

Habría que mirar que valores tienen las tablas y a que tabla pertenecen los campos que llamas en el select.

La estructura es:

Código:

# \d+ estado
                         Tabla «estado»
 Columna |         Tipo          | Modificadores |     Descripción     
---------+-----------------------+---------------+---------------------
 reg_001 | character varying(2)  | not null      | Código de la Región
 est_001 | character varying(2)  | not null      | Código del Estado
 est_002 | character varying(35) | not null      | Nombre del Estado

=# \d+ agri04
                                                     Tabla «agricola.agri04»
  Columna   |         Tipo         |                    Modificadores                    |              Descripción               
------------+----------------------+-----------------------------------------------------+----------------------------------------
 id         | integer              | not null default nextval('agri04_id_seq'::regclass) | 
 reg_001    | character varying(2) | not null                                            | 
 est_001    | character varying(2) | not null                                            | 
 agri04_001 | character varying(4) | not null                                            | Año de levantamiento de la información
 agri03_001 | integer              |                                                     | Rubro
 agri04_003 | numeric(10,2)        |                                                     | Superficie Sembrada en Fundación
 agri04_004 | numeric(10,2)        |                                                     | Superficie Sembrada en Renovación
 agri04_005 | numeric(10,2)        |                                                     | Total superficie sembrada

algunos datos:

Código:

# SELECT * FROMestado;
 reg_001 | est_001 | est_002  
---------+---------+----------
 06      | 14      | Mérida
 06      | 20      | Táchira
 06      | 21      | Trujillo
(3 filas)

# SELECT id, reg_001, est_001, agri04_001, agri04_003, agri04_004, agri04_005 FROM agri04 LIMIT 5;
  id  | reg_001 | est_001 | agri04_001 | agri04_003 | agri04_004 | agri04_005 
------+---------+---------+------------+------------+------------+------------
  334 | 06      | 14      | 2005       |            |            |      62.00
  583 | 06      | 14      | 2004       |            |            |      60.00
 5500 | 06      | 20      | 2007       |            |            |     116.55
 5498 | 06      | 20      | 2007       |            |            |     116.55
 5499 | 06      | 20      | 2007       |            |            |   11655.00
(5 filas)

Iniciado por huesos52

Cabe aclarar que cuando tienes valores nulos y haces un sum, solo sumará aquellos que tienen valores y omitirá los nulos.

Está clarísimo, sí no tiene nada que sumar, pues no suma nada.

Ahora, normlamente, con selects que no involucran campos con funciones de agregación, el join me devolvería los 3 estados aún y cuando sobre éstos no existan datos. Tal es el ejemplo de la documentación [0]

Código:

To put this together, assume we have tables t1:

 num | name
-----+------
   1 | a
   2 | b
   3 | c
and t2:

 num | value
-----+-------
   1 | xxx
   3 | yyy
   5 | zzz

en la que, por ejemplo:

Código:

SELECT * FROM t1 LEFT JOIN t2 USING (num);
 num | name | value
-----+------+-------
   1 | a    | xxx
   2 | b    |
   3 | c    | yyy
(3 rows

Por lo que empiezo a asumir, que con funciones de agregación, la cosa puede ser diferente...

Iniciado por huesos52

Para evitar esto podrías probar algo así:

Código SQL:

SELECT est_002, SUM(COALESCE(agri04_005,0))
FROM agri04 LEFT OUTER JOIN public.estado USING (reg_001, est_001)
WHERE agri04_001 = '2006'
GROUP BY est_002
ORDER BY est_002;
 

saludos

nop, no funciona...

[0]http://www.postgresql.org/docs/current/static/queries-table-expressions.html

[cacr]

Leyendo y relyendo la documentación:

LEFT OUTER JOIN
First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Thus, the joined table always has at least one row for each row in T1.

RIGHT OUTER JOIN
First, an inner join is performed. Then, for each row in T2 that does not satisfy the join condition with any row in T1, a joined row is added with null values in columns of T1. This is the converse of a left join: the result table will always have a row for each row in T2.

me doy cuenta que, para mi caso, no es left sino right, y sin embargo, no obtengo los resultados que busco

Por si hay sospechas, hice el ejemplo de la doc en una BD pruebas y esa sí furula...

[huesos52]

cacr...
Me dices que si la haces para 2006 te regresa 3 registros (Lo esperado)
Si lo haces con 2005 te regresa un solo registro.

Debes tener en cuenta que la consulta aplica el left o right join de acuerdo a la inf. que le pidas a las tablas, no a toda la información.

Entonces... si te devuelve 1 solo registro, sospecharía que es el registro que retorna merida... me equivoco?
Creo que es un problema mas de condiciones que no cumplen simultaneamente el año con la información solicitada y que está siendo escondida por el where.

Si lo que digo es cierto, si ejecutas esto:

Código SQL:

SELECT *FROM agri04 WHERE agri04_001 = '2006';
 

debes encontrar registros que cumplan con reg_001 y est_001 en (06 14) en (06 20) y (06 21)

Mientras que si ejecutas

Código SQL:

SELECT *FROM agri04 WHERE agri04_001 = '2005';
 

solo encontraras registros que coincidan con (06 14)

Nos cuentas como te va.

saludos

[cacr]

Toda esa teoría es muy acertada huesos52, pero... no choca con la teoría del left(rigth) join? donde dice:

...a joined row is added with null values in columns...

?

[huesos52]

No choca cacr.... Por que en la condición estas filtrando los registros que solo coinciden con esto.
El left o right join opera antes de la condición, pero el where se encarga de eliminar de la lista, todos aquellos registros que no cumplan la condición.

[cacr]

Vale... el where filtra después...
Una luz fuera del topic, y si quiero que me aparezcan ¿cuál debería ser el camino?

[huesos52]

Dejame masticarlo!!

[cacr]

Cómo ves esto?

Código:

SELECT t1.estado, t2.campo 
FROM (SELECT est_002 AS estado, reg_001, est_001
	FROM public.estado 
	WHERE reg_001 = '06') AS t1
LEFT JOIN (SELECT sum(agri04_005) AS campo,reg_001, est_001
	FROM agri04 
	WHERE reg_001 = '06' AND agri04_001 = '2005'
	GROUP BY reg_001, est_001) AS t2
ON (t1.reg_001=t2.reg_001 AND t1.est_001=t2.est_001)

Lo lancé y me hizo algo, que se parece a lo que busco

[huesos52]

Definitivamente la solución son subconsultas.

A mi se me ocurrió algo similar

Código SQL:

SELECT 
t.estado,
SUM(COALESCE(t.valor,0)) AS suma_definitiva
FROM 
(
 SELECT  
    e.est_002 AS estado,
    e.reg_001,
    e.est_001,
    a.agri04_001 AS ano,
    a.agri04_005 AS valor
 FROM estado e 
 LEFT JOIN 
 agri04 a
 ON
 (e.reg_001,e.est_001)=(a.reg_001,a.est_001)
) AS t
WHERE t.ano = '2005'
GROUP BY t.estado;
 

EDITO
o así

Código SQL:

SELECT 
t.estado,
SUM(COALESCE(t.valor,0)) AS suma_definitiva
FROM 
(
 SELECT  
    e.est_002 AS estado,
    e.reg_001,
    e.est_001,
    a.agri04_001 AS ano,
    a.agri04_005 AS valor
 FROM estado e 
 LEFT JOIN 
 agri04 a
 ON
 (e.reg_001,e.est_001)=(a.reg_001,a.est_001)
) AS t
GROUP BY t.estado
HAVING t.ano = '2005';
 

No tengo como probarla.

[cacr]

Le pasa lo mismo huesos52, el where está después y filtra.

Por el camino de las subconsultas me vuelvo a tirar

De todas todas, mil gracias!

[huesos52]

y la del having?

En teoría el having si se aplica después del agrupamiento.

Nos cuentas como te va.

[cacr]

idem

having y where se ejecutan, ciertamente, después