-- impl2 · 02 · Poblaciones (§3.1) y fuente-año calculable (§3.3) -- ── PLACSP, V0: la población común del job (Calculo.poblacion) ───────────────────────────────────────────────────── -- Publicaciones dobles: en lugar de row_number, se queda el min(id_canonical) de cada clave (GROUP BY trata los NULL como -- iguales, igual que PARTITION BY). La clave usa la base publicada en DOUBLE, como el job. CREATE OR REPLACE TABLE p0f AS SELECT id_canonical, organo_clave, entidad_clave, empresa_clave, tipo, cpv8, objeto_norm, expediente, fecha_adj, imp_adj_eur_comparable AS base_pub, imp_adj_eur_comparable::DECIMAL(18,2) AS base, CASE WHEN tipo_contrato = 'Obras' THEN 'obras' ELSE 'resto' END AS clase, CASE WHEN tipo_contrato = 'Obras' THEN 40000 WHEN tipo = 'investigacion' THEN 50000 ELSE 15000 END AS umbral FROM mb WHERE source = 'PLACSP' AND coalesce(cuenta, TRUE) AND anio_ref BETWEEN 2019 AND 2025 AND fecha_adj BETWEEN DATE '2019-01-01' AND DATE '2025-12-31' AND iva_base IN ('sin', 'sin_medido') AND imp_adj_eur_comparable > 0 AND persona_fisica = FALSE AND NOT coalesce(es_menor_dudoso, FALSE) AND organo_clave IS NOT NULL AND empresa_clave IS NOT NULL AND NOT da9 AND NOT coalesce(anulado_en_origen, FALSE); ANALYZE p0f; CREATE OR REPLACE TABLE p0keep AS SELECT min(id_canonical) AS id_canonical FROM p0f GROUP BY organo_clave, empresa_clave, base_pub, fecha_adj, objeto_norm, coalesce(expediente, ''); ANALYZE p0keep; CREATE OR REPLACE TABLE pob0 AS SELECT 'PLACSP' AS fuente, f.* EXCLUDE (base_pub, expediente) FROM p0f f SEMI JOIN p0keep k ON k.id_canonical = f.id_canonical; ANALYZE pob0; -- @ V0 PLACSP: población SELECT count(*) menores, sum(base)::DOUBLE importe, count(DISTINCT organo_clave) organos, (SELECT count(*) FROM p0f) antes_dedup FROM pob0; -- ── Demás fuentes, V2 ───────────────────────────────────────────────────────────────────────────────────────────── -- Base de los criterios del §3.3: todos los filtros del §3.1 salvo la fecha exacta, el objeto informativo y la empresa -- identificada (que es uno de los criterios); persona física = TRUE fuera (las de persona_fisica NULL se quedan: «no son -- de persona física»); sin quitar publicaciones dobles. Año: el de fecha_adj si es exacta; si no, anio_ref. CREATE OR REPLACE TABLE c2b AS SELECT m.source AS fuente, m.id_canonical, m.organo_clave, m.entidad_clave, m.empresa_clave, m.tipo, m.cpv8, m.objeto_norm, m.expediente, m.fecha_adj, m.obj_len, m.cuenta, CASE WHEN m.anio_precision = 'exacto' AND m.fecha_adj IS NOT NULL THEN year(m.fecha_adj) ELSE m.anio_ref END AS anio, (m.anio_precision = 'exacto' AND m.fecha_adj IS NOT NULL AND year(m.fecha_adj) BETWEEN 2019 AND 2025) AS fecha_ok, (m.obj_len >= 20 AND c.objeto_norm IS NULL) AS objeto_ok, (m.persona_fisica = FALSE AND m.empresa_clave IS NOT NULL) AS empresa_ok, CASE WHEN m.iva_base IN ('sin', 'sin_medido') THEN m.imp_adj_eur_comparable ELSE m.imp_adj_eur_corregido / 1.21 END AS base_d, CASE WHEN m.tipo_contrato = 'Obras' THEN 'obras' WHEN m.tipo_contrato IS NULL OR m.tipo_contrato = 'Otro' THEN 'desconocido' ELSE 'resto' END AS clase FROM mb m LEFT JOIN cat c ON c.source = m.source AND c.objeto_norm = m.objeto_norm WHERE NOT coalesce(m.es_menor_dudoso, FALSE) AND NOT coalesce(m.anulado_en_origen, FALSE) AND NOT m.da9 AND m.organo_clave IS NOT NULL AND m.persona_fisica IS DISTINCT FROM TRUE AND m.varias_empresas IS DISTINCT FROM TRUE AND (CASE WHEN m.iva_base IN ('sin', 'sin_medido') THEN m.imp_adj_eur_comparable ELSE m.imp_adj_eur_corregido / 1.21 END) > 0; ANALYZE c2b; -- Criterios por fuente y año (y la PLACSP con su población V0, solo informativa) CREATE OR REPLACE TABLE fuente_anio AS WITH k AS ( SELECT fuente, anio, count(*) AS filas, round(100.0 * avg(fecha_ok::INT), 2) AS pct_fecha_exacta, round(100.0 * avg(objeto_ok::INT), 2) AS pct_objeto_informativo, round(100.0 * avg((empresa_clave IS NOT NULL)::INT), 2) AS pct_empresa_identificada, round(100.0 * avg((cuenta IS NOT DISTINCT FROM FALSE)::INT), 2) AS pct_cuenta_false FROM c2b WHERE anio BETWEEN 2019 AND 2025 GROUP BY 1, 2) SELECT k.*, CASE WHEN fuente = 'PLACSP' THEN 'V0' ELSE 'V2' END AS poblacion, (pct_fecha_exacta >= 90) AS ok_fecha, (pct_objeto_informativo >= 50) AS ok_objeto, (pct_empresa_identificada >= 60) AS ok_empresa, (pct_cuenta_false >= 50) AS espejo, (fuente = 'CATALUNYA_PSCP' AND anio < 2023) AS pscp_antes_2023, (fuente = 'PLACSP') OR (pct_fecha_exacta >= 90 AND pct_objeto_informativo >= 50 AND pct_empresa_identificada >= 60 AND pct_cuenta_false < 50 AND NOT (fuente = 'CATALUNYA_PSCP' AND anio < 2023)) AS calculable FROM k; -- @ filas de c2b sin año 2019-2025 (no entran en ninguna fuente-año) SELECT fuente, count(*) FILTER (WHERE anio IS NULL) sin_anio, count(*) FILTER (WHERE anio NOT BETWEEN 2019 AND 2025) fuera FROM c2b GROUP BY 1 HAVING count(*) FILTER (WHERE anio IS NULL OR anio NOT BETWEEN 2019 AND 2025) > 0 ORDER BY 1; -- Población V2: filas de los años calculables con fecha exacta, objeto informativo y empresa; publicaciones dobles fuera CREATE OR REPLACE TABLE p2f AS SELECT b.fuente, b.id_canonical, b.organo_clave, b.entidad_clave, b.empresa_clave, b.tipo, b.cpv8, b.objeto_norm, b.expediente, b.fecha_adj, b.base_d::DECIMAL(18,2) AS base, b.clase, CASE WHEN b.clase IN ('obras', 'desconocido') THEN 40000 WHEN b.tipo = 'investigacion' THEN 50000 ELSE 15000 END AS umbral FROM c2b b JOIN fuente_anio fa ON fa.fuente = b.fuente AND fa.anio = b.anio AND fa.calculable WHERE b.fuente <> 'PLACSP' AND b.fecha_ok AND b.objeto_ok AND b.empresa_ok AND b.base_d::DECIMAL(18,2) > 0; ANALYZE p2f; CREATE OR REPLACE TABLE p2keep AS SELECT fuente, min(id_canonical) AS id_canonical FROM p2f GROUP BY fuente, organo_clave, empresa_clave, base, fecha_adj, objeto_norm, coalesce(expediente, ''); ANALYZE p2keep; CREATE OR REPLACE TABLE pob2 AS SELECT f.* EXCLUDE (expediente) FROM p2f f SEMI JOIN p2keep k ON k.fuente = f.fuente AND k.id_canonical = f.id_canonical; ANALYZE pob2; -- La PLACSP va con su población V0 (calculable por definición); sus criterios se miden igual, solo informativos CREATE OR REPLACE TABLE fa_n AS SELECT fa.*, coalesce(p.n_pob, 0) AS filas_poblacion FROM fuente_anio fa LEFT JOIN (SELECT fuente, year(fecha_adj) anio, count(*) n_pob FROM (SELECT fuente, fecha_adj FROM pob0 UNION ALL SELECT fuente, fecha_adj FROM pob2) GROUP BY 1, 2) p USING (fuente, anio); COPY (SELECT * FROM fa_n ORDER BY fuente, anio) TO '/w/salidas/fuente_anio.csv' (HEADER, DELIMITER ','); -- @ fuente-año (criterios) SELECT fuente, anio, filas, pct_fecha_exacta f, pct_objeto_informativo o, pct_empresa_identificada e, pct_cuenta_false cf, calculable, filas_poblacion FROM fa_n ORDER BY fuente, anio; -- @ población V2 por fuente SELECT fuente, count(*) n, count(DISTINCT organo_clave) organos, min(fecha_adj), max(fecha_adj), (SELECT count(*) FROM p2f f WHERE f.fuente = p.fuente) antes_dedup FROM pob2 p GROUP BY 1 ORDER BY 1;