グループ化のSQL文について

質問

このジャンルでお願いします。

tbl
id　　starttime　endtime　val
1　　　　　0　　　　　　40　　3
2　　　　　160　　　　170　　1
3　　　　　160　　　　190　　2

starttimeとendtimeは「分（160なら60×2＋40で2時40分になります）」で保存してます。
このようなテーブルで、時間毎にvalの平均を出したいのですが、

id　　hour　avg(val)
1　　　　0　　　3　　←　0～1時まで
2　　　　1　　　0　　←　1～2時まで
3　　　　2　　　1.5　　←　2～3時まで
4　　　　3　　　2　　←　3～4時まで
・
・
24　　　23　　　0　　←　23～24時まで

といった感じで24区間に分けてその区間毎のvalの平均を出したいです。
これはどのようなSQL文になるのでしょうか？
またこのようなSQL文で１度に取得するやり方と、
いったん全行取得してPHPなどでループして平均を計算するやり方とでは
レコード数が増えてきた場合にどちらが速いとかはあるのでしょうか？

回答No.8

Siegrune · ベストアンサー

select hr,avg(val) from
(
select 0 as hr,val from tbl where endtime >= 0 and starttime < 60
union all
select 1 as hr,val from tbl where endtime >= 60 and starttime < 120
union all
select 2 as hr,val from tbl where endtime >= 120 and starttime < 180
・・・
select 23 as hr,val from tbl where endtime >= 1380 and starttime < 1440
)
group by hr
order by hr

環境ないので検証はしていませんが。
(大抵のデータベースで使える機能しか使っていないのですが、
asの要否などが多少違っているかも。
・・・Select x.hr,avg(x.val) from (・・・) x group by x.hr order by x.hr かも。
また、MySQLのバージョンがあまり古すぎると使えないかも)

補足：
where endtime >= 60 and starttime < 120
終了時刻が60分以降、開始時刻が120分より前。
つまり、開始が0分であろうが、119分であろうが、(2時より前に始まるのは)対象だが、
終了が、60分よりまえ（0時59分までに終わる）のは対象外。

※副問い合わせを使うこのやり方はDBの資源を使うので件数が増えるとあまりよくないのですが
　（普通のＤＢ環境の設計をしていたら、この例なら数百万件くらいなら問題ないはずですが。）

>SQL文で１度に取得するやり方と、
>いったん全行取得してPHPなどでループして平均を計算するやり方とでは
DBからPHP等にデータを受け渡すのに時間とメモリーを食うので
通常は「いったん全行取得する」方がはるかに時間を必要とするでしょう。

ＤＢサーバのメモリが非常に貧弱で、ネットワーク回線が非常に太くて、
PHPのサーバにメモリがありあまっていて、バランスが悪すぎるくらい高性能なＣＰＵを
つんでいたらこの限りではありません。
（こんなケースは、一般的には、システム構成の設計ミスと思いますが）

お礼

ご回答ありがとうございます。
こちらの環境ではちゃんと理想とする結果が得られました。
ありがとうございます。
なるほど、たしかに全ての区間（０～２３時）を書く必要があるわけですね。

＞DBからPHP等にデータを受け渡すのに時間とメモリーを食うので
なるほど、やはり時間もかかるわけですね。
参考になりました。

回答No.9

Siegrune

訂正：
先ほどの回答で、>= はすべて、> が正しいです。

追記：
回答後、前の方の回答を再度見てみました。
直前の回答で「補足」としたところに書いた内容から、
where starttime <= reng * 60 and endtime >= (reng + 1) *60
を
where starttime < reng * (60 + 1) and endtime > reng *60
にすればいいのじゃないかな？
って感じでしたが、詳細は未確認。

回答No.7

seastar3

あいにくですが、当方で検証することはできません。
必要ならばご自身で試行錯誤するべきです。
わたしの回答はこれにて終わります。

回答No.6

seastar3

hourとrangとidと紛らわしくて混乱してすみません。
選択する素材がtblではなく、ViewAddedRangeを用意すると動くことでしょう。

select RangeTbl.hour + 1 as id, RangeTbl.hour, val
from ViewAddedRange LEFT JOIN RangeTbl ON ViewAddedRange.rang=RangeTbl.hour
where starttime <= reng * 60 and endtime >= (reng + 1) *60;

お礼

ご回答ありがとうございます。
たしかにViewAddedRangeならrangが出てるくるのですが、rangって次のVIEWだと

CREATE VIEW ViewAddedRange AS SELECT starttime, endtime, val, CAST(endtime/60 as UNSIGNED) as rang FROM tbl;

