随着时间推移的表统计信息（又称行计数） [英] Table statistics (aka row count) over time

问题描述

我正在准备一个关于我们的应用程序的演示文稿，并问自己以下问题：基于存储在我们的数据库中的数据，在过去几年中发展了多少增长？

所以我想在一个输出/图表中显示自项目开始以来存储的数据量。

我的当前查询如下：


SELECT DATE_FORMAT（created，'％y-％m' COUNT（id）FROM table GROUP BY label ORDER BY标签;


示例输出将是：

• 11-03：5


• 11-04：200


• 11-05：300

不幸的是，此查询缺少累积。我想收到以下结果：

• 11-03：5


• 11-04 ：205（200 + 5）


• 11-05：505（200 + 5 + 300）

有没有办法在mysql中解决这个问题，而不需要在php循环中调用查询？

解决方案

是的，有一种方法。一种方法使用MySQL用户定义的变量（和不能保证的行为）

  SELECT s.label 
，s .cnt 
，@tot：= @tot + s.cnt AS running_subtotal 
 FROM（SELECT DATE_FORMAT（t.created，'％y-％m'）AS`label` 
，COUNT （t.id）AS cnt 
 FROM articles t 
 GROUP BY`label` 
 ORDER BY`label` 
）s 
 CROSS 
 JOIN @tot：= 0）i 
  

让我们解开一下。

以 s 形式返回的内联视图返回与原始查询相同的结果集。

内联视图以 i 形式返回一行。我们不关心它返回什么（除了我们需要它返回一行，因为JOIN操作）;我们关心的是副作用，零值被分配给 @tot 用户变量。

由于MySQL将内联视图实现为派生表，在外部查询运行之前，该变量在外部查询运行之前被初始化。

对于由外部查询处理的每一行， cnt 的值将添加到 @tot 。

MySQL参考手册特别指出，这种用户定义变量的行为不能保证。

i'm preparing a presentation about one of our apps and was asking myself the following question: "based on the data stored in our database, how much growth have happend over the last couple of years?"

so i'd like to basically show in one output/graph, how much data we're storing since beginning of the project.

my current query looks like this:

SELECT DATE_FORMAT(created,'%y-%m') AS label, COUNT(id) FROM table GROUP BY label ORDER BY label;

the example output would be:

• 11-03: 5
• 11-04: 200
• 11-05: 300

unfortunately, this query is missing the accumulation. i would like to receive the following result:

• 11-03: 5
• 11-04: 205 (200 + 5)
• 11-05: 505 (200 + 5 + 300)

is there any way to solve this problem in mysql without the need of having to call the query in a php-loop?

解决方案

Yes, there's a way to do that. One approach uses MySQL user-defined variables (and behavior that is not guaranteed)

SELECT s.label
     , s.cnt
     , @tot := @tot + s.cnt  AS running_subtotal
  FROM ( SELECT DATE_FORMAT(t.created,'%y-%m') AS `label`
              , COUNT(t.id) AS cnt
           FROM articles t
          GROUP BY `label`
          ORDER BY `label`
       ) s
 CROSS
  JOIN ( SELECT @tot := 0 ) i

Let's unpack that a bit.

The inline view aliased as s returns the same resultset as your original query.

The inline view aliased as i returns a single row. We don't really care what it returns (except that we need it to return exactly one row because of the JOIN operation); what we care about is the side effect, a value of zero gets assigned to the @tot user variable.

Since MySQL materializes the inline view as a derived table, before the outer query runs, that variable gets initialized before the outer query runs.

For each row processed by the outer query, the value of cnt is added to @tot.

The return of s.cnt in the SELECT list is entirely optional, it's just there as a demonstration.

N.B. The MySQL reference manual specifically states that this behavior of user-defined variables is not guaranteed.

这篇关于随着时间推移的表统计信息（又称行计数）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！