Monaca(JavaScript)だけでSQL言語を学ぶ方法②

アシアル情報教育研究・所長の岡本です

前回はAlaSQLを使用してJavaScriptだけでSQLを学習する方法を紹介しました。
今回は続編として「結合(JOIN)」や「集計(Group)」を紹介します。

プロジェクトの準備

AlaSQL導入済みのプロジェクトをご用意ください。

データの準備

前回は一つのテーブルに商品名と金額を掲載していましたが、果物の値段というのは仕入れたときによって原価は変わりますし、販売価格も商品の状態などで変わってしまうので別表に分けました。

let items = [
    {id:1, name:"りんご"},
    {id:2, name:"ばなな"},
    {id:3, name:"梅"},
    {id:4, name:"いちご"},
    {id:5, name:"すいか"}            
];
let sales = [
    {id:1, item_id:1, price:200, cost:50, created:'2020-6-12 10:00:00' },
    {id:2, item_id:1, price:180, cost:50, created:'2020-6-12 11:00:00' },
    {id:3, item_id:1, price:250, cost:60, created:'2020-6-13 10:00:00' },
    {id:4, item_id:1, price:200, cost:60, created:'2020-6-13 14:00:00' },
    {id:5, item_id:1, price:150, cost:60, created:'2020-6-13 18:00:00' }
];

items表から価格を外し、id(商品ID)とname(商品名)を記録することにします。

sales表では商品を売ったときの記録を行います。
id(販売ID)とitem_id(商品ID)の他、price(価格)とcost(原価)そしてcreated(販売日時)の列を用意しました。販売日時も記録します。

今回の例では12日に2件、13日に3件商品を売ったことになっています。また、12日の原価は50円、13日の原価は60円と変動しています、そして、販売価格は毎回バラバラです（恐らく、商品の状態や時間帯によって販売価格を変えたのでしょう）。

SQLによるデータ分析

このデータを分析して、どの商品がどれくらい売れているのかを分析してみましょう。なお、サンプルデータはシンプルにするため「りんご」しか売れていないことにしていますが、販売データを増やしたり数字を加工して頂いて構いません。

販売データと商品データを結合する(JOIN)

今の状態では「item_id:1」となっていて、それがりんごなのか、ぱっとみでは良く分かりません。今はデータが少ないので目視で理解できますが、データ数が増えると大変なので、二つの表を結合して商品名を表示させてみましょう。

let result = alasql("SELECT * FROM " 
                    + " ? AS sales INNER JOIN ? items "
                        + " on  sales.item_id = items.id "
                    ,[sales,items]);
console.log(result);

実行結果

0: {id: 1, item_id: 1, price: 200, cost: 50, created: "2020-6-12 10:00:00", …}
1: {id: 1, item_id: 1, price: 180, cost: 50, created: "2020-6-12 11:00:00", …}
2: {id: 1, item_id: 1, price: 250, cost: 60, created: "2020-6-13 10:00:00", …}
3: {id: 1, item_id: 1, price: 200, cost: 60, created: "2020-6-13 14:00:00", …}
4: {id: 1, item_id: 1, price: 150, cost: 60, created: "2020-6-13 18:00:00", …}

列の情報が多すぎて、日付以降の情報がコンソール上に一発では表示しきれなくなりました…

この場合、コンソール上でクリックすれば詳細を確認できます。

あるいは、SELECT文を発行するときに「*」にするのではなく、列名を「選択」すれば余計な情報を削れます。商品名と価格と原価に絞ってみましょう。

販売データと商品データを結合しつつ列を選択して取得

let result = alasql("SELECT items.name,price,cost FROM " 
                    + " ? AS sales INNER JOIN ? items "
                        + " on  sales.item_id = items.id "
                    ,[sales,items]);
console.log(result);

　FROM句でテーブルを指定する際に、JOIN句を併用することで複数のテーブルを指定できます。連結するときに表の名前が必要となるのでAS句で表の名前を設定しています。

　テーブル同士を連結するときのJOIN方法は幾つか存在ますが、例えばINNER JOINでは双方のキーを指定することで、キーが一致した行だけを連結できます。上記の例では 「on sales.item_id = items.id」の部分で双方のキーとなる列を指定しています。

　複数のテーブルを連結する場合、両方のテーブルで同じ列名を使っていると重複します。その場合はテーブル名と列名をセットで指定して参照します。例えば、商品名はitems表のname列のため、items.nameで指定できます。

実行結果

0: {name: "りんご", price: 200, cost: 50}
1: {name: "りんご", price: 180, cost: 50}
2: {name: "りんご", price: 250, cost: 60}
3: {name: "りんご", price: 200, cost: 60}
4: {name: "りんご", price: 150, cost: 60}

集計(Group By)と集計関数で売り上げと原価と利益を計算する

sales表を集計して、トータルの売り上げなどを求めてみようと思います。

let result = alasql("SELECT "
                        + "item_id, "
                        + "SUM(price) AS price, "
                        + "SUM(cost) AS cost, "
                        + "SUM(price) - SUM(cost)  AS profit "
                        + "FROM ? GROUP BY item_id "
                    ,[sales]);
console.log(result);

実行結果

0: {item_id: 1, price: 980, cost: 280, profit: 700}

SELECT句で使用しているSUM()は集計関数の一つで値を合計します。複数の列の値を計算することもできますので、販売価格から原価を引いた粗利を集計するといったことも可能です。また、「SUM(price – cost)」のように記述することもできます。

更に集計

販売価格の最低値・最高値・中央値、それと粗利の平均を求めます。MIN()関数、MAX関数、MEDIAN()関数が使えます、また、粗利の平均はCOUNT()関数とSUM()関数を併用すれば求められます。COUNT()関数は結果の行数を求められます。

let result = alasql("SELECT "
                        + "item_id, "
                        + "MAX(price)    AS max_price, "
                        + "MIN(price)    AS min_price, "
                        + "MEDIAN(price) AS median_price, "
                        + "SUM(price - cost) / COUNT(id)  AS profit_avg "
                        + "FROM ? GROUP BY item_id "
                    ,[sales]);
console.log(result);

実行結果

0: {item_id: 1, max_price: 250, min_price: 150, median_price: 200, profit_avg: 140}

日別の売り上げ集計

全ての売り上げを集計するのではなく、日別で集計することもできます。
Group By句で商品IDと販売日を指定してグルーピングを行います。
なお、今のデータベースには販売日が日付だけでなく時分秒まで記録されているため、年月日だけに型を変換してからグルーピングします。

let result = alasql("SELECT "
                        + "item_id, "
                        + "SUM(price) AS price , "
                        + "SUM(cost) AS cost, "
                        + "SUM(price) - SUM(cost)  AS profit, "
                        + "CAST(created AS DATE) AS created "
                        + "FROM ? GROUP BY item_id, CAST(created AS DATE)"
                    ,[sales]);
console.log(result);

実行結果

0: {item_id: 1, price: 380, cost: 100, profit: 280, created: "2020.06.12"}
1: {item_id: 1, price: 600, cost: 180, profit: 420, created: "2020.06.13"}

集計した結果のテーブルと商品テーブルを結合する

SQLではSELECT句で集計した表を別の表とSQL一本で結合することも可能ですが、SQL文が好きな人以外にはとっつきにくいのと、AlaSQLの場合は分割が行いやすいため、今回は分割して記述してみます。

let sales2 = alasql("SELECT "
                        + "item_id, "
                        + "SUM(price) AS price , "
                        + "SUM(cost) AS cost, "
                        + "SUM(price) - SUM(cost)  AS profit, "
                        + "CAST(created AS DATE) AS created "
                        + "FROM ? GROUP BY item_id, CAST(created AS DATE) "
                    ,[sales]);
let result = alasql("SELECT * FROM " 
                    + " ? AS sales INNER JOIN ? items "
                        + " on  sales.item_id = items.id "
                    ,[sales2,items]);
console.log(result);

1本目のSQL文で売り上げの集計をして結果を変数sales2に格納します。そして、2本目のSQL文でsales2とitemsを結合(JOIN)して商品名を取れるようにします。

実行結果

0: {item_id: 1, price: 380, cost: 100, profit: 280, created: "2020.06.12", …}
1: {item_id: 1, price: 600, cost: 180, profit: 420, created: "2020.06.13", …}