MySQL で積み上げ集計
SQL で集計と言えば GROUP BY と SUM とか COUNT を使う方法が一般的ですが、一緒に積み上げたデータ？加算したデータも取得したいという感じです。
イメージとしてはこんな感じ。
元々のデータとして以下の様なテーブル Sales があったとします。
+--------+------+-------+ | date | name | price | +--------+------+-------+ | 5月1日 | A | 1500 | | 5月2日 | A | 2000 | | 5月2日 | B | 1500 | | 5月3日 | C | 3000 | | 5月4日 | A | 1000 | | 5月4日 | C | 2500 | +--------+------+-------+
購入日、購入者、購入金額的な感じだと思ってください。
これを以下の様な感じで集計したいという話。
+--------+-------------+-------------+ | date | daily_sales | total_sales | +--------+-------------+-------------+ | 5月1日 | 1500 | 1500 | | 5月2日 | 3500 | 5000 | | 5月3日 | 3000 | 8000 | | 5月4日 | 3500 | 11500 | +--------+-------------+-------------+
左から、
売り上げ発生日、日々の売り上げ、その日までの売上の合計
っと言った感じです。
で、日々の売り上げを集計する場合、普通に GROUP BY を使った集計はだいたい以下の様な感じの SQL文になると思います。
SELECT date, sum(price) daily_sales FROM Sales GROUP BY date
この場合の、結果は以下の様な感じです。
+--------+-------------+ | date | daily_sales | +--------+-------------+ | 5月1日 | 1500 | | 5月2日 | 3500 | | 5月3日 | 3000 | | 5月4日 | 3500 | +--------+-------------+
これでも十分ですが、その日までの売上の合計は出てきません。
イメージだと、こういう集計を行うための関数があってそれを使えば一発で出てきそうなんですが、探しても見つけられませんでした。
WITH ROLLUP はイメージとしては結構近いのですが、今回のケースでは上手く行きません。
なので強引な方法でやってみました。
SELECT date, sum(price) daily_sales, ( SELECT sum(price) FROM Sales WHERE date <= tmp.date ) total_sales FROM Sales tmp GROUP BY date
先程の SQL に total_sales として
SELECT sum(price) FROM Sales WHERE date <= tmp.date
の実行結果を入れていく感じです。
これで、
+--------+-------------+-------------+ | date | daily_sales | total_sales | +--------+-------------+-------------+ | 5月1日 | 1500 | 1500 | | 5月2日 | 3500 | 5000 | | 5月3日 | 3000 | 8000 | | 5月4日 | 3500 | 11500 | +--------+-------------+-------------+
の様な結果を取得できます。
まぁ、最終的に取得したデータを PHP とかで計算すれば良いのかもしれないですが、集計とかは SQL でやった方がスマートかなぁ...と思いやってみましたが、全然スマートじゃない。
他に良い方法は有るんでしょうかねぇ？
コメントする