MySQL 前週比をだしたい。

Question

###前提・実現したいこと
先日似たような質問をさせていただいたのですが、その応用的なことができるのかなーと思い自分で試してみたのですがなかなか上手くいかないので質問させていただきます。

以下のようなことができないか模索中です

前週とのコスト比率を算出。
自己結合にて解ける？

カラムとしては下記を含めたい

該当週の先頭日付（日曜日の日付） → ソート順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テーブルがあります
###該当のソースコード
前回の質問の時に以下のようなコードを書きました

SELECT
    DATE_SUB(work_day, INTERVAL (WEEKDAY(work_day) + 1) DAY) AS 該当週の先頭日付,
    team.name AS team_name,
    sum(time_sheets.working_hours * daily_salary.pay / 8) AS 当週コスト,
FROM 
    time_sheets 
INNER JOIN
    daily_salary 
ON 
    time_sheets.member_id = daily_salary.member_id
INNER JOIN
    team_member  
ON 
    time_sheets.member_id = team_member.member_id
INNER JOIN
    team 
ON 
    team_member.team_id = team.team_id
WHERE 
    time_sheets.work_day 
        BETWEEN
            '2015-06-01' 
        AND 
            '2015-06-30'
GROUP BY 
    該当週の先頭日付,team.team_id;


+--------------------------+-----------+---------------+
| 該当週の先頭日付         | team_name | コスト        |
+--------------------------+-----------+---------------+
| 2015-05-31               | Alfa      |  5825000.0000 |
| 2015-05-31               | Bravo     | 10637500.0000 |
| 2015-05-31               | Charlie   |  8025000.0000 |
| 2015-05-31               | Delta     |  8550000.0000 |
| 2015-05-31               | Echo      | 11762500.0000 |
| 2015-05-31               | Golf      | 17612500.0000 |
| 2015-06-07               | Alfa      |  5775000.0000 |
| 2015-06-07               | Bravo     | 10400000.0000 |
| 2015-06-07               | Charlie   |  8350000.0000 |
| 2015-06-07               | Delta     |  8600000.0000 |
| 2015-06-07               | Echo      | 12612500.0000 |
| 2015-06-07               | Golf      | 18262500.0000 |
| 2015-06-14               | Alfa      |  5462500.0000 |
| 2015-06-14               | Bravo     | 10387500.0000 |
| 2015-06-14               | Charlie   |  7925000.0000 |
| 2015-06-14               | Delta     |  8437500.0000 |
| 2015-06-14               | Echo      | 10975000.0000 |
| 2015-06-14               | Golf      | 17875000.0000 |
| 2015-06-21               | Alfa      |  5275000.0000 |
| 2015-06-21               | Bravo     | 10125000.0000 |
| 2015-06-21               | Charlie   |  8300000.0000 |
| 2015-06-21               | Delta     |  8800000.0000 |
| 2015-06-21               | Echo      | 12887500.0000 |
| 2015-06-21               | Golf      | 16875000.0000 |
| 2015-06-28               | Alfa      |  2650000.0000 |
| 2015-06-28               | Bravo     |  3912500.0000 |
| 2015-06-28               | Charlie   |  3075000.0000 |
| 2015-06-28               | Delta     |  3225000.0000 |
| 2015-06-28               | Echo      |  4812500.0000 |
| 2015-06-28               | Golf      |  7362500.0000 |
+--------------------------+-----------+---------------+

Comments

[退会済みユーザー]

「CREATE文を提示してくれれば、環境作って回答するけど、文字起こしまではしたくない。」←回答者の本音

[退会済みユーザー]

なるほど！確かにそうですね。指摘ありがとうございます。修正します。

Answer 1

週ごとに集計したVIEWをつくって、１週ずらして結合するのが妥当です。
ただし特定の週に１件もデータがないときは何週も遡るのか？
それとも0とみなして結合するのかによって処理がかわってきます

Comments

[退会済みユーザー]

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

Answer 2

サブクエリを使わない事にこだわっているのは、性能面ですかね？
たいした件数がなければ、そこまで気にせず、
できる方法で作ってしまった方が良い気もしますが。

あと、teamiDとteam_idがあったので、team_idに統一して、以下、回答です。
結局サブクエリですが、性能面は問題ないかと。

create table if not exists tmp_date(
		now_week_start				date			NOT NULL
	,	now_week_end				date			NOT NULL
	,	last_week_start				date			NOT NULL
	,	last_week_end				date			NOT NULL
	,	PRIMARY KEY( now_week_start )
)ENGINE=InnoDB DEFAULT CHARSET='utf8';

INSERT INTO tmp_date values
 ( '2015-06-08', '2015-06-14', '2015-06-01', '2015-06-07' )
,( '2015-06-15', '2015-06-22', '2015-06-08', '2015-06-14' )
; -- 好きなだけ増やしてください。

SELECT	T0.now_week_start as week
	,	T1.team_name
	,	(
			SELECT	sum( T3.working_hours * T4.pay / 8) AS now_week_cost
			FROM	team_member T2
						LEFT JOIN time_sheets T3 ON(
							T3.member_id	= T2.member_id
						)
						LEFT JOIN daily_salary T4 ON(
							T4.member_id	= T3.member_id
						)
			WHERE	T2.team_id			= T1.team_id
				AND	T3.work_day	BETWEEN	T0.now_week_start
									AND	T0.now_week_end
			GROUP BY T2.team_id
		) as now_week_cost
	,	(
			SELECT	sum( T3.working_hours * T4.pay / 8) AS last_week_cost
			FROM	team_member T2
						LEFT JOIN time_sheets T3 ON(
								T3.member_id	= T2.member_id
						)
						LEFT JOIN daily_salary T4 ON(
							T4.member_id	= T3.member_id
						)
			WHERE	T2.team_id	= T1.team_id
				AND	T3.work_day	BETWEEN	T0.last_week_start
									AND	T0.last_week_end
			GROUP BY T2.team_id
		) as last_week_cost
	,	round(
				(
					SELECT	sum( T3.working_hours * T4.pay / 8) AS now_week_cost
					FROM	team_member T2
								LEFT JOIN time_sheets T3 ON(
									T3.member_id	= T2.member_id
								)
								LEFT JOIN daily_salary T4 ON(
									T4.member_id	= T3.member_id
								)
					WHERE	T2.team_id			= T1.team_id
						AND	T3.work_day	BETWEEN	T0.now_week_start
											AND	T0.now_week_end
					GROUP BY T2.team_id
				)
			/	ifnull(
					(
						SELECT	sum( T3.working_hours * T4.pay / 8) AS last_week_cost
						FROM	team_member T2
									LEFT JOIN time_sheets T3 ON(
											T3.member_id	= T2.member_id
									)
									LEFT JOIN daily_salary T4 ON(
										T4.member_id	= T3.member_id
									)
						WHERE	T2.team_id	= T1.team_id
							AND	T3.work_day	BETWEEN	T0.last_week_start
												AND	T0.last_week_end
						GROUP BY T2.team_id
					)
					,	9999999999 )	-- 適当に0処理。
			*	100
			,	2
		) as cost_perform			-- サブクエリでの算出の方が理想。
FROM	tmp_date T0
	,	team T1
ORDER BY T0.now_week_start, T1.team_name

Comments

[退会済みユーザー]

ありがとうございます！
teamiDのところ記入ミスでした。

Answer 3

合計を二つ繋げてみました。

SELECT t1.sweek, t1.team_name, round(COALESCE(t2.cost,0)/t1.cost*100,2) pcost FROM
(
SELECT
    DATE_SUB(work_day, INTERVAL (WEEKDAY(work_day) + 1) DAY) AS sweek,
    team.name AS team_name,
    sum(time_sheets.working_hours * daily_salary.pay / 8) AS cost,
    DATE_SUB(work_day, INTERVAL (WEEKDAY(work_day) + 1 + 7) DAY) lweek
FROM
    time_sheets
INNER JOIN
    daily_salary
ON
    time_sheets.member_id = daily_salary.member_id
INNER JOIN
    team_member
ON
    time_sheets.member_id = team_member.member_id
INNER JOIN
    team
ON
    team_member.team_id = team.team_id
WHERE
    time_sheets.work_day
        BETWEEN
            '2015-06-01'
        AND
            '2015-06-30'
GROUP BY
    sweek,team.team_id
) t1
LEFT JOIN
(
SELECT
    DATE_SUB(work_day, INTERVAL (WEEKDAY(work_day) + 1) DAY) AS sweek,
    team.name AS team_name,
    sum(time_sheets.working_hours * daily_salary.pay / 8) AS cost
FROM
    time_sheets
INNER JOIN
    daily_salary
ON
    time_sheets.member_id = daily_salary.member_id
INNER JOIN
    team_member
ON
    time_sheets.member_id = team_member.member_id
INNER JOIN
    team
ON
    team_member.team_id = team.team_id
WHERE
    time_sheets.work_day
        BETWEEN
            '2015-05-01'
        AND
            '2015-06-30'
GROUP BY
    sweek,team.team_id
) t2 ON t2.sweek = t1.lweek AND t1.team_name=t2.team_name
ORDER BY t1.sweek, t1.team_name
;

SUBクエリしないのも作ってみました

SELECT
    DATE_SUB(t1.work_day, INTERVAL (WEEKDAY(t1.work_day) + 1) DAY) AS sweek,
    team.name AS team_name,
    round(COALESCE(sum(t2.working_hours * daily_salary.pay / 8),0) / sum(t1.working_hours * daily_salary.pay / 8) * 100,2) AS pcost
FROM
    time_sheets t1
INNER JOIN
    daily_salary
ON
    t1.member_id = daily_salary.member_id
INNER JOIN
    team_member
ON
    t1.member_id = team_member.member_id
INNER JOIN
    team
ON
    team_member.team_id = team.team_id
LEFT JOIN
    time_sheets t2 ON  DATE_SUB(t1.work_day, INTERVAL (WEEKDAY(t1.work_day) + 1 + 7) DAY)=DATE_SUB(t2.work_day, INTERVAL (WEEKDAY(t2.work_day) + 1) DAY)
    AND t1.member_id=t2.member_id
WHERE
    t1.work_day
        BETWEEN
            '2015-06-01'
        AND
            '2015-06-30'
GROUP BY
    sweek,team.team_id

Comments

[退会済みユーザー]

ありがとうございます！
これはサブクエリですか？

[A.Ichi]

そうですね。2階層のクエリですね。　ｔ１とｔ２のLEFT JOINがサブクエリになるんでしょうね。

[退会済みユーザー]

回答ありがとうございます！
サブクエリでの方法参考にさせていただきます！

どうやらサブクエリを使わないでもできるらしいので引き続き模索していきます。

[tomari_perform]

１つ目のクエリで良いと思うんですけどね。
２つ目のクエリは、出勤しない日もworking_hoursが0で行が存在する事が前提になりますね。

[退会済みユーザー]

なるほど。そうなんですね。
説明ありがとうございます。参考にさせていただきます。

[A.Ichi]

出社された人は最低1時間は、働きましょう。