-- 08. Muestra del cotejo (§7.4): por fuente con cálculo, 10 menores de la población q: 5 de M3 o de M1e (si los hay) y el -- resto al azar, con semilla fija (hash del id con 'p9-cotejo'). Fuentes con ficha de origen legible: la URL de la fila o, -- en el RPC y los menores de la Generalitat, la API de datos abiertos (Socrata). CREATE OR REPLACE TABLE mc AS WITH e AS (SELECT m.*, (m.m3 IS NOT NULL) AS es_m3, (x.en_m1e) AS es_m1e FROM m3f m LEFT JOIN m1x x USING (fuente, id_canonical)), s AS (SELECT *, row_number() OVER (PARTITION BY fuente, (es_m3 OR coalesce(es_m1e, FALSE)) ORDER BY hash(id_canonical || 'p9-cotejo')) AS rk FROM e WHERE fuente IN ('CATALUNYA_PSCP', 'ANDALUCIA', 'EUSKADI', 'MADRID_CAM', 'GALICIA', 'CATALUNYA', 'CATALUNYA_GENERALITAT') OR (fuente = 'VALENCIA' AND url_origen IS NOT NULL)), h AS (SELECT * FROM s WHERE (es_m3 OR coalesce(es_m1e, FALSE)) AND rk <= 5), r AS (SELECT * FROM s WHERE NOT (es_m3 OR coalesce(es_m1e, FALSE)) AND rk <= 10) SELECT * FROM (SELECT * FROM h UNION ALL SELECT * FROM r) QUALIFY row_number() OVER (PARTITION BY fuente ORDER BY (es_m3 OR coalesce(es_m1e, FALSE)) DESC, rk) <= 10; COPY (SELECT mc.fuente, mc.id_canonical, mc.url_origen, mc.es_m3, mc.m3, mc.es_m1e, p.organo_nombre, p.objeto, p.imp_adj_eur, p.iva_base, p.imp_adj_eur_sin_iva, p.imp_adj_eur_con_iva, p.fecha_adj, p.fecha_adj_es, p.adjudicatario_nombre, p.adjudicatario_nif, p.num_ofertas, p.expediente, p.tipo_contrato, mc.anio, p.tabla_origen FROM mc JOIN p ON p.source = mc.fuente AND p.id_canonical = mc.id_canonical ORDER BY mc.fuente, mc.es_m3 DESC, mc.id_canonical) TO '/w/cotejo/muestra.csv' (HEADER, DELIMITER ','); -- @max 100 -- @ Muestra del cotejo SELECT fuente, count(*) n, count(*) FILTER (WHERE es_m3) m3, count(*) FILTER (WHERE es_m1e) m1e FROM mc GROUP BY 1 ORDER BY 1;