"""端到端口径测试:用样本数据跑完整流程,并对结果做业务断言。 预言机来自 ``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)