当然1つの値（endtime＝250ならrang＝4）しか出てこないので、それと

select RangeTbl.hour + 1 as id, RangeTbl.hour, val
from ViewAddedRange LEFT JOIN RangeTbl ON ViewAddedRange.rang=RangeTbl.hour
where starttime <= reng * 60 and endtime >= (reng + 1) *60;

このSQLをしたところで、endtime＝250の行ならLEFT JOINの性質として
左側（ViewAddedRange）の行数以下（？）しか生成できないので
複数の時間帯

tbl
id　　starttime　endtime　val
3　　　　　160　　　　250　　2　（←160分は0時を基準とすると2時40分、250分は0時を基準とすると4時10分）

２～4時までの3行を生成することができなくて、
その生成ができない限りgroup byを利用しても意味ないというか・・・

すいません、もしお手数でなければseastar3さんの環境で
一度試して頂けないでしょうか？
どうも自分の環境だと質問の結果が得られないのです。。
よろしくお願い致します。

回答No.5

seastar3

節目が複数の場合の得点を取るには、starttimeとendtimeが自分の範囲にない記録について抽出し、自分の範囲区分をくっつければよい。

select RangeTbl.rang + 1 as id, RangeTbl.reng, val
from tbl LEFT JOIN RangeTbl ON tbl.hour=RangeTbl.hour
where starttime < reng * 60 and endtime > (reng + 1) *60;

これを結合してグループ集計すれば出ます。

回答前に気付きましたが、次のように不等号を以上以下にすれば結合せずとも両端も含めて得点を寄りだしてくれるはずです。検証してみて下さい。

select RangeTbl.rang + 1 as id, RangeTbl.reng, val
from tbl LEFT JOIN RangeTbl ON tbl.hour=RangeTbl.hour
where starttime <= reng * 60 and endtime >= (reng + 1) *60;

これを結合してグループ集計すれば出ます。
ちょっとした頭の体操になったので、なんだかセンター試験に行く前の受験生のような気分でした。

お礼

ご回答ありがとうございます。
すいません、どちらのSQL文にも「tbl.hour」が使われているのですが、
これはどこからきたものなのでしょうか？
おそらくtbl.hourは「0や1や23」のような時間が入ってるわけですよね？
tblの元は

tbl
id　　starttime　endtime　val
1　　　　　0　　　　　　40　　3
2　　　　　160　　　　170　　1
3　　　　　160　　　　190　　2

なのですが、何かviewを作り出した後のテーブル（tbl）なのでしょうか？

回答No.4

seastar3

またがる時刻が１回だけの場合、次のように前半時刻の範囲と広範時刻の範囲にスコアが割り当てられます。
次のように範囲コードを終了時刻からだけではなく、開始時刻からも作り結合します。ただし開始時刻からの追加記録は同一時刻範囲の場合は作らないように絞り込みます。それをグループ化して時刻グループテーブルと外部結合すれば両方とも足されます。

CREATE VIEW ViewAddedRang2
AS SELECT starttime, endtime, val, CAST(endtime/60 as UNSIGNED) as rang FROM tbl
union
SELECT starttime, endtime, val, CAST(starttime/60 as UNSIGNED) as rang FROM tbl where CAST(starttime/60) < cast(endtime/60);

なお、valがどういうスコアか分かりませんが、前半のウエイトと後半のウエイトを計算したいのなら、
前半分val = val * (節目時刻 - starttime) / (endtime -starttime)

後半分val = val * (endtime - 節目時刻) / (endtime -starttime)

もし、またがる時刻が２回以上のときは、またがる時刻の回数をカウントしたフィールドを含む記録ビューを作り、カウントフィールドが２以上の記録を抜き出します。それからどうすればいいかはＳＱＬ的には簡単には思いつきません。手作業で抜き出した記録を元に集計に加わる記録を作ってUNIONすることになるでしょう。

お礼

ご回答ありがとうございます。
なるほど、unionでまたがる時間の行をさらに結合するわけですね。
たしかにgroup by　を使うならその時間帯ごとにvalを持ったレコードを
新たに作らないといけないというわけですか。

＞前半のウエイトと後半のウエイトを計算したいのなら、
分りづらい質問で申し訳ございません。
これは別にウエイトを計算したいわけじゃなく、例えばtblのid＝3が
次のようなデータだったら

tbl
id　　starttime　endtime　val
1　　　　　0　　　　　　40　　3
2　　　　　160　　　　170　　1
3　　　　　160　　　　250　　2　（←160分は0時を基準とすると2時40分、250分は0時を基準とすると4時10分）

