-- 15. Reglas de nombre de P4b encima de las del satélite (solo documentos que el satélite deja en «otro» o sin tipo; y las -- actas de replanteo o recepción que el satélite toma por actas de la mesa): qué nombres caen en cada una (2019-2025, -- población de documentos_organo_anio) y cuántas licitaciones ganan o pierden cada documento CREATE OR REPLACE TEMP TABLE l AS SELECT licitacion_id, min(anio_ref) anio FROM read_parquet('/pub/unified/source=PLACSP/**/*.parquet', hive_partitioning=true) WHERE unidad = 'adjudicacion' AND tabla_origen = 'resultados_licitaciones' AND es_menor IS NOT TRUE AND coalesce(rol_am, '') NOT IN ('basado', 'basado_sda') AND procedimiento IS DISTINCT FROM 'Acuerdo_marco' AND NOT (procedimiento = 'Otro' AND procedimiento_native IS DISTINCT FROM 'Concurso de proyectos') GROUP BY 1 HAVING min(anio_ref) BETWEEN 2019 AND 2025; CREATE OR REPLACE TEMP TABLE d AS SELECT d.licitacion_id, l.anio, d.tipo_documento t, lower(strip_accents(d.nombre)) n FROM doc d JOIN l USING (licitacion_id) WHERE d.vigente; CREATE OR REPLACE TEMP TABLE r AS SELECT *, CASE WHEN coalesce(t, 'otro') = 'otro' AND (regexp_matches(n, 'memoria ?(justificativa|xustificativa|justifikatzaile)|memoria (de |del )?(inicio|necesidad|necesidades)( |\.|\(|$)|informe justificativo del contrato') OR regexp_matches(n, '^memoria( *\(|\.[a-z]{3,4}$|$)|(^|[^a-z])memoria\.[a-z]{3,4}$|[0-9]{6}(mem|mi|ijcp)_')) THEN 'memoria_justificativa' WHEN coalesce(t, 'otro') = 'otro' AND regexp_matches(n, '^resolucion (de )?aprobacion( del expediente)?(\.[a-z]{3,4})?$|[0-9]{6}rapge_') THEN 'aprobacion_expediente' WHEN coalesce(t, 'otro') = 'otro' AND regexp_matches(n, 'certificado (de |del )?(la )?(sesion de la )?mesa|[0-9]{6}amcp?_') THEN 'acta_mesa' WHEN t = 'acta_mesa' AND regexp_matches(n, 'acta (de |del )?(replanteo|recepcion)') THEN 'ejecucion' END nuevo FROM d; -- @ nombres que cambian de tipo, los 60 más frecuentes SELECT coalesce(t, '∅') antes, nuevo, regexp_replace(n, '[0-9]{3,}', '#', 'g') nombre, count(*) n FROM r WHERE nuevo IS NOT NULL GROUP BY 1, 2, 3 ORDER BY 4 DESC LIMIT 60; -- @ por documento y año: licitaciones con el documento, antes y con las reglas WITH x AS (SELECT licitacion_id, anio, bool_or(t = 'memoria_justificativa') m0, bool_or(coalesce(nuevo, t) = 'memoria_justificativa') m1, bool_or(t = 'aprobacion_expediente') a0, bool_or(coalesce(nuevo, t) = 'aprobacion_expediente') a1, bool_or(t = 'acta_mesa') c0, bool_or(coalesce(nuevo, t) = 'acta_mesa') c1 FROM r GROUP BY 1, 2) SELECT anio, (SELECT count(*) FROM l WHERE l.anio = x.anio) licitaciones, count(*) FILTER (WHERE m0) memoria, count(*) FILTER (WHERE m1 AND NOT m0) memoria_mas, count(*) FILTER (WHERE a0) aprobacion, count(*) FILTER (WHERE a1 AND NOT a0) aprobacion_mas, count(*) FILTER (WHERE c0) actas, count(*) FILTER (WHERE c1 AND NOT c0) actas_mas, count(*) FILTER (WHERE c0 AND NOT c1) actas_menos FROM x GROUP BY 1 ORDER BY 1;