SQLでrow_numberとoverを使って抽出

質問

SQLServer2012で、row_number()とover()を使って抽出を行っています。
以下の様なテーブルがあるとして

-------------------------------------------
ID NO distination expensen rnak flg
-------------------------------------------
A001 001 広尾 1000 1 1
A001 002 品川 1000 2 1
A002 001 大森 500 3 0
A003 001 品川 1500 2 0
A001 003 新宿 2000 4 0
A001 004 渋谷 1200 5 0
A003 002 新宿 3000 4 1
A004 003 広尾 4000 1 0
A005 002 大森 500 3 1
A003 002 品川 1000 2 1
A003 003 池袋 1300 6 0
A005 001 大森 500 3 1

これに対して
row_number()とover()を使って、
(1)IDとflgでグループ化し
(2)flgの降順(”1”が優先）
(3)flgが”1”の場合、rankの降順、NOの昇順
(4)flgが”0”の場合、rankの昇順、NOの昇順
(5)ID毎の連番（行番号）を割り当てる

これを以下に様なID毎の連番で結果を得たい。
row_number ID NO distination expensen rank flg
-------------------------------------------------------
1 A001 002 品川 1000 2 1
2 A001 001 広尾 1000 1 1
3 A001 003 新宿 2000 4 0
4 A001 004 渋谷 1200 5 0
1 A002 001 大森 500 3 0
1 A003 002 新宿 3000 4 1
2 A003 002 品川 1000 2 1
3 A003 001 品川 1500 2 0
4 A003 003 池袋 1300 6 0
1 A004 003 広尾 4000 1 0
1 A005 001 大森 500 3 1
2 A005 002 大森 500 3 1

と1回のSQLで抽出したいのですが、SQLを教えて下さい。

回答No.2

Siegrune · ベストアンサー

ANO1です。
PARTITION BY ID,flg毎に連番がふられるの意味は
これ
↓ rank*　ID NO distination expensen rank flg

1 -2 A001 002 品川 1000 2 1
2 -1 A001 001 広尾 1000 1 1
1 4 A001 003 新宿 2000 4 0
2 5 A001 004 渋谷 1200 5 0
1 3 A002 001 大森 500 3 0
1 -4 A003 002 新宿 3000 4 1
2 -2 A003 002 品川 1000 2 1
1 2 A003 001 品川 1500 2 0
2 6 A003 003 池袋 1300 6 0
1 1 A004 003 広尾 4000 1 0
1 -3 A005 001 大森 500 3 1
2 -3 A005 002 大森 500 3 1

2つめのrank*がcase when flg = 1 then -1 else 1 end * rankの結果です。
これ順で、同じならNo順に連番が振られます。
で、
PARTITION BY ID,flg毎にというのは、
IDがA001でflgが、1のデータに対して、1から連番。
IDがA001でflgが、0のデータに対して、1から連番。
IDがA002でflgが、1のデータに対して、1から連番。
・・・
という意味です。

Order by句の中にいれる項目や式は、select句と関係しないので、
以下に様なID毎の連番で結果を得たいのなら、
select句の中に別の記述をすればいいことになります。
ROW_NUMBER() OVER(
PARTITION BY ID
ORDER BY flg　desc,
case when flg = 1 then -1 else 1 end * rank,
no)
というような記述でいいはず。

お礼

ありがとうございます。
勉強になります。

回答No.1

Siegrune

select ・・・
order by ID,flg desc,
ROW_NUMBER() OVER(
PARTITION BY ID,flg
ORDER BY case when flg = 1 then -1 else 1 end * rank,no)
で出ると思いますが。

PARTITION BY ID,flg・・・ID,flgごとに連番を取得する。
ORDER BY
flg = 1のとき、-rankの昇順＝rankの降順
他 rankの昇順
で、rankが同じ値ならnoの昇順
で、ID,flgごとに1から連番を振られるのこれをorder by句で使えばいい。

お礼

回答ありがとうございます。
スキル不足ですみません。

>ORDER BY case when flg = 1 then -1 else 1 end * rank,no)
の意味が分かりません。
と
「PARTITION BY ID,flg・・・ID,flgごとに連番を取得する。」
以降の内容がわかりません。
もう少し説明いただけないでしょうか。