SQLで数式と集計関数を使用する方法

序章

構造化照会言語（SQL）は、リレーショナルデータベース管理システム（RDBMS）で情報を格納、管理、および整理するために使用されます。 SQLは、式を介して計算を実行し、データを操作することもできます。 式は、さまざまなSQL演算子、関数、および値を組み合わせて、値を計算します。 数式は、数値の加算、減算、除算、および乗算に一般的に使用されます。 さらに、集計関数を使用して値を評価およびグループ化し、特定の列の値の平均や合計などの要約を生成します。 数学的表現と集計式は、将来の意思決定に役立つデータ分析を通じて貴重な洞察を提供できます。

このチュートリアルでは、数式の使用を練習します。 まず、電卓で数値演算を使用し、次にサンプルデータでそれらの演算子を使用して集計関数でクエリを実行し、ビジネスシナリオで終了して、より複雑な情報と分析のためにサンプルデータをクエリします。

前提条件

このチュートリアルを完了するには、次のものが必要です。

• sudo管理者権限とファイアウォールが有効になっているroot以外のユーザーがいるUbuntu20.04を実行しているサーバー。 Ubuntu20.04を使用したサーバーの初期設定に従って開始します。
• MySQLがサーバーにインストールされ、保護されています。 これを設定するには、 Ubuntu20.04ガイドにMySQLをインストールする方法に従ってください。 このガイドは、このガイドのステップ3 で概説されているように、root以外のMySQLユーザーも設定していることを前提としています。

注：多くのRDBMSは、独自のSQL実装を使用していることに注意してください。 このチュートリアルで概説されているコマンドはほとんどのRDBMSで機能しますが、MySQL以外のシステムでテストした場合、正確な構文または出力が異なる場合があります。

このチュートリアルで数式の例の多くを練習するには、サンプルデータがロードされたデータベースとテーブルが必要です。 挿入する準備ができていない場合は、次の MySQLへの接続とサンプルデータベースの設定セクションを読んで、データベースとテーブルを作成する方法を学ぶことができます。 このチュートリアルでは、このサンプルデータベースとテーブル全体を参照します。

このページに埋め込まれているインタラクティブ端末を使用して、このチュートリアルのサンプルクエリを試すこともできます。 次のLaunch an Interactive Terminal!ボタンをクリックして開始します。

インタラクティブターミナルを起動します！

MySQLへの接続とサンプルデータベースの設定

SQLデータベースがリモートサーバーで実行されている場合は、ローカルマシンからサーバーにSSHで接続します。

ssh sammy@your_server_ip

次に、MySQLプロンプトを開き、sammyをMySQLユーザーアカウント情報に置き換えます。 このページに埋め込まれたインタラクティブ端末を使用している場合、プロンプトが表示されたときに使用するパスワードはsecretという単語であることに注意してください。

mysql -u sammy -p

mathDBという名前のデータベースを作成します。

CREATE DATABASE mathDB;

データベースが正常に作成されると、次の出力が表示されます。

OutputQuery OK, 1 row affected (0.01 sec)

mathDBデータベースを選択するには、次のUSEステートメントを実行します。

USE mathDB;

OutputDatabase changed

データベースを選択したら、CREATE TABLEコマンドを使用してデータベース内にテーブルを作成します。 このチュートリアルの例では、product_informationという名前のテーブルを作成して、小さな喫茶店の在庫と販売情報を保存します。 このテーブルには、次の8つの列が含まれます。

• product_id：intデータ型の値を表し、テーブルの主キーとして機能します。 これは、この列の各値がそれぞれの行の一意の識別子として機能することを意味します。
• product_name：最大30文字のvarcharデータ型を使用した製品名の詳細。
• product_type：最大30文字のvarcharデータ型で示されるように、製品のタイプを格納します。
• total_inventory：最大200のintデータ型を使用して、ストレージに残っている各製品のユニット数を表します。
• product_cost：decimalデータ型を使用して、コストで購入した各製品の価格を表示します。左側に最大3つの値、小数点以下に2つの値があります。
• product_retail：decimalデータ型で示されるように、小売店で販売される各製品の価格を格納します。左側に最大3つの値、小数点以下に2つの値があります。
• store_units：intデータ型の値を使用して、店舗の販売在庫に使用できる特定の製品のユニット数を表示します。
• online_units：intデータ型の値を使用して、特定の製品の何ユニットがオンライン販売在庫に利用できるかを表します

