mysql - 从某个时间戳获取总计

Question

我有一列时间戳，我希望在我能看到的地方得到一个结果

1. 特定日期添加条目的数量(added_on_this_date)
2. 和自开始以来的总金额(total_since_beginning)

我的 table :

added
==========
1392040040
1392050040
1392060040
1392070040
1392080040
1392090040
1392100040
1392110040
1392120040
1392130040
1392140040
1392150040
1392160040
1392170040
1392180040
1392190040
1392200040

结果应该是这样的:

date       | added_on_this_date | total_since_beginning
=========================================================
2014-02-10 |                  4 |                     4
2014-02-11 |                  9 |                    13
2014-02-12 |                  4 |                    17

我使用的查询给出了错误的结果

SELECT FROM_UNIXTIME(added, '%Y-%m-%d') AS date,
  count(*) AS added_on_this_date,

  (SELECT COUNT(*) FROM mytable t2 WHERE t2.added <= t.added) AS total_since_beginning 

FROM mytable t WHERE 1=1 GROUP BY date

为了更好地理解，我创建了一个 fiddle :http://sqlfiddle.com/#!2/a72a9/1

Answer 1

您的混合时间戳和 yyyy-mm-dd 日期...

当您按 yyyy-mm-dd 分组时，您不确定将采用哪个时间戳。

你可以做

SELECT FROM_UNIXTIME(added, '%Y-%m-%d') AS date,
  count(*) AS added_on_this_date,

  (SELECT COUNT(*) FROM mytable t2 WHERE FROM_UNIXTIME(t2.added, '%Y-%m-%d') <= FROM_UNIXTIME(t.added, '%Y-%m-%d')) AS total_since_beginning 

FROM mytable t  GROUP BY date