| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788 |
- """Small workload-driven index portfolio; no automatic index-per-column strategy."""
- import hashlib
- import json
- from .query_precision import register_precision
- INDEX_VERSION = '2026-11-18.1'
- def quote(value):
- return '"' + str(value).replace('"', '""') + '"'
- def create_query_indexes(db, table, columns, raw=False):
- register_precision(db)
- cols = set(columns)
- portfolios = []
- if raw:
- from .customs.engine.storage import ALIASES, normalize_header
- canonical = {}
- lookup = {normalize_header(k).upper(): v for k, v in ALIASES.items()}
- for col in columns:
- key = normalize_header(col).upper()
- canonical.setdefault(lookup.get(key, key), col)
- # Exact HS+year+week trace, or year+week scan across HS. Raw values untouched.
- for keys in [('HS_CODE', 'YEAR', 'WEEK'), ('YEAR', 'WEEK')]:
- if all(k in canonical for k in keys):
- portfolios.append([('_num_'+canonical[k]) if k in {'YEAR','WEEK'} and '_num_'+canonical[k] in cols else canonical[k] for k in keys] + ['_row_index'])
- else:
- # Timeline: one direction/class, chronological order. TopN: one month/scope.
- if table == 'q_class_month':
- candidates = [('进/出口','二级类目','年度','月度'),
- ('进/出口','年度','月度','二级类目','_num_实际金额(美元)')]
- elif table == 'q_model_month':
- candidates = [('进/出口','品牌','型号','年度','月度'),
- ('进/出口','年度','月度','二级类目','_num_实际金额(美元)')]
- else:
- candidates = [('进/出口','基础类目','年度','月度'),
- ('进/出口','年度','月度','统计范围','统计层级','_num_实际金额(美元)')]
- for keys in candidates:
- if all(k in cols for k in keys):
- actual = ['_num_'+k if k in {'年度','月度','ISO年','ISO周','归属年','归属月','选样年度'} and '_num_'+k in cols else k for k in keys]
- actual = ['实际金额(美元)' if k=='_num_实际金额(美元)' else k for k in actual]
- portfolios.append(actual + ['_row_index'])
- created=[]
- for keys in portfolios:
- orders = ['DESC' if k == '实际金额(美元)' else 'ASC' for k in keys]
- suffix=hashlib.sha256(json.dumps([table,keys,orders],ensure_ascii=False).encode()).hexdigest()[:12]
- name='idx_query_'+suffix
- key_sql = ','.join(quote(k)+(' COLLATE DECIMAL_VALUE' if k=='实际金额(美元)' else '')+' '+order for k,order in zip(keys,orders))
- db.execute(f'CREATE INDEX IF NOT EXISTS {quote(name)} ON {quote(table)}({key_sql})')
- created.append(dict(name=name,columns=keys))
- return created
- def create_monthly_indexes(db):
- """Keep existing three paths plus one measured covering focus query (4m fixture)."""
- indexes=[('class','direction,c1,c2,c3,c4,year'),
- ('model','direction,brand,model,month'),
- ('focus','direction,year,c1,c3,app,brand_country,brand'),
- ('focus_query','direction,year,c1,c3,brand,amount,quantity,n')]
- for suffix, columns in indexes:
- db.execute(f'CREATE INDEX IF NOT EXISTS idx_{suffix} ON monthly({columns})')
- db.execute('ANALYZE monthly')
- def raw_numeric_columns(columns):
- from .customs.engine.storage import ALIASES, normalize_header
- lookup = {normalize_header(k).upper(): v for k,v in ALIASES.items()}
- return [c for c in columns if lookup.get(normalize_header(c).upper(),normalize_header(c).upper()) in {'YEAR','WEEK'}]
- def ensure_raw_calendar(db, table, columns):
- """Backfill only missing derived calendar columns; keep raw text and row hashes."""
- from .query_precision import number
- existing={r[1] for r in db.execute(f'PRAGMA table_info({quote(table)})')}
- new=[c for c in raw_numeric_columns(columns) if '_num_'+c not in existing]
- if not new:return
- for c in new:db.execute(f'ALTER TABLE {quote(table)} ADD COLUMN {quote("_num_"+c)} REAL')
- cursor=db.execute(f'SELECT _row_index,{",".join(quote(c) for c in new)} FROM {quote(table)} ORDER BY _row_index')
- update=f'UPDATE {quote(table)} SET '+','.join(quote('_num_'+c)+'=?' for c in new)+' WHERE _row_index=?'
- while True:
- rows=cursor.fetchmany(1000)
- if not rows:break
- batch=[]
- for row in rows:
- parsed=[number(v) for v in row[1:]]
- batch.append(tuple(float(v) if v is not None else None for v in parsed)+(row[0],))
- db.executemany(update,batch)
|