A vueltas con un query

[Angel.Matilla]

He estado probando añadiendo y quitando trozos del query original y descubierto que el problema viene con el último LEFT JOIN:

Código SQL [-]
LEFT JOIN mesas e on  e.codprv = a.codprv and e.municipio = c.circunscripcion

Si se deja, salen mal los datos.

[Angel.Matilla]

Cada vez estoy más desanimado con este query, y mira que pintaba bien.

Código SQL [-]
select a.codigo, a.nombre, coalesce(sum(b.votos), 0) votos, coalesce(c.electos, 0) electos, c.circunscripcion, d.nombre
from partidos a
left join resultados b on b.partido = a.codigo and a.codprv = b.codprv
left join numelectos c on b.codprv = c.codprv and b.proceso = c.proceso and c.tipo = 'M' and b.partido = c.partido
LEFT join poblacion d on a.codprv = d.codprv and d.codigo = c.circunscripcion
where
a.codprv = rvins and
b.proceso = roceso and
b.mesa in (select codigo
           from mesas
           where
           codprv = a.codprv and
           municipio = c.circunscripcion)
group by a.codigo, a.nombre, electos, c.circunscripcion, d.nombre
order by d.nombre, votos desc, electos desc, a.nombre

He hecho la prueba con left join resultados y con join resultados y eliminando resultados en la tabla NumElectos para que de un municipio no hubiera esos campos pero sí resultados. Y con las tablas completas sale este resultado:

pero si suprimo lo que he comentado sale esto

aunque en la tabla resultados siga habiendo datos del municipio en cuestión (en este caso el 175). No lo entiendo: con LEFT JOIN ¿no deberían salir datos para ese valor? No sé que estoy haciendo mal o no entendiendo.

En el enlcae https://drive.google.com/file/d/1F3V...ew?usp=sharing hay un fichero TABLAS.RAR que tiene el script para crear y llenar las tablas implicadas en el query.

[[duilioisola]]

Al descargar el fichero te he pedido acceso.
En cuanto pueda descargarlo haré algunas pruebas para ver qué contienen las tablas...

[[duilioisola]]

He estado mirando las tablas...

Primera parte
La primera duda es cómo se unen mesa, poblacion y numelectos.
He inferido que mesas.codigo, poblacion.municipio y numelectos.circunscripcion es el mismo campo y lo he utilizado para los joins.

La segunda duda es qué es proceso? He inferido que se trata de diferentes votaciones y he estado utilizando la número 42.

Segunda parte.
Lo primero que necesitas antes de hacer las acumulaciones es la tabla con todos los datos necesarios unidos mediante JOIN / LEFT JOIN.

• Parto de PARTIDOS y le uno los RESULTADOS.
• Los RESULTADOS son de MESAS. Por lo tanto se unen a RESULTADOS.
• Las MESAS pertenecen a POBLACIONES, que se unen por el campo MUNICIPIO.
• Finalmente, NUMELECTOS se une mediante LEFT JOIN porque no necesariamente tiene electos para cada PROCESO-PROVINCIA-PARTIDO-MUNICIPIO

Esto me deja el siguiente SQL con alias identificados con la primera letra del nomrbre de la tabla. (excepto POBLACION po).

Este SQL devuelve 113511 registros.
Todos con valores excepto los que pertenecen a la tabla NUMELECTOS que a veces son nulos.

Código SQL [-]
select *
from partidos p
join resultados r on r.partido = p.codigo and p.codprv = r.codprv
join mesas m on m.codprv = r.codprv and m.codigo = r.mesa
join poblacion po on p.codprv = po.codprv and m.municipio = po.codigo
left join numelectos n on r.codprv = n.codprv and r.proceso = n.proceso and n.tipo = 'M' and r.partido = n.partido and po.codigo = n.circunscripcion

Ahora agrego el where para limitar los datos a tratar.
En este caso he elegido alguno que devuelve datos y que hemos hablado en este hilo

Este SQL devuelve 8 registros.
Todos con valores excepto los que pertenecen a la tabla NUMELECTOS que a veces son nulos.

Código SQL [-]
where
p.codprv = 13 and
r.proceso = 42 and
m.municipio = 175

Final
Ya tenemos los datos. Ahora solo tenemos que agruparlos y ordenarlos.

Código SQL [-]
select p.codigo, p.nombre, coalesce(sum(r.votos), 0) votos, coalesce(n.electos, 0) electos, n.circunscripcion,
       po.nombre
from partidos p
join resultados r on r.partido = p.codigo and p.codprv = r.codprv
join mesas m on m.codprv = r.codprv and m.codigo = r.mesa
join poblacion po on p.codprv = po.codprv and m.municipio = po.codigo
left join numelectos n on r.codprv = n.codprv and r.proceso = n.proceso and n.tipo = 'M' and r.partido = n.partido and po.codigo = n.circunscripcion
where
p.codprv = 13 and
r.proceso = 42 and
m.municipio = 175
group by p.codigo, p.nombre, electos, n.circunscripcion, po.nombre
order by po.nombre, 3 /*votos*/ desc, 4 /*electos*/ desc, p.nombre

