SQL見るだけクエリ#012 売上データを購入者と商品・サイズ毎の購入数でクロス集計 CASE式（その２）

記入日：2023-01-12　編集日：2023-01-12

SQL見るだけクエリ。今回は特別に前回のSQL記述の別バージョンを書いてみようと思います。

以下のsql_orderテーブルには、販売商品毎のレコードが入っております。

売上レコードを、顧客でグループ化しながら、各人の商品・サイズ毎の販売個数を合計していきます。最後に各人毎の販売総数も計算しています。

さていつもの様に、テーブルの情報を取得するSQLに関して、注目する部分は、以下の20~23行目です。

SELECT name , ' . $query_source . ' SUM(quantity) AS "total" FROM sql_order GROUP BY order_id　この部分で、各商品毎の条件に一致していれば合計、そうで無ければ0 を、顧客毎（実際にはorder_id毎に）に集計していっています。

今回は前回の記述方法に比べ随分とシンプルになったかと思います。
これは、商品・サイズ毎の個数を１種類ずつ計算するための記述部分を事前に作成し変数という形にしてSQL文を書く事でシンプルになりました。

4~13行目がその部分になります。
この記述の方が、見え方もスッキリし汎用的にもなったかと思います。

try {
    include_once '../model/class.php';

    $query_source = NULL;
    $items = array('sku-0001', 'sku-0002', 'sku-0003');
    $sizes = array('S', 'M', 'L');

    foreach($items as $i => $item_id) {
        foreach($sizes as $size) {
            $query_source .= 'SUM(CASE WHEN item_id = "' . $item_id . '" AND 
            size = "' . $size . '" THEN quantity ELSE 0 END) AS "' . ($i + 1) . $size . '" ,';
        }
    }

    // データベース接続
    $database = new Database();
    $db = $database->connect();

    // SQLを準備し、パラメータをバインドする
    $query = 'SELECT name , ' . $query_source . '
    SUM(quantity) AS "total" 
    FROM sql_order 
    GROUP BY order_id';
    $stmt = $db->prepare($query);

    // 実行する
    $stmt->execute();

    // 結果を取得する
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    print_r($result);

    // データベースから切断する
    $database->disconnect();

} catch(PDOException $e) {
    echo $e->getMessage();
}

これを実行すると、二次元連想配列で、取得することができます。

            Array
(
    [0] => Array
        (
            [name] => 伊藤 明日香
            [1S] => 1
            [1M] => 0
            [1L] => 0
            [2S] => 3
            [2M] => 0
            [2L] => 0
            [3S] => 1
            [3M] => 0
            [3L] => 0
            [total] => 5
        )

    [1] => Array
        (
            [name] => 山口 晴子
            [1S] => 0
            [1M] => 1
            [1L] => 0
            [2S] => 0
            [2M] => 0
            [2L] => 1
            [3S] => 0
            [3M] => 0
            [3L] => 3
            [total] => 5
        )

    [2] => Array
        (
            [name] => 原田 和美
            [1S] => 0
            [1M] => 0
            [1L] => 1
            [2S] => 0
            [2M] => 0
            [2L] => 1
            [3S] => 0
            [3M] => 0
            [3L] => 2
            [total] => 4
        )

    [3] => Array
        (
            [name] => 高橋 由美子
            [1S] => 0
            [1M] => 1
            [1L] => 0
            [2S] => 5
            [2M] => 0
            [2L] => 0
            [3S] => 0
            [3M] => 1
            [3L] => 0
            [total] => 7
        )

    [4] => Array
        (
            [name] => 田中 明日香
            [1S] => 1
            [1M] => 0
            [1L] => 0
            [2S] => 1
            [2M] => 0
            [2L] => 0
            [3S] => 2
            [3M] => 0
            [3L] => 0
            [total] => 4
        )

    [5] => Array
        (
            [name] => 小林 美佐子
            [1S] => 0
            [1M] => 0
            [1L] => 1
            [2S] => 0
            [2M] => 0
            [2L] => 1
            [3S] => 0
            [3M] => 0
            [3L] => 2
            [total] => 4
        )

    [6] => Array
        (
            [name] => 渡辺 雄一
            [1S] => 1
            [1M] => 0
            [1L] => 0
            [2S] => 1
            [2M] => 0
            [2L] => 0
            [3S] => 1
            [3M] => 0
            [3L] => 0
            [total] => 3
        )

    [7] => Array
        (
            [name] => 佐藤 智子
            [1S] => 0
            [1M] => 12
            [1L] => 0
            [2S] => 1
            [2M] => 0
            [2L] => 0
            [3S] => 0
            [3M] => 1
            [3L] => 0
            [total] => 14
        )

    [8] => Array
        (
            [name] => 坂本 純子
            [1S] => 1
            [1M] => 0
            [1L] => 0
            [2S] => 1
            [2M] => 0
            [2L] => 0
            [3S] => 3
            [3M] => 0
            [3L] => 0
            [total] => 5
        )

    [9] => Array
        (
            [name] => 加藤 和子
            [1S] => 0
            [1M] => 0
            [1L] => 1
            [2S] => 0
            [2M] => 0
            [2L] => 8
            [3S] => 0
            [3M] => 0
            [3L] => 1
            [total] => 10
        )

)