| 123456789101112131415161718192021222324252627282930313233343536373839404142434445 |
- """Auxiliary leaf summaries. Existing 23 workbook sheets and formulas stay intact."""
- import hashlib
- import json
- import sqlite3
- from ...catalog import CLASS_SUMMARY, MODEL_SUMMARY, AMOUNT, QUANTITY, PRICE
- from ...query_indexes import create_query_indexes, quote
- from ..contract import SheetInfo
- from .numbers import number, ratio, to_text
- BASE_DIMS = ['direction','year','month','c1','c2','c3','c4']
- BASE_HEADERS = ['进/出口','年度','月度','一级类目','二级类目','三级类目','描述']
- def build_query_summaries(source, output_dir):
- db=sqlite3.connect(str(output_dir/'stat.sqlite'))
- sheets=[]
- try:
- for name, extras in [(CLASS_SUMMARY, []), (MODEL_SUMMARY, ['brand','model'])]:
- dims=BASE_DIMS+extras
- headers=BASE_HEADERS+(['品牌','型号'] if extras else [])+[AMOUNT,QUANTITY,'记录数',PRICE]
- table='q_class_month' if not extras else 'q_model_month'
- numeric=['年度','月度',AMOUNT,QUANTITY,'记录数',PRICE]
- cols=['"_row_index" INTEGER PRIMARY KEY','"_row_hash" TEXT']+[quote(h)+' TEXT' for h in headers]+[quote('_num_'+h)+' REAL' for h in numeric]
- db.execute(f'CREATE TABLE {quote(table)}({",".join(cols)})')
- insert=f'INSERT INTO {quote(table)} VALUES({",".join("?" for _ in cols)})'
- sql=f'SELECT {",".join(dims)},dsum(amount) amount,dsum(quantity) quantity,SUM(n) n FROM monthly GROUP BY {",".join(dims)}'
- batch=[];count=0
- for row in source.execute(sql):
- amount=number(row['amount']);quantity=number(row['quantity'])
- 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))]
- rh='sha256:'+hashlib.sha256(json.dumps(values,ensure_ascii=False).encode()).hexdigest()
- num=[]
- for h in numeric:
- value=number(values[headers.index(h)])
- num.append(float(value) if value is not None else None)
- batch.append((count,rh,*values,*num));count+=1
- if len(batch)>=1000:
- db.executemany(insert,batch);batch.clear()
- if batch:db.executemany(insert,batch)
- create_query_indexes(db,table,headers+['_num_'+h for h in numeric])
- sheets.append(SheetInfo(name,'query_summary',count,headers,table))
- db.execute('ANALYZE')
- db.commit()
- finally:db.close()
- return sheets
|