集計を行うSQLについて

以下の2テーブルの構成で、from_uid毎のto_uidへの在庫移動個数(val)を集計したいと考えています。
ユーザテーブル(user)：uid(int), name(string)
在庫移動管理テーブル(zaiko)：id(int), from_uid(int), to_uid(int), val(int)
たとえば、それぞれ以下のようなレコードがあった場合、
<user>
uid, name
1 aaa
2 bbb
3 ccc

<zaiko>
id, from_uid, to_uid, val
1 1 2 3
2 1 2 5
3 2 1 1
4 3 1 4

結果として、以下を得たいと考えています。
uid=1で検索した場合
to_uid, val
1 0
2 7
3 -4
uid=2で検索した場合
1 -7
2 0
3 0
uid=3で検索した場合
1 4
2 0
3 0

（イメージとしては、対戦表？の以下のような表を一行ずつ取得する様な感じです。）
___1__2__3
1__0__7_-4
2_-7__0__0
3__4__0__0

上記のような結果を得たい場合、どのようなSQLを書けばよいのでしょうか。

内容について、わかりづらい点等がありましたらご指摘ください。

No.5 回答者： root139 回答日時： 2013/05/03 16:02

#2の補足です。

対戦表風に横に並べたいのなら、下記の様に並べてSELECT句にサブクエリを並べて書けばよいです。

SELECT
uid,
COALESCE((SELECT SUM(val) FROM zaiko WHERE from_uid = 1 AND to_uid = uid), 0) -
COALESCE((SELECT SUM(val) FROM zaiko WHERE to_uid = 1 AND from_uid = uid), 0) AS from_1,
COALESCE((SELECT SUM(val) FROM zaiko WHERE from_uid = 2 AND to_uid = uid), 0) -
COALESCE((SELECT SUM(val) FROM zaiko WHERE to_uid = 2 AND from_uid = uid), 0) AS from_2,
COALESCE((SELECT SUM(val) FROM zaiko WHERE from_uid = 3 AND to_uid = uid), 0) -
COALESCE((SELECT SUM(val) FROM zaiko WHERE to_uid = 3 AND from_uid = uid), 0) AS from_3
FROM user
ORDER BY uid;

(縦横を入替えるには引き算の前と後を入替えます)


また、#4さんのSQLは MySQL version 5.5.8 で問題無く動作しました。
下記の様にスカラサブクエリを使っても書けますね。

SELECT
u1.uid AS from_uid,
u2.uid AS to_uid,
COALESCE((SELECT SUM(val) FROM zaiko WHERE from_uid = u1.uid AND to_uid = u2.uid), 0) -
COALESCE((SELECT SUM(val) FROM zaiko WHERE to_uid = u1.uid AND from_uid = u2.uid), 0) AS val
FROM user u1 INNER JOIN user u2
ORDER BY u1.uid, u2.uid;

No.4 回答者： yamada_g 回答日時： 2013/04/30 10:40

from,toの全パターンを取得するバージョンです。

件数が多くなるときついと思いますが・・。

SELECT
users.from_uid
,users.to_uid
,COALESCE(SUM(CASE WHEN users.from_uid = zaiko.from_uid THEN val ELSE (val * -1) END), 0) AS val
FROM
(
SELECT u1.uid AS from_uid, u2.uid AS to_uid
FROM user u1
CROSS JOIN user u2
) AS users
LEFT JOIN zaiko ON
(
(users.from_uid = zaiko.from_uid AND users.to_uid = zaiko.to_uid)
OR
(users.from_uid = zaiko.to_uid AND users.to_uid = zaiko.from_uid)
)
GROUP BY users.from_uid, users.to_uid
ORDER BY from_uid, to_uid
;

MySQLの環境が無いので、動作確認はできていません。

No.3 回答者： yambejp 回答日時： 2013/04/26 11:09

この手の集計が前提であれば、fromとtoをひっくり返したデータを入れ込んでおくと

ぐっと集計が楽になります

create table user(uid int, name varchar(20));
insert into user values(1,'aaa'),(2,'bbb'),(3,'ccc');

