-- @universo: filas de la PLACSP (resultados_licitaciones) candidatas a U1/U2 (DEFINICIONES_PREVIAS §2) CREATE OR REPLACE TABLE uf AS WITH b AS ( SELECT p.*, coalesce(fecha_adj, fecha_formalizacion) AS ref, max(coalesce(valor_estimado_eur_expediente, imp_lic_eur_expediente)) OVER (PARTITION BY licitacion_id) AS ve, coalesce(lote_id, licitacion_id) AS lote, CASE WHEN procedimiento_native IN ('Abierto','Restringido','Negociado con publicidad','Licitación con negociación','Diálogo competitivo','Asociación para la innovación') THEN 'con_publicidad' WHEN procedimiento_native = 'Negociado sin publicidad' THEN 'nsp' END AS estrato, (tipo_contrato='Servicios' AND (cpv_code LIKE '55%' OR cpv_code LIKE '75%' OR cpv_code LIKE '7962%' OR cpv_code LIKE '797%' OR cpv_code LIKE '7995%' OR cpv_code LIKE '80%' OR cpv_code LIKE '85%' OR cpv_code LIKE '92%' OR cpv_code LIKE '98%')) AS anexo4 FROM p0 p WHERE tabla_origen='resultados_licitaciones' AND NOT coalesce(anulado_en_origen, FALSE) AND tipo_contrato IN ('Obras','Suministros','Servicios','Concesion_obras','Concesion_servicios') AND coalesce(rol_am,'-') NOT IN ('basado','basado_sda','sda') ), c AS ( SELECT *, CASE WHEN year(fecha_pub) BETWEEN 2018 AND 2019 THEN 0 WHEN year(fecha_pub) BETWEEN 2020 AND 2021 THEN 1 WHEN year(fecha_pub) BETWEEN 2022 AND 2023 THEN 2 WHEN year(fecha_pub) BETWEEN 2024 AND 2025 THEN 3 END AS per FROM b WHERE estrato IS NOT NULL AND ref BETWEEN DATE '2018-01-01' AND DATE '2025-12-31' AND fecha_pub >= DATE '2018-01-01' ), d AS ( SELECT *, CASE WHEN tipo_contrato IN ('Obras','Concesion_obras','Concesion_servicios') THEN [5548000,5350000,5382000,5538000][per+1] WHEN anexo4 THEN 750000 ELSE [221000,214000,215000,221000][per+1] END AS thr_gen, CASE WHEN tipo_contrato IN ('Obras','Concesion_obras','Concesion_servicios') THEN [5548000,5350000,5382000,5538000][per+1] WHEN anexo4 THEN 750000 WHEN organo_tipo='age' THEN [144000,139000,140000,143000][per+1] ELSE [221000,214000,215000,221000][per+1] END AS thr_u1 FROM c WHERE per IS NOT NULL ) SELECT *, (year(ref) BETWEEN 2023 AND 2025 AND sara IS TRUE AND ve >= thr_u1) AS in_u1, (sara IS DISTINCT FROM FALSE AND ve >= thr_gen) AS in_u2, (sara IS FALSE AND ve >= thr_gen) AS in_u2x, coalesce(imp_adj_eur_sin_iva, imp_adj_eur) AS imp_sin, coalesce(imp_adj_eur_con_iva, coalesce(imp_adj_eur_sin_iva, imp_adj_eur)*1.21) AS imp_con, CASE WHEN regexp_full_match(coalesce(entidad_clave,''), '[A-Z][0-9]{7}[0-9A-Z]') THEN entidad_clave END AS ent, CASE WHEN length(regexp_replace(upper(coalesce(expediente,'')), '[^A-Z0-9]', '', 'g'))>=5 AND regexp_matches(expediente,'[0-9]') THEN regexp_replace(upper(expediente), '[^A-Z0-9]', '', 'g') END AS exp_n, CASE WHEN adjudicatario_nif_valid THEN adjudicatario_nif END AS nif_adj, CASE WHEN length(adj_nombre_norm)>=8 THEN adj_nombre_norm END AS nom_adj FROM d WHERE (year(ref) BETWEEN 2023 AND 2025 AND sara IS TRUE AND ve >= thr_u1) OR ve >= thr_gen ; --; -- @recuento universo (unidades = licitación-lote) SELECT year(ref) anio, estrato, count(DISTINCT CASE WHEN in_u1 THEN licitacion_id||'#'||lote END) u1_unid, count(DISTINCT CASE WHEN in_u1 THEN licitacion_id END) u1_lic, count(DISTINCT CASE WHEN in_u2 THEN licitacion_id||'#'||lote END) u2_unid, count(DISTINCT CASE WHEN in_u2x THEN licitacion_id||'#'||lote END) u2x_unid FROM uf GROUP BY 1,2 ORDER BY 2,1 ; --; -- @pool TED (web + .versiones, con identidad) CREATE OR REPLACE TABLE tp AS WITH x AS ( SELECT id_canonical, licitacion_id, expediente, organo_nif, organo_nombre, dir3, adjudicatario_nif_raw, adjudicatario_nif, adjudicatario_nombre, adjudicatario_nif_valid, imp_adj_eur, fecha_pub, fecha_adj, unidad, 'web' AS capa FROM u WHERE source='TED' UNION ALL SELECT id_canonical, licitacion_id, expediente, organo_nif, organo_nombre, dir3, adjudicatario_nif_raw, adjudicatario_nif, adjudicatario_nombre, adjudicatario_nif_valid, imp_adj_eur, fecha_pub, fecha_adj, unidad, 'versiones' FROM uv WHERE source='TED') SELECT x.id_canonical AS tid, replace(x.licitacion_id,'TED:','') AS aviso, x.capa, x.unidad, x.imp_adj_eur AS v, x.fecha_pub AS tpub, x.fecha_adj AS tadj, CASE WHEN x.adjudicatario_nif_valid THEN x.adjudicatario_nif END AS nif_adj, CASE WHEN regexp_full_match(coalesce(o.entidad_clave,''), '[A-Z][0-9]{7}[0-9A-Z]') THEN o.entidad_clave END AS ent, o.organo_nombre_norm AS org_nom, o.organo_clave AS t_organo_clave, CASE WHEN length(regexp_replace(upper(coalesce(x.expediente,'')), '[^A-Z0-9]', '', 'g'))>=5 AND regexp_matches(x.expediente,'[0-9]') THEN regexp_replace(upper(x.expediente), '[^A-Z0-9]', '', 'g') END AS exp_n, CASE WHEN length(a.nombre_norm)>=8 THEN a.nombre_norm END AS nom_adj FROM x LEFT JOIN idn_organos_filas o ON o.organo_nif IS NOT DISTINCT FROM x.organo_nif AND o.organo_nombre IS NOT DISTINCT FROM x.organo_nombre AND o.dir3 IS NOT DISTINCT FROM x.dir3 LEFT JOIN idn_adjudicatarios_filas a ON a.adjudicatario_nif_raw IS NOT DISTINCT FROM x.adjudicatario_nif_raw AND a.adjudicatario_nif IS NOT DISTINCT FROM x.adjudicatario_nif AND a.adjudicatario_nombre IS NOT DISTINCT FROM x.adjudicatario_nombre WHERE x.fecha_pub >= DATE '2017-11-01' ; --; -- @pool TED: recuento SELECT capa, count(*), count(DISTINCT aviso), count(nif_adj), count(ent), count(exp_n), count(nom_adj), count(*) FILTER (WHERE v>=1.5) FROM tp GROUP BY 1