次のコマンドを実行して、このサンプルテーブルを作成します。

CREATE TABLE product_information (
product_id int, 
product_name varchar(30), 
product_type varchar(30), 
total_inventory int(200),
product_cost decimal(3, 2), 
product_retail decimal(3, 2), 
store_units int(100),
online_units int(100),
PRIMARY KEY (product_id)
); 

OutputQuery OK, 0 rows affected, 0 warnings (0.01 sec)

次に、いくつかのサンプルデータを空のテーブルに挿入します。

INSERT INTO product_information
(product_id, product_name, product_type, total_inventory, product_cost, product_retail, store_units, online_units)
VALUES
(1, 'chamomile', 'tea', 200, 5.12, 7.50, 38, 52),
(2, 'chai', 'tea', 100, 7.40, 9.00, 17, 27),
(3, 'lavender', 'tea', 200, 5.12, 7.50, 50, 112),
(4, 'english_breakfast', 'tea', 150, 5.12, 7.50, 22, 74),
(5, 'jasmine', 'tea', 150, 6.17, 7.50, 33, 92),
(6, 'matcha', 'tea', 100, 6.17, 7.50, 12, 41),
(7, 'oolong', 'tea', 75, 7.40, 9.00, 10, 29),
(8, 'tea sampler', 'tea', 50, 6.00, 8.50, 18, 25),
(9, 'ceramic teapot', 'tea item', 30, 7.00, 9.75, 8, 15),
(10, 'golden teaspoon', 'tea item', 100, 2.00, 5.00, 18, 67);

OutputQuery OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

データを挿入すると、数式の使用を開始できます。

数式を使用した計算

SQLでは、通常、SELECTを使用してデータベースにクエリを実行し、目的の結果セットを取得します。 ただし、SELECTキーワードを使用して、さまざまな数学演算を実行することもできます。

実際のシナリオでは、SQLは主に、実際のデータベースの値からクエリを実行して計算を行うために使用されることに注意してください。 ただし、このセクションでは、数式と演算子の構文に慣れるために、数値にのみSELECTを使用します。

始める前に、SQLで6つの算術演算を実行するために使用できる演算子の概要を以下に示します。 このリストは包括的ではなく、多くのRDBMSには固有の数学演算子のセットがあることに注意してください。

• 追加は+記号を使用します
• 減算は-記号を使用します
• 乗算は*記号を使用します
• 分割には/記号を使用します
• モジュロ演算は%記号を使用します
• べき乗はPOW(x,y)を使用します

独自の値の組み合わせを使用して、さまざまなタイプの計算を実行する練習をすることができます。 次の例を使用して、加算式から始めて説明します。

SELECT 893 + 579;

Output+-----------+
| 893 + 579 |
+-----------+
|      1472 |
+-----------+
1 row in set (0.00 sec)

データベースからデータを取得しておらず、生の数値のみを計算しているため、このセクションまたはこのセクションの他のクエリ例にFROM句を含める必要はありません。

次に、減算演算子を使用して計算を実行します。 また、次のように小数で値を計算できることに注意してください。

SELECT 437.82 - 66.34;

Output+----------------+
| 437.82 - 66.34 |
+----------------+
|         371.48 |
+----------------+
1 row in set (0.00 sec)

SQLの単一の計算に複数の値と演算子を含めることができます。 次の計算例では、3つの乗算演算子を使用して、4つの数値の積を求めています。

SELECT 60 * 1234 * 2 * 117; 

Output+---------------------+
| 60 * 1234 * 2 * 117 |
+---------------------+
|            17325360 |
+---------------------+
1 row in set (0.00 sec)

次に、次のように、10進値と整数値を組み合わせた除算問題を計算します。

SELECT 2604.56 / 41;

Output+--------------+
| 2604.56 / 41 |
+--------------+
|    63.525854 |
+--------------+
1 row in set (0.00 sec)

もう1つの除算演算子は、%、またはモジュロ演算子です。これは、被除数を除数で除算した後の残りの値を計算します。

SELECT 38 % 5;

Output+--------+
| 38 % 5 |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)

役立つ可能性のある別の演算子は、POW(x,y)です。これは、指定された基本値（x）の指数（y）の累乗を計算します。

SELECT POW(99,9);

Output+---------------------+
| POW(99,9)         |
+---------------------+
| 9.13517247483641e17 |
+---------------------+
1 row in set (0.01 sec)

各演算を単独で計算する練習をしたので、さまざまな数学演算子を組み合わせて、より複雑な数学方程式を練習することができます。

SQLの演算の順序を理解する

PEMDAS という用語はご存知かもしれません。これは、括弧、指数、乗算、除算、加算、および減算を表します。 この用語は、より複雑な方程式を解くために必要な演算の順序のガイドラインとして機能します。 PEMDASは米国で使用される用語ですが、他の国では、操作の順序の規則を表すために異なる頭字語を使用する場合があります。

括弧内にネストされたさまざまな数学演算を組み合わせる場合、SQLはそれらを左から右に読み取り、次に値を内側から外側に向かって読み取ります。 このため、括弧内の値が解決しようとしている問題を正確にキャプチャしていることを確認してください。

括弧といくつかの異なる演算子を使用して計算してみてください。

SELECT (2 + 4 ) * 8; 

Output+-----------+
| (2+4) * 8 |
+-----------+
|        48 |
+-----------+
1 row in set (0.00 sec)

括弧の配置が重要であり、注意しないと、結果全体が変わる可能性があることを忘れないでください。 たとえば、次の例では同じ3つの値と演算子を使用していますが、括弧の配置が異なるため、結果は異なります。

SELECT 2 + (4  * 8); 

Output+-------------+
| 2 + (4 * 8) |
+-------------+
|          34 |
+-------------+
1 row in set (0.00 sec)

括弧なしで計算を実行したい場合は、それも実行できます。 演算の優先順位のルールがまだあることを忘れないでください。 したがって、括弧の配置と同様に、これが評価される操作の順序に基づいて必要な方程式であることを確認してください。 次の例では、除算演算が減算演算子よりも優先され、負の値になることがわかります。

SELECT 100 / 5 - 300;

Output+---------------+
| 100 / 5 - 300 |
+---------------+
|     -280.0000 |
+---------------+
1 row in set (0.00 sec)

数式を数値計算やさまざまな演算子を組み合わせて複雑な計算に使用することに成功しました。 次に、サンプルデータを使用して集計関数で計算を行い、データに関する新しい情報を生成します。

集計関数を使用したデータの分析

あなたが小さな喫茶店のオーナーであり、データベースに保存した情報に関連する計算を実行したいとします。 SQLは数式を使用して、データベーステーブルとさまざまな列からデータを取得することにより、データをクエリおよび操作できます。 これは、分析したいデータに関する新しい情報を生成するのに役立ちます。 このセクションでは、ティーショップのビジネスに関する情報を見つけるために、集計関数を使用してサンプルデータのクエリと操作を練習します。

SQLの主な集計関数には、SUM、MAX、MIN、AVG、およびCOUNTがあります。 SUM関数は、列のすべての値を追加します。 たとえば、SUMを使用して、サンプルデータセットのtotal_inventory列の量を合計します。

SELECT SUM(total_inventory) FROM product_information;

Output+----------------------+
| SUM(total_inventory) |
+----------------------+
|                 1155 |
+----------------------+
1 row in set (0.00 sec)

MAX関数は、選択した列に保持されている最大値を検索します。 この例では、MAXを使用して、product_cost列にリストされている製品に費やされた最大金額を照会し、ASステートメントを使用してヘッダーにラベルを付け直し、詳細を読み取ります。明らかに：

SELECT MAX(product_cost) AS cost_max 
FROM product_information;

Output+----------+
| cost_max |
+----------+
|     7.40 |
+----------+
1 row in set (0.00 sec)

MIN関数は、最小値を計算するため、MAX関数の反対です。 MINを使用して、product_retailに費やされた最小金額を照会します。

SELECT MIN(product_retail) AS retail_min 
FROM product_information;

Output+------------+
| retail_min |
+------------+
|       5.00 |
+------------+
1 row in set (0.00 sec)

AVG関数は、テーブルの指定された列からすべての値の平均を計算します。 また、同じクエリで複数の集計関数を実行できることに注意してください。 クエリを組み合わせて、小売店で販売された製品とコストで購入された製品の平均コストを見つけてみてください。

SELECT AVG(product_retail) AS retail_average, 
AVG(product_cost) AS cost_average 
FROM product_information;

Output+----------------+--------------+
| retail_average | cost_average |
+----------------+--------------+
|       7.875000 |     5.750000 |
+----------------+--------------+
1 row in set (0.00 sec)

COUNT関数は、クエリによって返された行数をカウントすることによってテーブル自体から値を計算するため、他の関数とは動作が異なります。 COUNT関数をWHEREステートメントとともに使用して、小売価格が$8.00を超える製品の数を照会します。

SELECT COUNT(product_retail) 
FROM product_information 
WHERE product_retail > 8.00;

Output+-----------------------+
| COUNT(product_retail) |
+-----------------------+
|                     4 |
+-----------------------+
1 row in set (0.00 sec)

次に、ストアが$8.00以上で購入したproduct_costの商品の数を照会します。

SELECT COUNT(product_cost) 
FROM product_information 
WHERE product_cost > 8.00;

Output+---------------------+
| COUNT(product_cost) |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.00 sec)

集計関数を使用して、最大値、最小値、平均値、カウントなどの値の要約を提供することに成功しました。 サンプルデータからこの情報を取得して、実際のシナリオをシミュレートしました。 最後のセクションでは、数式と集計関数について学んだことをすべて適用して、小さな喫茶店のサンプルデータに対してより詳細なクエリと分析を実行します。

ビジネスシナリオでの数式の適用

このセクションでは、ティーショップのオーナーがビジネスに関連する意思決定を行うのに役立つデータ分析の実行を含むいくつかのシナリオ例を示します。

最初のシナリオとして、在庫で現在利用可能な合計ユニットを計算して、店内およびオンライン販売用に残っている製品の数を理解します。 このクエリには、データを最大量から最小量まで整理するためのDESCステートメントも含まれます。 通常、RDBMSはデフォルトで昇順になりますが、この例には、データを降順で表示できるDESCオプションが含まれています。

SELECT product_name, 
total_inventory - (store_units + online_units) 
AS remaining_inventory 
FROM product_information 
ORDER BY(remaining_inventory) DESC;

Output+-------------------+---------------------+
| product_name      | remaining_inventory |
+-------------------+---------------------+
| chamomile         |                 110 |
| chai              |                  56 |
| english_breakfast |                  54 |
| matcha            |                  47 |
| lavender          |                  38 |
| oolong            |                  36 |
| jasmine           |                  25 |
| golden teaspoon   |                  15 |
| tea sampler       |                   7 |
| ceramic teapot    |                   7 |
+-------------------+---------------------+
10 rows in set (0.00 sec)

このクエリは、残りの在庫を計算するので便利です。これは、ティーショップの所有者が商品が不足している場合に、より多くの注文を購入する計画を立てるのに役立ちます。

次のシナリオでは、店内販売とオンライン販売からの収益額を分析して比較します。

SELECT product_name, 
(online_units * product_retail) AS o, 
(store_units * product_retail) AS s 
FROM product_information;

Output
+-------------------+--------+--------+
| product_name      | o      | s      |
+-------------------+--------+--------+
| chamomile         | 390.00 | 285.00 |
| chai              | 243.00 | 153.00 |
| lavender          | 840.00 | 375.00 |
| english_breakfast | 555.00 | 165.00 |
| jasmine           | 690.00 | 247.50 |
| matcha            | 307.50 |  90.00 |
| oolong            | 261.00 |  90.00 |
| tea sampler       | 212.50 | 153.00 |
| ceramic teapot    | 146.25 |  78.00 |
| golden teaspoon   | 335.00 |  90.00 |
+-------------------+--------+--------+
10 rows in set (0.00 sec)

次に、SUM関数といくつかの数学演算子を使用して、店舗およびオンライン販売からの総収益を計算します。

SELECT SUM(online_units * product_retail) + 
SUM(store_units * product_retail) 
AS total_sales 
FROM product_information;

