R/modelo.R
In topazse/tse.sql: SQL hooks de TSE
#' Descargar datos de Modelo de Expansion
#'
#' Descargas datos para modelar de sql.
#'
#' @param con conexion abierta de RODBC
#' @param version version de descarga
#' @param pruebaMty si TRUE, solamente descarga datos de Monterrey
#' @export
t_datos_modelo <- function(con, version, pruebaMty = FALSE){
if(version == 1){
q <- "SELECT
sq1.AGEB_FOLIO,
sq1.LLAVEGEO,
POBLACION,
ZONA,
ZONA_AGRUPADA,
MACRO_ZONA,
ZONA_TOPAZ_ID,
CLUSTER_ID,
CLUSTER,
CERCANIA,
CENTRALIDAD_PODER,
CERCANIA_PROM,
CERCANIA_REL,
N2,
N3,
CLUSTER_AGEB,
ES_SOLO,
AM_GRAPROES,
AM_PERSXHOGAR,
AM_AUTOS,
AM_EDOCIVIL,
TASA_EST,
CRIMENES,
TENDENCIA,
COB_NET_PRIM,
COB_NET_SEC,
COB_NET_PREP,
COB_NET_PROF,
REZ_TOTAL_ADULTO,
REZ_PREPA_ADULTO,
ESCOLARIDAD,
PEA,
CATOLICOS,
CENS_PERSXHOGAR,
CENS_AUTO,
CENS_PC,
EDAD_EDU,
EDAD_EDU_PROF,
EDAD_EDUF_PREPA,
EDAD_EDUF_PROF,
P_EDAD_EDU,
P_EDAD_EDU_PROF,
P_EDAD_EDUF_PREPA,
P_EDAD_EDUF_PROF,
HACINAMIENTO,
DENSIDAD,
GEN_REZ_PREPA,
GEN_PART_PEA,
MIGRACION,
C_TEORICO,
C_PROM,
HH_UE_2014,
LQ_EMP_2014_11,
LQ_EMP_2014_21,
LQ_EMP_2014_22,
LQ_EMP_2014_23,
LQ_EMP_2014_43,
LQ_EMP_2014_46,
LQ_EMP_2014_51,
LQ_EMP_2014_52,
LQ_EMP_2014_53,
LQ_EMP_2014_54,
LQ_EMP_2014_55,
LQ_EMP_2014_56,
LQ_EMP_2014_61,
LQ_EMP_2014_62,
LQ_EMP_2014_71,
LQ_EMP_2014_72,
LQ_EMP_2014_81,
LQ_EMP_2014_SC,
EST_MODELO,
LQ_EMP_2014_OTROS,
LQ_ING_2014_OTROS,
LQ_EMP_2014_PRIMARIA,
LQ_ING_2014_PRIMARIA,
LQ_EMP_2014_SECUNDARIA,
LQ_ING_2014_SECUNDARIA,
LQ_EMP_2014_TERCIARIA,
LQ_ING_2014_TERCIARIA,
TMR_SEC,
TMR_PREP,
TMR_PROF,
TA_PREP,
TA_PRO,
L_ADMINISTRACION,
L_ARQUITECTURA_Y_CONSTRUCCION,
L_CIENCIAS_DE_LA_INFORMACION,
L_CIENCIAS_SOCIALES_Y_ESTUDIOS_DEL_COMPORTAMIENTO,
L_DERECHO,
L_HUMANIDADES,
L_INGENIERIA_INDUSTRIAL__MECANICA_Y_ELECTRICA,
L_MANUFACTURAS_Y_PROCESOS,
L_NEGOCIOS,
L_SALUD,
C_ADMINISTRACION,
C_ARQUITECTURA_Y_CONSTRUCCION,
C_CIENCIAS_DE_LA_INFORMACION,
C_CIENCIAS_SOCIALES_Y_ESTUDIOS_DEL_COMPORTAMIENTO,
C_DERECHO,
C_HUMANIDADES,
C_INGENIERIA_INDUSTRIAL__MECANICA_Y_ELECTRICA,
C_MANUFACTURAS_Y_PROCESOS,
C_NEGOCIOS,
C_SALUD
FROM
(SELECT
a.AGEB_FOLIO,
a.LLAVEGEO,
sum(cast(POBTOT as FLOAT)) as POBLACION
FROM bt_censoageb a
GROUP BY a.AGEB_FOLIO, a.LLAVEGEO)sq1
LEFT JOIN (SELECT AGEB_FOLIO, ZONA, ZONA_TOPAZ_ID,
ZONA_AGRUPADA, MACRO_ZONA FROM dim_zonastopaz)b on b.AGEB_FOLIO = sq1.AGEB_FOLIO
LEFT JOIN (SELECT LLAVEGEO, CLUSTER_ID, CLUSTER FROM m_clusters)c on c.LLAVEGEO = sq1.LLAVEGEO
LEFT JOIN (SELECT AGEB_FOLIO, CERCANIA, CENTRALIDAD_PODER,
CERCANIA_PROM, CERCANIA_REL, N2, N3, CLUSTER_AGEB, ES_SOLO
FROM m_conectividad)d on d.AGEB_FOLIO = sq1.AGEB_FOLIO
LEFT JOIN (SELECT AGEB_FOLIO, AM_GRAPROES, AM_PERSXHOGAR, AM_AUTOS, AM_EDOCIVIL
FROM m_alumnomodelo)e on e.AGEB_FOLIO = sq1.AGEB_FOLIO
LEFT JOIN (SELECT AGEB_FOLIO, TASA_EST, CRIMENES, TENDENCIA FROM m_crimen)f on f.AGEB_FOLIO = sq1.AGEB_FOLIO
LEFT JOIN (SELECT AGEB_FOLIO,
COB_NET_PRIM, COB_NET_SEC, COB_NET_PREP, COB_NET_PROF,
REZ_TOTAL_ADULTO, REZ_PREPA_ADULTO,
ESCOLARIDAD, PEA, CATOLICOS, CENS_PERSXHOGAR,
CENS_AUTO, CENS_PC,
EDAD_EDU, EDAD_EDU_PROF, EDAD_EDUF_PREPA, EDAD_EDUF_PROF,
P_EDAD_EDU, P_EDAD_EDU_PROF, P_EDAD_EDUF_PREPA, P_EDAD_EDUF_PROF,
HACINAMIENTO, DENSIDAD, GEN_REZ_PREPA, GEN_PART_PEA, MIGRACION,
C_TEORICO, C_PROM
FROM m_demografia)g on g.AGEB_FOLIO = sq1.AGEB_FOLIO
LEFT JOIN (SELECT LLAVEGEO, HH_UE_2014, LQ_EMP_2014_11, LQ_EMP_2014_21
,LQ_EMP_2014_22, LQ_EMP_2014_23, LQ_EMP_2014_43, LQ_EMP_2014_46
,LQ_EMP_2014_51, LQ_EMP_2014_52, LQ_EMP_2014_53, LQ_EMP_2014_54
,LQ_EMP_2014_55, LQ_EMP_2014_56, LQ_EMP_2014_61, LQ_EMP_2014_62
,LQ_EMP_2014_71, LQ_EMP_2014_72, LQ_EMP_2014_81, LQ_EMP_2014_SC, EST_MODELO
FROM m_empleadores)h on h.LLAVEGEO = sq1.LLAVEGEO
LEFT JOIN (SELECT LLAVEGEO, LQ_EMP_2014_OTROS, LQ_ING_2014_OTROS,
LQ_EMP_2014_PRIMARIA, LQ_ING_2014_PRIMARIA,
LQ_EMP_2014_SECUNDARIA, LQ_ING_2014_SECUNDARIA,
LQ_EMP_2014_TERCIARIA, LQ_ING_2014_TERCIARIA
FROM dbo.m_lq)i on i.LLAVEGEO = sq1.LLAVEGEO
LEFT JOIN (SELECT LLAVEGEO, TMR_SEC, TMR_PREP, TMR_PROF,
TA_PREP, TA_PRO,
L_ADMINISTRACION, L_ARQUITECTURA_Y_CONSTRUCCION,
L_CIENCIAS_DE_LA_INFORMACION, L_CIENCIAS_SOCIALES_Y_ESTUDIOS_DEL_COMPORTAMIENTO,
L_DERECHO, L_HUMANIDADES, L_INGENIERIA_INDUSTRIAL__MECANICA_Y_ELECTRICA,
L_MANUFACTURAS_Y_PROCESOS, L_NEGOCIOS, L_SALUD, C_ADMINISTRACION,
C_ARQUITECTURA_Y_CONSTRUCCION, C_CIENCIAS_DE_LA_INFORMACION,
C_CIENCIAS_SOCIALES_Y_ESTUDIOS_DEL_COMPORTAMIENTO, C_DERECHO,
C_HUMANIDADES, C_INGENIERIA_INDUSTRIAL__MECANICA_Y_ELECTRICA,
C_MANUFACTURAS_Y_PROCESOS, C_NEGOCIOS, C_SALUD
FROM dbo.m_oferta)j on j.LLAVEGEO = sq1.LLAVEGEO"
# warn!
print("Cuidado! Version 1 no contiene los datos de m_competidores")
}
if(version == 2){
q <- paste0("SELECT
sq1.AGEB_FOLIO,
sq1.LLAVEGEO,
POBLACION,
ZONA,
ZONA_AGRUPADA,
MACRO_ZONA,
ZONA_TOPAZ_ID,
CLUSTER_ID,
CLUSTER,
CERCANIA,
CENTRALIDAD_PODER,
CERCANIA_PROM,
CERCANIA_REL,
N2,
N3,
CLUSTER_AGEB,
ES_SOLO,
AM_GRAPROES,
AM_PERSXHOGAR,
AM_AUTOS,
AM_EDOCIVIL,
TASA_EST,
CRIMENES,
TENDENCIA,
COB_NET_PRIM,
COB_NET_SEC,
COB_NET_PREP,
COB_NET_PROF,
REZ_TOTAL_ADULTO,
REZ_PREPA_ADULTO,
ESCOLARIDAD,
PEA,
CATOLICOS,
CENS_PERSXHOGAR,
CENS_AUTO,
CENS_PC,
EDAD_EDU,
EDAD_EDU_PROF,
EDAD_EDUF_PREPA,
EDAD_EDUF_PROF,
P_EDAD_EDU,
P_EDAD_EDU_PROF,
P_EDAD_EDUF_PREPA,
P_EDAD_EDUF_PROF,
HACINAMIENTO,
DENSIDAD,
GEN_REZ_PREPA,
GEN_PART_PEA,
MIGRACION,
C_TEORICO,
C_PROM,
HH_UE_2014,
LQ_EMP_2014_11,
LQ_EMP_2014_21,
LQ_EMP_2014_22,
LQ_EMP_2014_23,
LQ_EMP_2014_43,
LQ_EMP_2014_46,
LQ_EMP_2014_51,
LQ_EMP_2014_52,
LQ_EMP_2014_53,
LQ_EMP_2014_54,
LQ_EMP_2014_55,
LQ_EMP_2014_56,
LQ_EMP_2014_61,
LQ_EMP_2014_62,
LQ_EMP_2014_71,
LQ_EMP_2014_72,
LQ_EMP_2014_81,
LQ_EMP_2014_SC,
EST_MODELO,
LQ_EMP_2014_OTROS,
LQ_ING_2014_OTROS,
LQ_EMP_2014_PRIMARIA,
LQ_ING_2014_PRIMARIA,
LQ_EMP_2014_SECUNDARIA,
LQ_ING_2014_SECUNDARIA,
LQ_EMP_2014_TERCIARIA,
LQ_ING_2014_TERCIARIA,
TMR_SEC,
TMR_PREP,
TMR_PROF,
TA_PREP,
TA_PRO,
L_ADMINISTRACION,
L_ARQUITECTURA_Y_CONSTRUCCION,
L_CIENCIAS_DE_LA_INFORMACION,
L_CIENCIAS_SOCIALES_Y_ESTUDIOS_DEL_COMPORTAMIENTO,
L_DERECHO,
L_HUMANIDADES,
L_INGENIERIA_INDUSTRIAL__MECANICA_Y_ELECTRICA,
L_MANUFACTURAS_Y_PROCESOS,
L_NEGOCIOS,
L_SALUD,
C_ADMINISTRACION,
C_ARQUITECTURA_Y_CONSTRUCCION,
C_CIENCIAS_DE_LA_INFORMACION,
C_CIENCIAS_SOCIALES_Y_ESTUDIOS_DEL_COMPORTAMIENTO,
C_DERECHO,
C_HUMANIDADES,
C_INGENIERIA_INDUSTRIAL__MECANICA_Y_ELECTRICA,
C_MANUFACTURAS_Y_PROCESOS,
C_NEGOCIOS,
C_SALUD,
HH_COMP_GDE,
HH_GENERAL,
SHR_PUB,
SHR_GDE,
SHR_MAYOR,
TEND_SHR_PUB
FROM
(SELECT
a.AGEB_FOLIO,
a.LLAVEGEO,
sum(cast(POBTOT as FLOAT)) as POBLACION
FROM bt_censoageb a
GROUP BY a.AGEB_FOLIO, a.LLAVEGEO)sq1
LEFT JOIN (SELECT AGEB_FOLIO, ZONA, ZONA_TOPAZ_ID,
ZONA_AGRUPADA, MACRO_ZONA
FROM dim_zonastopaz)b on b.AGEB_FOLIO = sq1.AGEB_FOLIO
LEFT JOIN (SELECT LLAVEGEO, CLUSTER_ID, CLUSTER
FROM m_clusters)c on c.LLAVEGEO = sq1.LLAVEGEO
LEFT JOIN (SELECT AGEB_FOLIO,
CASE WHEN CERCANIA = 0 THEN 0 ELSE 1/CERCANIA END as CERCANIA,
CENTRALIDAD_PODER,
CASE WHEN CERCANIA_PROM = 0 THEN 0 ELSE 1/CERCANIA_PROM END as CERCANIA_PROM,
CASE WHEN CERCANIA_REL = 0 THEN 0 ELSE 1/CERCANIA_REL END as CERCANIA_REL,
N2, N3, CLUSTER_AGEB,
1-ES_SOLO as ES_SOLO
FROM m_conectividad)d on d.AGEB_FOLIO = sq1.AGEB_FOLIO
LEFT JOIN (SELECT AGEB_FOLIO, AM_GRAPROES, AM_PERSXHOGAR, AM_AUTOS, AM_EDOCIVIL
FROM m_alumnomodelo)e on e.AGEB_FOLIO = sq1.AGEB_FOLIO
LEFT JOIN (SELECT AGEB_FOLIO,
TASA_EST*-1 as TASA_EST,
CRIMENES*-1 as CRIMENES,
1/TENDENCIA as TENDENCIA
FROM m_crimen)f on f.AGEB_FOLIO = sq1.AGEB_FOLIO
LEFT JOIN (SELECT AGEB_FOLIO,
COB_NET_PRIM, COB_NET_SEC,
CASE WHEN COB_NET_PREP=0 THEN 0 ELSE 1/COB_NET_PREP END as COB_NET_PREP,
CASE WHEN COB_NET_PROF=0 THEN 0 ELSE 1/COB_NET_PROF END as COB_NET_PROF,
CASE WHEN REZ_TOTAL_ADULTO=0 THEN 0 ELSE 1/REZ_TOTAL_ADULTO END as REZ_TOTAL_ADULTO,
CASE WHEN REZ_PREPA_ADULTO=0 THEN 0 ELSE 1/REZ_PREPA_ADULTO END as REZ_PREPA_ADULTO,
CASE WHEN ESCOLARIDAD=0 THEN 0 ELSE 1/ESCOLARIDAD END as ESCOLARIDAD,
PEA, CATOLICOS, CENS_PERSXHOGAR,
CENS_AUTO, CENS_PC,
EDAD_EDU, EDAD_EDU_PROF, EDAD_EDUF_PREPA, EDAD_EDUF_PROF,
P_EDAD_EDU, P_EDAD_EDU_PROF, P_EDAD_EDUF_PREPA, P_EDAD_EDUF_PROF,
CASE WHEN HACINAMIENTO=0 THEN 0 ELSE 1/HACINAMIENTO END as HACINAMIENTO,
CASE WHEN DENSIDAD=0 THEN 0 ELSE 1/DENSIDAD END as DENSIDAD,
GEN_REZ_PREPA, GEN_PART_PEA, MIGRACION,
C_TEORICO, C_PROM
FROM m_demografia)g on g.AGEB_FOLIO = sq1.AGEB_FOLIO
LEFT JOIN (SELECT LLAVEGEO, HH_UE_2014,
CASE WHEN LQ_EMP_2014_11 = 0 THEN 0 ELSE 1/LQ_EMP_2014_11 END as LQ_EMP_2014_11,
CASE WHEN LQ_EMP_2014_21 = 0 THEN 0 ELSE 1/LQ_EMP_2014_21 END as LQ_EMP_2014_21,
CASE WHEN LQ_EMP_2014_22 = 0 THEN 0 ELSE 1/LQ_EMP_2014_22 END as LQ_EMP_2014_22,
LQ_EMP_2014_23, LQ_EMP_2014_43, LQ_EMP_2014_46,
LQ_EMP_2014_51, LQ_EMP_2014_52, LQ_EMP_2014_53, LQ_EMP_2014_54,
LQ_EMP_2014_55, LQ_EMP_2014_56, LQ_EMP_2014_61, LQ_EMP_2014_62,
LQ_EMP_2014_71, LQ_EMP_2014_72, LQ_EMP_2014_81, LQ_EMP_2014_SC,
EST_MODELO
FROM m_empleadores)h on h.LLAVEGEO = sq1.LLAVEGEO
LEFT JOIN (SELECT LLAVEGEO,
LQ_EMP_2014_OTROS, LQ_ING_2014_OTROS,
CASE WHEN LQ_EMP_2014_PRIMARIA = 0 THEN 0 ELSE 1/LQ_EMP_2014_PRIMARIA END as LQ_EMP_2014_PRIMARIA,
CASE WHEN LQ_ING_2014_PRIMARIA = 0 THEN 0 ELSE 1/LQ_ING_2014_PRIMARIA END as LQ_ING_2014_PRIMARIA,
LQ_EMP_2014_SECUNDARIA, LQ_ING_2014_SECUNDARIA,
LQ_EMP_2014_TERCIARIA, LQ_ING_2014_TERCIARIA
FROM dbo.m_lq)i on i.LLAVEGEO = sq1.LLAVEGEO
LEFT JOIN (SELECT LLAVEGEO, TMR_SEC, TMR_PREP, TMR_PROF,
TA_PREP, TA_PRO,
L_ADMINISTRACION, L_ARQUITECTURA_Y_CONSTRUCCION,
L_CIENCIAS_DE_LA_INFORMACION, L_CIENCIAS_SOCIALES_Y_ESTUDIOS_DEL_COMPORTAMIENTO,
L_DERECHO, L_HUMANIDADES, L_INGENIERIA_INDUSTRIAL__MECANICA_Y_ELECTRICA,
L_MANUFACTURAS_Y_PROCESOS, L_NEGOCIOS, L_SALUD, C_ADMINISTRACION,
C_ARQUITECTURA_Y_CONSTRUCCION, C_CIENCIAS_DE_LA_INFORMACION,
C_CIENCIAS_SOCIALES_Y_ESTUDIOS_DEL_COMPORTAMIENTO, C_DERECHO,
C_HUMANIDADES, C_INGENIERIA_INDUSTRIAL__MECANICA_Y_ELECTRICA,
C_MANUFACTURAS_Y_PROCESOS, C_NEGOCIOS, C_SALUD
FROM dbo.m_oferta)j on j.LLAVEGEO = sq1.LLAVEGEO
LEFT JOIN (SELECT
ESTADO_ID+'-'+MUNICIPIO_ID AS LLAVEGEO,
ESTADO_R,
MUNICIPIO FROM dim_geografia)k on k.LLAVEGEO = sq1.LLAVEGEO
LEFT JOIN (SELECT LLAVEGEO,
CASE WHEN HH_COMP_GDE=0 THEN 0 ELSE 1/HH_COMP_GDE END as HH_COMP_GDE,
CASE WHEN HH_GENERAL=0 THEN 0 ELSE 1/HH_GENERAL END as HH_GENERAL,
CASE WHEN SHR_PUB=0 THEN 0 ELSE 1/SHR_PUB END as SHR_PUB,
CASE WHEN SHR_GDE=0 THEN 0 ELSE 1/SHR_GDE END as SHR_GDE,
CASE WHEN SHR_MAYOR=0 THEN 0 ELSE 1/SHR_MAYOR END as SHR_MAYOR,
CASE WHEN TEND_SHR_PUB=0 THEN 0 ELSE 1/TEND_SHR_PUB END as TEND_SHR_PUB
FROM m_competidores)l on l.LLAVEGEO = sq1.LLAVEGEO",
if(pruebaMty){" WHERE SUBSTRING(sq1.LLAVEGEO, 1, 2) = '19'"}else{})
}
d <- RODBC::sqlQuery(channel = con, query = q)
d
}
topazse/tse.sql documentation built on May 31, 2019, 6:23 p.m.
R Package Documentation
Browse R Packages
We want your feedback!
Note that we can't provide technical support on individual packages. You should contact the package authors for that.
#' Descargar datos de Modelo de Expansion
#'
#' Descargas datos para modelar de sql.
#'
#' @param con conexion abierta de RODBC
#' @param version version de descarga
#' @param pruebaMty si TRUE, solamente descarga datos de Monterrey
#' @export
t_datos_modelo <- function(con, version, pruebaMty = FALSE){
if(version == 1){
q <- "SELECT
sq1.AGEB_FOLIO,
sq1.LLAVEGEO,
POBLACION,
ZONA,
ZONA_AGRUPADA,
MACRO_ZONA,
ZONA_TOPAZ_ID,
CLUSTER_ID,
CLUSTER,
CERCANIA,
CENTRALIDAD_PODER,
CERCANIA_PROM,
CERCANIA_REL,
N2,
N3,
CLUSTER_AGEB,
ES_SOLO,
AM_GRAPROES,
AM_PERSXHOGAR,
AM_AUTOS,
AM_EDOCIVIL,
TASA_EST,
CRIMENES,
TENDENCIA,
COB_NET_PRIM,
COB_NET_SEC,
COB_NET_PREP,
COB_NET_PROF,
REZ_TOTAL_ADULTO,
REZ_PREPA_ADULTO,
ESCOLARIDAD,
PEA,
CATOLICOS,
CENS_PERSXHOGAR,
CENS_AUTO,
CENS_PC,
EDAD_EDU,
EDAD_EDU_PROF,
EDAD_EDUF_PREPA,
EDAD_EDUF_PROF,
P_EDAD_EDU,
P_EDAD_EDU_PROF,
P_EDAD_EDUF_PREPA,
P_EDAD_EDUF_PROF,
HACINAMIENTO,
DENSIDAD,
GEN_REZ_PREPA,
GEN_PART_PEA,
MIGRACION,
C_TEORICO,
C_PROM,
HH_UE_2014,
LQ_EMP_2014_11,
LQ_EMP_2014_21,
LQ_EMP_2014_22,
LQ_EMP_2014_23,
LQ_EMP_2014_43,
LQ_EMP_2014_46,
LQ_EMP_2014_51,
LQ_EMP_2014_52,
LQ_EMP_2014_53,
LQ_EMP_2014_54,
LQ_EMP_2014_55,
LQ_EMP_2014_56,
LQ_EMP_2014_61,
LQ_EMP_2014_62,
LQ_EMP_2014_71,
LQ_EMP_2014_72,
LQ_EMP_2014_81,
LQ_EMP_2014_SC,
EST_MODELO,
LQ_EMP_2014_OTROS,
LQ_ING_2014_OTROS,
LQ_EMP_2014_PRIMARIA,
LQ_ING_2014_PRIMARIA,
LQ_EMP_2014_SECUNDARIA,
LQ_ING_2014_SECUNDARIA,
LQ_EMP_2014_TERCIARIA,
LQ_ING_2014_TERCIARIA,
TMR_SEC,
TMR_PREP,
TMR_PROF,
TA_PREP,
TA_PRO,
L_ADMINISTRACION,
L_ARQUITECTURA_Y_CONSTRUCCION,
L_CIENCIAS_DE_LA_INFORMACION,
L_CIENCIAS_SOCIALES_Y_ESTUDIOS_DEL_COMPORTAMIENTO,
L_DERECHO,
L_HUMANIDADES,
L_INGENIERIA_INDUSTRIAL__MECANICA_Y_ELECTRICA,
L_MANUFACTURAS_Y_PROCESOS,
L_NEGOCIOS,
L_SALUD,
C_ADMINISTRACION,
C_ARQUITECTURA_Y_CONSTRUCCION,
C_CIENCIAS_DE_LA_INFORMACION,
C_CIENCIAS_SOCIALES_Y_ESTUDIOS_DEL_COMPORTAMIENTO,
C_DERECHO,
C_HUMANIDADES,
C_INGENIERIA_INDUSTRIAL__MECANICA_Y_ELECTRICA,
C_MANUFACTURAS_Y_PROCESOS,
C_NEGOCIOS,
C_SALUD
FROM
(SELECT
a.AGEB_FOLIO,
a.LLAVEGEO,
sum(cast(POBTOT as FLOAT)) as POBLACION
FROM bt_censoageb a
GROUP BY a.AGEB_FOLIO, a.LLAVEGEO)sq1
LEFT JOIN (SELECT AGEB_FOLIO, ZONA, ZONA_TOPAZ_ID,
ZONA_AGRUPADA, MACRO_ZONA FROM dim_zonastopaz)b on b.AGEB_FOLIO = sq1.AGEB_FOLIO
LEFT JOIN (SELECT LLAVEGEO, CLUSTER_ID, CLUSTER FROM m_clusters)c on c.LLAVEGEO = sq1.LLAVEGEO
LEFT JOIN (SELECT AGEB_FOLIO, CERCANIA, CENTRALIDAD_PODER,
CERCANIA_PROM, CERCANIA_REL, N2, N3, CLUSTER_AGEB, ES_SOLO
FROM m_conectividad)d on d.AGEB_FOLIO = sq1.AGEB_FOLIO
LEFT JOIN (SELECT AGEB_FOLIO, AM_GRAPROES, AM_PERSXHOGAR, AM_AUTOS, AM_EDOCIVIL
FROM m_alumnomodelo)e on e.AGEB_FOLIO = sq1.AGEB_FOLIO
LEFT JOIN (SELECT AGEB_FOLIO, TASA_EST, CRIMENES, TENDENCIA FROM m_crimen)f on f.AGEB_FOLIO = sq1.AGEB_FOLIO
LEFT JOIN (SELECT AGEB_FOLIO,
COB_NET_PRIM, COB_NET_SEC, COB_NET_PREP, COB_NET_PROF,
REZ_TOTAL_ADULTO, REZ_PREPA_ADULTO,
ESCOLARIDAD, PEA, CATOLICOS, CENS_PERSXHOGAR,
CENS_AUTO, CENS_PC,
EDAD_EDU, EDAD_EDU_PROF, EDAD_EDUF_PREPA, EDAD_EDUF_PROF,
P_EDAD_EDU, P_EDAD_EDU_PROF, P_EDAD_EDUF_PREPA, P_EDAD_EDUF_PROF,
HACINAMIENTO, DENSIDAD, GEN_REZ_PREPA, GEN_PART_PEA, MIGRACION,
C_TEORICO, C_PROM
FROM m_demografia)g on g.AGEB_FOLIO = sq1.AGEB_FOLIO
LEFT JOIN (SELECT LLAVEGEO, HH_UE_2014, LQ_EMP_2014_11, LQ_EMP_2014_21
,LQ_EMP_2014_22, LQ_EMP_2014_23, LQ_EMP_2014_43, LQ_EMP_2014_46
,LQ_EMP_2014_51, LQ_EMP_2014_52, LQ_EMP_2014_53, LQ_EMP_2014_54
,LQ_EMP_2014_55, LQ_EMP_2014_56, LQ_EMP_2014_61, LQ_EMP_2014_62
,LQ_EMP_2014_71, LQ_EMP_2014_72, LQ_EMP_2014_81, LQ_EMP_2014_SC, EST_MODELO
FROM m_empleadores)h on h.LLAVEGEO = sq1.LLAVEGEO
LEFT JOIN (SELECT LLAVEGEO, LQ_EMP_2014_OTROS, LQ_ING_2014_OTROS,
LQ_EMP_2014_PRIMARIA, LQ_ING_2014_PRIMARIA,
LQ_EMP_2014_SECUNDARIA, LQ_ING_2014_SECUNDARIA,
LQ_EMP_2014_TERCIARIA, LQ_ING_2014_TERCIARIA
FROM dbo.m_lq)i on i.LLAVEGEO = sq1.LLAVEGEO
LEFT JOIN (SELECT LLAVEGEO, TMR_SEC, TMR_PREP, TMR_PROF,
TA_PREP, TA_PRO,
L_ADMINISTRACION, L_ARQUITECTURA_Y_CONSTRUCCION,
L_CIENCIAS_DE_LA_INFORMACION, L_CIENCIAS_SOCIALES_Y_ESTUDIOS_DEL_COMPORTAMIENTO,
L_DERECHO, L_HUMANIDADES, L_INGENIERIA_INDUSTRIAL__MECANICA_Y_ELECTRICA,
L_MANUFACTURAS_Y_PROCESOS, L_NEGOCIOS, L_SALUD, C_ADMINISTRACION,
C_ARQUITECTURA_Y_CONSTRUCCION, C_CIENCIAS_DE_LA_INFORMACION,
C_CIENCIAS_SOCIALES_Y_ESTUDIOS_DEL_COMPORTAMIENTO, C_DERECHO,
C_HUMANIDADES, C_INGENIERIA_INDUSTRIAL__MECANICA_Y_ELECTRICA,
C_MANUFACTURAS_Y_PROCESOS, C_NEGOCIOS, C_SALUD
FROM dbo.m_oferta)j on j.LLAVEGEO = sq1.LLAVEGEO"
# warn!
print("Cuidado! Version 1 no contiene los datos de m_competidores")
}
if(version == 2){
q <- paste0("SELECT
sq1.AGEB_FOLIO,
sq1.LLAVEGEO,
POBLACION,
ZONA,
ZONA_AGRUPADA,
MACRO_ZONA,
ZONA_TOPAZ_ID,
CLUSTER_ID,
CLUSTER,
CERCANIA,
CENTRALIDAD_PODER,
CERCANIA_PROM,
CERCANIA_REL,
N2,
N3,
CLUSTER_AGEB,
ES_SOLO,
AM_GRAPROES,
AM_PERSXHOGAR,
AM_AUTOS,
AM_EDOCIVIL,
TASA_EST,
CRIMENES,
TENDENCIA,
COB_NET_PRIM,
COB_NET_SEC,
COB_NET_PREP,
COB_NET_PROF,
REZ_TOTAL_ADULTO,
REZ_PREPA_ADULTO,
ESCOLARIDAD,
PEA,
CATOLICOS,
CENS_PERSXHOGAR,
CENS_AUTO,
CENS_PC,
EDAD_EDU,
EDAD_EDU_PROF,
EDAD_EDUF_PREPA,
EDAD_EDUF_PROF,
P_EDAD_EDU,
P_EDAD_EDU_PROF,
P_EDAD_EDUF_PREPA,
P_EDAD_EDUF_PROF,
HACINAMIENTO,
DENSIDAD,
GEN_REZ_PREPA,
GEN_PART_PEA,
MIGRACION,
C_TEORICO,
C_PROM,
HH_UE_2014,
LQ_EMP_2014_11,
LQ_EMP_2014_21,
LQ_EMP_2014_22,
LQ_EMP_2014_23,
LQ_EMP_2014_43,
LQ_EMP_2014_46,
LQ_EMP_2014_51,
LQ_EMP_2014_52,
LQ_EMP_2014_53,
LQ_EMP_2014_54,
LQ_EMP_2014_55,
LQ_EMP_2014_56,
LQ_EMP_2014_61,
LQ_EMP_2014_62,
LQ_EMP_2014_71,
LQ_EMP_2014_72,
LQ_EMP_2014_81,
LQ_EMP_2014_SC,
EST_MODELO,
LQ_EMP_2014_OTROS,
LQ_ING_2014_OTROS,
LQ_EMP_2014_PRIMARIA,
LQ_ING_2014_PRIMARIA,
LQ_EMP_2014_SECUNDARIA,
LQ_ING_2014_SECUNDARIA,
LQ_EMP_2014_TERCIARIA,
LQ_ING_2014_TERCIARIA,
TMR_SEC,
TMR_PREP,
TMR_PROF,
TA_PREP,
TA_PRO,
L_ADMINISTRACION,
L_ARQUITECTURA_Y_CONSTRUCCION,
L_CIENCIAS_DE_LA_INFORMACION,
L_CIENCIAS_SOCIALES_Y_ESTUDIOS_DEL_COMPORTAMIENTO,
L_DERECHO,
L_HUMANIDADES,
L_INGENIERIA_INDUSTRIAL__MECANICA_Y_ELECTRICA,
L_MANUFACTURAS_Y_PROCESOS,
L_NEGOCIOS,
L_SALUD,
C_ADMINISTRACION,
C_ARQUITECTURA_Y_CONSTRUCCION,
C_CIENCIAS_DE_LA_INFORMACION,
C_CIENCIAS_SOCIALES_Y_ESTUDIOS_DEL_COMPORTAMIENTO,
C_DERECHO,
C_HUMANIDADES,
C_INGENIERIA_INDUSTRIAL__MECANICA_Y_ELECTRICA,
C_MANUFACTURAS_Y_PROCESOS,
C_NEGOCIOS,
C_SALUD,
HH_COMP_GDE,
HH_GENERAL,
SHR_PUB,
SHR_GDE,
SHR_MAYOR,
TEND_SHR_PUB
FROM
(SELECT
a.AGEB_FOLIO,
a.LLAVEGEO,
sum(cast(POBTOT as FLOAT)) as POBLACION
FROM bt_censoageb a
GROUP BY a.AGEB_FOLIO, a.LLAVEGEO)sq1
LEFT JOIN (SELECT AGEB_FOLIO, ZONA, ZONA_TOPAZ_ID,
ZONA_AGRUPADA, MACRO_ZONA
FROM dim_zonastopaz)b on b.AGEB_FOLIO = sq1.AGEB_FOLIO
LEFT JOIN (SELECT LLAVEGEO, CLUSTER_ID, CLUSTER
FROM m_clusters)c on c.LLAVEGEO = sq1.LLAVEGEO
LEFT JOIN (SELECT AGEB_FOLIO,
CASE WHEN CERCANIA = 0 THEN 0 ELSE 1/CERCANIA END as CERCANIA,
CENTRALIDAD_PODER,
CASE WHEN CERCANIA_PROM = 0 THEN 0 ELSE 1/CERCANIA_PROM END as CERCANIA_PROM,
CASE WHEN CERCANIA_REL = 0 THEN 0 ELSE 1/CERCANIA_REL END as CERCANIA_REL,
N2, N3, CLUSTER_AGEB,
1-ES_SOLO as ES_SOLO
FROM m_conectividad)d on d.AGEB_FOLIO = sq1.AGEB_FOLIO
LEFT JOIN (SELECT AGEB_FOLIO, AM_GRAPROES, AM_PERSXHOGAR, AM_AUTOS, AM_EDOCIVIL
FROM m_alumnomodelo)e on e.AGEB_FOLIO = sq1.AGEB_FOLIO
LEFT JOIN (SELECT AGEB_FOLIO,
TASA_EST*-1 as TASA_EST,
CRIMENES*-1 as CRIMENES,
1/TENDENCIA as TENDENCIA
FROM m_crimen)f on f.AGEB_FOLIO = sq1.AGEB_FOLIO
LEFT JOIN (SELECT AGEB_FOLIO,
COB_NET_PRIM, COB_NET_SEC,
CASE WHEN COB_NET_PREP=0 THEN 0 ELSE 1/COB_NET_PREP END as COB_NET_PREP,
CASE WHEN COB_NET_PROF=0 THEN 0 ELSE 1/COB_NET_PROF END as COB_NET_PROF,
CASE WHEN REZ_TOTAL_ADULTO=0 THEN 0 ELSE 1/REZ_TOTAL_ADULTO END as REZ_TOTAL_ADULTO,
CASE WHEN REZ_PREPA_ADULTO=0 THEN 0 ELSE 1/REZ_PREPA_ADULTO END as REZ_PREPA_ADULTO,
CASE WHEN ESCOLARIDAD=0 THEN 0 ELSE 1/ESCOLARIDAD END as ESCOLARIDAD,
PEA, CATOLICOS, CENS_PERSXHOGAR,
CENS_AUTO, CENS_PC,
EDAD_EDU, EDAD_EDU_PROF, EDAD_EDUF_PREPA, EDAD_EDUF_PROF,
P_EDAD_EDU, P_EDAD_EDU_PROF, P_EDAD_EDUF_PREPA, P_EDAD_EDUF_PROF,
CASE WHEN HACINAMIENTO=0 THEN 0 ELSE 1/HACINAMIENTO END as HACINAMIENTO,
CASE WHEN DENSIDAD=0 THEN 0 ELSE 1/DENSIDAD END as DENSIDAD,
GEN_REZ_PREPA, GEN_PART_PEA, MIGRACION,
C_TEORICO, C_PROM
FROM m_demografia)g on g.AGEB_FOLIO = sq1.AGEB_FOLIO
LEFT JOIN (SELECT LLAVEGEO, HH_UE_2014,
CASE WHEN LQ_EMP_2014_11 = 0 THEN 0 ELSE 1/LQ_EMP_2014_11 END as LQ_EMP_2014_11,
CASE WHEN LQ_EMP_2014_21 = 0 THEN 0 ELSE 1/LQ_EMP_2014_21 END as LQ_EMP_2014_21,
CASE WHEN LQ_EMP_2014_22 = 0 THEN 0 ELSE 1/LQ_EMP_2014_22 END as LQ_EMP_2014_22,
LQ_EMP_2014_23, LQ_EMP_2014_43, LQ_EMP_2014_46,
LQ_EMP_2014_51, LQ_EMP_2014_52, LQ_EMP_2014_53, LQ_EMP_2014_54,
LQ_EMP_2014_55, LQ_EMP_2014_56, LQ_EMP_2014_61, LQ_EMP_2014_62,
LQ_EMP_2014_71, LQ_EMP_2014_72, LQ_EMP_2014_81, LQ_EMP_2014_SC,
EST_MODELO
FROM m_empleadores)h on h.LLAVEGEO = sq1.LLAVEGEO
LEFT JOIN (SELECT LLAVEGEO,
LQ_EMP_2014_OTROS, LQ_ING_2014_OTROS,
CASE WHEN LQ_EMP_2014_PRIMARIA = 0 THEN 0 ELSE 1/LQ_EMP_2014_PRIMARIA END as LQ_EMP_2014_PRIMARIA,
CASE WHEN LQ_ING_2014_PRIMARIA = 0 THEN 0 ELSE 1/LQ_ING_2014_PRIMARIA END as LQ_ING_2014_PRIMARIA,
LQ_EMP_2014_SECUNDARIA, LQ_ING_2014_SECUNDARIA,
LQ_EMP_2014_TERCIARIA, LQ_ING_2014_TERCIARIA
FROM dbo.m_lq)i on i.LLAVEGEO = sq1.LLAVEGEO
LEFT JOIN (SELECT LLAVEGEO, TMR_SEC, TMR_PREP, TMR_PROF,
TA_PREP, TA_PRO,
L_ADMINISTRACION, L_ARQUITECTURA_Y_CONSTRUCCION,
L_CIENCIAS_DE_LA_INFORMACION, L_CIENCIAS_SOCIALES_Y_ESTUDIOS_DEL_COMPORTAMIENTO,
L_DERECHO, L_HUMANIDADES, L_INGENIERIA_INDUSTRIAL__MECANICA_Y_ELECTRICA,
L_MANUFACTURAS_Y_PROCESOS, L_NEGOCIOS, L_SALUD, C_ADMINISTRACION,
C_ARQUITECTURA_Y_CONSTRUCCION, C_CIENCIAS_DE_LA_INFORMACION,
C_CIENCIAS_SOCIALES_Y_ESTUDIOS_DEL_COMPORTAMIENTO, C_DERECHO,
C_HUMANIDADES, C_INGENIERIA_INDUSTRIAL__MECANICA_Y_ELECTRICA,
C_MANUFACTURAS_Y_PROCESOS, C_NEGOCIOS, C_SALUD
FROM dbo.m_oferta)j on j.LLAVEGEO = sq1.LLAVEGEO
LEFT JOIN (SELECT
ESTADO_ID+'-'+MUNICIPIO_ID AS LLAVEGEO,
ESTADO_R,
MUNICIPIO FROM dim_geografia)k on k.LLAVEGEO = sq1.LLAVEGEO
LEFT JOIN (SELECT LLAVEGEO,
CASE WHEN HH_COMP_GDE=0 THEN 0 ELSE 1/HH_COMP_GDE END as HH_COMP_GDE,
CASE WHEN HH_GENERAL=0 THEN 0 ELSE 1/HH_GENERAL END as HH_GENERAL,
CASE WHEN SHR_PUB=0 THEN 0 ELSE 1/SHR_PUB END as SHR_PUB,
CASE WHEN SHR_GDE=0 THEN 0 ELSE 1/SHR_GDE END as SHR_GDE,
CASE WHEN SHR_MAYOR=0 THEN 0 ELSE 1/SHR_MAYOR END as SHR_MAYOR,
CASE WHEN TEND_SHR_PUB=0 THEN 0 ELSE 1/TEND_SHR_PUB END as TEND_SHR_PUB
FROM m_competidores)l on l.LLAVEGEO = sq1.LLAVEGEO",
if(pruebaMty){" WHERE SUBSTRING(sq1.LLAVEGEO, 1, 2) = '19'"}else{})
}
d <- RODBC::sqlQuery(channel = con, query = q)
d
}
R Package Documentation
Browse R Packages
We want your feedback!
Note that we can't provide technical support on individual packages. You should contact the package authors for that.
Embedding an R snippet on your website
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.