SQLのWindow関数でGROUP BYする場合の、SUMの書き方

この記事は約4分で読めます。

結論。関数を入れ子にする。
sum(sum(price)) という書き方ができます。

SELECT
    dept
   ,emp_id
   ,sum(sum(price)) OVER (PARTITION BY emp_id)
FROM TableA
GROUP BY dept, emp_id
;

以下は解説です。


例えば、
次のようなデータがあったとします。

どの部署の、どの社員が、何年に、いくら費用を使った、みたいな例です。

dept | emp_id | year | price
A001 | 100001 | 2016 | 2300
A001 | 100001 | 2017 | 1500
A001 | 100001 | 2018 | 500
A001 | 100001 | 2019 | 1700
A001 | 100002 | 2016 | 2500
A001 | 100002 | 2017 | 2300
A001 | 100002 | 2018 | 1000
A001 | 100002 | 2019 | 2600
A002 | 100003 | 2016 | 2300
A002 | 100003 | 2017 | 2400
A002 | 100003 | 2018 | 2100
A002 | 100003 | 2019 | 2000
A002 | 100004 | 2016 | 1500
A002 | 100004 | 2017 | 1500
A002 | 100004 | 2018 | 1500
A002 | 100004 | 2019 | 2000
A003 | 100001 | 2018 | 500
A003 | 100001 | 2019 | 300
A003 | 100003 | 2018 | 200
A003 | 100003 | 2019 | 500

このデータのポイントは、社員100001さんが、部署A001とA003を兼務していることです。


単純にGROUP BYすると「部署ごと&社員ごと」の合計金額になります。

SELECT
    dept
   ,emp_id
   ,sum(price) AS price
FROM TableA
GROUP BY dept, emp_id
;

結果(社員100001のみ抜粋)

dept | emp_id | price
A001 | 100001 | 6000
A003 | 100001 | 800

この場合、社員100001さんのデータを足し算する必要がありますし、
そもそも誰が部署を兼務しているのかを、把握しなければなりません。(社員マスタなどを参照する?)


Window関数のみを使うと、合計金額は出るのですが、レコード件数がそのままなので状況によっては邪魔です。

SELECT
    dept
   ,emp_id
   ,sum(price) OVER (PARTITION BY emp_id)
FROM TableA
;

結果(社員100001のみ抜粋)

dept | emp_id | price
A001 | 100001 | 6800
A001 | 100001 | 6800
A001 | 100001 | 6800
A001 | 100001 | 6800
A003 | 100001 | 6800
A003 | 100001 | 6800

部署A001の2016~2019年と、部署A003の2018~2019年の、6レコード出力されます。


最後に、今回の結論となる「Window関数とGROUP BYの併用」の場合は、意図通りの結果が出てきます。

SELECT
    dept
   ,emp_id
   ,sum(sum(price)) OVER (PARTITION BY emp_id)
FROM TableA
GROUP BY dept, emp_id
;

結果(社員100001のみ抜粋)

dept | emp_id | price
A001 | 100001 | 6800
A003 | 100001 | 6800

今回はサンプルデータがイケてないので「社員番号でGROUP BY」すれば済んでしまうのですが、
もっと複雑で抽出しにくいデータの時にも、この方法は使えます。

sum(sum(price)) は、PARTITION BY のsum() と、GROUP BY のsum() を入れ子状態にしています。
こんな書き方で済むんですね。
初めて知った時は、驚きました。

タイトルとURLをコピーしました