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

Question

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

Answer 1

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

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

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

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

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

###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テーブルとＢテーブルの合計結果をくっつけて表示したいのですよね？

この場合は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句では中心テーブルを後ろ(右側)に書く必要があるので、一般的に可読性が悪い

Comments

[akasuka]

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

Answer 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 |
+------+------+------+------+

Comments

[akasuka]

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

Answer 3

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 が入っていることを想定してはいません

Answer 4

書いてみました。

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;

Answer 5

手元に 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

Comments

[Panzer_vor]

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

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

[KiyoshiMotoki]

KotoriMaturi様

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

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

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