Shulou(Shulou.com)06/01 Report--

What the editor wants to share with you this time is how to achieve cumulative computing in mysql. The article is rich in content. Interested friends can learn about it. I hope you can get something after reading this article.

Preface

After receiving a demand, the product wants to analyze the curve of user growth. That is, a list of the total daily number of people for a certain period of time. In order to evaluate the effectiveness of recent activities. This statistic sql still took me a short time. It takes some skill for mysql to calculate this.

Requirements Analysis user_idreg_time12019-09-0322019-09-0432019-09-0442019-09-0552019-09-0562019-09-06

If the above table user_info, we can easily count the daily increase according to the time dimension. Sql is as follows:

Select reg_time, count (user_id) daily_quantity from user_infogroup by reg_time

From the sql above, we can easily come up with the following list:

Reg_timedaily_quantity2019-09-0312019-09-0422019-09-0522019-09-061

But this is not what we want. What we want is the total number of people of the previous day plus the net growth of today, and so on. That is, we want to:

Reg_timedaily_quantity2019-09-0312019-09-0432019-09-0552019-09-066

This is a little tricky, and we need to do an accumulation calculation. I tried some operations such as self-linking, functions, and so on, but I still didn't get a correct result. At this point, it would be more appropriate for the java code to handle this. All we have to do is declare the initial value, and then add up the loop 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]; / / cumulative fill for (int I = 0; I < dailyIncrQuantity.length) Result +) {base + = dailyIncrQuantity [I]; result [I] = base;} return result;}

The pseudo code above can calculate the result. Of course, if possible, try to do this complex operation in the java business code. But the requirement given by the product is that we can provide an sql that can get the answer he wants directly in the visual data engine. So I got the inspiration from the above code.

Does mysql have such a variable? Yes! Of course there is. Remember the very common scenario, there are often businesses that require us to output sequence numbers. Oracle comes with a pseudo-column rownum, but mysql does not. Mysql usually generates sequence numbers by declaring self-incrementing variables. Take the user_info table as an example:

Select (@ i:=@i+1) as rownum, user_id from user_info, (select @ iVist0) as rMysql user variable

Mysql variables are divided into local variables, user variables, session variables and global variables. In the above statement, we use user variables. The user variable is related to the database connection, and the variable declared in the connection disappears after the user variable is created in the 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 beforehand, but are declared in the format @ varname when used. Assign the value through: = or =. If you need output, you need to use the select keyword, and the assignment must use: =.

Cumulative calculation using Mysql user variable

After learning the user variables, we know how to do the cumulative calculation, so the total daily total number of people in the user_info table should be as follows:

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 @ ii:=@i+a.daily as daily_quantity from) 0) b

The results of the query are as follows, which meets the logical needs.

Reg_timedailydaily_quantity2019-09-03112019-09-04232019-09-05252019-09-0616

But here is a small pit, in the actual business @ I initialization may not be 0, for example, we counted the total number of people before September 4 to September 6 as 1. You should pay special attention to this. In the actual business development, if we can implement it in the logic coding, it is still recommended to carry out some complex operations in the logic coding.

After reading this article on how mysql implements cumulative computing, if you think the article is well written, you can share it with more people.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.