分析関数の衝撃

PostgreSQLの分析関数の衝撃（7）
――window関数の変わった使用例

order by句でのwindow関数など

山岸賢治[著]

2010/02/15 14:00

ポスト

ダウンロード SourceCode (1.3 KB)

2. update文でwindow関数の値に更新

　次は、update文でwindow関数の値に更新する方法についてです。サンプルを見てみましょう。

updateOLAP

ID	Val	seq
1	100	null
1	300	null
1	700	null
2	200	null
2	400	null
2	800	null
3	500	null

　seq列をIDごとのValの昇順での（Row_Numberな）順位にupdateしてみます。

update文でwindow関数の値に更新

update updateOLAP a
set seq = b.rn
from (select ID,Val,
      Row_Number() over(partition by ID order by Val) as rn
        from updateOLAP) b
where a.ID=b.ID
  and a.Val=b.Val;

更新結果

ID	Val	seq
1	100	1
1	300	2
1	700	3
2	200	1
2	400	2
2	800	3
3	500	1

　『PostgreSQLのマニュアル UPDATE』をふまえて、from句でwindow関数を使用したインラインビューを記述して、update文のwhere句でプライマリキーを使って内部結合させてます。

　SQLのイメージは下記となります。Row_Number() over(partition by ID order by Val)のpartition by IDに対応する赤線と、order by Valに対応する黄緑線と青線を引いてます。

SQLのイメージ

MySQLのUpdate文でのLimit句指定を模倣

　上記のupdate文を応用して、『MySQLのUpdate文でのLimit句指定』を模倣することもできます。order by Valの順序で2行だけseqを999にupdateしてみます。

MySQLのUpdate文でのLimit句指定を模倣

update updateOLAP a
set seq = 999
from (select ID,Val,
      Row_Number() over(order by Val) as rn
        from updateOLAP) b
where a.ID=b.ID
  and a.Val=b.Val
  and b.rn <= 2;

更新結果