MySQL 如何計算累加數量?

發表於 更新於 所需閱讀時間 ≈ 1 分鐘

情境: 每天都有會員加入，想要看每日顯示累積到當日的會員總數

日期 今日註冊人數 累積人數
2021-01-01 5 5
2021-01-02 2 7
2021-01-03 1 8
2021-01-01 10 18

實作

1. 子查詢方式

缺點: 由於會去加總當日之前的會員，所以遞迴方式加總，效率不太好

範例:

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
	DATE( created_at ),
	(
SELECT
	count( * ) 
FROM
	member m2 
WHERE
	DATE( m2.created_at ) <= DATE( m1.created_at ) 
	) 
FROM
	member m1

2. 透過變數方式累積加總

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SET @count = 0;
SELECT
	today.date,
	@count := @count + today.count AS today_register_num 
FROM
	(
SELECT
	DATE( created_at ) AS date,
	count( member.id ) AS count 
FROM
	member 
GROUP BY
	DATE( createtime ) 
	) today