query_summaries.py 2.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445
  1. """Auxiliary leaf summaries. Existing 23 workbook sheets and formulas stay intact."""
  2. import hashlib
  3. import json
  4. import sqlite3
  5. from ...catalog import CLASS_SUMMARY, MODEL_SUMMARY, AMOUNT, QUANTITY, PRICE
  6. from ...query_indexes import create_query_indexes, quote
  7. from ..contract import SheetInfo
  8. from .numbers import number, ratio, to_text
  9. BASE_DIMS = ['direction','year','month','c1','c2','c3','c4']
  10. BASE_HEADERS = ['进/出口','年度','月度','一级类目','二级类目','三级类目','描述']
  11. def build_query_summaries(source, output_dir):
  12. db=sqlite3.connect(str(output_dir/'stat.sqlite'))
  13. sheets=[]
  14. try:
  15. for name, extras in [(CLASS_SUMMARY, []), (MODEL_SUMMARY, ['brand','model'])]:
  16. dims=BASE_DIMS+extras
  17. headers=BASE_HEADERS+(['品牌','型号'] if extras else [])+[AMOUNT,QUANTITY,'记录数',PRICE]
  18. table='q_class_month' if not extras else 'q_model_month'
  19. numeric=['年度','月度',AMOUNT,QUANTITY,'记录数',PRICE]
  20. cols=['"_row_index" INTEGER PRIMARY KEY','"_row_hash" TEXT']+[quote(h)+' TEXT' for h in headers]+[quote('_num_'+h)+' REAL' for h in numeric]
  21. db.execute(f'CREATE TABLE {quote(table)}({",".join(cols)})')
  22. insert=f'INSERT INTO {quote(table)} VALUES({",".join("?" for _ in cols)})'
  23. sql=f'SELECT {",".join(dims)},dsum(amount) amount,dsum(quantity) quantity,SUM(n) n FROM monthly GROUP BY {",".join(dims)}'
  24. batch=[];count=0
  25. for row in source.execute(sql):
  26. amount=number(row['amount']);quantity=number(row['quantity'])
  27. values=[row['direction'],row['year'],str(int(row['month'][-2:])),row['c1'],row['c2'],row['c3'],row['c4']]+[row[e] for e in extras]+[to_text(amount),to_text(quantity),str(row['n']),to_text(ratio(amount,quantity))]
  28. rh='sha256:'+hashlib.sha256(json.dumps(values,ensure_ascii=False).encode()).hexdigest()
  29. num=[]
  30. for h in numeric:
  31. value=number(values[headers.index(h)])
  32. num.append(float(value) if value is not None else None)
  33. batch.append((count,rh,*values,*num));count+=1
  34. if len(batch)>=1000:
  35. db.executemany(insert,batch);batch.clear()
  36. if batch:db.executemany(insert,batch)
  37. create_query_indexes(db,table,headers+['_num_'+h for h in numeric])
  38. sheets.append(SheetInfo(name,'query_summary',count,headers,table))
  39. db.execute('ANALYZE')
  40. db.commit()
  41. finally:db.close()
  42. return sheets