PHP+MySQL timing data statistics optimization

This time I will bring you PHP+MySQL timing data statistics optimization. What are the precautions for PHP+MySQL timing data statistics optimization? The following is a practical case, let’s take a look.

In Internet projects, data analysis of the project is essential. Usually, the trend of total daily data changes within a certain period of time is calculated to adjust marketing strategies. Let’s look at the following cases.

Case

There is usually an order form in the e-commerce platform to record all order information. Now we need to count the number of orders and sales amount per day in a certain month to draw the following statistical 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, how to query the number of orders placed on each day of a certain month and the total amount?

General method

The first and easiest way to think of is to use the php function <a href="http://www.php.cn/wiki/1230.html" target="_blank">cal_days_in_month</a>() Get the number of days in the month, then construct an array of all days in the month, then query the total number of each day in the loop, and construct a new array.

The code is as follows:

$month = '04';
$year = '2017';
$max_day = cal_days_in_month(CAL_GREGORIAN, $month, $year);   //当月最后一天
//构造每天的数组
$days_arr = array();
for($i=1;$i<=$max_day;$i++){
  array_push($days_arr, $i);
}
$return = array();
//查询
foreach ($days_arr as $val){
  $min = $year.&#39;-&#39;.$month.&#39;-&#39;.$val.&#39; 00:00:00&#39;;
  $max = $year.&#39;-&#39;.$month.&#39;-&#39;.$val.&#39; 23:59:59&#39;;
  $sql = "select count(*) as total_num,sum(`total_price`) as amount from `orders` where `enterdate` >= {$min} and `enterdate` <= {$max}";
  $return[] = mysqli_query($sql);
}
return $return;

This SQL is simple, but it requires 30 queries each time, which seriously slows down the response time.

Optimization

How to use a sql to directly query the total quantity of each day?

At this time, you need to use the date_format function of mysql to first find out all the orders of the current month in the subquery, and convert the enterdate into days using the date_format function, and then group by Group statistics. The code is as follows:

$month = '04';
$year = '2017';
$max_day = cal_days_in_month(CAL_GREGORIAN, $month, $year);   //当月最后一天
$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.enterdate";
$return = mysqli_query($sql);

I believe you have mastered the method after reading the case in this article. For more exciting information, please pay attention to other related articles on the PHP Chinese website!

Recommended reading:

Mysqld_multi deployment stand-alone detailed explanation

How to query different databases with one SQL statement

How to use React to develop component libraries

The above is the detailed content of PHP+MySQL timing data statistics optimization. For more information, please follow other related articles on the PHP Chinese website!