null値が来たら採番をリセットし再度1から振りなおす

Question

col1  col2         col3
A     2018-01-01   1
A     2018-02-01   1
A     2018-03-01   1
A     2018-04-01   null
A     2018-05-01   1
B     2018-01-01   1
B     2018-02-01   null
B     2018-03-01   1
B     2018-04-01   null
B     2018-05-01   1
C     2018-01-01   1
C     2018-02-01   1
C     2018-03-01   1
C     2018-04-01   1
C     2018-05-01   1

のようなデータの横に継続期間分の連番を振っていきたいのですが、
ROW_NUMBERなどを試してもうまくいかず...
下記のcol4のような形にしたいのですが方法ありますでしょうか。

col1  col2         col3  col4
A     2018-01-01   1     1
A     2018-02-01   1     2
A     2018-03-01   1     3
A     2018-04-01   null  null
A     2018-05-01   1     1
B     2018-01-01   1     2
B     2018-02-01   null  null
B     2018-03-01   1     1
B     2018-04-01   null  null
B     2018-05-01   1     1
C     2018-01-01   1     1
C     2018-02-01   1     2
C     2018-03-01   1     3
C     2018-04-01   1     4
C     2018-05-01   1     5

Answer 1

SQL Server で SELECT クエリでレコードを抽出してくる場合、その順序は ORDER BY 句を使って特定の列で並べ順を指定しない限り何の保証もないのですが、ご存知ですか？

そんなことは言われるまでもなく承知しているということでしたら以下はスルーしてください。

> 下記のcol4のような形にしたいのですが方法ありますでしょうか。

その順番で col4 に連番 null 連番 null ・・・ というように INSERT していきたいということだと理解しています。

何らかの方法を見つけてそうしたとして、ORDER BY 句を使わない限り SELECT した結果は INSERT した順番になるわけではないです。たまたまそうなるかもしれませんが、保証はされていません。

そこはどう考えるのでしょう？

別に IDENTITY 列を設けて、それで ORDER BY するということであれば、INSERT 順になることは保証されますが、どれか一行削除したり変更したりすると col4 の null 連番を振り直しということになると思います。

でも、それは現実的ではないように思うのですが、どうでしょう？

「下記のcol4のような形にしたい」という目的が分かりませんが、その目的が果たせる別の手段を考える方が現実的と思うのですが、いかがでしょうか？

余計なお世話でしたら失礼しました。

Answer 2

＃「B     2018-01-01   1     2」　は、「B     2018-01-01   1     1」の誤りじゃないのかな？

with CTE as(
select col1, col2, col3, ROW_NUMBER() over(order by col1, col2) 連番
from dbo.Null連番テスト
)
select col1, col2, col3,
	(select count(*) - count(t.col3) from CTE t where t.col1 = CTE.col1 and t.連番 < CTE.連番) グループ,
	case when col3 is null then col3
		 else ROW_NUMBER() over(partition by col1, (select count(*) - count(t.col3) from CTE t where t.col1 = CTE.col1 and t.連番 < CTE.連番) order by col2)
	end col4
from CTE
order by col1, col2

上記で、count関数は、count(*)と書くと全てのレコードを対象とし、count(列名)とするとその列がnullの場合は対象から外すという仕様を利用しています。よって、count(*) - count(t.col3)は、同じグループで自分より前にnullがいくつあるかを求めることにより、ROW_NUMBER関数を利用する際のpartition区切りに利用しています。

---

★良い回答には質問者は回答済みマークを、閲覧者は投票を！

Answer 3

Ogaki0452さん、こんにちは。フォーラムオペレーターのHarukaです。
MSDNフォーラムにご投稿くださいましてありがとうございます。

trapemiyaさんの投稿で説明したように、期待された結果('B','2018-01-01',1 ,2),に少しエラーがあるかどうかチェックしてください。 
もしそうなら、次のスクリプトを試してください。

IF OBJECT_ID('test') IS NOT NULL drop table  test
go 
CREATE TABLE test
(col1 varchar(20) ,
col2 date,
col3 int)
INSERT INTO test VALUES 
 ('A','2018-01-01',1   ),
('A','2018-02-01',1   ),
('A','2018-03-01',1   ),
('A','2018-04-01',null),
('A','2018-05-01',1   ),
('B','2018-01-01',1   ),
('B','2018-02-01',null),
('B','2018-03-01',1   ),
('B','2018-04-01',null),
('B','2018-05-01',1   ),
('C','2018-01-01',1   ),
('C','2018-02-01',1   ),
('C','2018-03-01',1   ),
('C','2018-04-01',1   ),
('C','2018-05-01',1   )

;with cte as (
select *,
sum(case when col3 is null then 1 else 0 end)over(order by col1,col2)  [value] 
from test )
select col1,col2,col3,
case when col3 is null then null else 
row_number()over(partition by col1,[value],col3 order by col2) end   col4
from cte 

/*
col1                 col2       col3        col4
-------------------- ---------- ----------- --------------------
A                    2018-01-01 1           1
A                    2018-02-01 1           2
A                    2018-03-01 1           3
A                    2018-04-01 NULL        NULL
A                    2018-05-01 1           1
B                    2018-01-01 1           1
B                    2018-02-01 NULL        NULL
B                    2018-03-01 1           1
B                    2018-04-01 NULL        NULL
B                    2018-05-01 1           1
C                    2018-01-01 1           1
C                    2018-02-01 1           2
C                    2018-03-01 1           3
C                    2018-04-01 1           4
C                    2018-05-01 1           5
*/

どうぞよろしくお願いします。

---

MSDN/ TechNet Community Support Haruka

～参考になった投稿には「回答としてマーク」をご設定ください。なかった場合は「回答としてマークされていない」も設定できます。同じ問題で後から参照した方が、情報を見つけやすくなりますので、
ご協力くださいますようお願いいたします。また、MSDNサポートに賛辞や苦情がある場合は、[e-mail]までお気軽にお問い合わせください。～