Access (一般機能)

Accessの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(指定なし : 指定なし)
集計処理+マッチング処理クエリについて
投稿日時: 17/01/06 14:21:35
投稿者: ピー

 いつもお世話になっております。
M(マスタ):複数キー 主キーなし
T(トラン):複数キー 主キーなし
(例)
M(マスタ)            T(トラン)
KEY1 2 3 ITEM     KEY1 2 3 ITEM
  @  @ @  3        @   @ @  2
  @  @ @  1        @   @ @  3
  @  A B  4        @   A A  9
  A  A A  1        A   A A 10
1.M(マスタ)を集計する…クエリ名(M_SUM)
  SELECT M.M_KEY1, M.M_KEY2, M.M_KEY3, Sum(M.M_ITEM) AS M_ITEM FROM M
                                         GROUP BY M.M_KEY1, M.M_KEY2, M.M_KEY3;
2.T(トラン)を集計する…クエリ名(T_SUM)
  SELECT T.T_KEY1, T.T_KEY2, T.T_KEY3, Sum(T.T_ITEM) AS T_ITEM FROM T
                                         GROUP BY T.T_KEY1, T.T_KEY2, T.T_KEY3;
3.マッチングする(MATCHクエリ)
   SELECT KEY.M_KEY1, KEY.M_KEY2, KEY.M_KEY3, Sum([M_SUM].M_ITEM) AS M_ITEM,
     Sum([T_SUM].T_ITEM) AS T_ITEM
     FROM ((SELECT M_SUM.M_KEY1,M_SUM.M_KEY2,M_SUM.M_KEY3 FROM M_SUM union
            SELECT T_SUM.T_KEY1,T_SUM.T_KEY2,T_SUM.T_KEY3 FROM T_SUM ) AS [KEY]
     LEFT JOIN M_SUM ON (KEY.M_KEY1=[M_SUM].M_KEY1) AND
                        (KEY.M_KEY2=[M_SUM].M_KEY2) AND
                        (KEY.M_KEY3=[M_SUM].M_KEY3))
     LEFT JOIN T_SUM ON (KEY.M_KEY1=[T_SUM].T_KEY1) AND
                        (KEY.M_KEY2=[T_SUM].T_KEY2) AND
                        (KEY.M_KEY3=[T_SUM].T_KEY3)
   GROUP BY KEY.M_KEY1, KEY.M_KEY2, KEY.M_KEY3
   ORDER BY KEY.M_KEY1, KEY.M_KEY2, KEY.M_KEY3;
結果:
 KEY1 2 3 M_ITEM T_ITEM
   @  @ @   4      5
   @  A A          9
   @  A B   4
   A  A A   1     10
 
クエリ M_SUM、T_SUMを作成せずに
  1文のSQLで行いたいのですが、記述方法が分かりません
 すなわち、(Mの集計+Tの集計)+マッチングを行いたい。
 宜しくお願い致しますm(__)m(SQL初心者)
 
 
 

回答
投稿日時: 17/01/06 16:09:45
投稿者: sk

引用:
クエリ M_SUM、T_SUMを作成せずに
1文のSQLで行いたいのですが、記述方法が分かりません

( SQL ビュー)
--------------------------------------------------------------
SELECT [KEY].[M_KEY1],
       [KEY].[M_KEY2],
       [KEY].[M_KEY3],
       [M_SUM].[M_ITEM] AS [M_ITEM],
       [T_SUM].[T_ITEM] AS [T_ITEM]
FROM ((SELECT [M].[M_KEY1],
              [M].[M_KEY2],
              [M].[M_KEY3]
       FROM [M]
       GROUP BY [M].[M_KEY1],
                [M].[M_KEY2],
                [M].[M_KEY3]
       UNION
       SELECT [T].[T_KEY1],
              [T].[T_KEY2],
              [T].[T_KEY3]
       FROM [T]
       GROUP BY [T].[T_KEY1],
                [T].[T_KEY2],
                [T].[T_KEY3]) AS [KEY]
      LEFT JOIN (SELECT [M].[M_KEY1],
                        [M].[M_KEY2],
                        [M].[M_KEY3],
                        Sum([M].[M_ITEM]) AS [M_ITEM]
                 FROM [M]
                 GROUP BY [M].[M_KEY1],
                          [M].[M_KEY2],
                          [M].[M_KEY3]) AS [M_SUM]
       ON [KEY].[M_KEY1] = [M_SUM].[M_KEY1]
      AND [KEY].[M_KEY2] = [M_SUM].[M_KEY2]
      AND [KEY].[M_KEY3] = [M_SUM].[M_KEY3])
