1 Minute Tips

SQLで累積和を計算する

Written by

sumito.tsukada

in

,

はじめに

最近SQLをガリガリ書くことも少なくなってきたので、
redashを使ってこんな情報を集計したいなーと思っても、どうやって書けばいいか頭を悩ませることがある。

単純な合計の場合、sum関数を使えば簡単に表示できるが、累積和を表示しようとした際、工夫が必要だった。

元データ

MySQLが公開している、世界の都市や人口についてまとまっているサンプルデータを利用。

https://qiita.com/S-T/items/923cf689ee5f44525457

やりたいこと

合算し累計を算出したい

元データ

SELECT NAME,
       Population
       
FROM city
WHERE countryCode = 'JPN'
  AND ID IN (1532,
             1533,
             1534);

累積和を計算

SET @cumulativeSum=0;

SELECT NAME,
       Population, 
       @cumulativeSum:=@cumulativeSum+Population AS Population_Sum
       
FROM city
WHERE countryCode = 'JPN'
  AND ID IN (1532,
             1533,
             1534);

追加したところは
SET @cumulativeSum=0;と
@cumulativeSum:=@cumulativeSum+計算したいカラム名をいれただけ。

最初このやり方にたどり着くまでに
SQLでcase文書いて、sumして。。という書き方をしたが、かなりシンプルにおさえることができた。

注意事項

残念ながら、ユーザ定義変数 はあくまでもSQLの実行・評価前に定義するための変数で、Select内で更新することを想定していない為、値の保証はしていないようだ

https://dev.mysql.com/doc/refman/5.6/ja/user-variables.html

SELECT などのほかのステートメントでは、予想した結果が得られることもありますが、これは保証されません。次のステートメントでは、MySQL が最初に @a を評価し、続いて 2 番目の割り当てを行います。

複数のクエリを試してみたが、動かない場合もあった。

SET @gokei=0;

SELECT counseling_date AS date,
       sum(member_status_id = 0 AND ordinal_attendance = 1) AS free_counseling_1,
       @gokei:=@gokei+sum(member_status_id = 0 AND ordinal_attendance = 1) as gokei
       
FROM counseling
WHERE counseling_date BETWEEN '2018-06-01' AND '2018-06-30'
group by date

これは工夫が必要なので、case文を使うなど工夫するしかなさそうだ。

解決策ご存知の方はご教示ください

 

More posts