質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.95%

カラムごとに集計し別テーブルに結合する方法で困っています

解決済

回答 5

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 3,616

score 21

mariaDBで集計した結果をjoin?union?する方法がうまくいかず困っています。

テーブルは下記のようなイメージです。

Aテーブル Bテーブル  Cテーブル
+--+----+ +--+----+   +--+----+
|id|avol| |id|bvol|   |id|name|
+--+----+ +--+----+   +--+----+
| 1|   1| | 2|   1|   | 1|aaaa|
| 2|   1| | 2|   1|   | 2|bbbb|
| 2|   2| | 2|   1|   | 3|cccc|
| 3|   2| | 3|   1|   | 4|dddd|
| 3|   3| | 3|   2|   +--+----+
| 3|   1| | 4|   1|  
+--+----+ +--+----+  

結果のイメージとしては

+--+----+----+----+
|id|name|avol|bvol|
+--+----+----+----+
| 1|aaaa|   1|null|
| 2|bbbb|   3|   3|
| 3|cccc|   6|   3|
| 4|dddd|null|   1|
+--+----+----+----+

上記の結果になるように初心者なりにWEBを見ながら試行錯誤し下記のようなsqlを考えてみたのですが、ほしい結果と違う結果しか出ず困っています。

SELECT c.id, c.name, SUM(tmp.avol) as avol, SUM(tmp.bvol) AS bvol
FROM 
 (
  SELECT a.id AS aid, b.id AS bid, a.avol AS avol, b.bvol AS bvol FROM a LEFT JOIN b ON a.id = b.id

UNION

  SELECT a.id AS aid, b.id AS bid, a.avol AS avol, b.bvol AS bvol FROM a RIGHT JOIN b ON a.id= b.id
 ) AS tmp

LEFT JOIN c ON c.id = tmp.aid OR c.id = tmp.bid GROUP BY id ORDER BY id


上記のsqlを試すと

+--+----+----+----+
|id|name|avol|bvol|
+--+----+----+----+
| 1|aaaa|   1|null|
| 2|bbbb|   3|   2|
| 3|cccc|  12|   9|
| 4|dddd|null|   1|
+--+----+----+----+


どのようにしたら思った通りの結果が出ますでしょうか?
もし、参考になるURL等ありましたら教えて頂けませんでしょうか?

よろしくお願い致します。

補足情報(言語/FW/ツール等のバージョンなど)

10.1.10-MariaDB, php 5.6.9

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 5

checkベストアンサー

+2

段階的に考えていけばいいと思います。

  • まずは、表aと表bを一つの表にする。
select a.id id, avol, NULL bvol from a
union all select b.id id, NULL avol, bvol from b
+------+------+------+
| id   | avol | bvol |
+------+------+------+
|    1 |    1 | NULL |
|    2 |    1 | NULL |
|    2 |    2 | NULL |
|    3 |    2 | NULL |
|    3 |    3 | NULL |
|    3 |    1 | NULL |
|    2 | NULL |    1 |
|    2 | NULL |    1 |
|    2 | NULL |    1 |
|    3 | NULL |    1 |
|    3 | NULL |    2 |
|    4 | NULL |    1 |
+------+------+------+
  • 次にidごとにsumする。
select id, sum(avol) avol, sum(bvol) bvol from (
  select a.id id, avol, NULL bvol from a
  union all select b.id id, NULL avol, bvol from b
) t1 group by id
+------+------+------+
| id   | avol | bvol |
+------+------+------+
|    1 |    1 | NULL |
|    2 |    3 |    3 |
|    3 |    6 |    3 |
|    4 | NULL |    1 |
+------+------+------+
  • そしてnameを付与する。
select t2.id id, c.name name, t2.avol avol, t2.bvol from (
  select id, sum(avol) avol, sum(bvol) bvol from (
    select a.id id, avol, NULL bvol from a
    union all select b.id id, NULL avol, bvol from b
  ) t1 group by id
) t2
left outer join c on t2.id=c.id
+------+------+------+------+
| id   | name | avol | bvol |
+------+------+------+------+
|    1 | aaaa |    1 | NULL |
|    2 | bbbb |    3 |    3 |
|    3 | cccc |    6 |    3 |
|    4 | dddd | NULL |    1 |
+------+------+------+------+

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/08/27 13:10

    解決出来ました!
    ありがとうございます!

    キャンセル

+2

snowfallerさんが既に回答しておりますが、
プログラムでもSQLでもそうなのですが、
一気に問題を解決しようとせずに、
段階的に問題を解決する習慣を付けることをお勧めします。

特にSQLは一気にクエリを作成すると、どの部分からデータ取得のされ方が想定と異なるのかを追いかけにくいため、
初学者のうちは手間がかかるとしても、
1つ1つの問題解決するクエリを作り、それを最後に1つにするアプローチを取るのが確実でしょう。

今回のケースでは行う必要のある要件は質問者さんの望む結果から汲み取ると以下となりますよね?

  1. AテーブルのIDごとにavolを合計する
  2. BテーブルのIDごとにbvolを合計する
  3. CテーブルのIDと一致する1.と2.の結果を取得する。ただし1.、2.のいずれか、または両方にCテーブルのIDが存在しない場合でもCテーブルのIDは全て表示する

