###やりたいこと
MySQLを使用してテーブル内データを使ったレポート作成をしようとしております。
下記のテーブルから日付毎にchk_pointそれぞれ0から1、1から2、2から3までの所要時間の最大・最小・平均を一覧で表示し、
最終的に別途作成したツールでCSVに出力したいと思っています。
※同じlaps同士で算出し、chk_pointは5まで想定しています。
・テーブルイメージ
user_id chk_point laps create_datetime
1 0 1 2017/08/03 10:00
1 1 1 2017/08/03 10:05
1 2 1 2017/08/03 10:09
1 0 2 2017/08/04 11:05
1 1 2 2017/08/04 11:08
2 0 1 2017/08/03 10:15
2 1 1 2017/08/03 10:23
・出力結果イメージ
日付 chk_point0_1最大 chk_point0_1最小 chk_point0_1平均 chk_point1_2最大 chk_point1_2最小 chk_point1_2平均
08/03 8:00 4:00 6:30 8:00 4:00 6:30
08/04 3:00 3:00 3:00 8:00 4:00
###発生している問題
同テーブルでの外部結合でやってみようとした所、8/4に存在するが8/3には存在しないチェックポイント情報が2重に表示されてしまいます。(存在しない場合null表示にしたい)
結合方法に問題がありそうなのですが、どのようにすれば想定通りに行くか分かりません。
・問題の出力イメージ chk_point0_1最大 chk_point0_1最小 chk_point0_1平均 結合日付(テスト用に表示) chk_point1_2最大 chk_point1_2最小 chk_point1_2平均 08/03 5:00 4:00 3:00 08/04 5:00 4:00 3:00 08/04 2:00 1:00 1:30 08/04 5:00 4:00 3:00 ・実現したい出力イメージ chk_point0_1最大 chk_point0_1最小 chk_point0_1平均 結合日付(テスト用に表示) chk_point1_2最大 chk_point1_2最小 chk_point1_2平均 08/03 5:00 4:00 3:00 08/03 null null null 08/04 2:00 1:00 1:30 08/04 5:00 4:00 3:00
###該当のソースコード
MySQL5.6を使用
SELECT DATE_FORMAT(START_TIME, '%Y-%m-%d') AS '日付', MAX(CHK1_TAKE_TIME) AS '第1区間:最大所要時間', MIN(CHK1_TAKE_TIME) AS '第1区間:CHK1最小所要時間', SEC_TO_TIME(TRUNCATE(AVG(TIME_TO_SEC(CHK1_TAKE_TIME)),0)) AS '第1区間:平均所要時間', DATE_FORMAT(CHK1_START_TIME, '%Y-%m-%d') AS '日付2', MAX(CHK2_TAKE_TIME) AS '第2区間:最大所要時間', MIN(CHK2_TAKE_TIME) AS '第2区間:最小所要時間', SEC_TO_TIME(TRUNCATE(AVG(TIME_TO_SEC(CHK2_TAKE_TIME)),0)) AS '第2区間:平均所要時間' FROM( SELECT RESULT_1.create_datetime AS START_TIME, RESULT_2.create_datetime AS CHK1_TIME, TIMEDIFF(RESULT_2.create_datetime,RESULT_1.create_datetime) AS CHK1_TAKE_TIME FROM result_time RESULT_1 LEFT JOIN result_time RESULT_2 ON RESULT_1.user_id = RESULT_2.user_id WHERE RESULT_1.chk_point = 0 AND RESULT_2.chk_point = 1 AND RESULT_1.laps = RESULT_2.laps AND RESULT_1.create_datetime BETWEEN '2017-07-27' and '2017-07-29' AND RESULT_2.create_datetime BETWEEN '2017-07-27' and '2017-07-29' ) test1, ( SELECT RESULT_3.create_datetime AS CHK1_START_TIME, RESULT_3.create_datetime AS CHK2_TIME, TIMEDIFF(RESULT_4.create_datetime,RESULT_3.create_datetime) AS CHK2_TAKE_TIME FROM result_time RESULT_3 LEFT JOIN result_time RESULT_4 ON RESULT_3.user_id = RESULT_4.user_id WHERE RESULT_3.chk_point = 1 AND RESULT_4.chk_point = 2 AND RESULT_3.laps = RESULT_4.laps AND RESULT_3.create_datetime BETWEEN '2017-07-27' and '2017-07-29' AND RESULT_4.create_datetime BETWEEN '2017-07-27' and '2017-07-29' ) test2 GROUP BY DATE_FORMAT(START_TIME, '%Y-%m-%d') ;
完全結合を調べて試した所右側テーブルが全てNULLになってしまいました。
お力をお貸し頂けると嬉しいです。
回答3件
あなたの回答
tips
プレビュー