Skip to content

Instantly share code, notes, and snippets.

@utgwkk
Last active May 30, 2023 09:34
Show Gist options
  • Save utgwkk/25703647ad9001b34f68e6c4dee84e47 to your computer and use it in GitHub Desktop.
Save utgwkk/25703647ad9001b34f68e6c4dee84e47 to your computer and use it in GitHub Desktop.
詳説 MySQLで累積和を求める方法

MySQL で累積和を求める方法

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でする場合

SELECT
    accum_date
  , count
  , SUM(count) OVER (ORDER BY accum_date)
FROM save_by_day
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment