メモ：同じ値は同じグループにするけど、null は 全部別扱いしたいクエリ (dense_rank)

タイトルの通りですが、

select * 
from (values 
  (10)
  , (20)
  , (null)
  , (null)
  , (10)
  , (30)
  , (40)
) as [t] ([Id])
order by [Id]

なデータがあった時に、Id が同じ物は同じグループにしたい場合に、
DENSE_RANK (Transact-SQL) - SQL Server | Microsoft Learn
ってのを使うんですが、null が複数あると null は同じグループになります。

select *
, dense_rank() over (order by [Id]) as [GroupId]
from (values 
  (10)
  , (20)
  , (null)
  , (null)
  , (10)
  , (30)
  , (40)
) as [t] ([Id])
order by [Id]

要件的に同じで良い場合はこれでいいんですが、null は 別扱いしたい！！って場合のメモです。

元データに一意になる値があれば、null の時に別物扱い出来ます。
今回はシンプルな例なので、一意の列が無いため 、
ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Learn
で突っ込みます。*1

select *
, row_number() over (order by [Id]) as [rownum]
from (values 
  (10)
  , (20)
  , (null)
  , (null)
  , (10)
  , (30)
  , (40)
) as [t] ([Id])
order by [Id]

で、dense_rank の order by をちょっと工夫するだけです。

select
  [Id]
  , dense_rank() over (
      order by case when [Id] is null 
        then concat('NULL', [rownum])
        else concat('', [Id])
      end) as [GroupId]
from (
  select *
  , row_number() over (order by [Id]) as [rownum]
  from (values 
    (10)
    , (20)
    , (null)
    , (null)
    , (10)
    , (30)
    , (40)
  ) as [t] ([Id])
) [tt]
order by [Id]

null の時は、一意の列、そうじゃない時は、Id 列 使う。
ただ、一意の列の値と Id 列の値が被る場合があるので、被らないような一工夫する感じです。
ここでは、文字列化して、null の場合は接頭語 'NULL' つけてます。

*1:既にある場合は不要