-- 02a lotes y años del descriptor (pre-registro §3; aclaraciones A.5, B.6, B.7) CREATE TEMP TABLE L AS SELECT feed, licitacion_id, lote_u, organo_clave, coalesce(cpv_division,'??') cpv_div, proc_nat, anio_ref, num_ofertas, desierto, adjudicado FROM read_parquet('/w/tmp/lotes_ind.parquet'); --; -- 02b años 2016-2026 CREATE TEMP TABLE anios AS SELECT range::INTEGER anio FROM range(2016, 2027); --; -- 02c descriptor órgano × división CPV × año (ventana anio_ref en [anio-3, anio-1], ambos feeds) CREATE TEMP TABLE parecidas AS SELECT organo_clave, cpv_div, anio, anio_desde, anio_hasta, lotes, con_ofertas, oferta_unica, desiertos, CASE WHEN con_ofertas > 0 THEN oferta_unica::DOUBLE / con_ofertas END tasa_oferta_unica, lotes_propio, con_ofertas_propio, oferta_unica_propio, desiertos_propio, CASE WHEN con_ofertas_propio > 0 THEN oferta_unica_propio::DOUBLE / con_ofertas_propio END tasa_oferta_unica_propio, con_ofertas >= 10 muestra_tasa FROM ( SELECT l.organo_clave, l.cpv_div, a.anio, (a.anio-3)::INTEGER anio_desde, (a.anio-1)::INTEGER anio_hasta, count(*) FILTER (WHERE l.adjudicado OR l.desierto) lotes, count(*) FILTER (WHERE l.num_ofertas >= 1) con_ofertas, count(*) FILTER (WHERE l.num_ofertas = 1) oferta_unica, count(*) FILTER (WHERE l.desierto) desiertos, count(*) FILTER (WHERE l.feed='propio' AND (l.adjudicado OR l.desierto)) lotes_propio, count(*) FILTER (WHERE l.feed='propio' AND l.num_ofertas >= 1) con_ofertas_propio, count(*) FILTER (WHERE l.feed='propio' AND l.num_ofertas = 1) oferta_unica_propio, count(*) FILTER (WHERE l.feed='propio' AND l.desierto) desiertos_propio FROM anios a JOIN L l ON l.anio_ref BETWEEN a.anio-3 AND a.anio-1 WHERE l.organo_clave IS NOT NULL GROUP BY 1,2,3,4,5 ); --; -- 02d mercado de respaldo: división CPV × procedimiento nativo (feed propio), misma ventana CREATE TEMP TABLE mercado AS SELECT cpv_div, proc_nat, anio, con_ofertas, oferta_unica, CASE WHEN con_ofertas > 0 THEN oferta_unica::DOUBLE / con_ofertas END tasa_oferta_unica, con_ofertas >= 10 muestra_tasa FROM ( SELECT l.cpv_div, l.proc_nat, a.anio, count(*) FILTER (WHERE l.num_ofertas >= 1) con_ofertas, count(*) FILTER (WHERE l.num_ofertas = 1) oferta_unica FROM anios a JOIN L l ON l.anio_ref BETWEEN a.anio-3 AND a.anio-1 WHERE l.feed = 'propio' GROUP BY 1,2,3 ); --; -- 02e resumen del descriptor por año SELECT anio, count(*) celdas, sum(muestra_tasa::int) celdas_muestra, sum((con_ofertas=0)::int) celdas_sin_ofertas, sum(lotes) lotes, sum(con_ofertas) con_ofertas, sum(oferta_unica) oferta_unica, sum(desiertos) desiertos, sum(con_ofertas_propio) con_ofertas_propio, round(sum(oferta_unica)/sum(con_ofertas),4) tasa_agregada, count(DISTINCT organo_clave) organos FROM parecidas GROUP BY 1 ORDER BY 1; --; -- 02f resumen del mercado por año SELECT anio, count(*) celdas, sum(muestra_tasa::int) celdas_muestra, sum(con_ofertas) con_ofertas, sum(oferta_unica) oferta_unica FROM mercado GROUP BY 1 ORDER BY 1; --; -- 02g comprobación: suma de celdas = lotes en ventana (sin duplicar) SELECT a.anio, count(*) lotes_ventana, sum((l.num_ofertas>=1)::int) con_ofertas FROM anios a JOIN L l ON l.anio_ref BETWEEN a.anio-3 AND a.anio-1 WHERE l.organo_clave IS NOT NULL GROUP BY 1 ORDER BY 1; --; -- 02h lotes sin resolver en la población (ni adjudicados ni desiertos) SELECT feed, organo_clave IS NOT NULL con_organo, anio_ref, num_ofertas, count(*) n FROM L WHERE NOT (adjudicado OR desierto) GROUP BY ALL; --; -- 02i guardar descriptor COPY (SELECT * FROM parecidas ORDER BY anio, organo_clave, cpv_div) TO '/w/salidas/parecidas_ind.parquet' (FORMAT PARQUET); --; -- 02j guardar mercado COPY (SELECT * FROM mercado ORDER BY anio, cpv_div, proc_nat) TO '/w/salidas/mercado_ind.parquet' (FORMAT PARQUET); --; -- 02k esquema guardado DESCRIBE SELECT * FROM read_parquet('/w/salidas/parecidas_ind.parquet'); --; -- 02l esquema mercado DESCRIBE SELECT * FROM read_parquet('/w/salidas/mercado_ind.parquet');