postgresで累計グラフ用のデータを作成

postgresで累計グラフ(積み上げ折れ線グラフ)を作成するための集計を実施します。
使うようで使わない気がしますが、私では利用する頻度が多いのでメモ含め書き留めます。


今回は、あるシステムで６月の日付ごとにコストと集客人数を
入力したテーブルに対して６月の日毎のと累計を知りたいのが目的とします。

環境

環境情報	構築日	ソフトウェア
CentOS 5.6	2013/06/18	postgresql 8.4以降

今回利用するSQLではpostgresql 8.4以降でないと扱えない文法のため 8.4以上でお試しください。 まずは、お決まりのテーブルを作成

-- コスト、集客管理
CREATE TABLE report (
 date TIMESTAMP, -- 集計日
 cost NUMERIC(19, 0), -- コスト 
 person NUMERIC(19, 0) -- 集客人数
);

-- 日毎のデータ
INSERT INTO report VALUES
('2013/06/01', 100, 1),
('2013/06/02', 120, 3),
('2013/06/03', 150, 0),
('2013/06/04', 110, 6),
('2013/06/05', 130, 1),
('2013/06/06', 120, 4),
('2013/06/07', 160, 5),
('2013/06/08', 190, 7),
('2013/06/09', 100, 1),
('2013/06/10', 110, 9),
('2013/06/11', 100, 8),
('2013/06/12', 100, 5),
('2013/06/13', 130, 4),
('2013/06/14', 120, 3),
('2013/06/15', 100, 6),
('2013/06/16', 100, 1),
('2013/06/17', 110, 0),
('2013/06/18', 100, 0),
('2013/06/19', 170, 1),
('2013/06/20', 100, 2),
('2013/06/21', 100, 6),
('2013/06/22', 190, 7),
('2013/06/23', 100, 9),
('2013/06/24', 100, 1),
('2013/06/25', 190, 8),
('2013/06/26', 100, 6),
('2013/06/27', 120, 7),
('2013/06/28', 120, 4),
('2013/06/29', 150, 2),
('2013/06/30', 110, 1);

上記のデータが元データです。 集計には「over」と呼ばれるWindow関数を利用します。

-- 集計用SQL
SELECT date, SUM(cost) over (order by date) AS cost, SUM(person) over (order by date) AS person FROM report;
        date         | cost | person 
---------------------+------+--------
 2013-06-01 00:00:00 |  100 |      1
 2013-06-02 00:00:00 |  220 |      4
 2013-06-03 00:00:00 |  370 |      4
 2013-06-04 00:00:00 |  480 |     10
 2013-06-05 00:00:00 |  610 |     11
 2013-06-06 00:00:00 |  730 |     15
 2013-06-07 00:00:00 |  890 |     20
 2013-06-08 00:00:00 | 1080 |     27
 2013-06-09 00:00:00 | 1180 |     28
 2013-06-10 00:00:00 | 1290 |     37
 2013-06-11 00:00:00 | 1390 |     45
 2013-06-12 00:00:00 | 1490 |     50
 2013-06-13 00:00:00 | 1620 |     54
 2013-06-14 00:00:00 | 1740 |     57
 2013-06-15 00:00:00 | 1840 |     63
 2013-06-16 00:00:00 | 1940 |     64
 2013-06-17 00:00:00 | 2050 |     64
 2013-06-18 00:00:00 | 2150 |     64
 2013-06-19 00:00:00 | 2320 |     65
 2013-06-20 00:00:00 | 2420 |     67
 2013-06-21 00:00:00 | 2520 |     73
 2013-06-22 00:00:00 | 2710 |     80
 2013-06-23 00:00:00 | 2810 |     89
 2013-06-24 00:00:00 | 2910 |     90
 2013-06-25 00:00:00 | 3100 |     98
 2013-06-26 00:00:00 | 3200 |    104
 2013-06-27 00:00:00 | 3320 |    111
 2013-06-28 00:00:00 | 3440 |    115
 2013-06-29 00:00:00 | 3590 |    117
 2013-06-30 00:00:00 | 3700 |    118
(30 行)

苦労してSQLをくまないと行けないと考えていましたがWindow関数で簡単にできました。 EC売り上げ集計や、広告レポートを集計するのに役に立ちそうです。