query_indexes.py 4.5 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788
  1. """Small workload-driven index portfolio; no automatic index-per-column strategy."""
  2. import hashlib
  3. import json
  4. from .query_precision import register_precision
  5. INDEX_VERSION = '2026-11-18.1'
  6. def quote(value):
  7. return '"' + str(value).replace('"', '""') + '"'
  8. def create_query_indexes(db, table, columns, raw=False):
  9. register_precision(db)
  10. cols = set(columns)
  11. portfolios = []
  12. if raw:
  13. from .customs.engine.storage import ALIASES, normalize_header
  14. canonical = {}
  15. lookup = {normalize_header(k).upper(): v for k, v in ALIASES.items()}
  16. for col in columns:
  17. key = normalize_header(col).upper()
  18. canonical.setdefault(lookup.get(key, key), col)
  19. # Exact HS+year+week trace, or year+week scan across HS. Raw values untouched.
  20. for keys in [('HS_CODE', 'YEAR', 'WEEK'), ('YEAR', 'WEEK')]:
  21. if all(k in canonical for k in keys):
  22. 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'])
  23. else:
  24. # Timeline: one direction/class, chronological order. TopN: one month/scope.
  25. if table == 'q_class_month':
  26. candidates = [('进/出口','二级类目','年度','月度'),
  27. ('进/出口','年度','月度','二级类目','_num_实际金额(美元)')]
  28. elif table == 'q_model_month':
  29. candidates = [('进/出口','品牌','型号','年度','月度'),
  30. ('进/出口','年度','月度','二级类目','_num_实际金额(美元)')]
  31. else:
  32. candidates = [('进/出口','基础类目','年度','月度'),
  33. ('进/出口','年度','月度','统计范围','统计层级','_num_实际金额(美元)')]
  34. for keys in candidates:
  35. if all(k in cols for k in keys):
  36. actual = ['_num_'+k if k in {'年度','月度','ISO年','ISO周','归属年','归属月','选样年度'} and '_num_'+k in cols else k for k in keys]
  37. actual = ['实际金额(美元)' if k=='_num_实际金额(美元)' else k for k in actual]
  38. portfolios.append(actual + ['_row_index'])
  39. created=[]
  40. for keys in portfolios:
  41. orders = ['DESC' if k == '实际金额(美元)' else 'ASC' for k in keys]
  42. suffix=hashlib.sha256(json.dumps([table,keys,orders],ensure_ascii=False).encode()).hexdigest()[:12]
  43. name='idx_query_'+suffix
  44. key_sql = ','.join(quote(k)+(' COLLATE DECIMAL_VALUE' if k=='实际金额(美元)' else '')+' '+order for k,order in zip(keys,orders))
  45. db.execute(f'CREATE INDEX IF NOT EXISTS {quote(name)} ON {quote(table)}({key_sql})')
  46. created.append(dict(name=name,columns=keys))
  47. return created
  48. def create_monthly_indexes(db):
  49. """Keep existing three paths plus one measured covering focus query (4m fixture)."""
  50. indexes=[('class','direction,c1,c2,c3,c4,year'),
  51. ('model','direction,brand,model,month'),
  52. ('focus','direction,year,c1,c3,app,brand_country,brand'),
  53. ('focus_query','direction,year,c1,c3,brand,amount,quantity,n')]
  54. for suffix, columns in indexes:
  55. db.execute(f'CREATE INDEX IF NOT EXISTS idx_{suffix} ON monthly({columns})')
  56. db.execute('ANALYZE monthly')
  57. def raw_numeric_columns(columns):
  58. from .customs.engine.storage import ALIASES, normalize_header
  59. lookup = {normalize_header(k).upper(): v for k,v in ALIASES.items()}
  60. return [c for c in columns if lookup.get(normalize_header(c).upper(),normalize_header(c).upper()) in {'YEAR','WEEK'}]
  61. def ensure_raw_calendar(db, table, columns):
  62. """Backfill only missing derived calendar columns; keep raw text and row hashes."""
  63. from .query_precision import number
  64. existing={r[1] for r in db.execute(f'PRAGMA table_info({quote(table)})')}
  65. new=[c for c in raw_numeric_columns(columns) if '_num_'+c not in existing]
  66. if not new:return
  67. for c in new:db.execute(f'ALTER TABLE {quote(table)} ADD COLUMN {quote("_num_"+c)} REAL')
  68. cursor=db.execute(f'SELECT _row_index,{",".join(quote(c) for c in new)} FROM {quote(table)} ORDER BY _row_index')
  69. update=f'UPDATE {quote(table)} SET '+','.join(quote('_num_'+c)+'=?' for c in new)+' WHERE _row_index=?'
  70. while True:
  71. rows=cursor.fetchmany(1000)
  72. if not rows:break
  73. batch=[]
  74. for row in rows:
  75. parsed=[number(v) for v in row[1:]]
  76. batch.append(tuple(float(v) if v is not None else None for v in parsed)+(row[0],))
  77. db.executemany(update,batch)