月次集計した売上データと予算テーブルを結合して予算比を算出する方法について

Question

###前提・実現したいこと
現在、月次売上を集計したものと、予算テーブルを結合しているのですが、うまく結合できません。
また、最終的に集計したものと、予算テーブルを結合して、予算比などを算出したいです

ご教授の程、宜しくお願いします。

-- 集計したテーブル
create table tbl(kaisha int,zokusei int,hiduke date,price int,custom_price int,tax int);
-- 会社を管理しているテーブル
create table t_kaisha(kid int unique key,kname varchar(20));
-- 販売場所を管理しているテーブル
create table t_zokusei(zid int unique key,zname varchar(20));
-- 目標金額を管理しているテーブル
create table budget_target(bid int,year int,month int,kid int,zid int,budget int);

insert into t_kaisha values(1,'F社'),(2,'R社'),(3,'C社'),(999,'全社');
insert into t_zokusei values(1,'店舗'),(2,'免税'),(3,'国内通販'),(4,'海外通販'),(999,'合計');
insert into tbl values
(1,1,'2017-06-01',100,0,8),
(1,2,'2017-06-01',0,100,0),
(1,3,'2017-06-01',100,0,8),
(1,4,'2017-06-01',0,100,0),
(2,1,'2017-06-01',100,0,8),
(2,2,'2017-06-01',0,100,0),
(2,3,'2017-06-01',100,0,8),
(2,4,'2017-06-01',0,100,0),
(3,1,'2017-06-01',100,0,8),
(3,2,'2017-06-01',0,100,0),
(3,3,'2017-06-01',100,0,8),
(3,4,'2017-06-01',0,100,0);

insert into budget_target values
(1,2017,06,1,1,100),
(2,2017,06,1,3,100),
(3,2017,06,1,4,100);

select concat(tk.kname,' - ',tz.zname) as kz
    ,coalesce(kaisha,999) as kaisha
    ,coalesce(zokusei2,999) as zokusei2
    ,t1.m6
from (
    select
        kaisha
	,case when zokusei in (1,2) then 1 else zokusei  end as zokusei2
	,sum((date_format(hiduke,'%m')='06')*(price+custom_price+tax)) as m6
	from tbl
	where date_format(hiduke,'%Y')='2017'
	group by kaisha asc ,zokusei2 asc with rollup
) as t1
left join t_kaisha  as tk on coalesce(kaisha,999)=tk.kid
left join t_zokusei as tz on coalesce(zokusei2,999)=tz.zid
order by kaisha,zokusei2;

###補足情報
MySQL 5.7

Comments

[sazi]

この質問も含め、これまでの一連の質問についてマルチポストされていますね。調べものしてたら、類似のサイトで見つけました。マルチポストは禁止されていますよ。

[marshmallowy]

規約があったんですね。以後、気を付けます。

[alg]

teratail利用規約の第7条（禁止事項）を確認しましたが、マルチポストを禁止する旨の記述は無いようです。他のサイトはわかりませんが、マルチポストを禁止しているサイトはあるかもしれません。また、規約として定められていないとしても、Q&Aサイトにおけるマルチポストは嫌われる傾向があるようです。詳しくは「マルチポスト」で検索すると色々と情報が出てきます。

[marshmallowy]

ありがとうござます。少なからず、気になった方がいらっしゃいますので、以後、気をつけます。

[sazi]

