MySQL 派生テーブル（Derived Table）の簡単な使用例分析

この記事では、例を使用して、MySQL 派生テーブルの簡単な使用方法を説明します。ご参考までに、詳細は以下の通りです。

この派生テーブルに関しては、まず、派生テーブルが SELECT ステートメントから返される仮想テーブルであることを知っておく必要があります。派生テーブルは一時テーブルに似ていますが、派生テーブルを SELECT ステートメントで使用すると、一時テーブルを作成する手順が不要なため、一時テーブルを使用するよりもはるかに簡単です。したがって、SELECT ステートメントの FROM 句でスタンドアロンサブクエリが使用される場合、それを派生テーブルと呼びます。では、早速詳しく説明しましょう。

選択 
  列リスト
から
* (選択 
* 列リスト
* から
* table_1) 派生テーブル名;
WHERE derived_table_name.column > 1...

アスタリスクでマークされた場所は派生テーブルが使用される場所です。これをさらに詳しく説明するために、具体的な例を見てみましょう。次に、データベースの orders テーブルと orderdetails テーブルから、2018 年に売上高が最も高かった上位 5 つの製品を取得する必要があります。まず、次の表のフィールドを見てみましょう。

まず次のSQLを見てみましょう。

選択 
  製品コード、 
  ROUND(SUM(注文数量 * 各価格)) 売上
から
  注文詳細
    内部結合
  注文 USING (orderNumber)
どこ
  年(出荷日) = 2018
製品コード別にグループ化
ORDER BY 売上順
制限5;

この SQL 文は、2 つのテーブルで共有されている orderNumber フィールドを結合クエリのノードとして使用します。その後、time を条件として使用し、productCode フィールドをグループ化の基準として使用します。その後、計算後にグループ化フィールドとエイリアスフィールドを取得し、sales フィールドを並べ替えの基準として使用して、最終的に最初の 5 つの結果を抽出します。おそらくそうです。完成した結果セットを一時テーブルなどと見なすことができます。結果セットを見てみましょう:

+-------------+---------+
| 製品コード | 販売 |
+-------------+---------+
| S18_3232 | 103480 |
| S10_1949 | 67985 |
| S12_1108 | 59852 |
| S12_3891 | 57403 |
| S12_1099 | 56462 |
+-------------+---------+
5行セット

これで完了です。派生テーブルについて学習しているので、もちろんこのクエリの結果を派生テーブルとして使用し、それを製品テーブルに関連付けることができます。製品テーブルの構造は次のとおりです。

mysql> desc 製品;
+--------------------+---------------+------+-----+--------+-------+
| フィールド | タイプ | Null | キー | デフォルト | 追加 |
+--------------------+---------------+------+-----+--------+-------+
| 製品コード | varchar(15) | NO | PRI | | |
| productName | varchar(70) | NO | | NULL | |
| productLine | varchar(50) | NO | MUL | NULL | |
| productScale | varchar(10) | NO | | NULL | |
| productVendor | varchar(50) | NO | | NULL | |
| productDescription | テキスト | NO | | NULL | |
| 在庫数 | smallint(6) | NO | | NULL | |
| 購入価格 | 小数点(10,2) | NO | | NULL | |
| 希望小売価格 | 小数点(10,2) | いいえ | | NULL | |
+--------------------+---------------+------+-----+--------+-------+
20行セット

テーブル構造を理解したので、次の SQL を見てみましょう。

選択 
  製品名、販売
から
# (選択 
# 製品コード、 
# ROUND(SUM(注文数量 * 各価格)) 売上
＃ から
#注文詳細
# INNER JOIN 注文 USING (orderNumber)
＃ どこ
# 年(出荷日) = 2018
# 製品コード別にグループ化
# ORDER BY 売上 DESC
# 制限 5) top5_products_2018
内部結合
  製品 USING (productCode);

上記の # 部分は以前の SQL です。皆さんにわかりやすくするために # を付けました。皆さんが書くときには使用しないでください。ここで、この SQL が何を意味するのか見てみましょう。 # でマークされた部分をテーブルとして扱い、単純な結合クエリを実行します。ただし、派生テーブルと呼ばれるこのテーブルは使用後すぐにクリアされるため、複雑なクエリを簡略化するときに使用することを検討できます。では、早速結果セットを見てみましょう。

