-- 04a reserva: lotes del feed propio con anio_anuncio 2014-2026 (aclaración C.11) CREATE TEMP TABLE R AS SELECT * FROM read_parquet('/w/tmp/lotes_ind.parquet') WHERE feed='propio' AND anio_anuncio BETWEEN 2014 AND 2026; --; -- 04b historial órgano × anio_ref (solo lotes con ofertas) CREATE TEMP TABLE oy AS SELECT organo_clave, anio_ref, count(*) n, sum((num_ofertas=1)::int) k FROM R WHERE num_ofertas>=1 AND organo_clave IS NOT NULL GROUP BY 1,2; --; -- 04c historial mercado (división CPV, NULL casa con NULL) × proc_nat × anio_ref CREATE TEMP TABLE my AS SELECT cpv_division, proc_nat, anio_ref, count(*) n, sum((num_ofertas=1)::int) k FROM R WHERE num_ofertas>=1 GROUP BY 1,2,3; --; -- 04d historial órgano × división CPV × anio_ref CREATE TEMP TABLE ocy AS SELECT organo_clave, cpv_division, anio_ref, count(*) n, sum((num_ofertas=1)::int) k FROM R WHERE num_ofertas>=1 AND organo_clave IS NOT NULL GROUP BY 1,2,3; --; -- 04e NSP del órgano: filas (no lotes) adjudicadas del feed propio, no menores, anio_ref 2012-2026 CREATE TEMP TABLE nsp AS SELECT f.organo_clave, x.anio_ref, count(*) n_adj, sum((x.procedimiento='Negociado_sin_publicidad')::int) n_nsp FROM u x JOIN read_parquet('/d/unified_20261018.identidad/organos_filas.parquet') f ON f.organo_nif IS NOT DISTINCT FROM x.organo_nif AND f.organo_nombre IS NOT DISTINCT FROM x.organo_nombre AND f.dir3 IS NOT DISTINCT FROM x.dir3 WHERE x.source='PLACSP' AND x.vigente AND x.es_contrato AND NOT x.es_menor AND x.tabla_origen='resultados_licitaciones' AND x.unidad='adjudicacion' AND x.anio_ref BETWEEN 2012 AND 2026 AND f.organo_clave IS NOT NULL GROUP BY 1,2; --; -- 04f criterios por ámbito (receta Fable 02b) y pliegos por licitación (02t) CREATE TEMP TABLE crit AS SELECT licitacion_id, count(*) n_crit, any_value(pesos_suman_100) suman100, any_value(n_sin_tipo_ambito) n_sin_tipo, any_value(peso_juicio_valor_ambito) peso_jv, any_value(peso_suma_ambito) peso_suma, sum(CASE WHEN subtipo_criterio_code='1' THEN peso END) peso_precio FROM read_parquet('/d/unified_20261018.criterios/criterios.parquet') WHERE ambito='expediente' GROUP BY 1; --; -- 04g pliegos CREATE TEMP TABLE docs AS SELECT licitacion_id, bool_or(elemento='legal') pcap, bool_or(elemento='tecnico') ppt FROM read_parquet('/d/unified_20261018.documentos/documentos.parquet') GROUP BY 1; --; -- 04h lotes del conjunto (2016-2025, con ofertas) y claves de historial CREATE TEMP TABLE D AS SELECT * FROM R WHERE anio_anuncio BETWEEN 2016 AND 2025 AND num_ofertas >= 1; --; -- 04i historial agregado por (clave, año de anuncio): solo anio_ref anteriores al anuncio CREATE TEMP TABLE h_org AS SELECT a.organo_clave, a.anio_anuncio, sum(oy.n) org_prev_n, sum(oy.k) org_prev_k FROM (SELECT DISTINCT organo_clave, anio_anuncio FROM D) a JOIN oy ON oy.organo_clave=a.organo_clave AND oy.anio_ref < a.anio_anuncio GROUP BY 1,2; --; -- 04j mercado 3 años CREATE TEMP TABLE h_mkt AS SELECT a.cpv_division, a.proc_nat, a.anio_anuncio, sum(my.n) mkt_prev_n, sum(my.k) mkt_prev_k FROM (SELECT DISTINCT cpv_division, proc_nat, anio_anuncio FROM D) a JOIN my ON my.cpv_division IS NOT DISTINCT FROM a.cpv_division AND my.proc_nat=a.proc_nat AND my.anio_ref BETWEEN a.anio_anuncio-3 AND a.anio_anuncio-1 GROUP BY 1,2,3; --; -- 04k órgano × CPV previo CREATE TEMP TABLE h_ocy AS SELECT a.organo_clave, a.cpv_division, a.anio_anuncio, sum(ocy.n) orgcpv_prev_n, sum(ocy.k) orgcpv_prev_k FROM (SELECT DISTINCT organo_clave, cpv_division, anio_anuncio FROM D) a JOIN ocy ON ocy.organo_clave=a.organo_clave AND ocy.cpv_division IS NOT DISTINCT FROM a.cpv_division AND ocy.anio_ref < a.anio_anuncio GROUP BY 1,2,3; --; -- 04l NSP 3 años CREATE TEMP TABLE h_nsp AS SELECT a.organo_clave, a.anio_anuncio, sum(nsp.n_adj) org_nsp_adj, sum(nsp.n_nsp) org_nsp_n FROM (SELECT DISTINCT organo_clave, anio_anuncio FROM D) a JOIN nsp ON nsp.organo_clave=a.organo_clave AND nsp.anio_ref BETWEEN a.anio_anuncio-3 AND a.anio_anuncio-1 GROUP BY 1,2; --; -- 04m conjunto del modelo CREATE TEMP TABLE ds AS SELECT l.licitacion_id, l.lote_u, l.anio_anuncio, l.anio_ref, l.num_ofertas, (l.num_ofertas=1)::INTEGER y, coalesce(l.proc_nat,'NULO§') proc_nat, coalesce(l.tram,'NULO§') tram, coalesce(l.tipo_contrato,'NULO§') tipo_contrato, coalesce(l.cpv_division,'NULO§') cpv_div, coalesce(l.tipo_organo,'NULO§') tipo_organo, coalesce(l.ccaa,'NULO§') ccaa, coalesce(month(l.fecha_pub)::VARCHAR,'NULO§') mes_pub, l.sara::INTEGER sara, l.financiacion_ue::INTEGER ue, l.duracion_meses dur, l.plazo_a, l.plazo_b, l.fpub_ok::INTEGER fpub_ok, coalesce(l.imp_lic_eur, l.imp_lic_eur_expediente) pbl, l.valor_estimado_eur_expediente ve_exp, (l.lote_u<>l.licitacion_id)::INTEGER es_lote, l.organo_clave, nl.n_lotes, c.n_crit, c.suman100::INTEGER suman100, c.n_sin_tipo, c.peso_jv, c.peso_suma, c.peso_precio, d.pcap::INTEGER pcap, d.ppt::INTEGER ppt, ho.org_prev_n, ho.org_prev_k, ol.n org_last_n, ol.k org_last_k, ml.n mkt_last_n, ml.k mkt_last_k, hm.mkt_prev_n, hm.mkt_prev_k, hc.orgcpv_prev_n, hc.orgcpv_prev_k, hn.org_nsp_adj, hn.org_nsp_n FROM D l LEFT JOIN (SELECT licitacion_id, count(*) n_lotes FROM R GROUP BY 1) nl ON nl.licitacion_id=l.licitacion_id LEFT JOIN crit c ON c.licitacion_id=l.licitacion_id LEFT JOIN docs d ON d.licitacion_id=l.licitacion_id LEFT JOIN h_org ho ON ho.organo_clave=l.organo_clave AND ho.anio_anuncio=l.anio_anuncio LEFT JOIN oy ol ON ol.organo_clave=l.organo_clave AND ol.anio_ref=l.anio_anuncio-1 LEFT JOIN my ml ON ml.cpv_division IS NOT DISTINCT FROM l.cpv_division AND ml.proc_nat=l.proc_nat AND ml.anio_ref=l.anio_anuncio-1 LEFT JOIN h_mkt hm ON hm.cpv_division IS NOT DISTINCT FROM l.cpv_division AND hm.proc_nat=l.proc_nat AND hm.anio_anuncio=l.anio_anuncio LEFT JOIN h_ocy hc ON hc.organo_clave=l.organo_clave AND hc.cpv_division IS NOT DISTINCT FROM l.cpv_division AND hc.anio_anuncio=l.anio_anuncio LEFT JOIN h_nsp hn ON hn.organo_clave=l.organo_clave AND hn.anio_anuncio=l.anio_anuncio; --; -- 04n resumen SELECT anio_anuncio, count(*) n, count(DISTINCT (licitacion_id, lote_u)) claves, round(avg(y),4) tasa, sum(fpub_ok) fpub_ok, round(100.0*count(org_prev_n)/count(*),1) c_org, round(100.0*count(org_last_n)/count(*),1) c_org_last, round(100.0*count(mkt_last_n)/count(*),1) c_mkt, round(100.0*count(orgcpv_prev_n)/count(*),1) c_orgcpv, round(100.0*count(n_crit)/count(*),1) c_crit, round(100.0*count(plazo_a)/count(*),1) c_plazo_a, round(100.0*count(plazo_b)/count(*),1) c_plazo_b, round(100.0*count(org_nsp_adj)/count(*),1) c_nsp, round(100.0*avg(pcap),1) pcap FROM ds GROUP BY 1 ORDER BY 1; --; -- 04o exportar COPY (SELECT * FROM ds ORDER BY anio_anuncio, licitacion_id, lote_u) TO '/w/tmp/dataset_ind.csv' (HEADER, DELIMITER ',');