UI設計したり、ウェブシステムを開発したりします。 

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

記入日:2023-01-11 編集日:2023-01-11

SQL見るだけクエリ。今回も通販サイトなどで良くある、売上データを購入者毎にグループ化し、購入商品・サイズ毎の販売数をクロス集計するSQLを記述してみようと思います。

以下のsql_orderテーブルには、販売商品毎のレコードが入っております。Shopifyの受注データもこの様な形式ですね

以下のような、売上テーブルを用意しました。

テーブル名:sql_order

idorder_idnameitem_idpricequantitysizedatetimestatus
11伊藤 明日香sku-000130001S2022-12-16 07:00:001
21伊藤 明日香sku-000225003S2022-12-16 07:00:001
31伊藤 明日香sku-000390001S2022-12-16 07:00:001
42原田 和美sku-000130001L2022-12-16 10:00:001
52原田 和美sku-000225001L2022-12-16 10:00:001
62原田 和美sku-000390002L2022-12-16 10:00:001
73高橋 由美子sku-000130001M2022-12-16 18:00:001
83高橋 由美子sku-000225005S2022-12-16 18:00:001
93高橋 由美子sku-000390001M2022-12-16 18:00:001
104田中 明日香sku-000130001S2022-12-16 22:00:001
114田中 明日香sku-000225001S2022-12-16 22:00:001
124田中 明日香sku-000390002S2022-12-16 22:00:001
135小林 美佐子sku-000130001L2022-12-17 10:00:001
145小林 美佐子sku-000225001L2022-12-17 10:00:001
155小林 美佐子sku-000390002L2022-12-17 10:00:001
166渡辺 雄一sku-000130001S2022-12-18 08:00:001
176渡辺 雄一sku-000225001S2022-12-18 08:00:001
186渡辺 雄一sku-000390001S2022-12-18 08:00:001
197佐藤 智子sku-0001300012M2022-12-18 15:00:001
207佐藤 智子sku-000225001S2022-12-18 15:00:001
217佐藤 智子sku-000390001M2022-12-18 15:00:001
228坂本 純子sku-000130001S2022-12-19 20:00:001
238坂本 純子sku-000225001S2022-12-19 20:00:001
248坂本 純子sku-000390003S2022-12-19 20:00:001
259加藤 和子sku-000130001L2022-12-20 10:00:001
269加藤 和子sku-000225008L2022-12-20 10:00:001
279加藤 和子sku-000390001L2022-12-20 10:00:001
2810山口 晴子sku-000130001M2022-12-20 19:00:001
2910山口 晴子sku-000225001L2022-12-20 19:00:001
3010山口 晴子sku-000390003L2022-12-20 19:00:001

この記事を読むと(約5分)
CASE式を使った、クロス集計の基本形を知る事ができます。購入者毎の集計や、販売日毎の集計なども、同じような形で実現できます。

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

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

SELECT name , SUM(CASE WHEN item_id = "sku-0001" AND size = "S" THEN quantity ELSE 0 END) AS "1S", SUM(CASE WHEN item_id = "sku-0001" AND size = "M" THEN quantity ELSE 0 END) AS "1M", SUM(CASE WHEN item_id = "sku-0001" AND size = "L" THEN quantity ELSE 0 END) AS "1L", SUM(CASE WHEN item_id = "sku-0002" AND size = "S" THEN quantity ELSE 0 END) AS "2S", SUM(CASE WHEN item_id = "sku-0002" AND size = "M" THEN quantity ELSE 0 END) AS "2M", SUM(CASE WHEN item_id = "sku-0002" AND size = "L" THEN quantity ELSE 0 END) AS "2L", SUM(CASE WHEN item_id = "sku-0003" AND size = "S" THEN quantity ELSE 0 END) AS "3S", SUM(CASE WHEN item_id = "sku-0003" AND size = "M" THEN quantity ELSE 0 END) AS "3M", SUM(CASE WHEN item_id = "sku-0003" AND size = "L" THEN quantity ELSE 0 END) AS "3L", SUM(quantity) AS "total" FROM sql_order GROUP BY order_id この部分で、各商品毎の条件に一致していれば合計、そうで無ければ0 を、顧客毎(実際にはorder_id毎に)に集計していっています。

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

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

    // SQLを準備し、パラメータをバインドする
    $query = 'SELECT name , 
    SUM(CASE WHEN item_id = "sku-0001" AND size = "S" THEN quantity ELSE 0 END) AS "1S",
    SUM(CASE WHEN item_id = "sku-0001" AND size = "M" THEN quantity ELSE 0 END) AS "1M",
    SUM(CASE WHEN item_id = "sku-0001" AND size = "L" THEN quantity ELSE 0 END) AS "1L",
    SUM(CASE WHEN item_id = "sku-0002" AND size = "S" THEN quantity ELSE 0 END) AS "2S",
    SUM(CASE WHEN item_id = "sku-0002" AND size = "M" THEN quantity ELSE 0 END) AS "2M",
    SUM(CASE WHEN item_id = "sku-0002" AND size = "L" THEN quantity ELSE 0 END) AS "2L",
    SUM(CASE WHEN item_id = "sku-0003" AND size = "S" THEN quantity ELSE 0 END) AS "3S",
    SUM(CASE WHEN item_id = "sku-0003" AND size = "M" THEN quantity ELSE 0 END) AS "3M",
    SUM(CASE WHEN item_id = "sku-0003" AND size = "L" THEN quantity ELSE 0 END) AS "3L",
    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
        )

)
			

売上一覧
顧客x販売数のクロス集計表

名前 1S 1M 1L 2S 2M 2L 3S 3M 3L 合計数
伊藤 明日香
1
0
0
3
0
0
1
0
0
5
山口 晴子
0
1
0
0
0
1
0
0
3
5
原田 和美
0
0
1
0
0
1
0
0
2
4
高橋 由美子
0
1
0
5
0
0
0
1
0
7
田中 明日香
1
0
0
1
0
0
2
0
0
4
小林 美佐子
0
0
1
0
0
1
0
0
2
4
渡辺 雄一
1
0
0
1
0
0
1
0
0
3
佐藤 智子
0
12
0
1
0
0
0
1
0
14
坂本 純子
1
0
0
1
0
0
3
0
0
5
加藤 和子
0
0
1
0
0
8
0
0
1
10

SQLの使いどころ

サイトに大量の情報を掲載することは、ユーザーにとってとても便利なことです。しかし、情報が多すぎると、目的の情報を見つけるのが困難になります。そのため、SQLを使用することで、情報を効率的に管理することができます。

SQLを使用することで、データベース内の情報を検索したり、フィルタリングしたりすることができます。これにより、ユーザーは目的の情報をスムーズかつ簡単に見つけることができます。

また、SQLを使用することで、データを集計したり、統合したりすることもできます。これにより、ユーザーはさまざまな視点から情報を分析することができます。つまり、サイトに多くの情報を掲載することが重要である一方で、その情報を効率的かつ簡単に管理することが求められる場合には、SQLが不可欠であるといえます。

KK

機械工学を専攻。工業デザイナーとして、国内及び海外の自動車・搬送ラインの設計などに従事した後、2003年にウェブシステム会社を設立。UI設計やウェブシステムの開発、DX化のディレクションなどを行っています。