-- 08. Reproducciones externas (DEFINICIONES_PREVIAS, R). -- IGAE (IAS 2022, módulo XI, págs. 93-95): actuación horizontal IGAE / IGSS / INTERGENDEF sobre los menores (contratos -- finalizados hasta el 1-abr-2021: ejercicio 2020 aprox.). Sus cifras: «indicios de fraccionamiento < 15 % en número y en -- importe en cada ámbito» (IGSS 16,33 % en importe); confirmados en el expediente 35,16 % (IGAE), 27,27 % (IGSS), 12,41 % -- (INTERGENDEF); 1 oferta: 23-45 %; 3 o más: 54,98 % (IGAE), 40 % (IGSS), 56,26 % (INTERGENDEF); «más del 65 %» con -- importes «muy próximos a los límites»; «no se observa concentración». Nuestro análogo: órganos de tipo 'age' (v3.2) en -- la población 'sin', 2019-2021; subconjuntos por NOMBRE del órgano (aproximación rotulada): Defensa y Seguridad Social. -- CyL 2021 (Consejo de Cuentas, contratación menor de la Administración de la Comunidad 2021): 140 de 17.759 (0,80 %) con -- posible fraccionamiento, «conceptos ... adjudicados al mismo tercero», solo con la base de datos del registro público de -- contratos de CyL. Nuestro análogo: órganos cuya entidad tiene administracion = 'Junta de Castilla y León' (identidad de -- P1b), 2021, (i) en la población 'sin' (PLACSP) y (ii) en la fuente CASTILLA_LEON de unified_p1b con las mismas reglas. CREATE OR REPLACE TABLE age_org AS SELECT o.organo_clave, o.nombre, CASE WHEN regexp_matches(lower(strip_accents(o.nombre)), 'defensa|ejercito|armada|guardia real|estado mayor|cuartel general|mando de|intendencia|base aerea|arsenal') THEN 'Defensa (por nombre)' WHEN regexp_matches(lower(strip_accents(o.nombre)), 'seguridad social|tesoreria general|instituto social de la marina|imserso|ingesa|instituto nacional de la seguridad') THEN 'Seguridad Social (por nombre)' ELSE 'resto AGE' END ambito FROM org_id o JOIN otipo t USING (organo_clave) WHERE t.tipo = 'age'; -- @ IGAE · AGE (tipo 'age'), población 'sin', por año: % de contratos y de importe en M1a, M1r y M1e; M6 SELECT x.anio, count(*) menores, count(DISTINCT x.organo_clave) organos, round(100.0 * count(*) FILTER (WHERE x.en_m1a) / count(*), 1) m1a_pct_contratos, round(100.0 * sum(x.base) FILTER (WHERE x.en_m1a) / sum(x.base), 1) m1a_pct_importe, round(100.0 * count(*) FILTER (WHERE x.en_m1r) / count(*), 2) m1r_pct_contratos, round(100.0 * sum(x.base) FILTER (WHERE x.en_m1r) / sum(x.base), 2) m1r_pct_importe, round(100.0 * count(*) FILTER (WHERE x.en_m1e) / count(*), 2) m1e_pct_contratos, round(100.0 * sum(x.base) FILTER (WHERE x.en_m1e) / sum(x.base), 2) m1e_pct_importe, round(100.0 * count(*) FILTER (WHERE x.en_m1e) / nullif(count(*) FILTER (WHERE x.en_m1a), 0), 1) m1e_sobre_m1a, round(100.0 * count(*) FILTER (WHERE x.en_m1r) / nullif(count(*) FILTER (WHERE x.en_m1a), 0), 1) m1r_sobre_m1a FROM m1x x JOIN age_org a USING (organo_clave) WHERE x.var = 'sin' AND x.anio BETWEEN 2019 AND 2021 GROUP BY 1 ORDER BY 1; -- @ IGAE · por ámbito aproximado (por nombre), 2020: M1a, M1e, M1r, una oferta, tres o más, cercanía al umbral SELECT a.ambito, count(*) menores, count(DISTINCT p.organo_clave) organos, round(100.0 * count(*) FILTER (WHERE x.en_m1a) / count(*), 1) m1a_pct_contratos, round(100.0 * sum(p.base) FILTER (WHERE x.en_m1a) / sum(p.base), 1) m1a_pct_importe, round(100.0 * count(*) FILTER (WHERE x.en_m1e) / count(*), 2) m1e_pct_contratos, round(100.0 * count(*) FILTER (WHERE x.en_m1r) / count(*), 2) m1r_pct_contratos, round(100.0 * count(*) FILTER (WHERE p.num_ofertas = 1) / count(p.num_ofertas), 1) una_oferta, round(100.0 * count(*) FILTER (WHERE p.num_ofertas >= 3) / count(p.num_ofertas), 1) tres_o_mas, round(100.0 * count(*) FILTER (WHERE p.base >= 0.5 * p.umbral) / count(*), 1) base_50pct_umbral, round(100.0 * count(*) FILTER (WHERE p.base >= 0.8 * p.umbral) / count(*), 1) base_80pct_umbral, round(100.0 * count(*) FILTER (WHERE p.base >= 0.9 * p.umbral) / count(*), 1) base_90pct_umbral FROM pobv p JOIN m1x x ON x.var = p.var AND x.id_canonical = p.id_canonical JOIN age_org a ON a.organo_clave = p.organo_clave WHERE p.var = 'sin' AND p.anio = 2020 GROUP BY ROLLUP (a.ambito) ORDER BY 1 NULLS LAST; -- @ IGAE · concentración (M4) en la AGE 2020: cuota del primer proveedor, órgano-años elegibles SELECT count(*) elegibles, round(100 * quantile_cont(m4_cuota_top1, 0.5), 1) cuota_p50, round(100 * quantile_cont(m4_cuota_top1, 0.9), 1) cuota_p90, count(*) FILTER (WHERE m4_cuota_top1 > 0.5) cuota_mas_50 FROM m4_oa m JOIN age_org a USING (organo_clave) WHERE m.var = 'sin' AND m.anio = 2020 AND m.m4_elegible; -- ===================== CyL 2021 ===================== CREATE OR REPLACE TABLE jcyl_org AS SELECT o.organo_clave, o.nombre, e.nombre entidad_nombre FROM org_id o JOIN ent_id e USING (entidad_clave) WHERE e.administracion = 'Junta de Castilla y León'; -- @ CyL · (i) PLACSP, población 'sin', órganos de la Junta de CyL, 2021 (y 2020-2022 de contexto) SELECT x.anio, count(*) menores, round(sum(x.base) / 1e6, 1) meur, count(DISTINCT x.organo_clave) organos, count(*) FILTER (WHERE x.en_m1e) m1e_contratos, round(100.0 * count(*) FILTER (WHERE x.en_m1e) / count(*), 2) m1e_pct, count(*) FILTER (WHERE x.en_m1r) m1r_contratos, round(100.0 * count(*) FILTER (WHERE x.en_m1r) / count(*), 2) m1r_pct, count(*) FILTER (WHERE x.en_m1e OR x.en_m1r) m1e_o_m1r, round(100.0 * count(*) FILTER (WHERE x.en_m1e OR x.en_m1r) / count(*), 2) m1e_o_m1r_pct, round(100.0 * count(*) FILTER (WHERE x.en_m1a) / count(*), 1) m1a_pct, count(*) FILTER (WHERE x.en_m1r_nat) m1r_anio_natural, round(100.0 * count(*) FILTER (WHERE x.en_m1r_nat) / count(*), 2) m1r_anio_natural_pct FROM m1x x JOIN jcyl_org j USING (organo_clave) WHERE x.var = 'sin' AND x.anio BETWEEN 2020 AND 2022 GROUP BY 1 ORDER BY 1; -- (ii) Fuente CASTILLA_LEON (registro autonómico) con las reglas de la población común; ventana de M1r con 2020-2022 CREATE OR REPLACE TABLE cyl AS WITH b AS ( SELECT u.id_canonical, u.fecha_adj, u.imp_adj_eur_comparable base, u.iva_base, u.tipo_contrato, u.objeto, u.expediente, u.copia_de, u.correccion_imp_adj, u.es_menor_dudoso, v.cuenta, a.persona_fisica, a.empresa_clave, o.organo_clave, lower(strip_accents(coalesce(u.objeto, ''))) objeto_low FROM u LEFT JOIN vf v ON v.source = u.source AND v.id_canonical = u.id_canonical LEFT JOIN af a ON a.adjudicatario_nif_raw IS NOT DISTINCT FROM u.adjudicatario_nif_raw AND a.adjudicatario_nif IS NOT DISTINCT FROM u.adjudicatario_nif AND a.adjudicatario_nombre IS NOT DISTINCT FROM u.adjudicatario_nombre LEFT JOIN ofi o ON o.organo_nif IS NOT DISTINCT FROM u.organo_nif AND o.organo_nombre IS NOT DISTINCT FROM u.organo_nombre AND o.dir3 IS NOT DISTINCT FROM u.dir3 WHERE u.source = 'CASTILLA_LEON' AND u.es_menor AND u.unidad = 'adjudicacion' AND u.fecha_adj BETWEEN DATE '2020-01-01' AND DATE '2022-12-31') SELECT b.*, year(fecha_adj) anio, CASE WHEN tipo_contrato = 'Obras' THEN 'obras' ELSE 'resto' END clase, CASE WHEN tipo_contrato = 'Obras' THEN 40000 ELSE 15000 END umbral, trim(regexp_replace(regexp_replace(objeto_low, '[^a-z ]', ' ', 'g'), '\s+', ' ', 'g')) objeto_norm, (coalesce(cuenta, TRUE) AND copia_de IS NULL AND correccion_imp_adj IS DISTINCT FROM 'copia_otra_fuente') unica, (j.organo_clave IS NOT NULL) junta FROM b LEFT JOIN jcyl_org j USING (organo_clave); -- @ CyL · (ii) embudo de la fuente CASTILLA_LEON 2021 (todas las filas menores; Junta de CyL) SELECT count(*) FILTER (WHERE anio = 2021) menores_2021, count(*) FILTER (WHERE anio = 2021 AND junta) junta_2021, count(*) FILTER (WHERE anio = 2021 AND junta AND unica) junta_unicos, round(sum(base) FILTER (WHERE anio = 2021 AND junta AND unica) / 1e6, 1) junta_meur_base, count(*) FILTER (WHERE anio = 2021 AND junta AND unica AND base > 0 AND persona_fisica = FALSE AND NOT coalesce(es_menor_dudoso, FALSE) AND empresa_clave IS NOT NULL) junta_poblacion, count(*) FILTER (WHERE anio = 2021 AND NOT junta) no_junta_2021, count(*) FILTER (WHERE anio = 2021 AND organo_clave IS NULL) sin_organo, string_agg(DISTINCT iva_base, ',') iva_base_valores, count(*) FILTER (WHERE anio = 2021 AND junta AND NOT coalesce(cuenta, TRUE)) junta_copias_por_vinculos, count(*) FILTER (WHERE anio = 2021 AND junta AND copia_de IS NOT NULL) junta_copia_de, count(*) FILTER (WHERE anio = 2021 AND junta AND base IS NULL) junta_sin_base_sin_iva FROM cyl; CREATE OR REPLACE TABLE cylp AS SELECT * FROM cyl WHERE junta AND unica AND base > 0 AND persona_fisica = FALSE AND NOT coalesce(es_menor_dudoso, FALSE) AND empresa_clave IS NOT NULL AND organo_clave IS NOT NULL; CREATE OR REPLACE TABLE cyl_m1 AS WITH d AS (SELECT organo_clave, empresa_clave, clase, fecha_adj FROM cylp GROUP BY ALL HAVING count(*) >= 2 AND sum(base::DECIMAL(18,2)) > any_value(umbral)), w AS (SELECT id_canonical, organo_clave, empresa_clave, clase, objeto_norm, fecha_adj, umbral, count(*) OVER v n12, sum(base::DECIMAL(18,2)) OVER v s12 FROM cylp WHERE length(objeto_norm) >= 5 WINDOW v AS (PARTITION BY organo_clave, empresa_clave, clase, objeto_norm ORDER BY fecha_adj RANGE BETWEEN INTERVAL 364 DAYS PRECEDING AND CURRENT ROW)), r AS (SELECT id_canonical, bool_or(n12 >= 2 AND s12 > umbral) OVER (PARTITION BY organo_clave, empresa_clave, clase, objeto_norm ORDER BY fecha_adj RANGE BETWEEN CURRENT ROW AND INTERVAL 364 DAYS FOLLOWING) en_m1r FROM w) SELECT p.id_canonical, p.anio, p.base, (d.organo_clave IS NOT NULL) en_m1e, coalesce(r.en_m1r, FALSE) en_m1r FROM cylp p LEFT JOIN d USING (organo_clave, empresa_clave, clase, fecha_adj) LEFT JOIN r USING (id_canonical); -- @ CyL · (ii) fuente CASTILLA_LEON, Junta de CyL: M1e y M1r por año (las mismas reglas que la población común) SELECT anio, count(*) menores, round(sum(base) / 1e6, 1) meur, count(*) FILTER (WHERE en_m1e) m1e, round(100.0 * count(*) FILTER (WHERE en_m1e) / count(*), 2) m1e_pct, count(*) FILTER (WHERE en_m1r) m1r, round(100.0 * count(*) FILTER (WHERE en_m1r) / count(*), 2) m1r_pct, count(*) FILTER (WHERE en_m1e OR en_m1r) m1e_o_m1r, round(100.0 * count(*) FILTER (WHERE en_m1e OR en_m1r) / count(*), 2) m1e_o_m1r_pct FROM cyl_m1 GROUP BY 1 ORDER BY 1; -- @ CyL · (ii) los 15 órganos de la Junta con más menores en la fuente CASTILLA_LEON 2021 (población), con M1e y M1r SELECT p.organo_clave, any_value(j.nombre) organo, count(*) menores, count(*) FILTER (WHERE m.en_m1e) m1e, count(*) FILTER (WHERE m.en_m1r) m1r FROM cylp p JOIN cyl_m1 m USING (id_canonical) JOIN jcyl_org j USING (organo_clave) WHERE p.anio = 2021 GROUP BY 1 ORDER BY 3 DESC LIMIT 15;