# impl2 · §4 recuentos por celda (fuente x base x clase) x periodo x banda -> tmp/impl2/mig_counts.csv import duckdb con = duckdb.connect() con.execute("SET memory_limit='7GB'; SET threads=6; SET temp_directory='/w/tmp/duck'") print('duckdb', duckdb.__version__) # periodos: etiqueta, desde, hasta (ambos inclusive) per = [('antes','2017-01-01','2018-03-08'), ('despues2T','2018-04-01','2018-09-30'), ('despues','2018-03-09','2019-12-31')] for y in (2017,2018,2019): for q,(a,b) in enumerate([('01-01','03-31'),('04-01','06-30'),('07-01','09-30'),('10-01','12-31')],1): per.append((f'{y}T{q}', f'{y}-{a}', f'{y}-{b}')) con.execute("CREATE TABLE per(periodo VARCHAR, d0 DATE, d1 DATE)") con.executemany("INSERT INTO per VALUES (?,?,?)", per) # bandas: nombre, lo, hi, cerrado_arriba ; por clase bandas = { 'resto': [('norm',5000,14500,0),('fable',5000,25000,0),('V',17500,18000,0),('V1',21175,21780,0),('N',14500,15000,0), ('N1',18000,18150,1),('belowV',16000,17500,0),('belowV1',19360,21175,0)], 'obras': [('norm',10000,38000,0),('V',49000,50000,0),('V1',59290,60500,0),('N',39000,40000,0),('N1',47190,48400,1), ('belowV',47500,49000,0),('belowV1',57475,59290,0),('belowV_x2',46000,49000,0),('belowV1_x2',55660,59290,0)], } cols=[] for cl in ('resto','obras'): for (nm,lo,hi,c) in bandas[cl]: op = '<=' if c else '<' cols.append(f"count(*) FILTER (WHERE clase='{cl}' AND imp >= {lo} AND imp {op} {hi}) AS {cl}_{nm}") sql = f""" SELECT p.source, p.base, p.clase, per.periodo, count(*) AS n_total, count(imp) AS n_con_imp, {', '.join(cols)} FROM read_parquet('/w/tmp/pob4.parquet') p JOIN per ON p.fecha_adj BETWEEN per.d0 AND per.d1 GROUP BY ALL ORDER BY ALL""" con.execute(f"COPY ({sql}) TO '/w/tmp/mig_counts.csv' (HEADER, DELIMITER ',')") print(con.execute("SELECT count(*) FROM read_csv_auto('/w/tmp/mig_counts.csv')").fetchall()) print(con.execute("SELECT source, base, clase, n_total, resto_norm, obras_norm FROM read_csv_auto('/w/tmp/mig_counts.csv') WHERE periodo='antes' ORDER BY 1,2,3").fetchall())