利用規約の方ではなくて[ヘルプ](https://teratail.com/help#posted-otherservice)の方ですね。禁止ではなく非推奨でした。

Answer 1

前回説明を省いてしまいましたが、外側のselectは
rollupする際の項目を表示するためのものなので、
ロジックをみるときは内側だけ考えて下さい

その上で、budgetをtblにリレーションするならこうです

select
kaisha
,case when zokusei in (1,2) then 1 else zokusei  end as zokusei2
,sum((date_format(hiduke,'%m')='06')*(price+custom_price+tax)) as m6
,sum((bt.month='06')*budget) as m6b
from tbl as t2
left join budget_target as bt on kaisha=bt.kid and zokusei=bt.zid and bt.year='2017'
where date_format(hiduke,'%Y')='2017'
group by kaisha asc ,zokusei2 asc with rollup

予算のnullが気になる場合は
,coalesce(sum((bt.month='06')*budget),0) as m6b
としてもいいかもしれません。

予算分調整

少し汎用性をあげました

select 
kaisha,zokusei2
,sum(m6*(t2.m=5)) as m5
,coalesce(sum(budget*(bt.month=5)),0) as m5b
,sum(m6*(t2.m=6)) as m6
,coalesce(sum(budget*(bt.month=6)),0) as m6b
 from (
select
kaisha
,case when zokusei in (1,2) then 1 else zokusei  end as zokusei2
,date_format(hiduke,'%Y') as y
,date_format(hiduke,'%m') as m
,sum(price) as m6
from tbl as t1
group by kaisha ,zokusei2 ,y,m
) as t2
left join budget_target as bt on bt.kid=t2.kaisha and bt.zid=t2.zokusei2 and bt.year=t2.y and bt.month=t2.m
where t2.y='2017'
group by kaisha asc,zokusei2 asc with rollup

これをt_kaisha,t_zaikoをかぶせて

select 
coalesce(kaisha,999) as kaisha,
coalesce(zokusei2,999) as zokusei2,
concat(tk.kname,' - ',tz.zname) as kz,
m5,
m5b,
m6,
m6b
 from 
(select 
kaisha,zokusei2
,sum(m6*(t2.m=5)) as m5
,coalesce(sum(budget*(bt.month=5)),0) as m5b
,sum(m6*(t2.m=6)) as m6
,coalesce(sum(budget*(bt.month=6)),0) as m6b
 from (
select
kaisha
,case when zokusei in (1,2) then 1 else zokusei  end as zokusei2
,date_format(hiduke,'%Y') as y
,date_format(hiduke,'%m') as m
,sum(price) as m6
from tbl as t1
group by kaisha ,zokusei2 ,y,m
) as t2
left join budget_target as bt on bt.kid=t2.kaisha and bt.zid=t2.zokusei2 and bt.year=t2.y and bt.month=t2.m
where t2.y='2017'
group by kaisha asc,zokusei2 asc with rollup
) as t3
left join t_kaisha  as tk on coalesce(kaisha,999)=tk.kid
left join t_zokusei as tz on coalesce(zokusei2,999)=tz.zid
order by kaisha,zokusei2

Comments

[marshmallowy]

問題を解決することができました。
何度かやり取りする中で、初めて知った、関数やコードがすごく勉強になりました。

本当にありがとうござます。

[yambejp]

よくよく考えたらtblが日付単位で、budgetが月単位なので
tblを集計した後しかリレーションできないかもしれませんね

[marshmallowy]

ほんとですね。
集計したあと、budget_targetを結合してbudgetを出力したのですが、最中でNullが出力されます。

Nullが出力されるところで、budgetを集約したいのですが、「Error Code: 1221. Incorrect usage of CUBE/ROLLUP and ORDER BY
」が出力されて対処できませんでした。

ご教授の程、宜しくお願い申し上げます。

[marshmallowy]

クエリです。
select concat(tk.kname,' - ',tz.zname) as kz
,coalesce(kaisha,999) as kaisha
,coalesce(zokusei2,999) as zokusei2
,t1.m6
,budget
,t1.m7
from (
select
kaisha
,case when zokusei in (1,2) then 1 else zokusei end as zokusei2
,sum((date_format(hiduke,'%m')='06')*(price+custom_price+tax)) as m6
,sum((date_format(hiduke,'%m')='07')*(price+custom_price+tax)) as m7
from tbl
where date_format(hiduke,'%Y')='2017'
group by kaisha asc ,zokusei2 asc with rollup
) as t1
left join budget_target as bt on kaisha=bt.kid and zokusei2=bt.zid and bt.year='2017'
left join t_kaisha as tk on coalesce(kaisha,999)=tk.kid
left join t_zokusei as tz on coalesce(zokusei2,999)=tz.zid
order by kaisha,zokusei2;

[yambejp]

ちょっとごちゃごちゃしてきましたが
追記のような感じでどうでしょうか？
もうちょっとサンプルデータを増やさないとチェックができないかも

[marshmallowy]

もう少し、サンプルデータを追加して、試してみたいと思います。

[marshmallowy]

実、売上データ(1087件)と予算を設定してテストしてみました。
月の売り上げが0のとき、予算を設定しているにもかかわらず、予算が0になりました。

どういったことが、原因と見られますでしょうか。

教授の程、宜しくお願いします。

[yambejp]

> 月の売り上げが0のとき、予算を設定しているにもかかわらず、予算が0

ああ、たしかに予算の方が売上より先ですよね
今回のケースですと売上集計表をベースに予算をリレーションしているので
売上がないものは予算は表示できないですね

考え方としては
(1)予めカレンダーテーブルを用意しておいて、十分な仮データ（たとえば10年分くらい）そこにjoinしていく
(2)売上と予算の日付をUNIONしたデータにたいしてjoinしていく
のどちらかが妥当でしょう

[marshmallowy]

気になることが1つあります。

サンプルの環境だと売上が0でも問題なく予算が出力されます。

[marshmallowy]

ほんとに何度も申し訳ございません。
以下のよう構成比率を算出したいのですが、どうすればよろしいでしょうか。

F 社 - 店舗 / F 社 - 全社 * 100 = 構成比率
F 社 - 国内通販 / F 社 - 全社 * 100 = 構成比率
F 社 - 海外通販 / F 社 - 全社 * 100 = 構成比率