-- 05. Frente a P1c (salida 14, 2024, feed propio): la regla de P1c sobre la construcción publicada del 18-oct y el embudo -- hasta la población de documentos_organo_anio, con cada documento en cada paso CREATE OR REPLACE TEMP VIEW up AS SELECT * FROM read_parquet('/pub/unified/source=PLACSP/**/*.parquet', hive_partitioning=true); CREATE OR REPLACE TEMP TABLE dl AS SELECT licitacion_id, list(DISTINCT tipo_documento) tipos FROM doc WHERE vigente GROUP BY 1; CREATE OR REPLACE TEMP TABLE cl AS SELECT licitacion_id, bool_or(tipo = 'juicio_valor') con_jv FROM cri GROUP BY 1; -- P1c: todas las filas no menores con licitacion_id; año = el mínimo de anio_ref de todas; adjudicada = alguna adjudicación CREATE OR REPLACE TEMP TABLE p1c AS SELECT licitacion_id, split_part(split_part(licitacion_id, ':', 2), '/', 1) feed, min(anio_ref) anio, any_value(tipo_contrato) tipo, bool_or(ofertas_anormales) anormales, bool_or(unidad = 'adjudicacion') adjudicada FROM up WHERE es_menor IS NOT TRUE AND licitacion_id IS NOT NULL GROUP BY 1; -- Filas de adjudicación del perfil con identidad CREATE OR REPLACE TEMP TABLE fa AS SELECT u.licitacion_id, u.anio_ref, u.tipo_contrato, u.ofertas_anormales, u.copia_de, u.correccion_imp_adj, u.anulado_en_origen, u.procedimiento, u.procedimiento_native, u.rol_am, o.organo_clave, a.persona_fisica FROM up u LEFT JOIN read_parquet('/pub/identidad/organos_filas.parquet') 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 LEFT JOIN read_parquet('/pub/identidad/adjudicatarios_filas.parquet') 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 WHERE u.es_menor IS NOT TRUE AND u.unidad = 'adjudicacion' AND u.tabla_origen = 'resultados_licitaciones'; CREATE OR REPLACE MACRO paso(f) AS TABLE SELECT licitacion_id, min(anio_ref) anio, any_value(tipo_contrato) tipo, bool_or(ofertas_anormales) anormales FROM fa WHERE f GROUP BY 1; CREATE OR REPLACE TEMP TABLE pasos AS SELECT '0 P1c (14): adjudicadas, año de todas sus filas' paso, licitacion_id, anio, tipo, anormales FROM p1c WHERE adjudicada AND feed = 'licitacionesPerfilContratante' UNION ALL SELECT '1 año y atributos de sus adjudicaciones', licitacion_id, min(anio_ref), any_value(tipo_contrato), bool_or(ofertas_anormales) FROM fa GROUP BY licitacion_id UNION ALL SELECT '2 sin las copias de otra fuente', licitacion_id, min(anio_ref), any_value(tipo_contrato), bool_or(ofertas_anormales) FROM fa WHERE copia_de IS NULL AND correccion_imp_adj IS DISTINCT FROM 'copia_otra_fuente' AND procedimiento IS DISTINCT FROM 'Menor' GROUP BY licitacion_id UNION ALL SELECT '3 sin anulados en origen', licitacion_id, min(anio_ref), any_value(tipo_contrato), bool_or(ofertas_anormales) FROM fa WHERE copia_de IS NULL AND correccion_imp_adj IS DISTINCT FROM 'copia_otra_fuente' AND procedimiento IS DISTINCT FROM 'Menor' AND anulado_en_origen IS NOT TRUE GROUP BY licitacion_id UNION ALL SELECT '4 sin personas físicas', licitacion_id, min(anio_ref), any_value(tipo_contrato), bool_or(ofertas_anormales) FROM fa WHERE copia_de IS NULL AND correccion_imp_adj IS DISTINCT FROM 'copia_otra_fuente' AND procedimiento IS DISTINCT FROM 'Menor' AND anulado_en_origen IS NOT TRUE AND persona_fisica IS NOT TRUE AND organo_clave IS NOT NULL GROUP BY licitacion_id UNION ALL SELECT '5 sin basados en acuerdo marco o SDA (= documentos_organo_anio con 6)', licitacion_id, min(anio_ref), any_value(tipo_contrato), bool_or(ofertas_anormales) FROM fa WHERE copia_de IS NULL AND correccion_imp_adj IS DISTINCT FROM 'copia_otra_fuente' AND procedimiento IS DISTINCT FROM 'Menor' AND anulado_en_origen IS NOT TRUE AND persona_fisica IS NOT TRUE AND organo_clave IS NOT NULL AND coalesce(rol_am, '') NOT IN ('basado', 'basado_sda') AND procedimiento IS DISTINCT FROM 'Acuerdo_marco' GROUP BY licitacion_id UNION ALL SELECT '6 sin normas internas ni «otros» (= documentos_organo_anio)', licitacion_id, min(anio_ref), any_value(tipo_contrato), bool_or(ofertas_anormales) FROM fa WHERE copia_de IS NULL AND correccion_imp_adj IS DISTINCT FROM 'copia_otra_fuente' AND procedimiento IS DISTINCT FROM 'Menor' AND anulado_en_origen IS NOT TRUE AND persona_fisica IS NOT TRUE AND organo_clave IS NOT NULL 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 licitacion_id; -- @ 2024: licitaciones y % con cada documento en cada paso (pasos 1-4: el año, el de las adjudicaciones del paso) SELECT paso, count(*) n, round(100.0 * count(*) FILTER (WHERE list_contains(coalesce(tipos, []), 'pliego_administrativo')) / count(*), 1) pcap, round(100.0 * count(*) FILTER (WHERE list_contains(coalesce(tipos, []), 'pliego_tecnico')) / count(*), 1) ppt, round(100.0 * count(*) FILTER (WHERE list_contains(coalesce(tipos, []), 'memoria_justificativa')) / count(*), 1) memoria, round(100.0 * count(*) FILTER (WHERE list_contains(coalesce(tipos, []), 'aprobacion_expediente')) / count(*), 1) aprobacion, round(100.0 * count(*) FILTER (WHERE list_contains(coalesce(tipos, []), 'acta_mesa')) / count(*), 1) actas, count(*) FILTER (WHERE con_jv) n_jv, round(100.0 * count(*) FILTER (WHERE con_jv AND list_contains(tipos, 'informe_valoracion_juicio_valor')) / nullif(count(*) FILTER (WHERE con_jv), 0), 1) valoracion, count(*) FILTER (WHERE tipo = 'Servicios') n_serv, round(100.0 * count(*) FILTER (WHERE tipo = 'Servicios' AND list_contains(tipos, 'informe_insuficiencia_medios')) / nullif(count(*) FILTER (WHERE tipo = 'Servicios'), 0), 1) insuficiencia, count(*) FILTER (WHERE anormales) n_anormales, round(100.0 * count(*) FILTER (WHERE anormales AND list_contains(tipos, 'informe_anormalidad')) / nullif(count(*) FILTER (WHERE anormales), 0), 1) anormalidad FROM pasos LEFT JOIN dl USING (licitacion_id) LEFT JOIN cl USING (licitacion_id) WHERE anio = 2024 GROUP BY 1 ORDER BY 1; -- @ 2024 en la tabla del módulo (suma de los órganos) SELECT sum(licitaciones) n, round(100.0 * sum(pcap_n) / sum(licitaciones), 1) pcap, round(100.0 * sum(ppt_n) / sum(licitaciones), 1) ppt, round(100.0 * sum(memoria_n) / sum(licitaciones), 1) memoria, round(100.0 * sum(aprobacion_n) / sum(licitaciones), 1) aprobacion, round(100.0 * sum(actas_n) / sum(licitaciones), 1) actas, sum(valoracion_base) n_jv, round(100.0 * sum(valoracion_n) / sum(valoracion_base), 1) valoracion, sum(insuficiencia_base) n_serv, round(100.0 * sum(insuficiencia_n) / sum(insuficiencia_base), 1) insuficiencia, sum(anormalidad_base) n_anormales, round(100.0 * sum(anormalidad_n) / sum(anormalidad_base), 1) anormalidad FROM read_parquet('/w/calc/indicadores_final/documentos_organo_anio.parquet') WHERE anio = 2024; -- @ peso del juicio de valor 2024: la regla de P1c (ámbitos por año de la versión) y por licitación (año de la adjudicación) SELECT 'P1c: ámbitos, año de la versión' regla, count(*) n, round(avg(peso_juicio_valor_ambito), 1) jv_medio, round(100.0 * count(*) FILTER (WHERE COALESCE(peso_juicio_valor_ambito, 0) = 0) / count(*), 1) pct_solo_automaticos FROM (SELECT DISTINCT licitacion_id, lote, fecha_version, peso_juicio_valor_ambito FROM cri WHERE pesos_suman_100 AND n_sin_tipo_ambito = 0) WHERE year(fecha_version) = 2024 UNION ALL SELECT 'módulo: licitaciones, año de la 1.ª adjudicación', sum(criterios_base), round(sum(peso_juicio_valor_medio * con_juicio_valor_n) / sum(con_juicio_valor_n), 1), round(100.0 * sum(solo_automaticos_n) / sum(criterios_base), 1) FROM read_parquet('/w/calc/indicadores_final/documentos_organo_anio.parquet') WHERE anio = 2024; -- @ modificaciones y prórrogas de las licitaciones de 2024 (P1c: por licitación del feed propio; aquí por órgano) SELECT 'P1c (14): filas de las licitaciones de 2024' regla, count(*) filas, count(*) FILTER (WHERE m.tipo = 'prorroga') prorrogas, count(*) FILTER (WHERE m.tipo = 'modificacion') modificaciones FROM modi m JOIN p1c l USING (licitacion_id) WHERE l.anio = 2024 UNION ALL SELECT 'módulo (modificación: también revisión de precios y, sin nota, las que cambian el importe)', NULL, sum(prorrogas_n), sum(modificaciones_n) FROM read_parquet('/w/calc/indicadores_final/documentos_organo_anio.parquet') WHERE anio = 2024;