Detailed explanation of MySQL cumulative calculation implementation method

This article describes the MySQL cumulative calculation example. Share with you for your reference, the details are as follows:

Preface

We received a request and the product wanted to analyze the user growth curve. That is, a list of the total number of people per day for a certain period of time. It is better to conduct an evaluation of the effectiveness of recent activities. This statistical SQL still took me a while. MySQL statistics still require certain skills.

Demand Analysis

If we look at the user_info table above, we can easily count the number of new users added each day based on the time dimension. The sql is as follows:

select reg_time, count(user_id) daily_quantity
 from user_info
group by reg_time 

Through the above sql we can easily get the following list:

But this is not the result we want. What we want is last day's total plus today's net increase, and so on. That is, we want:

This is a bit tricky, we need to do some cumulative calculations. I tried self-linking, functions and other operations but still couldn't get a correct result. At this point, it seems like it would be perfect if Java code were used to handle this. We only need to declare the initial value, and then loop and accumulate to calculate the result:

 public static void main(String[] args) {
  int[] arr = {1, 2, 2, 1};

  int[] ints = dailyQuantityArr(0, arr);


  for (int i : ints) {
   System.out.println("i = " + i);
  }


 }


 public static int[] dailyQuantityArr(int base, int[] dailyIncrQuantity) {


  int[] result = new int[dailyIncrQuantity.length];
  // Accumulate filling for (int i = 0; i < dailyIncrQuantity.length; i++) {
   base += dailyIncrQuantity[i];

   result[i] = base;

  }
  return result;
 }

The above pseudo code can calculate the result. Of course, if possible, try to perform this complex calculation in Java business code. However, the product requirement is that we can provide a SQL statement that can directly get the answer he wants in the visual data engine. So I got inspiration from the above code.
Does mysql have such a variable? have! Of course. I remember a very common scenario. In the past, there were often businesses that required us to output serial numbers. Oracle comes with a pseudo column rownum, but MySQL does not. MySQL usually generates serial numbers by declaring auto-increment variables. Take the user_info table as an example:

select (@i:=@i+1) as rownum, user_id 
 from user_info ,(select @i:=0) as r

Mysql user variables

MySQL variables are divided into local variables, user variables, session variables, and global variables. In the above statement we use user variables. User variables are related to database connections. Variables declared in a connection disappear after they are created in a stored procedure until the database instance is disconnected. Variables declared in this connection cannot be used in another connection.
User variables in MySQL do not need to be declared in advance, and are declared in the format of @varname when used. Assignment operations are performed through := or = . If you need to output externally, you need to use select keyword, and the assignment must use := .

Using Mysql user variables for cumulative calculations

After learning about user variables, we know how to perform cumulative calculations. The daily cumulative total number of people in the user_info table should be like this:

 
 select a.reg_time,
    a.daily,
   @i:=@i+a.daily as daily_quantity 
  from (select reg_time ,  
  count(user_id) daily 
  from user group by reg_time ) a ,
  (select @i:=0) b

The query results are as follows, which meet the logical requirements.

But there is a small pitfall here. In actual business, @i may not be 0 when initialized. For example, the total number of people from September 4th to September 6th is 1. Pay special attention to this. In actual business development, if we can implement it in logical coding, it is recommended to perform some complex operations in logical coding.

Summarize

Today we solved a problem of cumulative calculation in SQL through MySQL user variables. I wonder if you have any other good ideas? Welcome to leave a message for discussion.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.