storage.py 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294
  1. """单文件流式入库:CSV/XLSX → 本地 SQLite 工作库(无需任何数据库服务)。
  2. 入库阶段完成:
  3. * 字段别名归一(支持中文表头);
  4. * 范围校验:10 位 HS、进出口方向、截止日期、ISO 年周合法性;
  5. * 周四归月:整周记入“周四年份-月份”,不拆分跨月周;
  6. * 明细审计:原始行、解析结果、数值异常全部落表,便于复核。
  7. 聚合表 ``monthly`` 是全流程唯一的取数入口,所有报表都从它派生,
  8. 因此“同一批记录、不同视图”的口径可以被显式表达。
  9. """
  10. from __future__ import annotations
  11. import csv
  12. import hashlib
  13. import json
  14. import sqlite3
  15. import re
  16. import unicodedata
  17. from collections import Counter
  18. from ...source import iter_source
  19. from ...query_indexes import create_monthly_indexes
  20. from collections.abc import Callable, Iterator
  21. from datetime import date
  22. from pathlib import Path
  23. from .numbers import StrictSum, number
  24. from .rules import MEMORY_HS, Rules
  25. #: fact 表维度列(顺序即主键顺序)
  26. DIMENSIONS = [
  27. "direction",
  28. "year",
  29. "month",
  30. "c1",
  31. "c2",
  32. "c3",
  33. "c4",
  34. "brand",
  35. "model",
  36. "brand_country",
  37. "app",
  38. "origin",
  39. "trade_country",
  40. "dispatch",
  41. "port",
  42. "port_country",
  43. "mapped",
  44. ]
  45. #: 必需字段
  46. REQUIRED = ["I_E_FLAG", "HS_CODE", "YEAR", "WEEK", "MONORSUM", "COUNT", "G_MODEL"]
  47. #: 中文/别名表头 → 规范字段名
  48. ALIASES = {'MONORSUM': 'MONORSUM', '金额': 'MONORSUM', '金额(美元)': 'MONORSUM', '金额(美元)': 'MONORSUM', '金额:美元': 'MONORSUM', '美元金额': 'MONORSUM', '成交金额': 'MONORSUM', '总金额': 'MONORSUM', '进出口': 'I_E_FLAG', '进/出口': 'I_E_FLAG', '进出口标志': 'I_E_FLAG', '进口/出口': 'I_E_FLAG', '进出口类型': 'I_E_FLAG', 'HS编码': 'HS_CODE', 'HS 编码': 'HS_CODE', '商品编码': 'HS_CODE', '商品编号': 'HS_CODE', '海关编码': 'HS_CODE', '税则号列': 'HS_CODE', '商品编码(10位)': 'HS_CODE', '年份': 'YEAR', '年度': 'YEAR', '周': 'WEEK', '周数': 'WEEK', 'ISO周': 'WEEK', '统计周': 'WEEK', '数量': 'COUNT', '成交数量': 'COUNT', '数量(成交)': 'COUNT', '数量(成交)': 'COUNT', '品牌/型号': 'G_MODEL', '品牌型号': 'G_MODEL', '申报要素': 'G_MODEL', '规格型号': 'G_MODEL', '原产国': 'ORIGIN_COUNTRY', '原产国(地区)': 'ORIGIN_COUNTRY', '贸易国': 'TRADE_AREA_CODE', '贸易国(地区)': 'TRADE_AREA_CODE', '启运国': 'TRADE_COUNTRY', '启运国(地区)': 'TRADE_COUNTRY', '经停港/指运港': 'DISTINATE_PORT', '经停港': 'DISTINATE_PORT', '指运港': 'DISTINATE_PORT', '目的国': 'DESTINATION_COUNTRY', '境内目的地/境内货源地': 'DISTRICT_CODE', '境内目的地': 'DISTRICT_CODE', '贸易方式': 'TRADE_MODE', '申报地海关': 'DECLARING_CUSTOMS', '运输方式': 'TRAF_MODE', '包装种类': 'PACKING_TYPE', 'MONORSUM_USD': 'MONORSUM'}
  49. Progress = Callable[[str, float], None]
  50. def connect(path: str | Path) -> sqlite3.Connection:
  51. database = sqlite3.connect(str(path))
  52. database.row_factory = sqlite3.Row
  53. database.create_aggregate("dsum", 1, StrictSum)
  54. database.execute("PRAGMA temp_store=FILE")
  55. database.execute("PRAGMA cache_size=-65536")
  56. return database
  57. def sha256_file(path: str | Path, chunk: int = 1 << 20) -> str:
  58. digest = hashlib.sha256()
  59. with Path(path).open("rb") as handle:
  60. for block in iter(lambda: handle.read(chunk), b""):
  61. digest.update(block)
  62. return digest.hexdigest()
  63. def _iter_source_rows(source: Path, encoding: str, delimiter: str):
  64. # Retained for callers using the engine's previous reader helper.
  65. for row_id, end_line, headers, values in iter_source(source, encoding, delimiter):
  66. yield end_line, headers, values
  67. def normalize_header(value):
  68. return unicodedata.normalize("NFKC", str(value or "")).replace("\ufeff", "").replace("\u200b", "").strip()
  69. def normalize_code(value):
  70. text = normalize_header(value)
  71. # Excel may export integer code cells with a .0 suffix. No rounding or padding.
  72. if re.fullmatch(r"[0-9]+\.0+", text):
  73. text = text.split('.')[0]
  74. return text
  75. def ingest(config: dict, rules: Rules, out: Path, progress: Progress | None = None) -> tuple[sqlite3.Connection, dict]:
  76. """把源文件流式写入 ``working.sqlite``,返回连接与运行统计。"""
  77. def report(message: str, fraction: float) -> None:
  78. if progress:
  79. progress(message, fraction)
  80. source = Path(config["input_csv"])
  81. database = connect(out / "working.sqlite")
  82. database.execute("CREATE TABLE raw(row_id INTEGER PRIMARY KEY, end_line INTEGER, original_json TEXT, status TEXT)")
  83. columns = ",".join(f"{name} TEXT" for name in DIMENSIONS)
  84. database.execute(f"CREATE TABLE fact(row_id INTEGER,{columns},amount TEXT,quantity TEXT)")
  85. database.execute("CREATE TABLE audit(row_id INTEGER, hs TEXT, raw_model TEXT, parsed_json TEXT)")
  86. database.execute("CREATE TABLE issues(row_id INTEGER, field TEXT, raw_value TEXT, reason TEXT)")
  87. cutoff = date.fromisoformat(config["cutoff_date"]) if config.get("cutoff_date") else date.today()
  88. stats: dict = {
  89. "total": 0,
  90. "included": 0,
  91. "excluded": 0,
  92. "alerts": 0,
  93. "min_month": None,
  94. "max_month": None,
  95. "recognition": {},
  96. "exclusion_reasons": {},
  97. }
  98. def issue(row_id: int, field: str, raw: object, reason: str) -> None:
  99. database.execute("INSERT INTO issues VALUES(?,?,?,?)", (row_id, field, str(raw), reason))
  100. stats["alerts"] += 1
  101. headers_seen: list[str] = []
  102. recognition = Counter()
  103. exclusions = Counter()
  104. report("读取源文件…", 0.02)
  105. for row_id, end_line, headers, values in iter_source(source, config.get("encoding", "auto"), config.get("delimiter", "auto")):
  106. if values is None:
  107. cleaned = [normalize_header(name) for name in headers]
  108. lookup = {normalize_header(key).upper(): value for key, value in ALIASES.items()}
  109. headers_seen = [lookup.get(name.upper(), name.upper()) for name in cleaned]
  110. if "MONORSUM" not in headers_seen and any("人民币" in name or "RMB" in name.upper() or "CNY" in name.upper() for name in cleaned):
  111. raise ValueError("发现人民币金额列;本工具统计口径为美元,请先换汇并明确提供 MONORSUM_USD")
  112. stats["input_fields"] = headers_seen
  113. if len(set(headers_seen)) != len(headers_seen):
  114. raise ValueError("源文件存在重复字段名")
  115. missing = set(REQUIRED) - set(headers_seen)
  116. if missing:
  117. raise ValueError(f"缺少必需字段: {sorted(missing)}")
  118. continue
  119. stats["total"] += 1
  120. if len(values) != len(headers_seen):
  121. row = dict(zip(headers_seen, values))
  122. status = "CSV列数不一致"
  123. else:
  124. row = dict(zip(headers_seen, values))
  125. status = ""
  126. hs = normalize_code(row.get("HS_CODE"))
  127. direction_raw = normalize_code(row.get("I_E_FLAG")).upper()
  128. direction = {"进口": "I", "出口": "E"}.get(direction_raw, direction_raw)
  129. if not normalize_header(row.get("G_MODEL")):
  130. status = status or "G_MODEL为空"
  131. thursday: date | None
  132. try:
  133. thursday = date.fromisocalendar(int(normalize_code(row["YEAR"])), int(normalize_code(row["WEEK"])), 4)
  134. except (ValueError, TypeError, KeyError):
  135. thursday = None
  136. status = status or "无效ISO年份或周号"
  137. if len(hs) != 10 or not hs.isdigit():
  138. status = status or "HS必须为10位文本"
  139. elif hs not in rules.cap and hs not in MEMORY_HS:
  140. status = status or "不在项目HS范围"
  141. if direction not in config["directions"]:
  142. status = status or "不在配置进出口范围"
  143. if thursday and not int(config.get("start_year", 1900)) <= thursday.year <= int(config.get("end_year", 9998)):
  144. status = status or "不在配置年份范围"
  145. if thursday and thursday > cutoff:
  146. status = status or "晚于截止日期的周四"
  147. database.execute(
  148. "INSERT INTO raw VALUES(?,?,?,?)",
  149. (row_id, end_line, json.dumps(row, ensure_ascii=False), status or "纳入"),
  150. )
  151. for key in ("MONORSUM", "COUNT"):
  152. value = number(row.get(key))
  153. if value is None:
  154. issue(row_id, key, row.get(key), "无法识别;受影响指标留空")
  155. elif value <= 0:
  156. issue(row_id, key, row.get(key), "零值或负数;保留原值")
  157. if status:
  158. stats["excluded"] += 1
  159. exclusions[status] += 1
  160. issue(row_id, "记录范围", hs, status)
  161. else:
  162. parsed = rules.parse(hs, row["G_MODEL"])
  163. recognition["brand_resolved" if parsed["brand"] else "brand_unresolved"] += 1
  164. recognition["model_present" if parsed["model"] else "model_absent"] += 1
  165. for label, marker in [("explicit_no_brand", "无品牌标记"), ("explicit_no_model", "无型号标记"), ("unknown_brand", "疑似缺品牌:"), ("brand_conflict", "品牌映射冲突")]:
  166. if marker in parsed.get("flags", ""):
  167. recognition[label] += 1
  168. port, port_country = rules.port(row.get("DISTINATE_PORT"))
  169. record = dict(
  170. parsed,
  171. direction=direction,
  172. year=str(thursday.year),
  173. month=f"{thursday:%Y-%m}",
  174. origin=rules.country(row.get("ORIGIN_COUNTRY")),
  175. trade_country=rules.country(row.get("TRADE_AREA_CODE")),
  176. dispatch=rules.country(row.get("TRADE_COUNTRY")),
  177. port=port,
  178. port_country=port_country,
  179. )
  180. placeholders = ",".join("?" for _ in range(len(DIMENSIONS) + 3))
  181. database.execute(
  182. f"INSERT INTO fact VALUES({placeholders})",
  183. [row_id] + [str(record[key]) for key in DIMENSIONS] + [row["MONORSUM"], row["COUNT"]],
  184. )
  185. database.execute(
  186. "INSERT INTO audit VALUES(?,?,?,?)",
  187. (row_id, hs, row["G_MODEL"], json.dumps(parsed, ensure_ascii=False)),
  188. )
  189. stats["included"] += 1
  190. month = record["month"]
  191. stats["min_month"] = min(stats["min_month"] or month, month)
  192. stats["max_month"] = max(stats["max_month"] or month, month)
  193. if row_id % 10000 == 0:
  194. database.commit()
  195. report(f"已读取 {row_id:,} 条记录", min(0.55, 0.02 + row_id / max(stats["total"], 1) * 0.1))
  196. database.commit()
  197. report("汇总月度中间数据…", 0.6)
  198. dimensions = ",".join(DIMENSIONS)
  199. database.execute(
  200. f"CREATE TABLE monthly AS SELECT {dimensions},dsum(amount) amount,dsum(quantity) quantity,count(*) n "
  201. f"FROM fact GROUP BY {dimensions}"
  202. )
  203. create_monthly_indexes(database)
  204. database.commit()
  205. stats.update(
  206. recognition=dict(recognition),
  207. exclusion_reasons=dict(exclusions),
  208. sha256=sha256_file(source),
  209. cutoff=str(cutoff),
  210. partial_year_rule="仅 confirmed_complete_years 明确确认为全年,其余均为已提供期间暂算",
  211. )
  212. return database, stats
  213. class Store:
  214. """聚合查询封装:只暴露“维度 + 过滤 → 金额/数量/记录数”。"""
  215. def __init__(self, database: sqlite3.Connection) -> None:
  216. self.db = database
  217. def aggregate(self, dimensions: tuple = (), filters: dict | None = None) -> Iterator[dict]:
  218. dimensions = tuple(dimensions)
  219. if any(key not in DIMENSIONS for key in dimensions):
  220. raise ValueError(f"非法维度: {dimensions}")
  221. filters = filters or {}
  222. if any(key not in DIMENSIONS for key in filters):
  223. raise ValueError(f"非法过滤字段: {filters}")
  224. clause = " AND ".join(f"{key}=?" for key in filters) or "1=1"
  225. select = (",".join(dimensions) + ",") if dimensions else ""
  226. group = " GROUP BY " + ",".join(dimensions) if dimensions else ""
  227. query = f"SELECT {select}dsum(amount) amount,dsum(quantity) quantity,sum(n) n FROM monthly WHERE {clause}{group}" + (" ORDER BY " + ",".join(dimensions) if dimensions else "")
  228. for row in self.db.execute(query, list(filters.values())):
  229. result = dict(row)
  230. result["amount"] = number(row["amount"])
  231. result["quantity"] = number(row["quantity"])
  232. result["n"] = row["n"] or 0
  233. yield result
  234. def total(self, filters: dict) -> dict:
  235. return next(self.aggregate(filters=filters))
  236. def week_rows(start: int, end: int) -> Iterator[list]:
  237. """生成“ISO 年周 → 归属年/月”的核对表。"""
  238. for year in range(start - 1, end + 2):
  239. for week in range(1, 54):
  240. try:
  241. thursday = date.fromisocalendar(year, week, 4)
  242. except ValueError:
  243. continue
  244. yield [
  245. year,
  246. week,
  247. str(date.fromisocalendar(year, week, 1)),
  248. str(date.fromisocalendar(year, week, 7)),
  249. str(thursday),
  250. thursday.year,
  251. thursday.month,
  252. "ISO;整周按周四归月",
  253. ]