SQL Serverで前レコードの値を参照する②

2019/11/252021/02/04データベースSQLServer

前回、ROW_NUMBERを使った前レコードの値を参照するSQLを紹介しましたが、別の方法を紹介します。

1. コーディング例
2. 実行結果

コーディング例

BEGIN
  CREATE TABLE #勤怠(
     氏名      VARCHAR(10)
    ,年月      INT
    ,日        INT
    ,勤務時間  INT
  );

  INSERT INTO
    #勤怠
  VALUES
     ('佐藤',201909,1,500)
    ,('佐藤',201909,2,550)
    ,('佐藤',201909,3,0)
    ,('山本',201909,1,0)
    ,('山本',201909,2,460)
    ,('山本',201909,3,510)
    ,('田中',201909,1,520)
    ,('田中',201909,2,620)
    ,('田中',201909,3,0)
  ;

  SELECT
     氏名
    ,日
    ,勤務時間
    ,ISNULL(SUM(日) OVER (PARTITION BY 氏名 ORDER BY 日 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),0) AS [前日]
  FROM
    #勤怠
  WHERE
    年月 = 201909
  GROUP BY
     氏名
    ,勤務時間
    ,日
  ORDER BY
     氏名
    ,日
  ;

  DROP TABLE #勤怠;
END

実行結果

氏名	日	勤務時間	前日
佐藤	1	500	0
佐藤	2	550	1
佐藤	3	0	2
山本	1	0	0
山本	2	460	1
山本	3	510	2
田中	1	520	0
田中	2	620	1
田中	3	0	2

コーディングの27行目では、OVER句で「ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING」を指定していますが、構文としては「ROWS BETWEEN A AND B」となり、「PRECEDING」は日本語訳すると「先行する」という意味で、「1 PRECEDING」は「1つ前」という意味となります。
よって、「ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING」で「1つ前のレコードから1つ前のレコード」という意味となり、前レコードを参照できます。
注意点としては、複数レコードから抽出した1レコードに対して「ROWS BETWEEN A AND B」を使用しても、前レコードが抽出されていないので、前レコードを参照することはできません。
前レコードを参照するためには、前レコードも抽出する必要があります。

以上、「SQL Serverで前レコードの値を参照する②」の紹介でした。