月毎の累計を行うクエリ

先日、年毎の累計を行うクエリについての記事を書きました。

patorash.hatenablog.com

すると、「月毎のデータも欲しいです」と言われたため、そちらもやってみました。

集計関数を使うと月のデータが歯抜けになる問題

前回のクエリを改修すればすぐできるだろうと思っていたのですが、月毎で集計すると、データの登録が全くない月などもありまして、歯抜けのデータが作られました。以下のような感じ。

SELECT 
  date_trunc('month', created_at)::date AS "月",
  COUNT(*) AS "月間登録数"
FROM foos
GROUP BY date_trunc('month', created_at)::date
ORDER BY "月" ASC;

月	月間登録数
2013-06-01	10
2013-08-01	10
2013-09-01	10
2013-11-01	10
2013-12-01	10

2013-07-01と2013-10-01が抜けてます…。これでは欲しいデータになりません。

月データを生成する

Postgresqlには、generate_seriesという便利な関数があるので、これを利用して基準の月データを作ってみましょう。

SELECT
  generate_series('2013-06-01','2013-12-01', '1 month'::interval)::date AS month
ORDER BY month ASC;

結果は以下の通り。

月
2013-06-01
2013-07-01
2013-08-01
2013-09-01
2013-10-01
2013-11-01
2013-12-01

これに対して、JOINしていきます。

基準の月データに対してJOINする

あとは前回と同様、対象月以前のデータを連結するという条件でJOINします。

SELECT
  months.month AS "月",
  SUM(foos.created_count) AS "累計登録数"
FROM (
  SELECT
    generate_series('2013-06-01','2013-12-01', '1 month'::interval)::date AS month
) months
LEFT OUTER JOIN (
  SELECT 
    date_trunc('month', created_at)::date AS month,
    COUNT(*) AS created_count
  FROM foos
  GROUP BY date_trunc('month', created_at)::date
) foos ON months.month >= foos.month
GROUP BY months.month
ORDER BY months.month ASC

結果は以下の通り。

月	累計登録数
2013-06-01	10
2013-07-01	10
2013-08-01	20
2013-09-01	30
2013- 10-01	30
2013-11-01	40
2013-12-01	50

集計関数を使うと歯抜けになりがちなデータも、これでバッチリですね！