PostgreSQLでクロス集計をしたい

Question

前提・実現したいこと

DBから受信したデータを、日付別、グループ別に集計をして
CSVファイルに格納したいのですが、PHPエラーが発生して上手くいきません。
PHP,PostgreSQLにお詳しい方、ご教授頂きたく存じます。
宜しくお願い致します。

DBデータ

データベース名：m_test2
data1      | item
-----------------
2018-09-01 | item1
2018-09-01 | item1
2018-09-01 | item1
2018-09-01 | item2
2018-09-02 | item1
2018-09-02 | item2
2018-09-02 | item2
2018-09-02 | item3

集計後の理想のCSVファイル

CSVファイル：list.csv

days       | item1 | item2 | item3
2018-09-01 |     3 |     1 |     0
2018-09-02 |     1 |     2 |     1

該当のソースコード

<?php
// PostgreSQLに接続
$conn = pg_connect('host=localhost dbname=test user=XXXX password=XXXX');

$query = "COPY (SELECT * FROM
	crosstab('SELECT data1, item, count(item)
            FROM m_test
            GROUP BY data1, item
            ORDER BY data1, item'
          )
 AS m_test(
        days timestamp,
        item1 bigint,
        item2 bigint,
        item3 bigint
) TO '/Applications/MAMP/htdocs/web/csv/list.csv' CSV HEADER";

 $result = pg_query($conn, $query);
?>

試したこと

「GROUP BY」と「COUNT()」を使用した
日付ごと、アイテムごとの集計までは成功しておりますが、
「crosstab関数」の辺りが上手くいっていないようです。

補足情報（FW/ツールのバージョンなど）

■補足情報
・DBとの連携動作には問題ございません。
・PHPは以下のエラーが発生しております。

■PHPエラー情報
PHP：5.6.3
PostgreSQL： 10.5
MAMP：5.0.1

PHPエラー内容

※エラーが発生している２１行目は、「$result = pg_query($conn, $query);」です

PHP Warning:  pg_query(): Query failed: ERROR:  syntax error at or near "TO"
LINE 12: ) TO '/Applications/MAMP/htdocs/web/csv/list.csv' CSV HEAD...
           ^ in /Applications/MAMP/htdocs/web/index.php on line 21

Answer 1

見当違いだったら申し訳ないのですが下記

crosstab('SELECT data1, item, count(item)
            FROM m_test
            GROUP BY data1, item
            ORDER BY data1, item'
          )

最初のシングルクォートが邪魔してませんか？

Comments

[jam912sh]

ご確認頂き有難うございます！上記のご指摘の箇所、修正致しました。
現象は解消されませんでしたが、貴重な情報頂き有難うございました！

Answer 2

crosstab()内のカラム数とエイリアス内のカラム数が一致していません。

SELECT * FROM
    crosstab('SELECT data1, item, count(item)
            FROM m_test
            GROUP BY data1, item
            ORDER BY data1, item'
          )
 AS m_test(
        days timestamp,
        item1 bigint,
        item2 bigint
   )

こういった類はまずSQL単独で実行してみて意図通りなのかを確認してから埋め込むようにした方が効率がいいですよ。
後はCOPY の後の括弧が閉じていないようです。

Comments

[jam912sh]

ご確認頂き有難うございます。説明が分かりずらく申し訳ございません。
1つのカラム（item）の中を３つに分ける事が目的で御座いまして、
カラム数とエイリアス内のカラム数が一致しない状態で御座います。
（１つのカラムから３つのカラムを作り出すのが目的で御座います。）
また、SQL単独での動作は、日付ごと、アイテムごとの集計までは成功致しております。
「集計後の理想のCSVファイル」を目指しておりますが、お力お借り頂けますと幸いで御座います。
初心者の為、説明が分かりずらいかと存じますが、ご了承頂きたく存じます。

[sazi]

SQLが正しいなら、COPY で、SQLを括った()が合っていないように見えます。
COPYもSQLと同様に実行できますので確認できます。
因みに、指定するパスはPostgresが認識できるパスである必要があります。

[jam912sh]

ご返答有難うございます。上記の件、承知致しました。
今一度、SQLを括った()内に問題がないか調査致します。
ご指摘頂き有難うございました。

[sazi]

webサーバーとDBサーバーが別なら、'/Applications/MAMP/htdocs/web/csv/list.csv'はwebサーバー上のパスに見えるので、マウントされていないとpostgresからは認識できないと思われます。

[jam912sh]

再度のご指摘有難う御座います！
念のため、簡単な別コードにて、csvディレクトにlist.csvが生成できる事は確認致しております。

[jam912sh]

sazi様
以下のサイトを参考に作成致しております。
http://blog.fusic.co.jp/archives/954
参考サイトでは性別のカラムから「男性」と「女性」を生成する流れかと存じますが、
私の場合は、カラムから「アイテム１」「アイテム２」「アイテム３」を生成できればよいので御座いますが、中々うまく動作しない現状で御座います。
まだまだ勉強不足で御座います。ご迷惑をお掛け致します。