Detailed explanation of the steps to optimize data statistics within a fixed time period using PHP+MySQL

This time I will bring you a detailed explanation of the steps for PHP MySQL to optimize data statistics within a fixed time period. What are the precautions for PHP MySQL to optimize data statistics within a fixed time period? The following is a practical case. Get up and 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:

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);

In this way, reducing 30 queries to 1, the response time will be greatly improved.

Note:

1. Since all the data of the current month needs to be queried, this method is not suitable when the amount of data is too large.

2. In order to avoid data loss caused by no data on that day, after querying, the data should be processed according to needs.

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:

Detailed explanation of the steps to implement single sign-on with PHP

PHP RSA generated key number is 1024 bits Detailed explanation of steps

The above is the detailed content of Detailed explanation of the steps to optimize data statistics within a fixed time period using PHP+MySQL. For more information, please follow other related articles on the PHP Chinese website!