MySQL中的累计计数 [英] Cumulative count in mysql

问题描述

亲爱的所有人，
我可以在mysql中使用group by进行按日期的计数.但是我想要在mysql中按日期的计数.如何?


例如，

Dear all,
I can take date wise count using group by in mysql.But i want date wise cumulative count in mysql.How?


For example,

date count
10-11-2011 200
11-11-2011 500
12-11-2011 700

这是天数.

但是我想要按日累计计数.

This is day wise count.

But i want day wise cumulative count.

date count
10-11-2011 200
11-11-2011 700
12-11-2011 1400

像这样.

like this.

推荐答案

这里是一种可能的解决方案.创建一个将日期作为输入并计算该日期的累计计数然后返回计数的函数.然后在原始查询中，用对函数的调用替换count(some_field)，并向该函数传递行的日期.

Here''s one possible solution. Create a function that takes a date as an input and calculates the cumulative count for that date and then returns the count. Then in your original query, replace the count(some_field) with a call to the function, passing the function the row''s date.

create or replace function calc_cumulative_count(input_date DATE)
  return   LONG
as
  cumulative_count     LONG;
  select count(your_field_name)
    into cumulative_count
    from your_table
   where some_date_field <= input_date;

  return cumulative_count;
end calc_cumulative_count;
/

如果您现有的查询是

If your exisiting query is

select some_date_field
      ,count(your_field_name)
  from your_table
 group by some_date_field
;

可以更改为

this can be changed to

select distinct some_date_field
      ,calc_cumulative_count(some_date_field)
  from your_table
;

这篇关于MySQL中的累计计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！