これを上から1つずつ解決していきましょう。

AテーブルのIDごとにavolを合計する

これだけだと質問者さんのレベルでも容易に実現できるかと思われます。

一応サンプルコードは以下となります。

SELECT
    id    -- Cテーブルとの結合キー
,   SUM(avol) AS total_avol
FROM
    a
GROUP BY
    id

BテーブルのIDごとにbvolを合計する

取得元がBテーブルになるだけで、
Aテーブルの時とやることは変わりませんね。

SELECT
    id    -- Cテーブルとの結合キー
,   SUM(bvol) AS total_bvol
FROM
    b
GROUP BY
    id

CテーブルのIDと一致する1.と2.の結果を取得する。ただし1.、2.のいずれか、または両方にCテーブルのIDが存在しない場合でもCテーブルのIDは全て表示する

恐らく質問者さんがネックとなったのはここですよね。
1つ1つ考え方を整理していきましょう。

欲しい結果としては、
Cテーブルの内容に+αで、AテーブルとBテーブルの合計結果をくっつけて表示したいのですよね?

この場合はCテーブルの結果を中心に考えるのでCテーブルが中心テーブルとなります。

中心テーブルとは、
別テーブルにより結合されるテーブルと考えてください。

SQLでは以下のイメージです。

-- 内部結合の場合
中心テーブル INNER JOIN 別テーブル
-- 外部結合の場合
中心テーブル LEFT JOIN 別テーブル

さて以下からはAテーブル合計と、
Bテーブル合計をそれぞれCテーブルにくっつける例を示していきます。

Aテーブルの合計値、Bテーブルの合計値をそれぞれ取ってくる場合ですが、
各テーブルの合計値結果を別テーブルとしてCテーブルにくっつける必要があります。

また、Cテーブルの内容は全て表示するという制約から外部結合(LEFT JOIN)を利用しなければなりません。

それをまとめると最終的は完成像は以下となります。

SELECT
    c.id
,   c.name
,   t1.total_avol AS avol
,   t2.total_bvol AS bvol
FROM
    c 
    LEFT JOIN (
       Aテーブル合計取得クエリ
    ) t1 ON c.id = t1.id
    LEFT JOIN (
       Bテーブル合計取得クエリ
    ) t2 ON c.id = t2.id
ORDER BY
    c.id
;

この完成像に対して、
後は質問者さん自身がそれぞれの合計取得SQLを埋め込んでいけば、
望んだ結果が得られると思われます。

蛇足

質問者さん自身いろいろ試行錯誤したのかと思われますが、
掲示コードにある「RIGHT JOIN句」は一般的にはほぼ使われません。
あまり使われない理由として大きいのは以下2点です。

  • 大抵はLEFT JOINで代替可能
  • RIGHT JOIN句では中心テーブルを後ろ(右側)に書く必要があるので、一般的に可読性が悪い

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/08/27 13:11

    ご丁寧にせつめいありがとうございます。
    勉強になりますありがとうございます!

    キャンセル

+1

SELECT
    a.id,
    a.name,
    (SELECT sum(b.val) FROM b WHERE b.id = a.id) b_val,
    (SELECT sum(c.val) FROM c WHERE c.id = a.id) c_val
FROM a

のようにサブクエリを使えば統計は簡潔にかけますよ

注意事項

  1. 処理速度のチェックはしてません
  2. 履歴テーブル(数値入り)に NULL が入っていることを想定してはいません

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

手元に MariaDB の環境が無いので、MySQLで動作確認してあります。

SELECT c.id, c.name, a.avol, b.bvol 
FROM table_c AS c 
LEFT OUTER JOIN (
  SELECT id, SUM(avol) AS avol FROM table_a GROUP BY id
) AS a ON c.id = a.id 
LEFT OUTER JOIN (
  SELECT id, SUM(bvol) AS bvol FROM table_b GROUP BY id
) AS b ON c.id = b.id 
ORDER BY c.id;


http://sqlfiddle.com/#!9/d249d6/10

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/08/27 05:59 編集

    横から失礼致します。
    メインクエリ(Cテーブル)に対する集約って不要ではないでしょうか?

    おそらく質問者さんのCテーブルの構造ではIDが主キーなると思われるので出力結果には変化はありませんが、CテーブルのIDに重複レコードがあると重複IDのレコード数×各テーブルの値合計となり合計結果値自体が変わってしまいます。

    キャンセル

  • 2016/08/27 17:37

    KotoriMaturi様

    > メインクエリ(Cテーブル)に対する集約って不要ではないでしょうか?

    ご指摘ありがとうございます。
    確かに、仰る通りですね。

    回答を修正させていただきました。

    キャンセル

0

書いてみました。

select c.id, c.name, a.avol, b.bvol
from tableC as c
left join (select id, sum(avol) avol from tableA group by 1) as a using(id)
left join (select id, sum(bvol) bvol from tableB group by 1) as b using(id)
order by 1,2;

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.95%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る