| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256 |
- """端到端口径测试:用样本数据跑完整流程,并对结果做业务断言。
- 预言机来自 ``scripts/gen_sample_data.py`` 写出的 ``*.expected.json``,
- 它只依赖“生成了哪些行”,不依赖解析/分类逻辑,因此能独立校验引擎。
- """
- from __future__ import annotations
- import sqlite3
- import pytest
- TEMPLATE_SHEETS = [
- "1.0-3.0_MLCC",
- "4.1_MLCC",
- "4.2.1_MLCC",
- "4.2.2_MLCC",
- "4.2.3 (1-4)_MLCC",
- "4.2.3 (5)_MLCC",
- "4.3_MLCC",
- "1.0-3.0_存储器",
- "5.1_存储器",
- "5.2.1&3&5_存储器",
- "5.2.2_存储器",
- "5.2.4_存储器",
- "5.2.6_存储器",
- "5.2.7 (1-4)_存储器",
- "5.2.7 (5)_存储器",
- "5.3_存储器",
- ]
- SUPPLEMENTARY_SHEETS = [
- "样本权重",
- "选样排除",
- "重点全量层级",
- "供应链条件占比",
- "周四归月映射",
- "数据质量摘要",
- "映射冲突",
- ]
- def table_for(sheet: str) -> str:
- import re
- cleaned = re.sub(r"[^0-9A-Za-z\u4e00-\u9fff]+", "_", sheet).strip("_")
- return f"s_{cleaned}"
- @pytest.fixture(scope="module")
- def stat_db(processed):
- path = processed["result"].sqlite_path
- connection = sqlite3.connect(path)
- connection.row_factory = sqlite3.Row
- yield connection
- connection.close()
- def test_all_template_sheets_are_present(processed):
- names = [sheet.sheet for sheet in processed["result"].sheets]
- for sheet in TEMPLATE_SHEETS:
- assert sheet in names, f"缺少模板表 {sheet}"
- for sheet in SUPPLEMENTARY_SHEETS:
- assert sheet in names, f"缺少补充表 {sheet}"
- assert len(processed["result"].sheets) == 25
- def test_template_headers_keep_the_canonical_metric_names(stat_db):
- header_row = stat_db.execute(
- 'SELECT * FROM "s_1_0_3_0_MLCC" LIMIT 1'
- ).fetchone()
- columns = list(header_row.keys())
- for expected in ["实际金额(美元)", "成交数量", "平均单价(美元/成交数量单位)", "金额指数", "数量指数", "价格指数", "金额占比", "数量占比", "金额环比", "数量环比", "计算说明"]:
- assert expected in columns
- # 旧模板的“前五大型号”必须被纠正为“前十大型号”
- top_columns = stat_db.execute('SELECT * FROM "s_4_2_3_1_4_MLCC" LIMIT 1').fetchone().keys()
- assert "前十大型号" in top_columns
- assert "前五大型号" not in top_columns
- def test_row_scope_matches_expected_oracle(processed, sample_expected):
- stats = processed["result"].stats
- assert stats["total"] == sample_expected["rows"]
- assert stats["included"] == sample_expected["included"]
- assert stats["excluded"] == sample_expected["excluded"]
- assert stats["min_month"] == sample_expected["months"][0]
- assert stats["max_month"] == sample_expected["months"][1]
- def test_monthly_totals_match_expected_amount(processed, sample_expected):
- working = sqlite3.connect(processed["output"] / "working.sqlite")
- try:
- total = working.execute("SELECT SUM(CAST(amount AS REAL)) FROM monthly").fetchone()[0]
- quantity = working.execute("SELECT SUM(CAST(quantity AS REAL)) FROM monthly").fetchone()[0]
- finally:
- working.close()
- assert total == pytest.approx(sample_expected["amount"], rel=1e-9)
- assert quantity == pytest.approx(sample_expected["quantity"], rel=1e-9)
- def test_product_totals_split_by_first_level_category(stat_db, sample_expected):
- capacitor = stat_db.execute(
- f'SELECT SUM(CAST("_num_实际金额(美元)" AS REAL)) FROM "{table_for("4.1_MLCC")}"'
- ).fetchone()[0]
- memory = stat_db.execute(
- f'SELECT SUM(CAST("_num_实际金额(美元)" AS REAL)) FROM "{table_for("5.1_存储器")}"'
- ).fetchone()[0]
- assert capacitor == pytest.approx(sample_expected["amount_by_c1"]["电容器"], rel=1e-9)
- assert memory == pytest.approx(sample_expected["amount_by_c1"]["存储器"], rel=1e-9)
- def test_base_month_indices_are_exactly_1000(stat_db):
- rows = stat_db.execute(
- f'SELECT * FROM "{table_for("1.0-3.0_MLCC")}" WHERE "年度"="2025" AND "月度"="1"'
- ).fetchall()
- assert rows, "2025-01 应该有指数行"
- for row in rows:
- assert float(row["_num_金额指数"]) == pytest.approx(1000.0, abs=1e-6)
- assert float(row["_num_数量指数"]) == pytest.approx(1000.0, abs=1e-6)
- def test_price_index_at_base_month_is_1000_when_basket_complete(stat_db):
- """样本充足时,加权价格指数在基期必须刚好等于 1000。"""
- rows = stat_db.execute(
- f'SELECT * FROM "{table_for("1.0-3.0_MLCC")}" '
- 'WHERE "年度"="2025" AND "月度"="1" AND "基础类目" LIKE "%MLCC%"'
- ).fetchall()
- assert rows, "缺少 MLCC 基期行"
- price = rows[0]["_num_价格指数"]
- assert price is not None, f"价格指数不应为空:{dict(rows[0])}"
- assert float(price) == pytest.approx(1000.0, abs=1e-6)
- def test_price_basket_weights_sum_to_one(stat_db):
- rows = stat_db.execute(
- f'SELECT "基础类目","选样年度",SUM(CAST("_num_权重" AS REAL)) s,COUNT(*) n FROM "{table_for("样本权重")}" GROUP BY "基础类目","选样年度"'
- ).fetchall()
- assert rows
- for row in rows:
- assert row["s"] == pytest.approx(1.0, abs=1e-9), f"{row['基础类目']} {row['选样年度']} 权重合计 {row['s']}"
- assert row["n"] <= 100
- def test_price_basket_cap_is_respected(stat_db):
- rows = stat_db.execute(
- f'SELECT "基础类目","选样年度",MAX(CAST("_num_权重" AS REAL)) m,COUNT(*) n FROM "{table_for("样本权重")}" GROUP BY "基础类目","选样年度"'
- ).fetchall()
- for row in rows:
- if row["n"] >= 10:
- assert row["m"] <= 0.1 + 1e-12
- def test_annual_structure_shares_sum_to_one(stat_db):
- rows = stat_db.execute(
- f'SELECT "年度",SUM(CAST("_num_金额占比" AS REAL)) s FROM "{table_for("4.2.1_MLCC")}" GROUP BY "年度"'
- ).fetchall()
- assert rows
- for row in rows:
- assert row["s"] == pytest.approx(1.0, abs=1e-9)
- def test_nested_analysis_has_other_bucket_and_top10(stat_db):
- rows = stat_db.execute(
- f'SELECT "统计层级","应用领域标签","品牌国别","品牌","前十大型号" FROM "{table_for("4.2.3 (1-4)_MLCC")}"'
- ).fetchall()
- levels = {row["统计层级"] for row in rows}
- assert {"应用领域标签", "品牌国别", "品牌", "前十大型号"} <= levels
- flattened = [str(value) for row in rows for value in tuple(row)[1:] if value is not None]
- assert any("其他" in value for value in flattened), "缺少“其他”汇总桶"
- models = stat_db.execute(
- f"""SELECT DISTINCT "品牌","前十大型号" FROM "{table_for("4.2.3 (1-4)_MLCC")}" WHERE "统计层级"='前十大型号'"""
- ).fetchall()
- per_brand: dict[str, int] = {}
- for row in models:
- per_brand[row["品牌"]] = per_brand.get(row["品牌"], 0) + 1
- assert per_brand, "缺少型号层级"
- assert max(per_brand.values()) <= 11 # 10 个型号 + “其他(未入选合计)”
- def test_monthly_model_sheet_has_two_scope_versions(stat_db):
- rows = stat_db.execute(
- f'SELECT DISTINCT "统计范围" FROM "{table_for("4.2.3 (5)_MLCC")}"'
- ).fetchall()
- scopes = {row["统计范围"] for row in rows}
- assert "项目范围全部进口" in scopes
- assert "限定入选路径" in scopes
- def test_supply_chain_sheet_covers_both_port_granularities(stat_db):
- rows = stat_db.execute(
- f'SELECT DISTINCT "港口统计粒度" FROM "{table_for("4.3_MLCC")}"'
- ).fetchall()
- granularities = {row["港口统计粒度"] for row in rows}
- assert {"具体港口", "港口国家地区"} <= granularities
- def test_condition_share_sheet_has_two_denominator_modes(stat_db):
- rows = stat_db.execute(
- f'SELECT DISTINCT "分析方式" FROM "{table_for("供应链条件占比")}"'
- ).fetchall()
- modes = {row["分析方式"] for row in rows}
- assert {"完整前缀条件", "品牌国别条件"} <= modes
- def test_week_mapping_sheet_uses_iso_thursday_rule(stat_db):
- rows = stat_db.execute(
- f'SELECT COUNT(*) c FROM "{table_for("周四归月映射")}" WHERE "规则" LIKE "ISO%"'
- ).fetchone()
- assert rows["c"] > 100
- def test_quality_sheet_reports_required_metadata(stat_db):
- items = {
- row["项目"]: row["值"]
- for row in stat_db.execute(f'SELECT "项目","值" FROM "{table_for("数据质量摘要")}"')
- }
- for key in ["币种", "数量单位", "日期口径", "基期", "精度", "国家代码制"]:
- assert key in items
- assert items["币种"] == "USD"
- assert items["基期"] == "2025-01"
- def test_detail_csv_files_are_written(processed):
- output = processed["output"]
- for name in ["issues.csv", "audit.csv", "raw.csv", "monthly.csv", "解析明细.csv", "应用二轮变化.csv", "未识别及提取异常.csv"]:
- assert (output / name).exists(), f"缺少明细文件 {name}"
- def test_excel_workbook_contains_all_sheets(processed):
- from openpyxl import load_workbook
- workbook = load_workbook(processed["result"].xlsx_path, read_only=True)
- try:
- names = workbook.sheetnames
- finally:
- workbook.close()
- assert len(names) == 23
- assert "1.0-3.0_MLCC" in names
- def test_lineage_hashes_are_recorded(processed):
- lineage = processed["result"].lineage
- for key in ["input_sha256", "params_hash", "output_hash", "engine"]:
- assert lineage.get(key)
- assert lineage["output_hash"].startswith("sha256:")
- def test_unparsed_records_are_flagged_for_review(processed):
- import csv
- with (processed["output"] / "未识别及提取异常.csv").open(encoding="utf-8-sig") as handle:
- rows = list(csv.DictReader(handle))
- assert rows, "应该存在需要人工复核的记录"
- assert any(row["flags"] for row in rows)
|