【SQL Server】分類ごとに１つだけフラグを立てる

最近SQL Serverの記事を挙げられていなかったので、
題目通り、任意の分類ごとに１つだけフラグを立てるやり方。

例えば、次のようなデータにID毎に１つだけフラグを立てたいとか、ID・日付毎に１つだけフラグを立てたいというような場面。
用途として、立てたフラグを sum して分類単位での件数集計を行う。


SQL Serverでそれらしい関数が見当たらなかったので自作してみる。

SASで言う first by 句的なやつです。

/* サンプル */
data tmp;
length ID1 $10. DATE $20. CLASS1 8.;
input ID1 DATE CLASS1 @@;
cards;
001 2021-01-01 1
001 2021-01-01 1
001 2021-01-02 1
001 2021-01-02 1
001 2021-01-02 1
001 2021-01-02 2
001 2021-01-02 2
002 2021-01-02 1
002 2021-01-02 2
002 2021-01-02 2
;run;
 
/* フラグ付け */
proc sort data=tmp; by ID1 DATE CLASS1;
data tmp;
  set tmp;
  by ID1 DATE CLASS1;
  if first.ID1    then FIRSTFL1 = 1; else FIRSTFL1 = 0;
  if first.DATE1  then FIRSTFL2 = 1; else FIRSTFL2 = 0;
  if first.CLASS1 then FIRSTFL3 = 1; else FIRSTFL3 = 0;
run;

これをSQL Serverでやると：

-- サンプル
drop table #tmp1;
create table #tmp1 ( ID1 varchar(3), DATE1 varchar(10), CLASS1 int )
insert #tmp1 values
 ('001', '2021-01-01', 1)
,('001', '2021-01-01', 1)
,('001', '2021-01-02', 1)
,('001', '2021-01-02', 1)
,('001', '2021-01-02', 1)
,('001', '2021-01-02', 2)
,('001', '2021-01-02', 2)
,('002', '2021-01-02', 1)
,('002', '2021-01-02', 2)
,('002', '2021-01-02', 2)
;
 
-- フラグ付け
select
     *
    ,FIRSTFL1 = case when row_number() over (partition by ID1                order by CLASS1) = 1 then 1 else 0 end
    ,FIRSTFL2 = case when row_number() over (partition by ID1, DATE1         order by CLASS1) = 1 then 1 else 0 end
    ,FIRSTFL3 = case when row_number() over (partition by ID1, DATE1, CLASS1 order by CLASS1) = 1 then 1 else 0 end
from #tmp1

こんな感じで任意のグループごとに１つフラグが立つ。


中身は row_number関数でパーティション毎に順番をつけて、case文で順番が１の時だけ１となるようにしている。
効率が良いとは言えないだろうが、一つの方法として。