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

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

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

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

Q&A

解決済

3回答

365閲覧

MySQLで同じテーブルで日付毎の所要時間を一覧で出したい

Bolboldo

総合スコア11

MySQL

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

0グッド

1クリップ

投稿2017/08/03 14:06

###やりたいこと
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になってしまいました。
お力をお貸し頂けると嬉しいです。

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

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

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

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

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

SVC34

2017/08/03 23:15

実現したい出力イメージが誤っているのではないでしょうか。例えば8/3のchk_point1_2最大は、10:05:00から10:09:00までの差分の00:04:00ではないのでしょうか。
guest

回答3

0

ベストアンサー

こんな感じでしょうか。

サブクエリで以下を求める

  • user_id, lapsが一致し、さらにchk_pointの差が1のレコード同士を結合する
  • タイムスタンプの差分を求める

サブクエリを日付で集約し、以下の処理を行う

  • CASE式でchk_pointの間隔ごとにMAX, MIN, AVGを求める
  • 対象外の間隔についてはELSE NULLにしておくことで集約関数の母数から除外される

sql

1SELECT 2 create_date 3 ,DATE_FORMAT(SEC_TO_TIME(MAX(CASE WHEN chk1 = 0 THEN diff ELSE NULL END)), '%i:%s') 4 ,DATE_FORMAT(SEC_TO_TIME(MIN(CASE WHEN chk1 = 0 THEN diff ELSE NULL END)), '%i:%s') 5 ,DATE_FORMAT(SEC_TO_TIME(AVG(CASE WHEN chk1 = 0 THEN diff ELSE NULL END)), '%i:%s') 6 ,DATE_FORMAT(SEC_TO_TIME(MAX(CASE WHEN chk1 = 1 THEN diff ELSE NULL END)), '%i:%s') 7 ,DATE_FORMAT(SEC_TO_TIME(MIN(CASE WHEN chk1 = 1 THEN diff ELSE NULL END)), '%i:%s') 8 ,DATE_FORMAT(SEC_TO_TIME(AVG(CASE WHEN chk1 = 1 THEN diff ELSE NULL END)), '%i:%s') 9FROM 10 ( 11 SELECT 12 t1.user_id 13 ,DATE(t1.create_datetime) AS create_date 14 ,t1.laps 15 ,t1.chk_point AS chk1 16 ,t2.chk_point AS chk2 17 ,TIMESTAMPDIFF(SECOND, t1.create_datetime, t2.create_datetime) AS diff 18 FROM 19 result_time AS t1 20 INNER JOIN result_time AS t2 21 ON t1.user_id = t2.user_id 22 AND t1.laps = t2.laps 23 AND t1.chk_point + 1 = t2.chk_point 24 ) AS tmp 25GROUP BY 26 create_date 27;

投稿2017/08/03 23:08

編集2017/08/04 03:33
SVC34

総合スコア1149

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

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

sazi

2017/08/04 03:46 編集

こんにちは。 日付の結合条件について、成程と思ったんですがよく考えると、日に複数回走ると条件としては意味が無いですね。そのためにlapsがあるのではないかと。 後は、出力結果の書式が時刻型になっていないことでですね。
SVC34

2017/08/04 03:34

指摘ありがとうございます。確かに、日付とlapsについてはその前提でよさそうですね。
Bolboldo

2017/08/04 18:27 編集

ご回答ありがとうございます。考えていた通りの結果が出力できました! 追加質問になってしまって申し訳ないのですが、この出力結果の日付を横にchk_point間時間を縦にすることは可能でしょうか…?。 指定した期間内の日付毎に以下のようなレポートにしたいのですが、SQL段階で整形可能なのか別途モジュール側で編集すべきなのかが判断できず困っています…。 以下のような感じでレコードがない日付も含めて指定日を全て出すイメージです。 | 7/1 | 7/2 |・・・| 7/31 | check_point0_1 timea1 null ・・・ timea31 check_point1_2 timeb1 null ・・・ timeb31
SVC34

2017/08/05 01:20

別の質問にしてもらえないでしょうか。その方が他の方からの回答も集めやすいかと。
Bolboldo

2017/08/06 07:23

お手数おかけしてすみません、別質問にて立てます。ご指摘ありがとうございます。
guest

0

lapsの1は初日、2は2日目としています。
chk_pointは2としていますが、さらに継ぎ足して5としてもできるハズ

sql

1select date_format(t9.create_datetime, '%m-%d') MMDD, 2 min(c01), max(c01), avg(time_to_sec(c01)) av01, 3 min(c12), max(c12), avg(time_to_sec(c12)) av02 4from 5(select t0.user_id, t0.laps, t0.create_datetime, 6 timediff(t1.create_datetime,t0.create_datetime) c01, 7 timediff(t2.create_datetime,t1.create_datetime) c12 8 from 9 (select user_id, laps, create_datetime from tableA where check_point=0) t0 10 left join 11 (select user_id, laps, create_datetime from tableA where check_point=1) t1 12 on t0.user_id=t1.user_id and t0.laps=t1.laps 13 left join 14 (select user_id, laps, create_datetime from tableA where check_point=2) t2 15 on t0.user_id=t2.user_id and t0.laps=t2.laps 16) t9 17group by laps;

投稿2017/08/03 23:33

編集2017/08/03 23:40
A.Ichi

総合スコア4070

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

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

0

日付を基準としたいのですから、先ずその日付を抽出することを考えます。
その日付のリストを基準に各チェックポイントごとのリストを外部結合することで、
対象が無いところはnullで出力されます。

SQL

1select base.lapdate 2 , ck1.maxlap as ck1maxlap, ck1.minlap as ck1minlap, ck1.avglap as ck1avglap 3 , ck2.maxlap as ck2maxlap, ck2.minlap as ck2minlap, ck2.avglap as ck2avglap 4from ( 5 select date_format(create_datetime, '%y-%m-%d') as lapdate 6 from result_time 7 group by date_format(create_datetime, '%y-%m-%d') 8 ) base left join ( 9 select lapdate, max(laptime) maxlap, min(laptime) minlap 10 , sec_to_time(truncate(avg(time_to_sec(laptime)),0)) avglap 11 from( 12 select f.* 13 , date_format(f.create_datetime, '%y-%m-%d') as lapdate 14 , timediff(f.create_datetime, s.create_datetime) as laptime 15 from result_time s inner join result_time f 16 on s.user_id=f.user_id and s.laps=f.laps and s.chk_point=f.chk_point-1 17 where f.chk_point=1 18 ) src 19 group by lapdate 20 ) ck1 21 on base.lapdate=ck1.lapdate 22 left join ( 23 select lapdate, max(laptime) maxlap, min(laptime) minlap 24 , sec_to_time(truncate(avg(time_to_sec(laptime)),0)) avglap 25 from( 26 select f.* 27 , date_format(f.create_datetime, '%y-%m-%d') as lapdate 28 , timediff(f.create_datetime, s.create_datetime) as laptime 29 from result_time s inner join result_time f 30 on s.user_id=f.user_id and s.laps=f.laps and s.chk_point=f.chk_point-1 31 where f.chk_point=2 32 ) src 33 group by lapdate 34 ) ck2 35 on base.lapdate=ck2.lapdate

各チェックポイント毎のSQL部分は抽出条件(where f.chk_point=X)を除き同じ。
※withが使えればシンプルになりますが、バージョン8.0以降でないと使えないので。

投稿2017/08/03 17:58

編集2017/08/04 00:47
sazi

総合スコア25138

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

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

sazi

2017/08/04 00:15 編集

SVC34さんの回答がシンプルで良いですね。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

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

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

質問する

関連した質問