SQL見るだけクエリ#013 売上データを日別と商品毎の販売額でクロス集計 CASE式

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

SQL見るだけクエリ。今回は売上テーブルから、商品別に日ごとの売上額を集計してみようと思います。

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

売上レコードを、日付でグループ化しながら、商品ごとの販売額を合計していきます。最後に日別の販売額合計も計算しています。

また、今回は、SQL文内で、変換をしないといけない箇所が一つあります。日時（日付）の部分です。 テーブル内には、datetimeとして、日時のデータが入っております。このままですと、日付ごとのグループができない為、日時（datetime）を日付の形式に変更して集計をします。

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

SELECT DATE(datetime) AS date , ' . $query_source . ' SUM(price*quantity) AS "total" FROM sql_order GROUP BY date　この部分で、各商品毎の条件に一致していれば販売額、そうで無ければ0 を、日付ごと（実際にはdatetimeの日時データを日付データに変換して）に集計していっています。

今回も前回行った事と同様に、SQL文の中で、長くなるCASEの部分は事前に配列を作って成形しておきます。
コードを簡素化しておくと、こういった変形バージョンを作る際に、非常に可読性が高くなります。

4~10行目がその部分になります。

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

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

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

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

    // SQLを準備し、パラメータをバインドする
    $query = 'SELECT  DATE(datetime) AS date , ' . $query_source . '
    SUM(price*quantity) AS "total" 
    FROM sql_order 
    GROUP BY date';
    $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
        (
            [date] => 2022-12-16
            [1] => 12000
            [2] => 25000
            [3] => 54000
            [total] => 91000
        )

    [1] => Array
        (
            [date] => 2022-12-17
            [1] => 3000
            [2] => 2500
            [3] => 18000
            [total] => 23500
        )

    [2] => Array
        (
            [date] => 2022-12-18
            [1] => 39000
            [2] => 5000
            [3] => 18000
            [total] => 62000
        )

    [3] => Array
        (
            [date] => 2022-12-19
            [1] => 3000
            [2] => 2500
            [3] => 27000
            [total] => 32500
        )

    [4] => Array
        (
            [date] => 2022-12-20
            [1] => 6000
            [2] => 22500
            [3] => 36000
            [total] => 64500
        )

)