【PHP8.x】Pdo\Sqlite::createAggregate()メソッドの使い方

Copy
1<?php
2
3/**
4 * SQLite データベースでカスタム集約関数を定義し、使用するサンプルコードです。
5 *
6 * この例では、PDO::sqliteCreateAggregate メソッドを使用して、
7 * 数値の平均を計算する 'MY_AVG' というカスタム集約関数を登録します。
8 * これは、PostgreSQL の CREATE AGGREGATE コマンドのように、
9 * データベースに新しい集約ロジックを追加する概念と類似しています。
10 *
11 * システムエンジニアを目指す初心者の方へ:
12 * データベースには SUM, AVG, COUNT などの標準的な集約関数が組み込まれていますが、
13 * これでは表現できない独自の計算をグループごとに実行したい場合に、
14 * カスタム集約関数を定義することができます。
15 * ここでは、PHPのコードでそのカスタムロジックを実装し、データベースに登録します。
16 */
17function createAndUseSqliteCustomAggregate(): void
18{
19    try {
20        // 1. SQLite インメモリデータベースに接続
21        //    'sqlite::memory:' を使用することで、物理的なファイルを作成せずに
22        //    一時的なデータベースをメモリ上に作成し、スクリプトの終了時に破棄します。
23        $pdo = new PDO('sqlite::memory:');
24        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
25
26        echo "SQLite インメモリデータベースに接続しました。\n";
27
28        // 2. カスタム集約関数 'MY_AVG' を登録
29        //    この関数は、引数として与えられた数値の平均を計算します。
30        //
31        //    - 'MY_AVG': SQL クエリで使用するカスタム関数の名前。
32        //    - $step_func: 各行が処理されるたびに呼び出されるコールバック関数。
33        //                 集約の途中経過 (現在の合計値とカウント) を保持・更新します。
34        //                 第一引数: 現在の集約状態 (配列)
35        //                 第二引数: カラムの値 (例: price)
36        //                 戻り値: 更新された集約状態の配列
37        //    - $finalize_func: すべての行が処理された後に呼び出されるコールバック関数。
38        //                     最終的な集約結果を計算し、返します。
39        //                     第一引数: 最終的な集約状態の配列
40        //                     戻り値: 最終結果 (平均値)
41        //    - 1: 'MY_AVG' 関数が受け取る引数の数。この例では price カラムの1つです。
42        $success = $pdo->sqliteCreateAggregate(
43            'MY_AVG',
44            // step_func: 各行の処理
45            function (array $context, $value): array {
46                // 初期状態の場合、合計とカウントを0で初期化
47                if (!isset($context['sum'])) {
48                    $context['sum'] = 0;
49                    $context['count'] = 0;
50                }
51                // 値が数値の場合のみ合計とカウントを更新
52                if (is_numeric($value)) {
53                    $context['sum'] += (float)$value;
54                    $context['count']++;
55                }
56                return $context;
57            },
58            // finalize_func: 最終結果の計算
59            function (array $context): float {
60                // データがあり、カウントが0より大きい場合のみ平均を計算
61                if (isset($context['count']) && $context['count'] > 0) {
62                    return $context['sum'] / $context['count'];
63                }
64                return 0.0; // データがない場合やエラーの場合は0を返す
65            },
66            1 // MY_AVG 関数は1つの引数 (例: price) を受け取ります
67        );
68
69        if (!$success) {
70            throw new Exception("カスタム集約関数 'MY_AVG' の登録に失敗しました。");
71        }
72        echo "カスタム集約関数 'MY_AVG' を登録しました。\n";
73
74        // 3. テスト用のテーブルを作成
75        $pdo->exec("
76            CREATE TABLE IF NOT EXISTS products (
77                id INTEGER PRIMARY KEY AUTOINCREMENT,
78                name TEXT NOT NULL,
79                price REAL NOT NULL
80            );
81        ");
82        echo "テーブル 'products' を作成しました。\n";
83
84        // 4. テストデータを挿入
85        //    既存データがあれば削除し、新しいデータを挿入します。
86        $pdo->exec("DELETE FROM products;");
87        $stmt = $pdo->prepare("INSERT INTO products (name, price) VALUES (?, ?)");
88        $testProducts = [
89            ['Laptop', 1200.50],
90            ['Mouse', 25.00],
91            ['Keyboard', 75.25],
92            ['Monitor', 300.75],
93            ['Webcam', 50.00],
94            ['Headphones', 150.99]
95        ];
96        foreach ($testProducts as [$name, $price]) {
97            $stmt->execute([$name, $price]);
98        }
99        echo count($testProducts) . "件のテストデータを挿入しました。\n";
100
101        // 5. カスタム集約関数 'MY_AVG' を使用して平均価格を計算
102        echo "\n--- カスタム集約関数 'MY_AVG' の使用例 ---\n";
103        $stmt = $pdo->query("SELECT MY_AVG(price) AS average_price FROM products;");
104        $customAverage = $stmt->fetchColumn();
105
106        echo "カスタム関数 MY_AVG(price) による平均価格: " . sprintf("%.2f", $customAverage) . "\n";
107
108        // 参考: SQLite 標準の AVG 関数と比較
109        $stmt = $pdo->query("SELECT AVG(price) AS standard_average FROM products;");
110        $standardAverage = $stmt->fetchColumn();
111
112        echo "標準関数 AVG(price) による平均価格: " . sprintf("%.2f", $standardAverage) . "\n";
113
114        // 結果の比較
115        if (abs($customAverage - $standardAverage) < 0.00001) {
116            echo "カスタム関数 'MY_AVG' の結果は標準関数 'AVG' と一致します。\n";
117        } else {
118            echo "カスタム関数 'MY_AVG' の結果が標準関数 'AVG' と一致しません！\n";
119        }
120
121    } catch (PDOException $e) {
122        // PDO関連のエラーが発生した場合
123        error_log("データベースエラー: " . $e->getMessage());
124        echo "データベースエラーが発生しました: " . $e->getMessage() . "\n";
125    } catch (Exception $e) {
126        // その他のPHPエラーが発生した場合
127        error_log("アプリケーションエラー: " . $e->getMessage());
128        echo "アプリケーションエラーが発生しました: " . $e->getMessage() . "\n";
129    }
130}
131
132// サンプルコードを実行
133createAndUseSqliteCustomAggregate();

PDO::sqliteCreateAggregateメソッドは、PHPのPDO拡張機能を通じてSQLiteデータベースに独自の集約関数を定義するために使用されます。これは、データベースに組み込まれているSUMやAVGなどの標準関数では表現できない、独自の集計ロジックをSQLクエリ内で利用可能にする機能です。PostgreSQLのCREATE AGGREGATEコマンドと同様に、カスタムの集約処理をデータベースに組み込む概念と似ています。

このメソッドは、第一引数にSQLクエリで呼び出すカスタム関数の名前（$name）、第二引数に集約対象の各行が処理されるたびに呼び出される$stepコールバック関数、第三引数にすべての行の処理が完了した後に最終的な集約結果を計算する$finalizeコールバック関数、そして第四引数にカスタム関数が受け取る引数の数（$numArgs）を指定します。$step関数は集約の途中経過を保持・更新し、$finalize関数はその最終的な集約状態から結果を導き出します。メソッドの実行が成功するとtrueを、失敗するとfalseを返します。

サンプルコードでは、このメソッドを使って数値の平均を計算するMY_AVGというカスタム集約関数をSQLiteインメモリデータベースに登録しています。$step関数で各行の値を合計しカウントを増やし、$finalize関数でその合計とカウントから平均値を算出しています。登録後、このMY_AVG関数をSQLクエリで使用して商品の平均価格を計算し、標準のAVG関数と比較することで、カスタム関数の動作を確認しています。

Copy
1<?php
2
3/**
4 * PDO_SQLite::createAggregate() メソッドを使用して、カスタム集約関数を登録し、
5 * SQLクエリで利用するサンプルコードです。
6 *
7 * この例では、SQLiteデータベースに対して、指定されたカラムのすべての文字列を
8 * カンマ区切りで連結するカスタム集約関数 'CONCAT_ALL' を作成します。
9 * システムエンジニアを目指す初心者の方にも理解しやすいように、
10 * 各ステップにコメントを付記しています。
11 */
12function demonstrateSqliteCustomAggregate(): void
13{
14    // 1. SQLiteのインメモリデータベースに接続
15    // ':memory:' を使用することで、ファイルを作成せずに一時的なデータベースを使用します。
16    // エラーが発生した場合はPDOExceptionをキャッチするために、try-catchブロックを使用します。
17    try {
18        $pdo = new PDO('sqlite::memory:');
19        // エラーモードを設定し、SQL実行エラー時に例外をスローするようにします。
20        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
21
22        echo "SQLite インメモリデータベースに接続しました。\n";
23
24        // 2. カスタム集約関数 'CONCAT_ALL' の登録
25        // 集約関数は2つのコールバック関数で構成されます:
26        //   - ステップ関数: 各行で呼び出され、集約の状態を更新します。
27        //   - ファイナライズ関数: すべての行が処理された後に呼び出され、最終結果を返します。
28        $aggregateName = 'CONCAT_ALL';
29
30        // ステップ関数: 集約の各ステップで実行されます。
31        // $context: 集約の状態を保持する変数。初回はnull。
32        // $value: 現在処理している行から渡される値（この例ではitem_name）。
33        $stepFunction = function ($context, $value) {
34            // $contextがnullの場合（集約の初回呼び出し）は、最初の値で初期化します。
35            if ($context === null) {
36                return (string)$value;
37            }
38            // 既存の$contextに新しい値をカンマとスペースで区切って追加します。
39            return $context . ', ' . (string)$value;
40        };
41
42        // ファイナライズ関数: すべての行が処理された後に、最終結果を返します。
43        // $context: ステップ関数によって更新されてきた最終的な集約状態。
44        $finalizeFunction = function ($context) {
45            // $contextがnullの場合（集約対象のデータがなかった場合）は、空文字列を返します。
46            if ($context === null) {
47                return '';
48            }
49            // 最終的な連結文字列を返します。
50            return $context;
51        };
52
53        // PDO::sqliteCreateAggregate() メソッドを使用して、カスタム集約関数をデータベースに登録します。
54        // 引数: 集約関数名、ステップ関数、ファイナライズ関数、引数の数（-1は可変長を意味）
55        $success = $pdo->sqliteCreateAggregate($aggregateName, $stepFunction, $finalizeFunction, 1);
56
57        if ($success) {
58            echo "カスタム集約関数 '{$aggregateName}' をデータベースに登録しました。\n";
59        } else {
60            echo "カスタム集約関数 '{$aggregateName}' の登録に失敗しました。\n";
61            return;
62        }
63
64        // 3. テスト用のテーブルを作成し、データを挿入
65        $pdo->exec("CREATE TABLE products (id INTEGER PRIMARY KEY, category TEXT, item_name TEXT)");
66        echo "テーブル 'products' を作成しました。\n";
67
68        $pdo->exec("INSERT INTO products (category, item_name) VALUES ('Fruit', 'Apple')");
69        $pdo->exec("INSERT INTO products (category, item_name) VALUES ('Fruit', 'Banana')");
70        $pdo->exec("INSERT INTO products (category, item_name) VALUES ('Vegetable', 'Carrot')");
71        $pdo->exec("INSERT INTO products (category, item_name) VALUES ('Fruit', 'Orange')");
72        $pdo->exec("INSERT INTO products (category, item_name) VALUES ('Vegetable', 'Broccoli')");
73        echo "テストデータを挿入しました。\n";
74
75        // 4. 登録したカスタム集約関数を使用してデータを取得
76        // 各カテゴリに属するすべてのアイテム名を 'CONCAT_ALL' 関数で連結します。
77        $statement = $pdo->query("SELECT category, CONCAT_ALL(item_name) AS all_items FROM products GROUP BY category");
78        $results = $statement->fetchAll(PDO::FETCH_ASSOC);
79
80        echo "\n--- カスタム集約関数 '{$aggregateName}' を使用した結果 ---\n";
81        foreach ($results as $row) {
82            echo "カテゴリ: " . $row['category'] . ", アイテム一覧: " . $row['all_items'] . "\n";
83        }
84
85    } catch (PDOException $e) {
86        // データベース関連のエラーが発生した場合、そのメッセージを表示します。
87        echo "データベースエラーが発生しました: " . $e->getMessage() . "\n";
88    } catch (Exception $e) {
89        // その他の予期せぬエラーが発生した場合、そのメッセージを表示します。
90        echo "予期せぬエラーが発生しました: " . $e->getMessage() . "\n";
91    }
92}
93
94// スクリプトの実行を開始します。
95demonstrateSqliteCustomAggregate();

PHP 8で利用できるPDO::sqliteCreateAggregateメソッドは、SQLiteデータベース内で独自の集約関数を定義するために使用されます。これにより、標準のSQL関数では実現できない複雑なデータ集約ロジックをSQLクエリ内で直接利用できるようになります。

このメソッドは、第一引数に登録したい集約関数の名前（文字列 $name）、第二引数に各行で実行されるステップ関数（callable $step）、第三引数にすべての行が処理された後に最終結果を返すファイナライズ関数（callable $finalize）、そして第四引数に集約関数が受け取る引数の数（int $numArgs）を指定します。ステップ関数は集約の各ステップで呼び出され、集約の状態を更新し、ファイナライズ関数はその最終的な状態を元に結果を計算します。メソッドの実行結果は、関数の登録が成功したかどうかを真偽値（bool）で返します。

サンプルコードでは、この機能を利用して、指定されたカラムのすべての文字列をカンマ区切りで連結するCONCAT_ALLというカスタム集約関数をSQLiteデータベースに登録しています。その後、productsテーブルを作成しテストデータを挿入することで、カテゴリごとにアイテム名を連結するクエリを実行し、カスタム集約関数が正しく動作することを示しています。これにより、複雑なデータ変換や集計処理をSQLレベルで柔軟に実現できるメリットを理解できます。