+-----------------------------+--------+
| 製品名 | 販売 |
+-----------------------------+--------+
| 1992 フェラーリ 360 スパイダー レッド | 103480 |
| 1952 アルピーヌ ルノー 1300 | 67985 |
| 2001 フェラーリ エンツォ | 59852 |
| 1969 フォード ファルコン | 57403 |
| 1968 フォード マスタング | 56462 |
+-----------------------------+--------+
5行セット

それでは、簡単にまとめてみましょう。

まず、サブクエリが実行され、結果セットまたは派生テーブルが作成されます。
次に、外部クエリが、top5_products_2018 派生テーブルを productCode 列の products テーブルに結合します。

以上です。これで、単純な派生テーブルについての理解と使用は終わりです。もう少し複雑な例を試してみましょう。まず、2018 年の顧客をプラチナ、ホワイトゴールド、シルバーの 3 つのグループに分ける必要があると仮定します。さらに、次のように各グループの顧客数を知る必要があります。

合計注文金額が 100,000 を超えるお客様はプラチナ顧客です。
ゴールド顧客とは、合計注文金額が10,000～100,000の顧客です。
合計注文金額が10,000未満のお客様はシルバー顧客となります。

このクエリを構築するには、まず、次に示すように、case 式と group by 句を使用して各顧客を対応するグループに分類する必要があります。

選択 
  顧客番号、
  ROUND(SUM(注文数量 * 各価格)) 売上、
  （場合
    SUM(注文数量 * 各価格) < 10000 の場合、「シルバー」
    SUM(注文数量 * 各価格) が 10000 から 100000 の間であれば、「ゴールド」
    SUM(注文数量 * 各価格) > 100000 の場合、「プラチナ」
  終了) 顧客グループ
から
  注文詳細
    内部結合
  注文 USING (orderNumber)
どこ
  年(出荷日) = 2018
顧客番号によるグループ化 
ORDER BY 売上 DESC;

結果セットの例を見てみましょう。

+----------------+--------+---------------+
| 顧客番号 | 売上 | 顧客グループ |
+----------------+--------+---------------+
| 141 | 189840 | プラチナ |
| 124 | 167783 | プラチナ |
| 148 | 150123 | プラチナ |
| 151 | 117635 | プラチナ |
| 320 | 93565 | ゴールド |
| 278 | 89876 | ゴールド |
| 161 | 89419 | ゴールド |
| ************ここでは多くのデータが省略されています*********|
| 219 | 4466 | シルバー |
| 323 | 2880 | シルバー |
| 381 | 2756 | シルバー |
+----------------+--------+---------------+

これで、上記のクエリから取得したテーブルを派生テーブルとして使用して、関連するクエリを実行し、それらをグループ化して目的のデータを取得できるようになりました。次の SQL を見て、その感覚をつかんでみましょう。

選択 
  顧客グループ、 
  COUNT(cg.customerGroup) AS groupCount
から
  （選択 
    顧客番号、
      ROUND(SUM(注文数量 * 各価格)) 売上、
      （場合
        SUM(注文数量 * 各価格) < 10000 の場合、「シルバー」
        SUM(注文数量 * 各価格) が 10000 から 100000 の間であれば、「ゴールド」
        SUM(注文数量 * 各価格) > 100000 の場合、「プラチナ」
      終了) 顧客グループ
  から
    注文詳細
  INNER JOIN 注文 USING (orderNumber)
  どこ
    年(出荷日) = 2018
  GROUP BY 顧客番号) cg
cg.customerGroup によってグループ化されます。

皆さんは私よりもその意味をよく理解している賢い人々だと信じているので、これ以上詳しく説明することはしません。終わったら、結果セットを見てみましょう。

+---------------+------------+
| 顧客グループ | グループ数 |
+---------------+------------+
| ゴールド | 61 |
| プラチナ | 4 |
| シルバー | 8 |
+---------------+------------+
3行セット

さて、ここでやめておきましょう。

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL クエリスキル」、「MySQL 共通関数の概要」、「MySQL ログ操作スキル」、「MySQL トランザクション操作スキルの概要」、「MySQL ストアドプロシージャスキル」、および「MySQL データベースロック関連スキルの概要」

この記事が皆様のMySQLデータベース設計に役立つことを願っています。

以下もご興味があるかもしれません: