記入日:2023-01-13 編集日:2023-01-13
SQL見るだけクエリ。今回は売上テーブルから、商品別に日ごとの売上額を集計してみようと思います。
以下のsql_orderテーブルには、販売商品毎のレコードが入っております。
読み込みテーブルは前回と同じ以下になります。
テーブル名:sql_order
id | order_id | name | item_id | price | quantity | size | datetime | status |
---|---|---|---|---|---|---|---|---|
1 | 1 | 伊藤 明日香 | sku-0001 | 3000 | 1 | S | 2022-12-16 07:00:00 | 1 |
2 | 1 | 伊藤 明日香 | sku-0002 | 2500 | 3 | S | 2022-12-16 07:00:00 | 1 |
3 | 1 | 伊藤 明日香 | sku-0003 | 9000 | 1 | S | 2022-12-16 07:00:00 | 1 |
4 | 2 | 原田 和美 | sku-0001 | 3000 | 1 | L | 2022-12-16 10:00:00 | 1 |
5 | 2 | 原田 和美 | sku-0002 | 2500 | 1 | L | 2022-12-16 10:00:00 | 1 |
6 | 2 | 原田 和美 | sku-0003 | 9000 | 2 | L | 2022-12-16 10:00:00 | 1 |
7 | 3 | 高橋 由美子 | sku-0001 | 3000 | 1 | M | 2022-12-16 18:00:00 | 1 |
8 | 3 | 高橋 由美子 | sku-0002 | 2500 | 5 | S | 2022-12-16 18:00:00 | 1 |
9 | 3 | 高橋 由美子 | sku-0003 | 9000 | 1 | M | 2022-12-16 18:00:00 | 1 |
10 | 4 | 田中 明日香 | sku-0001 | 3000 | 1 | S | 2022-12-16 22:00:00 | 1 |
11 | 4 | 田中 明日香 | sku-0002 | 2500 | 1 | S | 2022-12-16 22:00:00 | 1 |
12 | 4 | 田中 明日香 | sku-0003 | 9000 | 2 | S | 2022-12-16 22:00:00 | 1 |
13 | 5 | 小林 美佐子 | sku-0001 | 3000 | 1 | L | 2022-12-17 10:00:00 | 1 |
14 | 5 | 小林 美佐子 | sku-0002 | 2500 | 1 | L | 2022-12-17 10:00:00 | 1 |
15 | 5 | 小林 美佐子 | sku-0003 | 9000 | 2 | L | 2022-12-17 10:00:00 | 1 |
16 | 6 | 渡辺 雄一 | sku-0001 | 3000 | 1 | S | 2022-12-18 08:00:00 | 1 |
17 | 6 | 渡辺 雄一 | sku-0002 | 2500 | 1 | S | 2022-12-18 08:00:00 | 1 |
18 | 6 | 渡辺 雄一 | sku-0003 | 9000 | 1 | S | 2022-12-18 08:00:00 | 1 |
19 | 7 | 佐藤 智子 | sku-0001 | 3000 | 12 | M | 2022-12-18 15:00:00 | 1 |
20 | 7 | 佐藤 智子 | sku-0002 | 2500 | 1 | S | 2022-12-18 15:00:00 | 1 |
21 | 7 | 佐藤 智子 | sku-0003 | 9000 | 1 | M | 2022-12-18 15:00:00 | 1 |
22 | 8 | 坂本 純子 | sku-0001 | 3000 | 1 | S | 2022-12-19 20:00:00 | 1 |
23 | 8 | 坂本 純子 | sku-0002 | 2500 | 1 | S | 2022-12-19 20:00:00 | 1 |
24 | 8 | 坂本 純子 | sku-0003 | 9000 | 3 | S | 2022-12-19 20:00:00 | 1 |
25 | 9 | 加藤 和子 | sku-0001 | 3000 | 1 | L | 2022-12-20 10:00:00 | 1 |
26 | 9 | 加藤 和子 | sku-0002 | 2500 | 8 | L | 2022-12-20 10:00:00 | 1 |
27 | 9 | 加藤 和子 | sku-0003 | 9000 | 1 | L | 2022-12-20 10:00:00 | 1 |
28 | 10 | 山口 晴子 | sku-0001 | 3000 | 1 | M | 2022-12-20 19:00:00 | 1 |
29 | 10 | 山口 晴子 | sku-0002 | 2500 | 1 | L | 2022-12-20 19:00:00 | 1 |
30 | 10 | 山口 晴子 | sku-0003 | 9000 | 3 | L | 2022-12-20 19:00:00 | 1 |
この記事を読むと(約5分)
CASE式を使った、クロス集計の基本形を知る事ができます。購入者毎の集計や、販売日毎の集計なども、同じような形で実現できます。
売上レコードを、日付でグループ化しながら、商品ごとの販売額を合計していきます。最後に日別の販売額合計も計算しています。
また、今回は、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 ) )
日付 | sku-0001 | sku-0002 | sku-0003 | 合計数 |
---|---|---|---|---|
2022-12-16 |
12,000 |
25,000 |
54,000 |
91,000 |
2022-12-17 |
3,000 |
2,500 |
18,000 |
23,500 |
2022-12-18 |
39,000 |
5,000 |
18,000 |
62,000 |
2022-12-19 |
3,000 |
2,500 |
27,000 |
32,500 |
2022-12-20 |
6,000 |
22,500 |
36,000 |
64,500 |
SQLの使いどころ
サイトに大量の情報を掲載することは、ユーザーにとってとても便利なことです。しかし、情報が多すぎると、目的の情報を見つけるのが困難になります。そのため、SQLを使用することで、情報を効率的に管理することができます。
SQLを使用することで、データベース内の情報を検索したり、フィルタリングしたりすることができます。これにより、ユーザーは目的の情報をスムーズかつ簡単に見つけることができます。
また、SQLを使用することで、データを集計したり、統合したりすることもできます。これにより、ユーザーはさまざまな視点から情報を分析することができます。つまり、サイトに多くの情報を掲載することが重要である一方で、その情報を効率的かつ簡単に管理することが求められる場合には、SQLが不可欠であるといえます。
KK
機械工学を専攻。工業デザイナーとして、国内及び海外の自動車・搬送ラインの設計などに従事した後、2003年にウェブシステム会社を設立。UI設計やウェブシステムの開発、DX化のディレクションなどを行っています。