SQL Server 時間別集計で1時間ごとの中に30分、15分ごとの集計もしたい

Question

分かりにくいタイトルで申し訳ないのですが、実現したいことは以下の通りです。

とあるログテーブル「log」があるとします。
datetime, int, で構成されているものとし、ログの落ちた日付時刻, ログ番号(1~3),とします。

この「log」テーブルを時間別で集計したいです。
集計したい時間を1時間ごとなら簡単にできますが、その中に「8時のみ8:00と8:30」、「17時のみ17:00と17:15」で集計したい場合どうしたらよいでしょうか？

集計したい時刻のテーブル「TimeTable」を作成し、そのテーブルでGROUP BYをしたのですが、
「8時のみ8時でかつ、30分より上か下か」と「17時のみ17時でかつ、15分より上か下か」の判断ができなくて困っています。
単純に「TimeTable」でグループ分けしてしまうと上の2つ以外は条件が合わず、集計が合いません。

「TimeTable」は以下の通りです。

Comments

[Orlofsky]

質問にCREATE TABLEや現状のうまくいっていないSELECT文も載せた方が適切なコメントが付きやすいです。日や月、年などをまたぐデータもあるでしょうから、日付や時分秒はDATETIME型のカラムひとつで良いのでは？ https://docs.microsoft.com/ja-jp/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql?view=sql-server-2017

Answer 1

集計する単位を整理しましょう。
集計は以下の場合に分けられます。この集計の単位を作り出せばいいわけです。
・datetime >= 8:00 and datetime < 8:30
・datetime >= 8:30 and datetime < 9:00
・datetime >=17:00 and datetime <17:15
・datetime >=17:15 and datetime <18:00
・上記以外は１時間単位

case文を用いて場合分けして、集計の単位を作り、集計します。

select timeunit, count(*)
from (
  select  *
        , case
            when timepart >= '08:00' and timepart < '08:30' then '08:00'
            when timepart >= '08:30' and timepart < '09:00' then '08:30'
            when timepart >= '17:00' and timepart < '17:15' then '17:00'
            when timepart >= '17:15' and timepart < '18:00' then '17:15'
            else Format(datetime, 'HH') || ':00'
          end as timeunit
  from (select *, Format(datetime, 'HH:mm') as timepart from log) step1
) step2
group by timeunit

Comments

[退会済みユーザー]

ご回答ありがとうございます。
私の知識では例文として頂いたSQLを理解できなかったので、3行目以降のSELECT文から紐解いていこうと思い、その部分だけをSELECTしたのですが、「Format(datetime, 'HH') || ':00'」でどうもエラーが発生してしまいました。
「Format(datetime, 'HH') || ':00'」は「Format(datetime, 'HH:00')」ということでしょうか？
間違っていたらすみません。
私の環境はSQL Server2017の無償版、クエリを実行しているのはSSMSです。

[退会済みユーザー]

すみません。途中で投稿してしまいました。
「Format(datetime, 'HH:00')」と書き換えて3行目以降のSELECT文を実行すると、期待する値らしきものが取得できます。

[sazi]

手打ちでしかやってなかったので、エラーでしたかすみません。
最初は、Format(datetime, 'HH:00')としていましたが、'00'の書式でエラーになるのが嫌だったので、文字列の結合にしました。
エラー無く、変換できているなら問題ありません。

[退会済みユーザー]

ありがとうございます。頂いた例文で実現したいことができました。
大変勉強になりました。

Answer 2

TimeTableには範囲の開始時刻だけではなく、終了時刻もあったほうがいいのでは？
例えば次のようなテーブル構成にします。

dtDateName	dtDatTimeSt	dtDatTimeEd
0	0:00	1:00
1	1:00	2:00
～
8	8:00	8:30
8:30	8:30	9:00
～

あとは次のような感じで集計すればいいのかな、と思います。

SELECT A.dtDateName, B.no, COUNT(B.no)
FROM TimeTable AS A,log AS B
WHERE A.dtDatTimeSt <= B.datetime AND B.datetime < A.dtDatTimeEd
GROUP BY A.dtDateName, B.no