MySQL の動的クロス集計

特定の列に未知の数の値を持つテーブル (提供された例の「way」など) を扱う場合、 MySQL でのクロス集計は難しいように思えるかもしれません。従来のアプローチでは、クエリ時に列名を指定する必要がありますが、動的な状況では実現できない可能性があります。

ソリューション アプローチ

この課題に取り組むには、2 つのプログラムによるソリューションを採用できます。 :

1.個別の値のクエリとピボット クエリの構築

このアプローチには、不確実なカウントを持つ列の個別の値をフェッチすることが含まれます。この値のリストを使用すると、動的ピボット クエリを構築し、必要な列を SELECT ステートメントに追加できます。

foreach ($pdo->query("SELECT DISTINCT `way` FROM `MyTable`") as $row) {
  $way = (int) $row["way"];
  $way_array[] = "MAX(IF(`way`=$way, `time`)) AS way_$way";
}
$pivotsql = "SELECT stop, " . join(", ", $way_array) . "FROM `MyTable` GROUP BY `stop`";

2.行ごとにクエリを実行し、ポストプロセスでピボットを実行する

この代替方法には、データベース内で構造化されているデータを行ごとにクエリすることが含まれます。データを配列に収集し、それを表示する前に列にピボット処理します。

$stoparray = array();
foreach ($pdo->query("SELECT * FROM `MyTable`") as $row) {
  $stopkey = $row["stop"];
  if (!array_key_exists($stopkey, $stoparray)) {
    $stoparray[$stopkey] = array("stop"=>$stopkey);
  }
  $waykey = "way_" . $row["way"];
  $stoparray[$stopkey][$waykey] = $row["time"];
}

どちらのアプローチでも、列数が不明な動的な状況に適応するコーディングが必要です。どのアプローチを選択するかは、特定のデータ操作要件と効率性の考慮事項によって異なります。