MySQLでの効率の良いSQLを教えてください

質問

お世話になります。

以下のようなテーブルがあるとします。

通話記録として、電話番号、通話時間(秒)、通話終了時のタイムスタンプのデータがあります。

call_table
id(一意),telno(電話番号),time(通話時間),timestamp(タイムスタンプ)
1,'090xxxxyyyy',61.4,'2022-06-27 16:27:15'
2,'090xxxxyyyy',34.1,'2022-06-27 16:57:45'
3,'090xxxxyyyy',121.0,'2022-06-27 17:05:41'
4,'090xxxxyyyy',150.4,'2022-06-27 17:44:01'
5,'090xxxxyyyy',450.7,'2022-06-27 17:48:20'
6,'090xxxxyyyy',88.1,'2022-06-27 18:10:15'
7,'090xxxxyyyy',364.9,'2022-06-27 19:36:31'
8,'090xxxxyyyy',55.1,'2022-06-27 19:45:12'

検索の条件は、電話番号とタイムスタンプの時間から日にちを指定したいです。

WHERE telno = '090xxxxyyyy' AND time BETWEEN '2022-06-27 00:00:00' AND '2022-06-27 23:59:59'

結果として欲しいのは以下の状態を返して欲しいです。

何時台,回数,最大通話時間,最小通話時間,平均通話時間
0,0,0,0,0
1,0,0,0,0
2,0,0,0,0
3,0,0,0,0
4,0,0,0,0
5,0,0,0,0
6,0,0,0,0
7,0,0,0,0
8,0,0,0,0
9,0,0,0,0
10,0,0,0,0
11,0,0,0,0
12,0,0,0,0
13,0,0,0,0
14,0,0,0,0
15,0,0,0,0
16,2,61.4,34.1,13.65
17,3,450.7,121.0,240.7
18,1,88.1,88.1,88.1
19,2,364.9,55.1,210.0
20,0,0,0,0
21,0,0,0,0
22,0,0,0,0
23,0,0,0,0

また、通話がなかった時間帯(回数が0)の場合、表示がないパターンも参考までに欲しいです。

よろしくお願いします。

回答No.2

dell_OK · ベストアンサー

訂正します。

select
d.dai,
ifnull(c.count, 0) as count,
ifnull(c.max_time, 0) as max_time,
ifnull(c.min_time, 0) as min_time,
ifnull(c.ave_time, 0) as ave_time
from
(
select
@dai := 0 as dai
union
select
@dai := @dai + 1 as dai
from
information_schema.columns
limit
24
) as d
left join (
select
time_format(timestamp, '%H') as dai,
count(*) as count,
max(time) as max_time,
min(time) as min_time,
avg(time) as ave_time
from
call_table
where
telno = '090xxxxyyyy'
group by
dai
) as c
on d.dai = c.dai
group by
d.dai
order by
d.dai

お礼

素晴らしいSQLをありがとうございます！
とても短いSQLで、欲しい状態で完璧に値が取得できました。

回答No.1

dell_OK

こんな感じでしょうか。

SELECT
d.dai,
count(*) AS count,
ifnull(max(TIME), 0) AS max_time,
ifnull(min(TIME), 0) AS min_time,
ifnull(avg(TIME), 0) AS ave_time
FROM
(
SELECT
d.dai
FROM
(
SELECT
@dai := 0 AS dai
UNION
SELECT
@dai := @dai + 1 AS dai
FROM
information_schema.COLUMNS
LIMIT
24
) AS d
) AS d
LEFT JOIN (
SELECT
*,
TIME_FORMAT(TIMESTAMP, '%H') AS dai
FROM
call_table
) AS c
ON d.dai = c.dai
GROUP BY
d.dai
ORDER BY
d.dai

通話がなかった場合に表示しないパターンは、LEFT JOINをINNER JOINにしてください。