はじめに

通常データベースの内容をCSVなりExcelなりにエクスポートする場合、ひとつのカラムに対してひとつの列が割り当てられ、ひとつのレコードに対して行がひとつ、つまりレコード数が増えると見た目縦に伸びていくような構造に、通常はなると思います。

しかし今回お客様のご要望は縦横が逆で、カラムが縦に並び、1レコードが1列に対応するものでした。

通常
姓	名	住所
山田	一郎	北海道
田中	二郎	沖縄県

今回
姓	山田	田中
名	一郎	二郎
住所	北海道	沖縄県

単純に実装するとリソースや処理時間をかなり消費するので、どうにかする必要がありました。

困難

PHPのCSV関連メソッド(他の言語でもそうではないかと思いますが)は1行分のデータを配列に入れて渡す形になっています。

典型的なユーザーデータを今回の形式で出力するとして、一列分、例えば全ユーザーの名前を配列に入れてメソッドに渡す必要があります。しかし、DBレコードの取得は通常1レコードずつ行うものなので、単純には下記のどちらかになると思います。

全カラム分(全行分)の配列群に1レコードずつデータを追加していって、最終的にCSV全体のデータを作り上げてから出力する
1カラムごとに全ユーザーのデータをループして1行分のデータを作り上げて出力する

今回のケースでは、カラム数(行数)が2,600程度、レコード数(列数)が数千程度でした。
(カラムと書いていますが、一部を除いて実際にはJSONのデータでDB上は1カラムに入っています)

最初のやり方では、総セル数がレコード数5,000として13M個になり、1レコードあたりのデータ量はばらつきがありますがメモリー使用量がギガ単位になることは確実なので、避けたい状況です。

2番目のやり方ではメモリーは少なくて済みますが、データベースから取ってくるレコードの数が上記の総セル数と同じになるので、これもやはり避けたいです。

解決

解決すべき項目は2つあり、

メモリー使用量を抑える
余計なレコードの取得をしない

です。

ひとつの解決法として挙がったのは、あるひとつカラムのデータだけ順に取得する、つまり

select first_name from users order by id;
select last_name from users order by id;
...

のようにして、1行分のデータを順に構築することでしたが、今回JSON内にあるデータが多く含まれ、技術的にはJSON内のデータをピックアップすることはもちろん可能なのですが、SQLのJSON独特の書き方を数千個分対応するのは大変そうなので諦めました。

今回採用したのは、SQLiteのDBを作って通常のCSVのような形でいったんデータを格納して、取り出す時に縦横逆転する、というものでした。

ひとつのセルに対応するようなテーブルをひとつ用意します。

create table summary_data (
  row_no integer not null, 
  col_no integer not null, 
  value text not null
)

次に、通常よくある1レコード1行になるようにデータを格納します。

イメージコード：

$row_no = 0;
foreach ($users as $user) {
  $col_no = 0;
  foreach ($user->cols as $col) {
    insert([
      'row_no' => $row_no,
      'col_no' => $col_no,
      'value' => $col
    ]);

    $col_no++;
  }

  $row_no++;
}

最後に、行番号と列番号の扱いを逆にして(つまり縦横逆転)データを取り出し、CSV化します。

イメージコード：

$row_no = 0;
for (;;) {
  $data = fetch('select value from summary_data where col_no = ? order by row_no', $row_no);
  
  if (!$data) break;
  
  fputcsv($fp, $data);
  
  $row_no++;
}

おわりに

この方法により、

データはファイル格納にし、メモリー使用量を最小限にする
レコード取得回数は一回だけ
仕組みはわりと単純でわかりやすい

という、まぁまぁの結果で実装することができました。

ただし、いったん全セル分のデータをSQLiteに格納するのでそれなりに時間はかかります。
他にもっと良い方法があれば教えて下さい。