LEFT JOIN (SELECT [T].[T_KEY1],
                  [T].[T_KEY2],
                  [T].[T_KEY3],
                  Sum([T].[T_ITEM]) AS [T_ITEM]
           FROM [T]
           GROUP BY [T].[T_KEY1],
                    [T].[T_KEY2],
                    [T].[T_KEY3]) AS [T_SUM]
 ON [KEY].[M_KEY1] = [T_SUM].[T_KEY1]
AND [KEY].[M_KEY2] = [T_SUM].[T_KEY2]
AND [KEY].[M_KEY3] = [T_SUM].[T_KEY3]
ORDER BY [KEY].[M_KEY1],
         [KEY].[M_KEY2],
         [KEY].[M_KEY3];
--------------------------------------------------------------
 
ご覧の通り、あきらかに可読性は低いし
デバッグも修正もしづらいことこの上ないので、
複数のクエリ(キー項目を集約するユニオンクエリ、
[M]の集計クエリ、[T]の集計クエリ)に
分割なさった方が明らかに判りやすいと思いますが。

回答
投稿日時: 17/01/06 16:23:52
投稿者: sk

別解:
 
[M], [T]のそれぞれにおいて、
Sum 関数の集計対象となるるフィールドが
1 つだけである場合は、以下のような SQL でも
同じ結果を得られます。
 
-------------------------------------------------------------------
SELECT [KEY].[M_KEY1],
       [KEY].[M_KEY2],
       [KEY].[M_KEY3],
       (SELECT Sum([M].[M_ITEM])
        FROM [M]
        WHERE [KEY].[M_KEY1] = [M].[M_KEY1]
          AND [KEY].[M_KEY2] = [M].[M_KEY2]
          AND [KEY].[M_KEY3] = [M].[M_KEY3]) AS [M_ITEM],
       (SELECT Sum([T].[T_ITEM])
        FROM [T]
        WHERE [KEY].[M_KEY1] = [T].[T_KEY1]
          AND [KEY].[M_KEY2] = [T].[T_KEY2]
          AND [KEY].[M_KEY3] = [T].[T_KEY3]) AS [T_ITEM]
FROM (SELECT [M].[M_KEY1],
             [M].[M_KEY2],
             [M].[M_KEY3]
      FROM [M]
      GROUP BY [M].[M_KEY1],
               [M].[M_KEY2],
               [M].[M_KEY3]
      UNION
      SELECT [T].[T_KEY1],
             [T].[T_KEY2],
             [T].[T_KEY3]
      FROM [T]
      GROUP BY [T].[T_KEY1],
               [T].[T_KEY2],
               [T].[T_KEY3]) AS [KEY]
ORDER BY [KEY].[M_KEY1],
         [KEY].[M_KEY2],
         [KEY].[M_KEY3];
-------------------------------------------------------------------
 
・メインクエリの FROM 句では
 「キー項目を集約するユニオンクエリ」のみを
 呼び出すようにする。
 
・[M], [T]それぞれの集計( Sum )は
  メインクエリの SELECT 句のサブクエリによって行なう。

投稿日時: 17/01/10 09:26:49
投稿者: ピー

 sk様 早々の回答ありがとうございます。
集計となるフィールドは複数になります
(サンプルとして同質問では1フィールドとしましたが。)
 
 もう一つ質問ですが、
処理パフォーマンス的には、複数SQLクエリ、1文SQLクエリの
いずれが速いのでしょうか?
 データ件数、パソコン環境は同一条件とした場合
宜しくお願い致します。

回答
投稿日時: 17/01/10 18:17:39
投稿者: sk

引用:
集計となるフィールドは複数になります
(サンプルとして同質問では1フィールドとしましたが。)

集計対象となるレコードの抽出条件や集計単位(グループ)が
同じなのであれば、それこそ複数のクエリに分割した方が
分かりやすいと思います。
 
引用:
もう一つ質問ですが、
処理パフォーマンス的には、複数SQLクエリ、1文SQLクエリの
いずれが速いのでしょうか?

(身も蓋もない回答になりますが)
実際に実行してみない限りは分かりません
 
比較対象となっている[M], [T]のそれぞれのフィールドに
インデックスが設定されているかどうかにもよりますので、
一概に判断出来ることではありません。

投稿日時: 17/01/11 08:44:37
投稿者: ピー

  sk様、ご回答ありがとうございます。
 テストデータを作成し、処理パフォーマンスを確認し、
 インデックスなどの設定も考え、
 1文SQLクエリ・個別SQLクエリにするかを決めます。
 色々とご親切に教えて頂きありがとうございました。