id　　hour　avg(val)
1　　　　0　　　3　　←　0～1時まで（tblのid＝1のval）
2　　　　1　　　0　　←　1～2時まで
3　　　　2　　　1.5　　←　2～3時まで（tblのid＝2のvalとtblのid＝3のvalの平均）
4　　　　3　　　2　　←　3～4時まで（tblのid＝3のval）
5　　　　4　　　2　　←　4～5時まで（ここにもtblのid＝3のvalが加わる）
・
・
24　　　23　　　0　　←　23～24時まで

こういう結果が得たいのです。

すいません、この説明でもしseastar3さんに質問の内容が誤解なく伝わったようでしたら
再度この説明をふまえたうえでアドバイスなどがあれば頂けないでしょうか？
もしこれ以上ないようでしたら、そもそもこのデータ構造に無理があるかもしれないので
もう一度検討（大量のレコードの場合、いかに速く取得できるか）したいと思います。

回答No.3

seastar3

もう一つのご質問については、
ViewAddedRangeの中の、
CAST(endtime/60+1 as UNSIGNED) as rang を
CAST(endtime/60 as UNSIGNED) と
1を足さなければ、0から開始します。
グループの開始番号を勘違いしておりまして、
申し訳ありませんでした。

お礼

ご回答ありがとうございます。
たしかにそれのやり方（CREATE　VIEWで前もって仮想表を作っておく）で
8割型質問の内容はできたのですが、ただ本来なら

4　　　　3　　　2　　←　3～4時まで

この区間の平均も出るはずなのですが、どうやら viewaddedrangeテーブル

starttime　endtime　val　　rang
0　　　　　　　40　　　　3　　　0
160　　　　　170　　　　1　　　2
160　　　　　190　　　　2　　　2

のrangが当然ですが1つの値しか持っていないので
2つの時間（この場合だと2～3時と3～4時）の平均が取れなく
片方の時間（この場合だと2～3時）の平均だけ取得している状況なのです・・・
確認なのですが、GROUP BY　というのは1つの値でグループ化するわけですよね？
なんとかtblのid　3　が

tbl
id　　starttime　endtime　val
3　　　　　160　　　　190　　2　←　（2～3時と3～4時）

2つの時間両方にvalを加算できるようなアイデアはないのでしょうか？
もしあれば再度アドバイス頂けないでしょうか？

回答No.2

seastar3

検証せずに回答したので、個々の食いちがいはご容赦下さい。
とりあえず稼働しよかったですね。
1-24まで全て出すためには、次のhour 1フィールドの範囲指定テーブル RangeTbl を作ります。
hour
0
1
:
23

そして、多対１の結合を考慮した外部結合を指定し、示してもらったグループ化したHourGroupViewとLeft Join します。

SELECT
Select id, hour, avgのアエリアス
FROM HourGroupView LEFT JOIN RangeTbl ON HourGroupView.hour=RangeTbl.hour;

そうすることで空欄のhourももらさずに並べてくれます。これも検証していませんが、考え方は合っているはずです。

回答No.1

seastar3

endtimeを基準にグループ化するのなら、まずグループ化に使う区分コード（到着時間）付きの競技記録一覧ビュー（クエリ）ViewAddedRangeを次のように作ります。

SELECT starttime, endtime, val, Int(endtime/60+1) as range FROM tbl;

そしてグループ集計し、

SELECT range +1 as id, range as hour, avg(val) from ViewAddedRange group by range;

といった形で結果が出ます。

この形はグループのコードが単純ですが、範囲が複雑なときはグループ上限下限テーブルを用意して、where 判定値　between グループ上限下限テーブル.下限値 and グループ上限下限テーブル.上限値
のようなコード絞り込みを行います。

お礼

ご回答ありがとうございます。
どうやらrangeだとエラーになるようなのでrangに変えたのと
INTもCASTに変更したらできました。
ありがとうございます。

CREATE VIEW ViewAddedRange AS SELECT starttime, endtime, val, CAST(endtime/60+1 as UNSIGNED) as rang FROM tbl;

ただ

id hour avg(val)
3 2 3.0000
5 4 1.5000

このような結果で、　0～1時までの平均がないのと
できれば結果avg(val)が0だとしても全区間（24区間）を表示できるようにしたいのですが、
どこを変更すればそのように表示させることが可能なのでしょうか？

補足

表が見にくくてすいません・・・

id hour avg(val)
3 2 3.0000
5 4 1.5000