Php+mysql a detailed description of the optimization steps for data statistics during fixed time periods

This time, we bring you php+mysql. For a fixed time period of data statistical optimization steps in detail, php+mysql on the fixed time period of statistical optimization of the points of attention, the following is the actual case, together to see.

In Internet projects, data analysis of the project is essential. The daily data totals change trend in a certain period of time to adjust the marketing strategy. Here's a look at the following cases.

Case

In the e-commerce platform, there is usually an order form that records all order information. Now we need to count the number of orders per day of a month and the amount of sales data in order to draw the following chart for data analysis.

The Order table data structure is as follows:

order_id	ORDER_SN	Total_price	enterdate
25396	a4e610e250c2d378d7ec94179e14617f	2306.00	2017-04-01 17:23:26
25397	Ead217c0533455eecdde39659abcdae9	17.90	2017-04-01 22:15:18
25398	032e6941dad44f29651b53c41f6b48a0	163.03	2017-04-02 07:24:36

At this time to query a month of the world singular, the total amount should be how to do?

General method

First, the easiest way to think about it is to take the PHP function to get the number of days cal_days_in_month() of the month, then construct an array of all days of the month, then query the total number of each day in the loop and construct a new array.

The code is as follows:

$month = ' _day '; $year = '; $max = Cal_days_in_month (Cal_gregorian, $month, $year);   The last day of the month//constructs an array of daily $days_arr = array (); for ($i =1; $i <= $max _day; $i + +) {  Array_push ($days _arr, $i);} $return = Array (),//query foreach ($days _arr as $val) {$min = $year. '-' $month. '-' $val. '  00:00:00 ';  $max = $year. '-'. $month. '-'. $val. ' 23:59:59 ';  $sql = "SELECT count (*) as total_num,sum (' Total_price ') as amount from ' orders ' where ' enterdate ' >= {$min} and ' Enterd Ate ' <= {$max} ';  $return [] = Mysqli_query ($sql);} return $return;

This SQL is simple, but you need to make 30 queries each time, seriously slow down the response time.

Optimization

How do I use a single SQL to directly query the total number of days?

At this point, we need to take advantage of the MySQL date_format function, in the subquery first to find out the month all orders, and the Enterdate with Date_format function converted to days, and then by day group by group statistics. The code is as follows:

$month = ' _day '; $year = '; $max = Cal_days_in_month (Cal_gregorian, $month, $year);   Last day of the month $min = $year. '-'. $month. ' -01 00:00:00 '; $max = $year. '-' $month. '-' $max _day. ' 23:59:59 '; $sql = "Select T.enterdate,count (*) as Total_num,sum (T.total_price) as Amount (select Date_format (enterdate, ' %e ') as Enterdate,total_price from orders where enterdate between {$min} and {$max}) T GROUP by t.enterdate ORDER by t.ent Erdate "; $return = Mysqli_query ($sql);

This reduces the 30 queries to 1 times, and the response time is greatly increased.

Attention:

1. Due to the need to query all the data of the month, when the volume of data is too large, it is inappropriate to take this method.

2. In order to avoid data loss due to lack of data on the day, the data should be processed according to demand after the query.

Believe that you have read the case of this article you have mastered the method, more exciting please pay attention to the PHP Chinese network other related articles!

Recommended reading:

PHP implementation of the single sign-on steps detailed

Php+rsa generate key bits in 1024-bit steps