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

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

ただいまの
回答率

87.49%

mysqlで週別の集計をしたいです

解決済

回答 3

投稿

  • 評価
  • クリップ 0
  • VIEW 8,366
退会済みユーザー

退会済みユーザー

前提・実現したいこと

最近になりMySQLの勉強を始めて問題集を解いているのですが週別集計で詰まってしまいました

以下が詰まっている問題です

各プロジェクトの人件費を週別に出力せよ。
(複数PJに入っている人のコストは、それぞれに全額つけて計算する)

なお出力するカラムには下記も含めよ。
(下記以外が含まれていても問題無い)

該当週の先頭日付(日曜日の日付)(※データがあろうが無かろうが、日曜日の日付を表示) → ソート順1番目
チーム名 → ソート順2番目
該当週のコスト

現在あるテーブルは以下の通りです。おそらく使用するであろうテーブルだけ書いておきます

teamテーブル

teamiD team_nam
1 aaa
2 bbb
3 ccc
4 ddd
5 eee

各メンバーの日給
daily_salaryテーブル

member_id pay
1 100000
2 200000
3 300000
:

メンバーがどこのチームに所属しているか
team_memberテーブル

member_id team_id
1 3
2 2
3 1
: :

各メンバーが1日何時間働いたか
time_sheetsテーブル

member_id work_day working_hours
1 2015-06-01 8
1 2015-06-02 8
1 2015-06-03 8
: : :
2 2015-06-01 8
2 2015-06-02 8
2 2015-06-03 8
: : :

このほかにメンバーidやメンバーの名前のレコードがあるmemberテーブルがあります

試したこと

日給/8で時給を出して計算しています
以下のように月別での集計はできたのですが週別はBETWEENを複数使用すればいいのでしょうか?
それだといくつも書くことになって非効率な気がします

SELECT
    team.name,
    SUM(salary.june_salary) AS 6月のコスト
FROM
    (
        SELECT
            time_sheets.member_id AS member_id,
            (MAX(daily_salary.pay) / 8) * SUM(time_sheets.working_hours) AS june_salary
        FROM
            time_sheets
        INNER JOIN
            daily_salary
        ON
            time_sheets.member_id = daily_salary.member_id
        WHERE
            time_sheets.work_day
                BETWEEN
                    '2015-06-01'
                    AND
                    '2015-06-30'
        GROUP BY
            time_sheets.member_id
    ) AS salary
INNER JOIN
    team_member
ON
    salary.member_id = team_member.member_id
INNER JOIN
    team
ON
    team_member.team_id = team.team_id
GROUP BY
    team.team_id
;

+---------+------------------+
| name    | 6月のコスト      |
+---------+------------------+
| Alfa    |    24987500.0000 |
| Bravo   |    45462500.0000 |
| Charlie |    35675000.0000 |
| Delta   |    37612500.0000 |
| Echo    |    53050000.0000 |
| Golf    |    77987500.0000 |
+---------+------------------+


週別の他、該当週の先頭日付(日曜日の日付)を表示させる方法にも詰まっています
そもそも月別の集計でも、もっと効率的なものがあるのでしょうか。
何か追加の情報が必要でしたら教えてください
よろしくお願いします

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 3

+2

週別、月別の集計頻度が高く、データが相当数になる場合は、それを前提にテーブルを設計します。
各レコードに週初の日付および、月初の日付を埋め込んで、集計項目と複合インデックスを貼っておきます

 sample

  • 元データ
create table tbl(id int unique,d date);
insert into tbl values
(1,'2016-12-31'),
(2,'2017-01-01'),
(3,'2017-01-31'),
(4,'2017-02-01'),
(5,'2017-12-01'),
(6,'2017-12-31'),
(7,'2018-01-01');
  • 集計
select *
,date_format(d,'%Y-%m-01') as m
,date_format(d,'%X%V') as w
from tbl


日曜を先頭とする場合は「%X%V」で集計します。
X年のV週目が集計されます。

 追記

週の頭を日付でほしいならこうしてください

select *
,date_format(d,'%Y-%m-01') as m
,date_format(d,'%X-%V') as w1
,d - interval date_format(d,'%w') day as w2
from tbl

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/07/03 10:31

    ありがとうございます!
    やはりテーブルを修正する必要があるということでしょうか。

    キャンセル

  • 2017/07/03 10:52 編集

    保存されるデータが軽ければ、計算式でやっても問題ないです
    サンプルを追記しておきました

    キャンセル

  • 2017/07/03 13:25

    週の頭を日付でほしいパターンも追記しておきます

    キャンセル

  • 2017/07/03 13:42

    ありがとうございます!
    参考にさせていただきます!

    キャンセル

checkベストアンサー

+1

そもそも月別の集計でも、もっと効率的なものがあるのでしょうか。

月別だけですが、こんな書き方で合ってますでしょうか

select t4.team_nam, sum(t1.working_hours*t2.pay/8) AS 6月のコスト from time_sheets t1
join
daily_salary t2 on t1.member_id=t2.member_id
join
team_member t3 on t1.member_id=t3.member_id
join
team t4 on t3.team_id=t4.team_id
where t1.work_day BETWEEN '2015-06-01' AND '2015-06-30'
GROUP BY t4.team_id

さらに週にしてみました(日曜日始め)

select t4.team_nam,
 DATE_ADD('2015-01-01',INTERVAL ((WEEKOFYEAR(work_day)-1)*7-(WEEKDAY('2015-01-01')+1)) DAY) wday,
 sum(t1.working_hours*t2.pay/8) AS 6月のコスト from time_sheets t1
join
daily_salary t2 on t1.member_id=t2.member_id
join
team_member t3 on t1.member_id=t3.member_id
join
team t4 on t3.team_id=t4.team_id
where t1.work_day BETWEEN '2015-06-01' AND '2015-06-31'
GROUP BY 1,2


働いていない週が有ると抜けてしまうのが難点です。

これの方が良いかも

select t4.team_nam,
 DATE_SUB(work_day, INTERVAL (WEEKDAY(work_day)+1) DAY) wday,
 sum(t1.working_hours*t2.pay/8) AS 6月のコスト from time_sheets t1
join
daily_salary t2 on t1.member_id=t2.member_id
join
team_member t3 on t1.member_id=t3.member_id
join
team t4 on t3.team_id=t4.team_id
where t1.work_day BETWEEN '2015-06-01' AND '2015-06-31'
GROUP BY 1,2

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/07/03 09:34

    ありがとうございます!
    これは自己結合というやつですか?

    キャンセル

  • 2017/07/03 10:39

    ありがとうございます!
    こちら参考に勉強します。

    キャンセル

  • 2017/07/03 10:48

    こちらのコードまだ理解できていないのですが
    とりあえず実行したところ最初の週が6月7日で最後の週が7月5日になってしまいました。
    できれば5月31日を最初にしたいのですがそれは可能でしょうか
    何度もすいません。

    キャンセル

  • 2017/07/03 13:43

    ありがとうございます!
    参考にさせていただきます!

    キャンセル

+1

データが無い日付も表示したい場合は、日付カラム一つだけのテーブルを作って、そこに使用するであろう全ての日付を入れておくと便利ですよ。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/07/03 13:45

    なるほど!ありがとうございます!

    キャンセル

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

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

関連した質問

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