詳説 MySQLで累積和を求める方法

mysql-accumlate.md

MySQL で累積和を求める方法

• MySQL で累積和を求める - 私が歌川です
• 日ごとの保存した画像の枚数とその累積を取るSQL
• 画像1枚とテーブルの1行が対応付いている
• 変数は邪道なので使いたくない

SELECT
    t1.accum_date
  , t1.count
  , SUM(t2.count) AS accum
FROM (
    SELECT
        DATE(created_at) AS accum_date
      , COUNT(*) AS count
    FROM
        images
    GROUP BY accum_date
    ORDER BY accum_date
) AS t1
JOIN (
    SELECT
        DATE(created_at) AS accum_date
      , COUNT(*) AS count
    FROM
        images
    GROUP BY accum_date
    ORDER BY accum_date
) AS t2
ON
    t1.accum_date >= t2.accum_date
GROUP BY 1
ORDER BY 1

解説

累積和

• 始点からある地点までの値の和

日付	値	累積和
2017-10-01	1	1
2017-10-02	5	6
2017-10-03	10	16
2017-10-04	11	27

日ごとの画像保存件数を取得する

SELECT
    DATE(created_at) AS accum_date
  , COUNT(*) AS count
FROM
    images
GROUP BY 1
ORDER BY 1

accum_date	count
2015-10-15	14
2015-10-16	160
2015-10-17	760
2015-10-18	81
2015-10-19	103

• 日付ごとに COUNT(*) を取るだけ
• ついでなので今後の簡単のためにビューを作っておく
  • 同じクエリを何度も書かなくていい

CREATE VIEW save_by_day AS
SELECT
    DATE(created_at) AS accum_date
  , COUNT(*) AS count
FROM
    images
GROUP BY 1
ORDER BY 1

save_by_day 同士を左外部結合させる

左外部結合

SELECT * FROM t1
LEFT OUTER JOIN t2
AS (expr)

• テーブルt1の行に対して，テーブルt2のうち expr の条件を満たす行を連結させる
• t1の行に対応するt2の行が存在しない場合は全部 NULL の行が入る

id	name
1	utgwkk
2	nonylene
3	suzusime
4	drafear

user_id	title	score
2	hoge	3
2	fuga	4
3	piyo	4
4	payo	5

SELECT u.name, g.title, g.score
FROM user u
LEFT OUTER JOIN grade g
AS u.id = g.user_id

name	title	score
utgwkk	NULL	NULL
nonylene	hoge	3
nonylene	fuga	4
suzusime	piyo	4
drafear	payo	5

実践

• さっき作った save_by_day ビュー(s1)に対して save_by_day ビュー(s2)を左外部結合させる
• 結合条件は s1.accum_date >= s2.accum_date

SELECT
    s1.*
  , s2.*
FROM
    save_by_day s1
LEFT OUTER JOIN save_by_day s2
ON
    s1.accum_date >= s2.accum_date
ORDER BY 1, 3

s1.accum_date	s1.count	s2.accum_date	s2.count
2015-10-15	14	2015-10-15	14
2015-10-16	160	2015-10-15	14
2015-10-16	160	2015-10-16	160
2015-10-17	760	2015-10-15	14
2015-10-17	760	2015-10-16	160
2015-10-17	760	2015-10-17	760
2015-10-18	81	2015-10-15	14
2015-10-18	81	2015-10-16	160
2015-10-18	81	2015-10-17	760
2015-10-18	81	2015-10-18	81

• s1.accum_date ごとに SUM(s2.count) を取ればいけそう

SELECT
    s1.accum_date
  , s1.count
  , SUM(s2.count) AS accum
FROM
    save_by_day s1
LEFT OUTER JOIN save_by_day s2
ON
    s1.accum_date >= s2.accum_date
GROUP BY 1
ORDER BY 1

accum_date	count	accum
2015-10-15	14	14
2015-10-16	160	174
2015-10-17	760	934
2015-10-18	81	1015
2015-10-19	103	1118
2015-10-20	223	1341
2015-10-21	144	1485
2015-10-22	201	1686
2015-10-23	60	1746
2015-10-24	285	2031

• ヤッター！

同じことをPostgreSQLでする場合

• もっと簡単に取得できる
• https://www.postgresql.jp/document/9.6/html/tutorial-window.html
• PostgreSQLを使いましょう

SELECT
    accum_date
  , count
  , SUM(count) OVER (ORDER BY accum_date)
FROM save_by_day