#710 各レコードの値とグループごとの平均値の差異を取得する方法 クエリ

あるテーブルの全レコードを出力するとともに、同じテーブルのあるフィールドをキーにした各グループの平均値も各行に出力、さらにそれらの差異を求めるクエリの作り方の例です。

このような処理を行いたい場合、平均値だけを取り出すクエリを作ったあと、元となるテーブルとそのクエリを結合する、いわゆる”クエリのクエリ”のような方法もありますが、ここでは単一のクエリで出力する方法を説明します。


まず、元となっているテーブルには下記のような構造でデータが保存されているものとします。ここでは同じ商品コードのレコードが複数登録されています。


このテーブルにおいて、「商品コード」でグループ化を行い、それぞれの商品コードの「数量」の”平均値”も各レコードに出力するようにします。
  1. ここではまず、『商品コードでグループ化、数量の平均を求めるクエリ』を考えます。仮に単独のクエリで作るとすると次のようなものとなります(仮なので保存する必要はありません)。

    SELECT 商品コード, Avg(数量) AS 平均数量
    FROM 受注明細
    GROUP BY 商品コード




  2. 次に、最終的に保存するクエリを新規作成し、まずは上記のテーブルの全レコードの任意のフィールドを作るクエリを作成します。

    SELECT ID, 受注ID, 商品コード, 数量
    FROM 受注明細




  3. SQLビューに切り替え、このクエリのSQL文を次のように書き換えます(このクエリは必要に応じて保存します)。

    SELECT ID, 受注ID, 受注明細.商品コード, 数量, 平均数量, 数量 - 平均数量 AS 差異
    FROM 受注明細
    INNER JOIN
    (SELECT 商品コード, Avg(数量) AS 平均数量 FROM 受注明細 GROUP BY 商品コード)  AS 平均Data
    ON 受注明細.商品コード = 平均Data.商品コード



■ポイント
  • まず、2で作ったクエリのSQL文に対して、受注明細テーブルから取り出すフィールドは同じなので、SELECTやFROMの部分はそのまま残します。
  • 出力するフィールドとして「平均数量」および「差異」を追加するので、SELECTの後ろにそれらを追記します(「差異」にはその演算式を設定します)。

    , 平均数量, 数量 - 平均数量 AS 差異

  • 次に、1のSQL文を”サブクエリ”としてJOINします。
    1のSQL文をそのままペーストしたあと、”サブクエリ”なのでそれ全体をカッコで囲みます。さらにサブクエリ内のフィールドを参照できるよう、そのサブクエリに任意の”別名”を付けておきます(ここでは「平均Data」)。

    INNER JOIN
    (SELECT 商品コード, Avg(数量) AS 平均数量 FROM 受注明細 GROUP BY 商品コード)  AS 平均Data

  • 最後に、メインとなる「受注明細」とサブクエリの「平均Data」の結合条件を追記します(ここでは両者の「商品コード」が同じレコードを結合)。

    ON 受注明細.商品コード = 平均Data.商品コード


実行例:


※なお、ここで作ったクエリのデザインビューを表示すると下図のようになります。最終的に出力するフィールドや演算式などはここで編集可能ですが、サブクエリ内の構造はここでは変えられないので、基本的にはSQLビューで編集することになります。
| Index | Prev | Next |

この情報は参考になりましたか?、問題は解決しましたか?、もしまだなら......
T'sWareのワンポイトテクニカルアドバイスをご利用ください。3000円/件〜でご支援します。
スタンドアロンからSQL Server対応まで、オーダーメイドのシステムを短納期・安価でお届けします


Copyright © T'sWare All rights reserved