SQLで数式と集計関数を使用する方法
序章
構造化照会言語(SQL)は、リレーショナルデータベース管理システム(RDBMS)で情報を格納、管理、および整理するために使用されます。 SQLは、式を介して計算を実行し、データを操作することもできます。 式は、さまざまなSQL演算子、関数、および値を組み合わせて、値を計算します。 数式は、数値の加算、減算、除算、および乗算に一般的に使用されます。 さらに、集計関数を使用して値を評価およびグループ化し、特定の列の値の平均や合計などの要約を生成します。 数学的表現と集計式は、将来の意思決定に役立つデータ分析を通じて貴重な洞察を提供できます。
このチュートリアルでは、数式の使用を練習します。 まず、電卓で数値演算を使用し、次にサンプルデータでそれらの演算子を使用して集計関数でクエリを実行し、ビジネスシナリオで終了して、より複雑な情報と分析のためにサンプルデータをクエリします。
前提条件
このチュートリアルを完了するには、次のものが必要です。
- Ubuntu 20.04を実行しているサーバーで、root以外のユーザーが
sudo
管理者権限とファイアウォールが有効になっています。 Ubuntu20.04を使用したサーバーの初期設定に従って開始します。 - MySQLがサーバーにインストールされ、保護されています。 これを設定するには、 Ubuntu20.04ガイドにMySQLをインストールする方法に従ってください。 このガイドは、このガイドのステップ3 で概説されているように、root以外のMySQLユーザーも設定していることを前提としています。
注:多くのRDBMSは、独自のSQL実装を使用していることに注意してください。 このチュートリアルで概説されているコマンドはほとんどのRDBMSで機能しますが、MySQL以外のシステムでテストした場合、正確な構文または出力が異なる場合があります。
このチュートリアルで数式の例の多くを練習するには、サンプルデータがロードされたデータベースとテーブルが必要です。 挿入する準備ができていない場合は、次の MySQLへの接続とサンプルデータベースの設定セクションを読んで、データベースとテーブルを作成する方法を学ぶことができます。 このチュートリアルでは、このサンプルデータベースとテーブル全体を参照します。
MySQLへの接続とサンプルデータベースの設定
SQLデータベースがリモートサーバーで実行されている場合は、ローカルマシンからサーバーにSSHで接続します。
- ssh sammy@your_server_ip
Next, open the MySQL prompt, replacing sammy with your MySQL user account information:
- 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
:を使用して製品の名前を詳しく説明しますvarchar
最大30文字のデータ型。product_type
:によって示されるように、製品のタイプを格納しますvarchar
最大30文字のデータ型。total_inventory
:を使用して、各製品のユニットがストレージに残っている数を表しますint
最大200のデータ型。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)
別の除算演算子は %
、またはモジュロ演算子。被除数を除数で除算した後の残りの値を計算します。
- SELECT 38 % 5;
Output+--------+
| 38 % 5 |
+--------+
| 3 |
+--------+
1 row in set (0.00 sec)
役に立つかもしれない別の演算子は POW(x,y)
、指数のべき乗を計算します(y
)指定された基本値(x
):
- 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
. The SUM
関数は、列のすべての値を追加します。 たとえば、 SUM
の金額を合計するには total_inventory
サンプルデータセットの列:
- SELECT SUM(total_inventory) FROM product_information;
Output+----------------------+
| SUM(total_inventory) |
+----------------------+
| 1155 |
+----------------------+
1 row in set (0.00 sec)
The 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)
The 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)
The 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)
The 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)
次に、からの製品の数を照会します product_cost
ストアが8.00ドル以上で購入したもの:
- 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_cost
から product_retail
行ごとに。 次に、この値を製品小売で割って、利益率のパーセンテージを計算します。
- 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のデータベースでできることの詳細をご覧ください。