SQL Serverで累計を算出する

2020/07/12データベースSQLServer

記事内に広告が含まれています。

レコード毎に前レコードからの累計を算出するSQLを紹介します。

コーディング例

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
BEGIN
  CREATE TABLE #勤怠(
     氏名      VARCHAR(10)
    ,年月      INT
    ,日        INT
    ,勤務時間  INT
  );
 
  INSERT INTO
    #勤怠
  VALUES
     ('佐藤',201909,1,500)
    ,('佐藤',201909,2,550)
    ,('佐藤',201909,3,0)
    ,('佐藤',201909,4,600)
    ,('佐藤',201909,5,450)
    ,('山本',201909,1,0)
    ,('山本',201909,2,460)
    ,('山本',201909,3,510)
    ,('山本',201909,4,410)
    ,('山本',201909,5,610)
    ,('田中',201909,1,520)
    ,('田中',201909,2,620)
    ,('田中',201909,3,0)
    ,('田中',201909,4,570)
    ,('田中',201909,5,470)
    ,('佐藤',201910,1,500)
  ;
 
  SELECT
     氏名
    ,日
    ,勤務時間
    ,SUM(勤務時間) OVER (PARTITION BY 氏名 ORDER BY ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [前日]
  FROM
    #勤怠
  WHERE
    年月 = 201909
  GROUP BY
     氏名
    ,勤務時間
    ,日
  ORDER BY
     氏名
    ,日
  ;
 
  DROP TABLE #勤怠;
END

実行結果

氏名 勤務時間 累計
佐藤 1 500 500
佐藤 2 550 1050
佐藤 3 0 1050
佐藤 4 600 1650
佐藤 5 450 2100
山本 1 0 0
山本 2 460 460
山本 3 510 970
山本 4 410 1380
山本 5 610 1990
田中 1 520 520
田中 2 620 1140
田中 3 0 1140
田中 4 570 1710
田中 5 470 2180

34行目の、「PARTITION BY 氏名」で氏名毎にグループ化することを指定し、「ORDER BY 日」で日の昇順で並び替えすることを指定し、「ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW」では参照レコードの開始を「UNBOUNDED PRECEDING」とすることでグループ化した先頭レコードを指定し、参照レコードの終了を「CURRENT ROW」とすることでカレントレコードを指定しています。
これによりグループ化した先頭レコードからカレントレコードまでの合計(SUM)、すなわち累計が算出されます。

以上、「SQL Serverで累計を算出する」の紹介でした。

 

0
システムトラストでは一緒に働いていただける仲間を募集中です。
株式会社システムトラスト

システムトラストでは、システムエンジニア、プログラマーなどを随時募集中です。気軽にご相談ください。

お問合せ