[SQL_Server]レコードが○件以上あり、かつ□日以上経過したレコードの抽出(削除)

こんな要望がありました。

　操作ログを、一定期間で削除して欲しい。
　でも、レコードが100件以内だったら、一定期間が経過していても、
　そのユーザーのレコード(操作ログ)は削除しないでほしい。

ちなみに、この対象テーブルには「No列」のようなものはありません。
前回【ROW_NUMBER()を使ってみる】で使用したテーブルです。
これです。

--*--*  操作ログテーブル  *--*--*--*--*--
テーブル：　tbOperationLog -- 操作履歴を格納するテーブル
　カラム：　dtOperation    DATETIME    -- 操作日時
　　　　　　cUserID        NCHAR(8)    -- 操作ユーザーID
　　　　　　cProgramID     NCHAR(8)    -- 操作画面ID
　　　　　　vcContents     NVARCHAR(2) -- 操作種別コード
--*--*--*--*--*--*--*--*--*--*--*--*--*--

で、前回の「ROW_NUMBER()」が登場するワケです。
とりあえず、こんな感じで。
いきなり削除しちゃうのはアレなんで、まずは抽出してみる。
抽出結果が、「削除したいものである」と確認できたら、
01行目の 'SELECT *' を 'DELETE' に直せばOK。

※エディタな感じで 行に番号付けてみた。

------------------------------------------------------------------------------------------
01:  SELECT * 
02:    FROM tbOperationLog
03:   WHERE  EXISTS( SELECT *
04:                    FROM ( SELECT ROW_NUMBER() OVER( PARTITION BY cUserID
05:                                                         ORDER BY dtOperation ASC ) AS RNum
06:                                 ,dtOperation
07:                                 ,cUserID
08:                                 ,cProgramID
09:                                 ,vcContents
10:                             FROM tbOperationLog ) Obj
11:                   WHERE tbOperationLog.cUserID = Obj.cUserID
12:                     AND tbOperationLog.dtOperation = Obj.dtOperation
13:                     AND Obj.RNum > 100
14:                     AND DATEDIFF( DAY, Obj.dtOperation, CURRENT_TIMESTAMP ) > 90 )
------------------------------------------------------------------------------------------

解説：
１つ目のポイントは、03行目の「EXISTS」で条件を指定しているトコロ。
-- というか、要件を満たす条件を指定するには、と考えたトキ、この方法しか思いつかなかった。

２つ目のポイントは、03-10行目で「EXISTS」の条件を、更にサブクエリにしているトコロ。
先ずサブクエリ(04行の「SELECT～」-10行目)で、
仮想の行番号を「ROW_NUMBER()」で付与してテーブルを作ってあげる。
この時点で、ユーザー毎に日付順で行番号が配される。(詳しくは前回を参照)

で、このテーブルを「EXISTS」の条件で SELECTして、
「WHERE句」条件(13行目)で仮想の行番号を指定( Obj.RNum > 100 )する。
こうすることで、100件以内のレコードは条件より外される。

-- ちなみに、サブクエリとして使用しないと、仮想の行番号を「WHERE句」に指定することができない。
-- ソコで初めて作成されるのだから、このQuery内では条件には使えないよね。

あとは、「DATEDIFF()」関数(返り値はINT)で削除期間を指定してあげる。
上記の場合、
DATEDIFF( 日付を, Obj.dtOperationから, 本日日時を引いた ) 数値が、'90' より大きい( > 90 )
なら、という条件になる。


Query一発でできるなんて、ステキング。