# impl2 · §6 recuentos agregados por órgano x año x clase x fuente(PLACSP/otra) x ccaa_nula x tramo -> tmp/impl2/carril_agg.csv, carril_org.csv import duckdb, time t0=time.time() con = duckdb.connect() con.execute("SET memory_limit='7GB'; SET threads=6; SET temp_directory='/w/tmp/duck'; SET preserve_insertion_order=false") print('duckdb', duckdb.__version__) con.execute("CREATE VIEW R AS SELECT * FROM read_parquet('/w/tmp/R6.parquet')") con.execute("CREATE TABLE par AS SELECT organo_clave, empresa_clave, anio_ref, count(*) AS n_par FROM R GROUP BY ALL") con.execute("CREATE TABLE obj AS SELECT organo_clave, objeto_norm, anio_ref, count(*) AS n_obj FROM R WHERE length(objeto_norm) >= 10 GROUP BY ALL") print('par', con.execute("SELECT count(*), sum(n_par) FROM par").fetchall(), 'obj', con.execute("SELECT count(*), sum(n_obj) FROM obj").fetchall()) con.execute(""" CREATE TABLE S AS SELECT r.organo_clave, r.anio_ref, r.source, r.ccaa, r.tipo_org, r.agente_secti, r.imp, CASE WHEN r.tipo_contrato IN ('Servicios','Suministros') THEN 'SS' ELSE 'OB' END AS clase, (p.n_par = 1) AS fb, (length(r.objeto_norm) >= 10 AND o.n_obj = 1) AS fc FROM R r JOIN par p ON p.organo_clave = r.organo_clave AND p.empresa_clave = r.empresa_clave AND p.anio_ref = r.anio_ref LEFT JOIN obj o ON o.organo_clave = r.organo_clave AND o.objeto_norm = r.objeto_norm AND o.anio_ref = r.anio_ref WHERE r.base = 'sin' AND coalesce(r.ccaa, '') <> 'NA' AND ( (r.tipo_contrato IN ('Servicios','Suministros') AND r.imp >= 5000 AND r.imp < 15000) OR (r.tipo_contrato = 'Obras' AND r.imp >= 12000 AND r.imp < 40000) ) """) print('S', con.execute("SELECT clase, count(*), count(*) FILTER (WHERE fb), count(*) FILTER (WHERE coalesce(fc,FALSE)), count(*) FILTER (WHERE ccaa IS NULL), count(*) FILTER (WHERE tipo_org='investigacion') FROM S GROUP BY 1").fetchall()) con.execute("""CREATE TABLE orgs AS SELECT organo_clave, row_number() OVER (ORDER BY organo_clave) - 1 AS oid FROM (SELECT DISTINCT organo_clave FROM S)""") con.execute(""" COPY ( SELECT g.oid, s.anio_ref, s.clase, (s.source = 'PLACSP')::int AS placsp, (s.ccaa IS NULL)::int AS ccaa_nula, CASE WHEN s.clase = 'SS' THEN floor(s.imp / 100)::int ELSE floor(s.imp / 250)::int END AS tramo, count(*) AS n_a, count(*) FILTER (WHERE s.fb) AS n_b, count(*) FILTER (WHERE coalesce(s.fc, FALSE)) AS n_c FROM S s JOIN orgs g USING (organo_clave) GROUP BY ALL ORDER BY ALL ) TO '/w/tmp/carril_agg.csv' (HEADER, DELIMITER ',')""") con.execute(""" COPY ( SELECT g.oid, max(s.tipo_org) AS tipo_org, max(s.agente_secti::int) AS agente_secti, count(DISTINCT s.tipo_org) AS k_tipo FROM S s JOIN orgs g USING (organo_clave) GROUP BY 1 ORDER BY 1 ) TO '/w/tmp/carril_org.csv' (HEADER, DELIMITER ',')""") print(con.execute("SELECT count(*), sum(n_a), sum(n_b), sum(n_c), min(tramo), max(tramo) FROM read_csv_auto('/w/tmp/carril_agg.csv') GROUP BY clase").fetchall()) print(con.execute("SELECT count(*), max(k_tipo), sum(agente_secti) FROM read_csv_auto('/w/tmp/carril_org.csv')").fetchall()) # control de redondeo de tramos: ningún importe fuera de [lo, hi) de su tramo print('control tramo', con.execute("""SELECT count(*) FILTER (WHERE clase='SS' AND NOT (imp >= floor(imp/100)*100 AND imp < floor(imp/100)*100+100)), count(*) FILTER (WHERE clase='OB' AND NOT (imp >= floor(imp/250)*250 AND imp < floor(imp/250)*250+250)) FROM S""").fetchall()) print('total %.1fs'%(time.time()-t0))