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

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

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

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

SQL

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

Q&A

解決済

3回答

10978閲覧

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

退会済みユーザー

退会済みユーザー

総合スコア0

MySQL

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

SQL

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

0グッド

0クリップ

投稿2017/07/02 13:55

###前提・実現したいこと
最近になりMySQLの勉強を始めて問題集を解いているのですが週別集計で詰まってしまいました

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

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

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

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

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

teamテーブル

teamiDteam_nam
1aaa
2bbb
3ccc
4ddd
5eee

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

member_idpay
1100000
2200000
3300000
:

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

member_idteam_id
13
22
31
::

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

member_idwork_dayworking_hours
12015-06-018
12015-06-028
12015-06-038
:::
22015-06-018
22015-06-028
22015-06-038
:::

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

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

MySQL

1SELECT 2 team.name, 3 SUM(salary.june_salary) AS 6月のコスト 4FROM 5 ( 6 SELECT 7 time_sheets.member_id AS member_id, 8 (MAX(daily_salary.pay) / 8) * SUM(time_sheets.working_hours) AS june_salary 9 FROM 10 time_sheets 11 INNER JOIN 12 daily_salary 13 ON 14 time_sheets.member_id = daily_salary.member_id 15 WHERE 16 time_sheets.work_day 17 BETWEEN 18 '2015-06-01' 19 AND 20 '2015-06-30' 21 GROUP BY 22 time_sheets.member_id 23 ) AS salary 24INNER JOIN 25 team_member 26ON 27 salary.member_id = team_member.member_id 28INNER JOIN 29 team 30ON 31 team_member.team_id = team.team_id 32GROUP BY 33 team.team_id 34; 35 36+---------+------------------+ 37| name | 6月のコスト | 38+---------+------------------+ 39| Alfa | 24987500.0000 | 40| Bravo | 45462500.0000 | 41| Charlie | 35675000.0000 | 42| Delta | 37612500.0000 | 43| Echo | 53050000.0000 | 44| Golf | 77987500.0000 | 45+---------+------------------+

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

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

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答3

0

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

sample

  • 元データ

SQL

1create table tbl(id int unique,d date); 2insert into tbl values 3(1,'2016-12-31'), 4(2,'2017-01-01'), 5(3,'2017-01-31'), 6(4,'2017-02-01'), 7(5,'2017-12-01'), 8(6,'2017-12-31'), 9(7,'2018-01-01');
  • 集計

SQL

1select * 2,date_format(d,'%Y-%m-01') as m 3,date_format(d,'%X%V') as w 4from 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 01:22

編集2017/07/03 04:25
yambejp

総合スコア114572

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2017/07/03 01:31

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

2017/07/03 02:11 編集

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

2017/07/03 04:25

週の頭を日付でほしいパターンも追記しておきます
退会済みユーザー

退会済みユーザー

2017/07/03 04:42

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

0

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

投稿2017/07/03 03:55

hihijiji

総合スコア4150

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2017/07/03 04:45

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

0

ベストアンサー

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

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

sql

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

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

sql

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

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

これの方が良いかも

sql

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

投稿2017/07/03 00:06

編集2017/07/03 04:22
A.Ichi

総合スコア4070

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2017/07/03 00:34

ありがとうございます! これは自己結合というやつですか?
退会済みユーザー

退会済みユーザー

2017/07/03 01:39

ありがとうございます! こちら参考に勉強します。
退会済みユーザー

退会済みユーザー

2017/07/03 01:48

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

退会済みユーザー

2017/07/03 04:43

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問