SQL 以下の様な集計を行いたいのですがどのように実現すればよいのかで止まっています。

Question

テーブルに 列 No,Name,Val があり以下のようになっているものとします。
No,Name,Val
1,'A',10
2,'A',11
3,'A',12
4,'B',13
5,'B',14
6,'A',15
7,'A',16
Nameの列が連続しているところは集計して以下の様な出力を期待しています。
No,Name,SUM_Val
1,'A',33
4,'B',27
6,'A',31

どのようなSQLで実現できるのかお知恵を拝借させてください。
DBはSQLServer2008R2です。
よろしくお願いします

Answer 1

SQLServerの分析関数の使用例の
10. 連続範囲の最小値と最大値 (3人旅人算)
https://oraclesqlpuzzle.ninja-web.net/sqlserver2008-sql1-olap.html#1-10

と、キー項目がブレイクしたタイミングでサマリ集計するSQL
http://d.hatena.ne.jp/gonsuke777/20141202/1417527092

を見ながら作ってみました。
SQLServerの環境がないので、Oracle11gR2で作りました。

with t(No,Name,Val) as(
select 1,'A',10 from dual union
select 2,'A',11 from dual union
select 3,'A',12 from dual union
select 4,'B',13 from dual union
select 5,'B',14 from dual union
select 6,'A',15 from dual union
select 7,'A',16 from dual),
tmp as(
select No,Name,Val,
No-Row_Number() over(partition by Name order by No) as Distance
  from t)
select Min(No) as No,Name,sum(Val) as Val
  from tmp
group by Name,Distance
order by No;

NO  Name   VAL
--  ----  ----
 1  A       33
 4  B       27
 6  A       31

Comments

[Yoshikatsu_]

回答ありがとうございます。旅人算ですか？
SQL見ただけでは理解できなかったので、参考URLを見てみます。
こんな方法でもできるんですね。

Answer 2

こんな感じ↓
※TEST2を実テーブル名に置換して検証してください。

SELECT
I,
SUM(VAL)
FROM
(
SELECT
T.,
TEMP.I
FROM
TEST2 T,
(
SELECT
T1.,
ROW_NUMBER() OVER(ORDER BY T1.NO) AS I
FROM
TEST2 T1
WHERE
(
T1.NO = (
SELECT
MAX(NO)
FROM
TEST2
)
)
OR EXISTS(
SELECT
1
FROM
TEST2 T2
WHERE
T2.NO = T1.NO + 1
AND T1.NAME <> T2.NAME
)
) TEMP
WHERE
T.NAME = TEMP.NAME
AND T.NO <= TEMP.NO
) T
GROUP BY
I

Comments

[Yoshikatsu_]

素早い回答ありがとうございます。
ROW_NUMBER() OVER(ORDER BY T1.NO) AS I
として I でGroup Byですかこのような方法があるのですね。

Answer 3

こんな感じでどうでしょう。各行について間に他のNameに割り込まれていない最小のNoをminNoとして求めてそれでgroup byしています。

SELECT
    t1.minNo, t1.Name, SUM(t1.Val)
FROM (
    SELECT
        No,
        Name,
        Val,
        (SELECT MIN(No)
            FROM t as b
            WHERE
                b.Name = a.Name
                AND b.No <= a.No
                AND NOT EXISTS
                    (SELECT * FROM t as c WHERE c.Name != a.Name AND c.No BETWEEN b.No AND a.No)
        ) as minNo
    FROM
        t AS a
) AS t1
GROUP BY t1.minNo

Comments

[Yoshikatsu_]

素早い回答ありがとうございます。
Not EXISTS　と　BETWEEN　をこんなふうに使うのですね。

[Yoshikatsu_]

Where条件の見通しが良いのでベストアンサーとさせていただきました。