"""LOB1 · 50 · pregunta (c) (DEFINICIONES §6): cobertura «K de N» por administración × mercado × año, y salida publicable con supresión.""" import sys, json, csv; sys.path.insert(0, '/w/py'); from comun import * con = abrir(ro=True); x = con.execute REG = {'GENCAT': 'CAT-R', 'AYTO_MAD': 'MAD-R', 'CAM': 'CAM-R'} res = {'cobertura_registro': {}, 'total_x': {}} for X, R in REG.items(): res['cobertura_registro'][R] = dict(zip(['inscripciones', 'casadas_empresa', 'empresas_casadas', 'empresas_adjudicatarias_de_X', 'empresas_adjudicatarias_en_territorio'], x(f""" SELECT (SELECT count(*) FROM reg WHERE registro = '{R}'), (SELECT count(DISTINCT gid) FROM casado WHERE registro = '{R}'), (SELECT count(DISTINCT nif) FROM casado WHERE registro = '{R}'), (SELECT count(DISTINCT emp) FROM lot WHERE x = '{X}' AND emp IN (SELECT nif FROM casado WHERE registro = '{R}')), (SELECT count(DISTINCT emp) FROM lot WHERE ccaa = '{'CT' if X == 'GENCAT' else 'MD'}' AND emp IN (SELECT nif FROM casado WHERE registro = '{R}'))""").fetchone())) for uni, filt in (('sin_menores', 'AND NOT menor'), ('con_menores', '')): res['total_x'][f'{X}_{uni}'] = dict(zip(['N_adjudicatarias', 'K_inscritas', 'lotes', 'lotes_inscritas', 'importe', 'importe_inscritas'], x(f""" SELECT count(DISTINCT emp), count(DISTINCT emp) FILTER (WHERE r), count(*), count(*) FILTER (WHERE r), sum(imp), sum(imp) FILTER (WHERE r) FROM (SELECT l.*, l.emp IN (SELECT nif FROM casado WHERE registro = '{R}') r FROM lot l WHERE x = '{X}' {filt})""").fetchone())) log(X, uni, res['total_x'][f'{X}_{uni}']) log(res['cobertura_registro']) filas = [] for X, R in REG.items(): for uni, filt in (('sin_menores', 'AND NOT menor'), ('con_menores', '')): filas += [(X, uni) + tuple(r) for r in x(f""" SELECT coalesce(cpv2, 'NA'), anio, count(DISTINCT emp), count(DISTINCT emp) FILTER (WHERE r), count(*), count(*) FILTER (WHERE r), sum(imp), sum(imp) FILTER (WHERE r) FROM (SELECT l.*, l.emp IN (SELECT nif FROM casado WHERE registro = '{R}') r FROM lot l WHERE x = '{X}' {filt}) GROUP BY 1, 2 ORDER BY 1, 2""").fetchall()] NOMX = {'GENCAT': 'Generalitat de Catalunya (sector público, sin universidades)', 'AYTO_MAD': 'Ayuntamiento de Madrid', 'CAM': 'Comunidad de Madrid'} cab = ['administracion', 'administracion_nombre', 'registro', 'universo', 'mercado_cpv2', 'anio', 'n_adjudicatarias', 'k_inscritas', 'lotes', 'lotes_de_inscritas', 'pct_importe_inscritas'] sup = {'filas': 0, 'suprimidas_n_menor_10': 0, 'k_1_2': 0} with open('/w/salidas/publicable_administracion_mercado_anio.csv', 'w', newline='') as fh, open('/w/interno/50_completo.csv', 'w', newline='') as fi: w = csv.writer(fh); w.writerow(cab); wi = csv.writer(fi); wi.writerow(cab[:-1] + ['importe', 'importe_inscritas']) for X, uni, cpv, anio, n, k, lo, lok, imp, impk in filas: wi.writerow([X, NOMX[X], REG[X], uni, cpv, anio, n, k, lo, lok, imp, impk]); sup['filas'] += 1 if n < 10: sup['suprimidas_n_menor_10'] += 1; continue if 1 <= k <= 2: sup['k_1_2'] += 1; w.writerow([X, NOMX[X], REG[X], uni, cpv, anio, n, '<3', lo, '', '']); continue pct = round(100 * impk / imp, 1) if imp and impk is not None else (0.0 if k == 0 else '') w.writerow([X, NOMX[X], REG[X], uni, cpv, anio, n, k, lo, lok, pct]) res['supresion'] = sup; log(sup) # mercados con más peso de inscritas (agregado 2017-2025, sin menores, N ≥ 30) res['mercados'] = {} for X, R in REG.items(): res['mercados'][X] = [dict(cpv2=r[0], N=r[1], K=r[2], pct_importe=round(100 * (r[4] or 0) / r[3], 1) if r[3] else None) for r in x(f""" SELECT coalesce(cpv2,'NA'), count(DISTINCT emp), count(DISTINCT emp) FILTER (WHERE r), sum(imp), sum(imp) FILTER (WHERE r) FROM (SELECT l.*, l.emp IN (SELECT nif FROM casado WHERE registro = '{R}') r FROM lot l WHERE x = '{X}' AND NOT menor) GROUP BY 1 HAVING count(DISTINCT emp) >= 30 ORDER BY sum(imp) DESC LIMIT 12""").fetchall()] json.dump(res, open('/w/interno/50_res.json', 'w'), indent=1, default=float)