create table zaiko (id int,type int, from_uid int, to_uid int, val int);
insert into zaiko values(1,1,1,2,3),(2,1,1,2,5),(3,1,2,1,1),(4,1,3,1,4);
insert into zaiko values(1,2,2,1,-3),(2,2,2,1,-5),(3,2,1,2,-1),(4,2,1,3,-4);

※表裏を明確にするためtypeカラムをつけておきましたが実質プラスマイナスだけでも
判断はできます。

これを前提に
select from_uid,to_uid,sum(val) as sumvalfrom zaiko group by from_uid,to_uid;
とすれば、いかが得られます

from_uid to_uid sumval
1 2 7
1 3 -4
2 1 -7
3 1 4

これをuserテーブルとリンクさせると表が簡単につくれます
select uid
,sum((to_uid=1)*sub.sumval) as 1
,sum((to_uid=2)*sub.sumval) as 2
,sum((to_uid=3)*sub.sumval) as 3
from user
left join(select from_uid ,to_uid ,sum(val) as sumval from zaiko group by from_uid,to_uid) sub
on user.uid=sub.from_uid
group by uid

No.2 回答者： root139 回答日時： 2013/04/26 10:07

スカラサブクエリを使って、全ユーザに対して在庫の出入りの合計を求めれば良いでしょう。

対象のuidを指定するのが2箇所になってちょっと冗長ですが、下記の様な感じになるかと。

uid=1 の場合 -----------------------------

SELECT
uid,
COALESCE((SELECT SUM(val) FROM zaiko WHERE from_uid = 1 AND to_uid = uid), 0) -
COALESCE((SELECT SUM(val) FROM zaiko WHERE to_uid = 1 AND from_uid = uid), 0)
FROM user
ORDER BY uid;


uid=2 の場合 -----------------------------

SELECT
uid,
COALESCE((SELECT SUM(val) FROM zaiko WHERE from_uid = 2 AND to_uid = uid), 0) -
COALESCE((SELECT SUM(val) FROM zaiko WHERE to_uid = 2 AND from_uid = uid), 0)
FROM user
ORDER BY uid;


※ MySQL version 5.5.8 で確認

参考URL：http://gihyo.jp/dev/serial/01/sql_academy2/000402

No.1 回答者： Siegrune 回答日時： 2013/04/26 02:24

uid=1で検索した場合

to_uid, val
1 0
uid=2で検索した場合
2 0
・・・
は必ず0のはずですが、それも出したいということですね。
ならば、まず、to_uidを出すためのSQLを考えます。
select distinct to_uid from <zaiko>
で求まりますね。
後は単純に、

select to_uid,sum(val from
(select to_uid, val from <zaiko> where from_uid = 1
union all
select to_uid,0 as val from (select distinct to_uid from <zaiko>)
)
group by to_uid
order by to_uid
で、
uid=1で検索した場合
が求まります。
2・・・も同様。
というのは大嘘で、新たにユーザテーブルに登録されて、from_uidはあれどもto_uidはない場合
漏れます。
select to_uid,sum(val) from
(select to_uid, val from <zaiko> where from_uid = 1
union all
select uid,0 as val from <user>
)
group by to_uid
order by to_uid
のほうが正しいと思われます。
（ただし、to_uidにないuidはto_uidのほうには出したくないなら最初のほうを使ってください）

次に、uid=1,2,3・・・を横に並べる以下のような結果を求めるのは、難しいです。
___1__2__3
1__0__7_-4
2_-7__0__0
3__4__0__0
uidに増減がない（または、増減に対してSQLを修正するつもりがあるならば）のならば、
select to_uid,sum(val1),sum(val2),sum(val3),・・・ from
(select to_uid, val as val1,0 as val2,0 as val3,・・・ from <zaiko>
where from_uid = 1
union all
select to_uid, 0 as val1,val as val2,0 as val3,・・・ from <zaiko>
where from_uid = 2
union all
select to_uid, 0 as val1,0 as val2,val as val3,・・・ from <zaiko>
where from_uid = 3
・・・
union all
select uid,0 as val from <user>
)
group by to_uid
order by to_uid
で求めることができますが、通常はプログラム作って対応するほうが簡明かつ柔軟性に富んでいます。
（というよりユーザテーブルの追加・削除でSQLを変えるというはお薦めしません。）