Output+-------------+
| total_sales |
+-------------+
|     5706.75 |
+-------------+
1 row in set (0.00 sec)

これらのクエリを実行することは、2つの理由で重要です。 第一の理由は、ティーショップのオーナーがどの商品がベストセラーであるかを評価し、将来さらに購入する際にそれらの商品に優先順位を付けることができるようにするためです。 次に、店内およびオンラインでの商品販売で、ティーショップの全体的なパフォーマンスを分析できます。

次に、各製品の利益率を確認します。 特定の製品の利益率は、販売するその製品の各ユニットに対してビジネスが獲得する収益額です。 あなたがどれだけの収入を得たかを理解するために、あなたは売上高に利益率を掛けることができます。

個々の製品の利益率を計算するには、各行のproduct_retailからproduct_costを引きます。 次に、この値を製品小売で割って、利益率のパーセンテージを計算します。

SELECT product_name, 
(product_retail - product_cost) / product_retail 
AS profit_margin
FROM product_information;

Output+-------------------+-------------+
| product_name      | profit_margin |
+-------------------+-------------+
| chamomile         |    0.317333 |
| chai              |    0.177778 |
| lavender          |    0.317333 |
| english_breakfast |    0.317333 |
| jasmine           |    0.177333 |
| matcha            |    0.177333 |
| oolong            |    0.177778 |
| tea sampler       |    0.294118 |
| ceramic teapot    |    0.282051 |
| golden teaspoon   |    0.600000 |
+-------------------+-------------+
10 rows in set (0.00 sec)

この出力に基づいて、利益率が最も高い製品は60％のゴールデンティースプーンであり、最も低い製品は18％のチャイ、ジャスミン、抹茶、ウーロン茶であることがわかります。 ゴールデンティースプーンの場合、これは、小売価格が5.00ドル、利益率が60％の場合、収益が3.00ドルになることを意味します。

集計関数AVGを使用して、ティーショップのすべての製品の平均利益率を計算することもできます。 この平均は、ティーショップの所有者がその数を下回る製品を特定し、改善方法を戦略化するためのベンチマークとして機能します。

SELECT AVG((product_retail - product_cost) / product_retail) 
AS avg_profit_margin 
FROM product_information;

Output+-------------------+
| avg_profit_margin |
+-------------------+
|   0.2838391151 |
+-------------------+
1 row in set (0.00 sec)

この計算から、このティーショップの製品の平均利益率は28％であると結論付けることができます。

この新しい情報を使用して、現在27％未満の利益率を持つ製品について、ティーショップの所有者が次の四半期に利益率を31％に増やしたいと考えていると想像してください。 これを行うには、目標利益率を1（1 - 0.31）から差し引き、返品された各製品のコストをこの値で除算します。 その結果、31％の利益率を達成するために、製品が小売店で販売しなければならない新しい価格になります。

SELECT product_name, product_cost / (1 - 0.31) 
AS new_retail 
FROM product_information 
WHERE (product_retail - product_cost) / product_retail < 0.27;

Output+--------------+------------+
| product_name | new_retail |
+--------------+------------+
| chai      |  10.724638 |
| jasmine   |   8.942029 |
| matcha    |   8.942029 |
| oolong    |  10.724638 |
+--------------+------------+
4 rows in set (0.00 sec)

これらの結果は、業績の悪い製品が31％の利益率を達成するために必要な新しい小売価格を示しています。 このようなデータ分析により、ティーショップの所有者は、次の四半期の収益を改善する方法について決定的なビジネス上の決定を下し、何を目指すべきかを理解することができます。

結論

SQLで数式を使用する場合は、電卓のように算術問題を解決することから、ビジネス上の意思決定に影響を与える可能性のある実際のデータに対して複雑な分析を実行することまで、さまざまです。 主要な数学演算子と演算の優先順位の規則を認識できれば、計算の可能性は無限大です。 また、データをさらに分析したい場合は、これらの演算子を集計関数とともに使用すると、戦略的計画に役立つ可能性のある「whatif」の質問に対する回答を計算できます。 SQLの使用方法のシリーズで、SQLのデータベースでできることの詳細をご覧ください。