-- 01a filas de la población §2 (pre-registro §2) CREATE TEMP TABLE pobf AS SELECT CASE WHEN tabla_origen='resultados_licitaciones' THEN 'propio' ELSE 'agregacion' END feed, coalesce(lote_id, licitacion_id) lote_u, * FROM u WHERE source='PLACSP' AND vigente AND es_contrato AND NOT es_menor AND tabla_origen IN ('resultados_licitaciones','resultados_agregacion') AND procedimiento IN ('Abierto','Restringido','Negociado_publicidad','Dialogo_competitivo','Asociacion_innovacion') AND coalesce(rol_am,'') NOT IN ('basado','basado_sda') AND coalesce(tramitacion,'') <> 'emergencia' AND anio_ref BETWEEN 2012 AND 2026; --; -- 01b lotes (aclaración A.2): agregados + primer valor no nulo por id_canonical CREATE TEMP TABLE lot0 AS SELECT feed, licitacion_id, lote_u, max(num_ofertas) num_ofertas, bool_or(unidad='desierto') desierto, bool_or(unidad='adjudicacion') adjudicado, max(anio_ref) anio_ref, min(fecha_adj) fecha_adj, arg_min(fecha_pub, id_canonical) fecha_pub, arg_min(fecha_fin_ofertas, id_canonical) ffo, arg_min(procedimiento_native, id_canonical) proc_nat, arg_min(tramitacion, id_canonical) tram, arg_min(tipo_contrato, id_canonical) tipo_contrato, arg_min(cpv_division, id_canonical) cpv_division, arg_min(cpv_code, id_canonical) cpv_code, arg_min(imp_lic_eur, id_canonical) imp_lic_eur, arg_min(imp_lic_eur_expediente, id_canonical) imp_lic_eur_expediente, arg_min(valor_estimado_eur_expediente, id_canonical) valor_estimado_eur_expediente, arg_min(duracion_meses, id_canonical) duracion_meses, arg_min(sara, id_canonical) sara, arg_min(financiacion_ue, id_canonical) financiacion_ue, arg_min(organo_nif, id_canonical) organo_nif, arg_min(organo_nombre, id_canonical) organo_nombre, arg_min(dir3, id_canonical) dir3, arg_min(ccaa, id_canonical) ccaa, arg_min(url_origen, id_canonical) url_origen, count(*) filas FROM pobf GROUP BY 1,2,3; --; -- 01c derivadas del lote (A.3, A.4, C.13) y órgano casado CREATE TEMP TABLE lotes AS SELECT l.*, (l.fecha_pub IS NOT NULL AND (l.fecha_adj IS NULL OR l.fecha_pub <= l.fecha_adj)) fpub_ok, CASE WHEN l.fecha_pub IS NOT NULL AND (l.fecha_adj IS NULL OR l.fecha_pub <= l.fecha_adj) AND year(l.fecha_pub) BETWEEN 2012 AND 2026 THEN year(l.fecha_pub)::INTEGER ELSE l.anio_ref::INTEGER END anio_anuncio, CASE WHEN l.fecha_pub IS NOT NULL AND l.ffo IS NOT NULL AND l.ffo > l.fecha_pub AND l.ffo - l.fecha_pub <= 180 AND (l.fecha_adj IS NULL OR l.fecha_pub <= l.fecha_adj) THEN l.ffo - l.fecha_pub END plazo_a, CASE WHEN l.ffo - l.fecha_pub > 0 AND l.ffo - l.fecha_pub <= 180 THEN l.ffo - l.fecha_pub END plazo_b, f.organo_clave, t.tipo tipo_organo FROM lot0 l LEFT JOIN read_parquet('/d/unified_20261018.identidad/organos_filas.parquet') f ON f.organo_nif IS NOT DISTINCT FROM l.organo_nif AND f.organo_nombre IS NOT DISTINCT FROM l.organo_nombre AND f.dir3 IS NOT DISTINCT FROM l.dir3 LEFT JOIN read_parquet('/d/unified_20261018.identidad/organos_tipo.parquet') t ON t.organo_clave = f.organo_clave; --; -- 01d comprobaciones: un lote por clave, recuentos SELECT feed, count(*) lotes, count(DISTINCT (licitacion_id, lote_u)) claves, sum((filas>1)::int) multi, count(organo_clave) con_organo, sum((num_ofertas>=1)::int) con_ofertas, sum((num_ofertas=1)::int) oferta_unica, sum(desierto::int) desiertos, sum((adjudicado OR desierto)::int) resueltos, sum((anio_anuncio>anio_ref)::int) anuncio_posterior, sum((NOT fpub_ok)::int) fpub_no_ok, count(plazo_a) plazo_a, count(plazo_b) plazo_b FROM lotes GROUP BY 1 ORDER BY 1; --; -- 01e por año de anuncio (feed propio, con ofertas) SELECT anio_anuncio, count(*) lotes, sum((num_ofertas>=1)::int) con_ofertas, round(avg(CASE WHEN num_ofertas>=1 THEN (num_ofertas=1)::int END),4) tasa, sum((num_ofertas>=1 AND fpub_ok)::int) con_ofertas_fpub_ok FROM lotes WHERE feed='propio' GROUP BY 1 ORDER BY 1; --; -- 01f guardar COPY (SELECT * FROM lotes ORDER BY feed, licitacion_id, lote_u) TO '/w/tmp/lotes_ind.parquet' (FORMAT PARQUET);