Dos formas de evitar que salgan registros "sin electos".

Código SQL [-]
select p.codigo, p.nombre, coalesce(sum(r.votos), 0) votos, coalesce(n.electos, 0) electos, n.circunscripcion,
       po.nombre
from partidos p
join resultados r on r.partido = p.codigo and p.codprv = r.codprv
join mesas m on m.codprv = r.codprv and m.codigo = r.mesa
join poblacion po on p.codprv = po.codprv and m.municipio = po.codigo
left join numelectos n on r.codprv = n.codprv and r.proceso = n.proceso and n.tipo = 'M' and r.partido = n.partido and po.codigo = n.circunscripcion
where
p.codprv = 13 and
r.proceso = 42 and
m.municipio = 175
/* Para evitar registros sin electos */
and n.circunscripcion is not null
group by p.codigo, p.nombre, electos, n.circunscripcion, po.nombre
order by po.nombre, 3 /*votos*/ desc, 4 /*electos*/ desc, p.nombre

Código SQL [-]
select p.codigo, p.nombre, coalesce(sum(r.votos), 0) votos, coalesce(n.electos, 0) electos, n.circunscripcion,
       po.nombre
from partidos p
join resultados r on r.partido = p.codigo and p.codprv = r.codprv
join mesas m on m.codprv = r.codprv and m.codigo = r.mesa
join poblacion po on p.codprv = po.codprv and m.municipio = po.codigo
left join numelectos n on r.codprv = n.codprv and r.proceso = n.proceso and n.tipo = 'M' and r.partido = n.partido and po.codigo = n.circunscripcion
where
p.codprv = 13 and
r.proceso = 42 and
m.municipio = 175
group by p.codigo, p.nombre, electos, n.circunscripcion, po.nombre
/* Para evitar registros sin electos */
having coalesce(n.electos, 0) > 0
order by po.nombre, 3 /*votos*/ desc, 4 /*electos*/ desc, p.nombre

[[duilioisola]]

PD.

Después de ejecutar el script, todo iba muy lento.
He tenido que recaclular los índices para que todo vaya bien.

IbExpert tiene la opción de ir a DataBase-> Recompute celectivity of all Indeces

Básicamente recorre los índices de la base de datos y ejecuta SET STATISTICS INDEX [IndexName]:

Código SQL [-]
SET STATISTICS INDEX DISMES;

SET STATISTICS INDEX MUNMES;

SET STATISTICS INDEX PK_MESAS;

SET STATISTICS INDEX PK_NUMELECTOS;

SET STATISTICS INDEX PK_PARTIDOS;

SET STATISTICS INDEX PK_POBLACION;

SET STATISTICS INDEX PK_RESULTADOS;

[Angel.Matilla]

Empezado por duilioisola

PD.

Después de ejecutar el script, todo iba muy lento.
He tenido que recaclular los índices para que todo vaya bien.

Muchísimas gracias pro tu ayuda. Has hecho un pleno. Sólo he tenido que cambiar una cosa para que me saque todos los municipios, pero el query es perfecto.

Por otra parte, sobre la velocidad de ejecución he de decirte que para sacar todos los municipios a mi en SQL Manager me ha tardado 62 milisegundos. Únicamente las lecturas de la tabla mesas se hacen sin usar índices; tengo que ver por qué.

[[duilioisola]]

A mi también me va muy rápida la consulta, pero al principio los índices estaban desbalanceados (supongo que por el insert masivo del script) y todos tenínan un valor de 1 (o 0, no recuerdo bien).
Las estadísticas de los índices es una parte importante de lo que mira el planificador de SQL al hacer los JOINS.


Esto te devuelve las estadísticas de los índices y deberían tener valores lo más bajo posibles sin ser 0.

Código SQL [-]
select rdb$relation_name, rdb$index_name, rdb$statistics
from rdb$indices
where
/* Evito indices de sistema */
rdb$system_flag = 0
order by rdb$relation_name, rdb$index_name

Esto es lo que me devuelve ahora, después de ejeructar el "SET STATISTICS [indice]".

RDB$RELATION_NAME   RDB$INDEX_NAME   RDB$STATISTICS
-----------------   --------------   --------------
MESAS               DISMES           0,001355013577
MESAS               MUNMES           0,009803921916
MESAS               PK_MESAS         0,001355013577
NUMELECTOS          PK_NUMELECTOS    0,001915708766
PARTIDOS            PK_PARTIDOS      0,018518518656
POBLACION           PK_POBLACION     0,000017048846
RESULTADOS          PK_RESULTADOS    0,000008809486

[Angel.Matilla]

Empezado por duilioisola

He estado mirando las tablas...

Primera parte

Perdona por no haberte explicado las relaciones, pero has acertado en todas.