月次累計を求めるSQL
こんなデータがあるときに
Date | Sales |
---|---|
2017-07-01 | 110 |
2017-07-02 | 120 |
2017-07-03 | 130 |
・・・ | ・・・ |
2017-07-31 | 410 |
2017-08-01 | 100 |
2017-08-02 | 110 |
ひとつカラムを追加して、月次累計(Monthly Running Total)を求めたい場合に
Date | Sales | MRT_Sales |
---|---|---|
2017-07-01 | 110 | 110 |
2017-07-02 | 120 | 230 |
2017-07-02 | 130 | 360 |
・・・ | ・・・ | ・・・ |
2017-07-31 | 410 | 8060 |
2017-08-01 | 100 | 100 |
2017-08-02 | 110 | 210 |
こんな感じでSQLを書く。ここで、GROUP BY 1,2はGROUP BY t1.Date,t1.Salesと同じ意味。
SELECT t1.Date ,t1.Sales ,sum(t2.Sales) RunningTotal_Sales FROM Daily_Sales t1 ,Daily_Sales t2 WHERE year(t1.Date)=year(t2.Date) AND month(t1.Date)=month(t2.Date) AND t1.Date >= t2.Date GROUP BY 1,2
ここでなぜsum(t2.Sales)が月次累計になるかというと、以下のように集計を外したSQLを書くと
SELECT t1.Date ,t1.Sales ,t2.Sales t2_Sales FROM Daily_Sales t1 ,Daily_Sales t2 WHERE year(t1.Date)=year(t2.Date) AND month(t1.Date)=month(t2.Date) AND t1.Date >= t2.Date
こんな感じの結果セットが取得できる。(1日目は1レコード、2日目は2レコード、・・・31日目は31レコード)
Date | Sales | t2_Sales | |
---|---|---|---|
2017-07-01 | 110 | 110 | |
2017-07-02 | 120 | 110 | ←7/1の値 |
2017-07-02 | 120 | 120 | |
2017-07-03 | 130 | 110 | ←7/1の値 |
2017-07-03 | 130 | 120 | ←7/2の値 |
2017-07-03 | 130 | 130 | |
・・・ | ・・・ | ・・・ | |
2017-08-01 | 100 | 100 | |
2017-08-02 | 110 | 100 | ←8/1の値 |
2017-08-02 | 110 | 110 |
これに対してsum(t2.Sales) GROUP BY t1.Date,t1.Salesをするので、月次累計となる。
もし日ごと製品カテゴリごとの売上というテーブルがあった場合は、同様にSELECT句・WHERE句・GROUP BY句に製品カテゴリを含めればOK。