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

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

ただいまの
回答率

90.49%

  • MySQL

    5990questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

  • SQL

    2463questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

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

解決済

回答 1

投稿

  • 評価
  • クリップ 0
  • VIEW 370

mr.hironobu

score 148

前提・実現したいこと

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

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

-- 集計したテーブル
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

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

質問への追記・修正、ベストアンサー選択の依頼

  • alg

    2017/09/06 12:58

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

    キャンセル

  • mr.hironobu

    2017/09/06 13:46

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

    キャンセル

  • sazi

    2017/09/06 14:50

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

    キャンセル

回答 1

checkベストアンサー

0

前回説明を省いてしまいましたが、外側の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

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/09/05 13:07

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

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

    キャンセル

  • 2017/09/05 13:08

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

    キャンセル

  • 2017/09/05 16:37

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

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

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

    キャンセル

  • 2017/09/05 16:49

    クエリです。
    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;

    キャンセル

  • 2017/09/05 17:24

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

    キャンセル

  • 2017/09/05 17:39

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

    キャンセル

  • 2017/09/06 12:01

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

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

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

    キャンセル

  • 2017/09/06 12:12

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

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

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

    キャンセル

  • 2017/09/06 12:42

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

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

    キャンセル

  • 2017/09/06 15:14

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

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

    キャンセル

関連した質問

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

  • MySQL

    5990questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

  • SQL

    2463questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。