Created
November 6, 2013 02:05
-
-
Save jazzido/7329751 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH votos_summary AS | |
(SELECT ve.mesa_desde, | |
ve.mesa_hasta, | |
SUM(total) AS total_parcodigo, | |
ve.vot_parcodigo, | |
ve.dne_distri, | |
ve.dne_seccio, | |
row_number() over(partition BY ve.mesa_desde,ve.mesa_hasta | |
ORDER BY sum(total) DESC) AS rk | |
FROM votos_establecimiento_octubre ve | |
WHERE ve.votacion = '<%- votacion %>' \ | |
GROUP BY ve.mesa_desde, | |
ve.mesa_hasta, | |
ve.dne_distri, | |
ve.dne_seccio, | |
vot_parcodigo) | |
SELECT l.id, | |
l.cartodb_id, | |
l.the_geom, | |
l.the_geom_webmercator, | |
l.mesa_desde, | |
l.mesa_hasta, | |
cant_mesas, | |
establecim, | |
direccion, | |
l.dne_seccio, | |
l.dne_distri, | |
l.circuito, | |
SUM(meselectores) AS electores, | |
SUM(mestotalvotantes) AS votantes, | |
SUM(mesvotospositivos) AS positivos, | |
vs.total_parcodigo AS total_parcodigo, | |
vs.vot_parcodigo, | |
sqrt( | |
(SELECT total_parcodigo | |
FROM votos_summary | |
WHERE votos_summary.rk = 1 | |
AND votos_summary.mesa_hasta = l.mesa_hasta | |
AND votos_summary.mesa_desde = l.mesa_desde | |
AND votos_summary.dne_distri = l.dne_distri | |
AND votos_summary.dne_seccio = l.dne_seccio) - | |
(SELECT total_parcodigo | |
FROM votos_summary | |
WHERE votos_summary.rk = 2 | |
AND votos_summary.mesa_hasta = l.mesa_hasta | |
AND votos_summary.dne_distri = l.dne_distri | |
AND votos_summary.dne_seccio = l.dne_seccio)) AS margin_of_victory, | |
sqrt(sum(mesvotospositivos)) AS sqrt_positivos | |
FROM locales l | |
INNER JOIN mesas m ON (m.mescodigomesa BETWEEN l.mesa_desde AND l.mesa_hasta) | |
AND (mes_procodigoprovincia=l.dne_distri) | |
AND (mes_depcodigodepartamento=l.dne_seccio) | |
INNER JOIN votos_summary vs ON vs.mesa_desde = l.mesa_desde | |
AND vs.mesa_hasta = l.mesa_hasta | |
AND vs.dne_distri = l.dne_distri | |
AND vs.dne_seccio = l.dne_seccio | |
AND vs.rk = 1 | |
AND m.votacion = '<%- votacion %>' | |
GROUP BY l.id, | |
l.cartodb_id, | |
l.the_geom, | |
l.the_geom_webmercator, | |
l.mesa_desde, | |
l.mesa_hasta, | |
cant_mesas, | |
establecim, | |
direccion, | |
vs.total_parcodigo, | |
vs.vot_parcodigo |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment