年毎の累計を行うクエリ

担当しているサービスのマスタデータにて、年毎にどれ位の登録数が増えていっていたかを見たいと言われたのでSQLを組んでみたのだが、なかなか思いつかなかったのでメモを残しときます。

年毎の累計とは

以下のような感じで、年間登録数と、累計登録数を使いたいという場合の話です。

年	年間登録数	累計登録数
2013	100	100
2014	100	200
2015	100	300
2016	100	400
2017	100	500
2018	100	600

年毎の登録数を出す

年間登録数はサクッとGROUP BY date_part('year', created_at)で出せます。

SELECT 
  date_part('year', created_at) AS "年",
  COUNT(*) AS "年間登録数"
FROM foos
GROUP BY date_part('year', created_at)
ORDER BY "年" ASC;

年	年間登録数
2013	100
2014	100
2015	100
2016	100
2017	100
2018	100

ここで、累計どうすればええんや…と悩んでしまいました。

過去のデータをJOINせよ！

まずは、全く同じデータをLEFT OUTER JOINして、条件にA.year >= B.yearとします。 Postgresqlのgenerate_series関数を使って、年を生成し、そこに先ほどのデータをINNER JOINして、条件にA.year >= B.yearとします。

SELECT
  A.year AS "A.年",
  B.year AS "B.年",
  B.created_count AS "B.年間登録数",
FROM(
  SELECT generate_series(2013,2018) AS year
) A
INNER JOIN (
  SELECT 
    date_part('year', created_at) AS year,
    COUNT(*) AS created_count
  FROM foos
  GROUP BY date_part('year', created_at)
)  B ON A.year >= B.year
ORDER BY A.year ASC

すると、以下のような感じに…。

A.年	B.年	B.年間登録数
2013	2013	100
2014	2013	100
2014	2014	100
2015	2013	100
2015	2014	100
2015	2015	100
2016	2013	100
2016	2014	100
2016	2015	100
2016	2016	100
2017	2013	100
2017	2014	100
2017	2015	100
2017	2016	100
2017	2017	100
2018	2013	100
2018	2014	100
2018	2015	100
2018	2016	100
2018	2017	100
2018	2018	100

基準の年(A.year)以前の年のデータを全てINNER JOINで連結します。 あとは、GROUP BY A.yearを行い、B.年間登録数をSUMで集計すればOK！

以下が、完成形のSQLです。無駄なSELECTなどは削除してます。

SELECT
  A.year AS "年",
  SUM(B.created_count) AS "累計登録数"
FROM (
  SELECT generate_series(2013,2018) AS year
) A
INNER JOIN (
  SELECT 
    date_part('year', created_at) AS year,
    COUNT(*) AS created_count
  FROM foos
  GROUP BY date_part('year', created_at)
) B ON A.year >= B.year
GROUP BY A.year
ORDER BY A.year ASC

これで、累計データが取れました。

年	累計登録数
2013	100
2014	200
2015	300
2016	400
2017	500
2018	600

晩御飯を食べてる時に、妻に相談したら、ヒントになる話が得られたので、助かりました。

2018-05-11 追記

Postgresqlのgenerate_seriesで年を生成すると、万が一その年のデータが存在しない場合にも歯抜けにならなくて済むのでそうしました。