CREATE OR REPLACE MACRO wil_lo(x, n) AS CASE WHEN n=0 THEN NULL ELSE ((x/n + 1.96^2/(2*n)) - 1.96*sqrt((x/n)*(1-x/n)/n + 1.96^2/(4*n^2)))/(1+1.96^2/n) END ; --; CREATE OR REPLACE MACRO wil_hi(x, n) AS CASE WHEN n=0 THEN NULL ELSE ((x/n + 1.96^2/(2*n)) + 1.96*sqrt((x/n)*(1-x/n)/n + 1.96^2/(4*n^2)))/(1+1.96^2/n) END ; --; CREATE OR REPLACE TABLE fin AS SELECT *, coalesce(est365 IN ('E1','E2'), FALSE) AS pub_lo, (est365 IS NOT NULL) AS pub_hi, coalesce(est365 IN ('E3','E4','E5'), FALSE) AS dudosa FROM unc ; --; -- @F1 U1 con publicidad por año: cota baja (E1+E2, aceptadas) y alta (todas); IC de Wilson 95 % SELECT anio, count(*) n, sum(pub_lo::INT) x_lo, round(100.0*avg(pub_lo::INT),1) pct_lo, round(100*wil_lo(sum(pub_lo::INT)::DOUBLE,count(*)),1) lo, round(100*wil_hi(sum(pub_lo::INT)::DOUBLE,count(*)),1) hi, sum(dudosa::INT) dudosas, round(100.0*avg(pub_hi::INT),1) pct_hi, sum((NOT pub_hi)::INT) sin_casar FROM fin WHERE u1 AND estrato='con_publicidad' GROUP BY ROLLUP(1) ORDER BY 1 ; --; -- @F2 U1 negociado sin publicidad por año SELECT anio, count(*) n, round(100.0*avg(pub_lo::INT),1) pct_lo, round(100*wil_lo(sum(pub_lo::INT)::DOUBLE,count(*)),1) lo, round(100*wil_hi(sum(pub_lo::INT)::DOUBLE,count(*)),1) hi, round(100.0*avg(pub_hi::INT),1) pct_hi FROM fin WHERE u1 AND estrato='nsp' GROUP BY ROLLUP(1) ORDER BY 1 ; --; -- @F3 U2 (serie por umbral) con publicidad por año SELECT anio, count(*) n, round(100.0*avg(pub_lo::INT),1) pct_lo, round(100*wil_lo(sum(pub_lo::INT)::DOUBLE,count(*)),1) lo, round(100*wil_hi(sum(pub_lo::INT)::DOUBLE,count(*)),1) hi, round(100.0*avg(pub_hi::INT),1) pct_hi FROM fin WHERE u2 AND estrato='con_publicidad' GROUP BY 1 ORDER BY 1 ; --; -- @F4 U1 con publicidad por tipo de contrato SELECT tipo, count(*) n, round(100.0*avg(pub_lo::INT),1) pct_lo, round(100*wil_lo(sum(pub_lo::INT)::DOUBLE,count(*)),1) lo, round(100*wil_hi(sum(pub_lo::INT)::DOUBLE,count(*)),1) hi, round(100.0*avg(pub_hi::INT),1) pct_hi FROM fin WHERE u1 AND estrato='con_publicidad' GROUP BY 1 ORDER BY 2 DESC ; --; -- @F5 U1 con publicidad por procedimiento SELECT proc, count(*) n, round(100.0*avg(pub_lo::INT),1) pct_lo, round(100*wil_lo(sum(pub_lo::INT)::DOUBLE,count(*)),1) lo, round(100*wil_hi(sum(pub_lo::INT)::DOUBLE,count(*)),1) hi, round(100.0*avg(pub_hi::INT),1) pct_hi FROM fin WHERE u1 AND estrato='con_publicidad' GROUP BY 1 ORDER BY 2 DESC ; --; -- @F6 U1 con publicidad por tipo de órgano (y por año) SELECT organo_tipo, anio, count(*) n, count(DISTINCT organo_clave) organos, round(100.0*avg(pub_lo::INT),1) pct_lo, round(100*wil_lo(sum(pub_lo::INT)::DOUBLE,count(*)),1) lo, round(100*wil_hi(sum(pub_lo::INT)::DOUBLE,count(*)),1) hi, round(100.0*avg(pub_hi::INT),1) pct_hi FROM fin WHERE u1 AND estrato='con_publicidad' GROUP BY ROLLUP(1,2) HAVING organo_tipo IS NOT NULL ORDER BY 1,2 NULLS FIRST ; --; -- @órgano-año (U1 con publicidad, n >= 10) con sus pares (mismo tipo de órgano y año) CREATE OR REPLACE TABLE org_anio AS WITH o AS (SELECT organo_clave, any_value(organo_nombre) organo_nombre, organo_tipo, anio, count(*) n, sum(pub_lo::INT) x_lo, sum(pub_hi::INT) x_hi FROM fin WHERE u1 AND estrato='con_publicidad' AND organo_clave IS NOT NULL GROUP BY organo_clave, organo_tipo, anio), p AS (SELECT organo_tipo, anio, count(*) FILTER (WHERE n>=10) pares, sum(x_lo)::DOUBLE/sum(n) tasa_pares, median(x_lo::DOUBLE/n) FILTER (WHERE n>=10) mediana_pares FROM o GROUP BY 1,2) SELECT o.*, round(100.0*x_lo/n,1) pct_lo, round(100*wil_lo(x_lo::DOUBLE,n),1) lo, round(100*wil_hi(x_lo::DOUBLE,n),1) hi, round(100.0*x_hi/n,1) pct_hi, p.pares, round(100*p.tasa_pares,1) tasa_pares, round(100*p.mediana_pares,1) mediana_pares FROM o JOIN p USING (organo_tipo, anio) WHERE o.n >= 10 ; --; -- @F7 órgano-año: cuántos y distribución de la cota baja SELECT anio, count(*) organos_anio, sum(n) unidades, round(median(pct_lo),1) mediana_pct_lo, sum((pct_lo<50)::INT) bajo_50, sum((hi<80)::INT) ic_alto_bajo_80 FROM org_anio GROUP BY ROLLUP(1) ORDER BY 1 ; --; COPY (SELECT * FROM org_anio ORDER BY anio, organo_tipo, organo_clave) TO '/c/interno/organo_anio.csv' (HEADER) ; --; -- @F8 casadas por estrategia en U1 con publicidad SELECT coalesce(est365,'(sin casar)') est, count(*) n, round(100.0*count(*)/sum(count(*)) OVER (),1) pct FROM fin WHERE u1 AND estrato='con_publicidad' GROUP BY 1 ORDER BY 1 ; --; -- @F9 TED: avisos de 2023-2025 sin contenido en nuestros datos (sin_datos / sin adjudicatario ni importe) SELECT year(tpub) anio, count(DISTINCT aviso) avisos, count(DISTINCT aviso) FILTER (WHERE unidad='sin_datos') sin_datos FROM tpc WHERE year(tpub) BETWEEN 2023 AND 2025 GROUP BY 1 ORDER BY 1 ; --; -- @F10 U1 con publicidad por licitación (al menos un lote casado por E1/E2) SELECT anio, count(*) n_lic, round(100.0*avg(lo::INT),1) pct_lo, round(100*wil_lo(sum(lo::INT)::DOUBLE,count(*)),1) lo95, round(100*wil_hi(sum(lo::INT)::DOUBLE,count(*)),1) hi95, round(100.0*avg(hi::INT),1) pct_hi FROM (SELECT lic, min(anio) anio, bool_or(pub_lo) lo, bool_or(pub_hi) hi FROM fin WHERE u1 AND estrato='con_publicidad' GROUP BY lic) GROUP BY ROLLUP(1) ORDER BY 1