SQL(SQL Server)で、特定の順番に並んでいるレコードについて、特定の条件の値が現れるところまでを1ブロックとして合計値を出したい

Question

前提・実現したいこと

SQL(SQL Server)で、特定の順番に並んでいるレコードについて、値の合計値を取得したいです。
ただし、特定の条件の値が現れるところまでを1ブロックとして合計値を出したいです。

test_table1

time	group_name	value
00:30	groupA	10
01:30	groupA	20
03:30	groupA	130
05:30	groupA	30
07:30	groupA	40
09:30	groupA	70
00:30	groupB	30
01:30	groupB	50

例えば、group_name毎に合計値を算出しますが、その中でも「100以上のvalueが現れるまで」を1ブロックとするとします。
そうすると、groupAは、03：30の130が現れるところでブロックが分かれますので、
10, 20のブロックと30, 40, 70の2つのブロックに分かれます。

これらの合計値は、10 + 20 = 30, 30 + 40 + 70 = 140
となり、30と140という合計値になります。
この内の大きい方を求めるとして、結果は140となります。

groupBは、100以上の値がありませんので、全てを合計することになり、30 + 50 = 80
となり、結果は80となります。

テーブル作成およびデータ挿入は下記です。

CREATE TABLE test_table1(
  time TIME,
  group_name NVARCHAR(10),
  value INT
);

INSERT INTO test_table1 (time, group_name, value) 
VALUES
('00:30', 'groupA', 10),
('01:30', 'groupA', 20),
('03:30', 'groupA', 130),
('05:30', 'groupA', 30),
('07:30', 'groupA', 40),
('09:30', 'groupA', 70),
('00:30', 'groupB', 30),
('01:30', 'groupB', 50)

まずは中間結果として、valueが100以上のところで区切って、group_name毎のvalueの合計値を出したいです。

group_name	valueの合計
groupA	30
groupA	140
groupB	80

そして、同じgroup_nameの場合は最大値の方を最終結果とし、下記としたいです。

group_name	valueの合計
groupA	140
groupB	80

試したこと

テーブルにサブグループIDの列を追加し、valueが100以上の場合にカウントアップするようにし、以下のテーブルを作れば求められると考えました。

test_table2

time	group_name	value	sub_gid
00:30	groupA	10	1
01:30	groupA	20	1
03:30	groupA	130	2
05:30	groupA	30	2
07:30	groupA	40	2
09:30	groupA	70	2
00:30	groupB	30	2
01:30	groupB	50	2

SELECT group_name, MAX(total)
FROM (
  SELECT group_name, sub_gid, SUM(value) AS total 
  FROM test_table2 
  WHERE value < 100 
  GROUP BY group_name, sub_gid
) AS tmp_tbl
GROUP BY group_name

しかし、test_table1からtest_table2をSQLで作る方法が分かりませんでした。
別の方法でも構いませんので、特定の条件でブロックを分けるとして、そこまでの合計を算出する
SQLを教えていただきたくお願いします。

Comments

[gentaro]

質問文がややこしくて最終的に欲しい結果セットの形がわかりづらいので、それを質問文に書いてください。
あとSQLの質問を投げる時は、そのテーブルを作成するCREATE文とINSERT文を質問文に貼っておけば、回答者が気軽に試して回答しやすくなります。

[BrandNewMenu]

慣れておらず申し訳ありません。記載しました。

Answer 1

test_table2 は以下で求められますね。

SELECT *
     , COUNT(CASE WHEN value >= 100 THEN 1 END) 
       OVER(PARTITION BY group_name ORDER BY time) AS sub_gid
from test_table1

Comments

[BrandNewMenu]

ありがとうございます。教えていただいたものの方が私のものよりもシンプルですね。
SQLがよく分かっていないのですが、COUNT(CASE WHEN .. THEN .. END)で、THENの値には意味がなく、CASEにマッチした回数をカウントしているということで良いでしょうか？
また、OVER()にしたことで、累積のマッチ回数が出ているという解釈で良いでしょうか？

[sazi]

>THENの値には意味がなく
COUNTですからその通りです。逆にelseを省略してNullを生じさせる事に意味があります。
マッチさせているのはcaseで、over()内でorder by でtimeを指定する事で、time順でのマッチの累積となります。

[BrandNewMenu]

ありがとうございました。

Answer 2

valueが100以上の場合に1を立てておき、SUM() OVER()でその累積値を出せば良い気がしました。

WITH test_table2 AS (
SELECT *, SUM(tmp) OVER(PARTITION BY group_name ORDER BY group_name, time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sub_gid
FROM (
  SELECT *, CASE WHEN value >= 100 THEN 1 ELSE 0 END AS tmp 
  FROM test_table1
) AS tmp_tbl1
)
SELECT group_name, MAX(total)
FROM (
  SELECT group_name, sub_gid, SUM(value) AS total 
  FROM test_table2 
  WHERE value < 100 
  GROUP BY group_name, sub_gid
) AS tmp_tbl2
GROUP BY group_name

間違っている、またはもっと良い方法があれば教えてください。