# フィールド値クロス集計 — 要件整理と実装方針 操作手順・DB 全体の注意は [crawl-db-operations.md](./crawl-db-operations.md)、テーブル関係は [crawl-db-er.md](./crawl-db-er.md) を参照してください。 ## 1. 目的 `crawl_field_value_field_defs()` で定義済みのフィールドから **行軸・列軸を選び**、件数をマトリクス表示する。**既存の「件数一覧」「値一覧」**（`crawl_field_value_counts.php` / `crawl_field_value_list.php`）の用語・パラメータ方針に揃えつつ、**実体は別 PHP ファイル・別 URL** で追加する。 --- ## 2. 確定したプロダクト要件（回答の整理） | 論点 | 決定内容 | |------|----------| | 集計の単位 | **子テーブルの 1 行**を主たる観測単位とする（後述の「親のみ軸」のときは親 1 行＝1 観測）。 | | JOIN と重複 | **多対一 JOIN で行が膨れて同じ観測が二重に数えられない**ようにする（子を駆動表にする、キーで結合する等）。 | | 軸フィールド | **`crawl_destinations` の列と子テーブル列の両方**を軸候補に含める。 | | 禁止ペア | **実装時の困難さ・意味の曖昧さ**を基準にホワイトリストまたは禁止リストで管理する（初期案は §4）。 | | NULL / 空文字 | **第一版では実装対象外**（SQL 上はそのまま集約されるが、一覧ページと同等の `(NULL)` / `(空文字)` 分岐は必須としない）。 | | 長大な値 | 軸ラベルは **先頭のみ表示し省略**し、**省略していることが分かる表現**を付ける（一覧 URL 用の base64 方針は既存定数 `CRAWL_FIELD_VALUE_B64_MAX_LEN` と整合させる）。 | | カーディナリティ | 行軸・列軸それぞれ **上位 N のカテゴリ**に限定し、それ以外は **「その他」** にまとめる（N は定数または設定で固定）。 | | パフォーマンス | **クエリ実行の上限 60 秒**（PDO / DB セッションの `max_execution_time` 等で揃える）。 | | 結果の返し方 | **ページ分割なし**（行列サイズは上位 N により上限制御）。 | | 表示 | **行合計・列合計・全体合計**を表示。**行合計に対する割合・全体に対する割合**も表示。 | | セルからの遷移 | **詳細一覧**（既存の `crawl_field_value_list.php` と同等の条件で絞った一覧、またはクロス用に拡張した一覧）へリンクする。 | | 行・列の並び | **件数降順**（「その他」は最後など、仕様で固定してよい）。 | | スコープ | **日付の範囲**で対象を絞る。`crawl_destinations` 単体には取込日時列がないため、**`crawl_captures` の日時**（推奨: `imported_at`。必要なら `started_at` も選択可）で `capture_id` 経由に絞り込む。 | | API | **サーバが行列済み JSON** を返す（同期 1 リクエスト、60 秒以内）。 | | アクセス制御 | **URL 直叩き可**（認証なし）。 | | ログ | **クエリ条件（パラメータ）と生成セル数**（および行われた処理の成否）を保存。SQL 全文の常時保存は必須としない。 | | 既存機能 | **用語・URL クエリの命名は既存に揃える**。実装ファイル・エントリ URL は **新規**。`crawl_field_value_field_defs()` を **軸マスタの正本**とする。 | --- ## 3. 集計モデル（実装で守るべき定義） ### 3.1 観測単位と JOIN - **列定義の `table` が `crawl_destinations` のとき** - その軸の値は **destination 1 行につき 1 値**。集計の駆動は `crawl_destinations`（日付フィルタ後）を基点にする。 - **列定義の `table` が子テーブルのとき** - その軸の値は **子 1 行につき 1 値**。集計の駆動は **`FROM 子 INNER JOIN crawl_destinations`**（＋日付のため `INNER JOIN crawl_captures`）を基本形とし、**子の各行が最大 1 回だけ**現れるようにする。 **行軸・列軸の組み合わせごとに「FROM の主表」が変わる**。 | 行軸テーブル | 列軸テーブル | 主な方針（初期） | |--------------|--------------|------------------| | `crawl_destinations` | `crawl_destinations` | `crawl_destinations` に日付 JOIN、`GROUP BY row_col, col_col`。 | | `crawl_destinations` | 子 | `FROM 子 JOIN crawl_destinations`（＋ captures）。行値は親列、列値は子列。**子行数が件数の素**になる。 | | 子 | `crawl_destinations` | 上と対称。 | | 子 | 子（**同一**子テーブル） | 同一行に行軸・列軸の 2 列がある場合のみ意味がある。現行定義では **多くの子は単一テキスト列**のため、**初期実装では未対応または禁止**が現実的。 | | 子 | 子（**別**子テーブル） | 同一 `(capture_id, dest_num)` でデカルト積になりやすく、件数の解釈がブレる。**初期実装では禁止**（§4）。 | ### 3.2 「子の 1 行」と「重複しない」の両立 - 親 1 行に子が複数あるとき、**子を起点**にすれば子行は自然に 1 回ずつ。 - 親列を子行に付けるときは **`(capture_id, dest_num)` で親に JOIN** し、**子行を `GROUP BY` の粒度**に含める（親の値を子行に複写してよい）。 - **決して**「親 × 別子の無制限 JOIN」で行数を増やした結果だけを `COUNT(*)` しない（その場合は §4 の禁止か、別仕様が必要）。 --- ## 4. 禁止ペア（初期案） 実装の困難性・意味の曖昧さを理由に、**第一版では次を禁止**することを推奨する。 1. **異なる子テーブル同士**の組み合わせ（例: `complex_text` × `audience_text`）。 2. **同一子テーブル内で、同一行に存在しない 2 列**を軸にする組み合わせ（現行スキーマでは該当しにくいが、将来列が増えた場合も「1 行に両方ある」ことを検証する）。 3. **クエリが 60 秒を超える**見込みの組み合わせ（事前ヒューリスティックまたは試行で拒否・メッセージ表示）。 **許可する組み合わせ**はコード上でホワイトリスト化するか、上記禁止を満たす場合のみ許可するロジックにすると安全。 --- ## 5. 上位 N・「その他」・マージン・割合 1. **素の集計**で `(row_key, col_key) → count` を得る（キーは正規化済みの文字列またはハッシュ）。 2. **行ごとの合計**で行をソートし、**行軸を上位 N** に切り、残りを **`__OTHER_ROW__`** のような単一行に集約。 3. **列も同様**に上位 N と **`__OTHER_COL__`**。 4. セルは、切り捨てられた行・列に属していたカウントを **対応する「その他」行/列セルに加算**する（実装は「行のみその他」「列のみその他」「両方その他」の 4 象限も考慮）。 5. **JSON** には少なくとも次を含める: - 行列のセル値（件数） - 各行の行合計、各列の列合計、全体合計 - **セルごと**: `count / row_total`、`count / grand_total`（分母 0 は `null` または `0` で統一） - 軸ラベル用の表示文字列（省略済み）と、一覧 URL 生成に必要な **識別子**（既存の `field` / `match_kind` / `value_b64` 等に合わせる） --- ## 6. 日付範囲フィルタ - **推奨**: `crawl_captures.imported_at` が `date_from` / `date_to`（含むかどうかは API で固定）の範囲内の `capture_id` に限定する。 - `crawl_destinations` は `capture_id` で captures に JOIN。子は `crawl_destinations` 経由で同じ制約を継承。 - クエリパラメータ名は既存の `crawl_field_value_list.php` と衝突しないよう、**新規でも意味が通じる名前**にしつつ、ドキュメント上で **「件数一覧と同じ語彙（field / match_kind 等）」** と明記する。 --- ## 7. 画面・API の責務分割 | 層 | 責務 | |----|------| | **新規 PHP（画面）** | 軸選択、日付、N の表示、JSON を受け取り表描画、セルリンク。初回はサーバサイドレンダリング + `fetch` でも可。 | | **新規 PHP（JSON API）** または同一ファイル内 POST 分岐 | パラメータ検証、禁止ペアチェック、SQL 実行、上位 N 集約、行列 JSON 組み立て、**ログ書き込み**。 | | **`crawl_field_value_list.php`（既存）** | 可能なら **クエリパラメータを拡張**し、クロスセル相当の「行値＋列値」条件の一覧を開けるようにする。**拡張が大きい場合**は、クロス専用の一覧 PHP を分け、用語だけ揃える（第二案）。 | セルからの「詳細一覧」は、**既存一覧の条件モデルに載せられるか**を実装前に精査する（2 次元条件は URL 長・base64 制限の影響が大きい）。 --- ## 8. ログ保存 保存する情報の例（個人情報・巨大本文は避ける）: - 日付範囲、行軸ラベル、列軸ラベル、上位 N の値 - 応答に含まれた **セル数**（データ行列のサイズ）、**処理結果**（成功 / タイムアウト / バリデーションエラー） - 任意: 実行時間（ms）、拒否理由コード 保存先は **DB テーブル**、**ファイルログ**、のいずれかを実装フェーズで決定する。 --- ## 9. パラメータ・用語の整合（既存との揃え） - フィールド識別子: 既存どおり **`field`（= `label`）** を使う。 - 値の一致条件: 既存の **`match_kind`** / **`value_b64`** の考え方に合わせ、クロス用に **2 軸分**渡す必要がある場合は、**命名を並べて説明できる形**にする（例: `row_field` / `col_field` と、対応する値ペイロード）。 - 新規 URL は `index.php` や件数一覧からリンク追加する際、**説明文で既存ページとの関係**を一文で示す。 --- ## 10. 実装フェーズの提案 1. **Phase 1**: `crawl_destinations` × `crawl_destinations` のみ + 日付 + 上位 N + 行列 JSON + 簡易表 + ログ。 2. **Phase 2**: 子を含む組み合わせ（親×子、子×親）をホワイトリスト SQL で追加。 3. **Phase 3**: 詳細一覧連携の完成（パラメータ拡張 or 専用一覧）、割合・マージンの UI 調整。 --- ## 11. オープン事項（実装着手時に確定） - **上位 N の N** の既定値と上限。 - **日付の境界**（終日 JST か、UTC か、`date_to` を含むか）。 - **NULL/空を第一版で SQL から除外するか**（要件上は任意だが、セル意味が変わるためドキュメント化）。 - **詳細一覧**を既存 `crawl_field_value_list.php` で賄うか、専用画面にするか。 - **ログの物理保存先**。 以上を前提に、実装時は **`sql/crawl_schema.sql` と `php/crawl_field_value_defs.php` を正**として SQL インジェクション対策（識別子のホワイトリスト化）を既存ページと同一水準に保つこと。