更改查询SQL [英] Change query sql
问题描述
我有以下查询来获取一年中52周的数据平均值，如下所示:
I have the following query to obtain an average of a data of the 52 weeks of the year as follows:
$dates = array();
$firstDate = date("Y-m-d", strtotime('first day of January 2016'));
$lastDate = date("Y-m-d", strtotime('last day of December 2016'));
for($i=strtotime($firstDate); $i<=strtotime($lastDate); $i+=86400 *7){
array_push($dates, date("Y-m-d", strtotime('monday this week', $i)));
}
for($i = 0; $i < count($dates); $i++){
$sql = "SELECT pr_products.product,
CONCAT(YEAR('".$dates[$i]."'),'-',LPAD(WEEK('".$dates[$i]."'),2,'0')) AS Week,
SUM(IF(sw_sowing.type = 'SW', sw_sowing.quantity,0)) AS PlantSowing,
SUM(IF(ROUND(DATEDIFF(TIMESTAMPADD(DAY,(6 WEEKDAY('".$dates[$i]."')),'".$dates[$i]."'), sw_sowing.date)/7) >= pr_products.week_production AND sw_sowing.type = 'SW',sw_sowing.quantity,0)) AS production
FROM (
SELECT max(sw_sowing.id) AS id
FROM sw_sowing
WHERE sw_sowing.status != 0
AND sw_sowing.id_tenant = :id_tenant
AND sw_sowing.status = 100
AND sw_sowing.date <= TIMESTAMPADD(DAY,(6-WEEKDAY('".$dates[$i]."')),'".$dates[$i]."')
GROUP BY sw_sowing.id_production_unit_detail
) AS sw
INNER JOIN sw_sowing ON sw_sowing.id = sw.id
INNER JOIN pr_products ON pr_products.id = sw_sowing.id_product
INNER JOIN pr_varieties ON sw_sowing.id_variety = pr_varieties.id
INNER JOIN pr_lands ON pr_lands.id = sw_sowing.id_land
WHERE pr_varieties.code != 1
AND sw_sowing.id_product = 1
AND sw_sowing.status = 100
GROUP BY pr_products.product
HAVING plantSowing > 0
ORDER BY pr_products.product";
}
我最初声明两个变量，分别是$firstdate是开始日期和$lastDate是结束日期.
I declare two variables initially that are $firstdate what is the start date and $lastDate which is the end date.
然后我做一个遍历两个日期并将每个星期一的日期保留在数组中的事情.
Then I make a for to go through the two dates and keep in an array the dates of Monday of each week.
然后我遍历新数组以获取每周需要的数据.
Then I go through that new array to get the data I need from week to week.
注意:在查询中，变量$dates[$i]是每周的星期一.
Note: Within the query the variables $dates[$i] are the Monday dates of each week.
无论如何，该查询非常有效，因为它为我带来了一年中52周的数据.问题是需要一段时间.
Anyway, the query works perfectly because it brings me the data I need from the 52 weeks of the year. The problem is that it takes a while.
我已经在mysql中索引了表，我做了一些改进，但还不够，查询实际上并不繁琐，每个周期平均需要0.60秒.
I already indexed the tables in mysql, I improve a little but not enough, the query is not actually heavy it takes an average of 0.60 seconds per cycle.
我想知道是否有可能删除我正在做的事情，并且在查询中添加我不知道的WHERE，它比较两个日期并将数据带给我，或者是否存在任何改进查询的方法.
I would like to know if there is a possibility of deleting the for what I am doing and within the query add I do not know, a WHERE that compares the two dates and brings me the data, or if there is any way to improve the query.
我已经使用答案的建议更新了查询:
I already updated the query with the suggestions of the answer:
$data = array();
$start = new DateTime('first monday of January 2016');
$end = new DateTime('last day of December 2016');
$datePeriod = new DatePeriod($start , new DateInterval('P7D') , $end);
$sql = "SELECT product AS product,
Week AS label,
ROUND(SUM(harvest)/SUM(production),2) AS value
FROM (
(
SELECT pr_products.product,
CONCAT(YEAR(:dates),'-', LPAD(WEEK(:dates1),2,'0')) AS Week,
SUM(IF(sw_sowing.type = 'SW', sw_sowing.quantity,0)) AS PlantSowing,
SUM(IF(ROUND(DATEDIFF(TIMESTAMPADD(DAY,(6-WEEKDAY(:dates2)),:dates3), sw_sowing.date)/7) >= pr_products.week_production AND sw_sowing.type = 'SW',sw_sowing.quantity,0)) AS production,
0 AS Harvest
FROM (
SELECT max(sw_sowing.id) AS id
FROM sw_sowing
WHERE sw_sowing.status != 0
AND sw_sowing.date <= TIMESTAMPADD(DAY,(6-WEEKDAY(:dates4)),:dates5)
GROUP BY sw_sowing.id_production_unit_detail
) AS sw
INNER JOIN sw_sowing ON sw_sowing.id = sw.id
INNER JOIN pr_products ON pr_products.id = sw_sowing.id_product
INNER JOIN pr_varieties ON sw_sowing.id_variety = pr_varieties.id
WHERE pr_varieties.code != 1
AND sw_sowing.id_product = 1
AND sw_sowing.status = 100
AND sw_sowing.id_tenant = :id_tenant
GROUP BY pr_products.product
HAVING plantSowing > 0
ORDER BY pr_products.product
)
UNION ALL
(
SELECT pr_products.product,
CONCAT(YEAR(:dates6),'-', LPAD(WEEK(:dates7),2,'0')) AS Week,
0 AS plantSowing,
0 AS Production,
SUM(pf_harvest.quantity) AS Harvest
FROM pf_harvest
INNER JOIN pr_products ON pr_products.id = pf_harvest.id_product
INNER JOIN pr_varieties ON pr_varieties.id = pf_harvest.id_variety
INNER JOIN pf_performance ON pf_performance.id = pf_harvest.id_performance
WHERE pf_harvest.date BETWEEN TIMESTAMPADD(DAY,(0-WEEKDAY(:dates8)),:dates9)
AND TIMESTAMPADD(DAY,(6-WEEKDAY(:dates10)),:dates11)
AND pr_varieties.code != 1
AND pf_harvest.id_product = 1
AND pf_performance.status = 100
AND pf_harvest.id_tenant = :id_tenant1
GROUP BY pr_products.product
ORDER BY pr_products.product
)
) AS sc
GROUP BY product, label
ORDER BY label";
$statement = $this->db->prepare($sql);
$id_tenant = $this->getIdTenant();
foreach($datePeriod AS $dates){
$values = [
':dates' => $dates->format('Y-m-d'),
':dates1' => $dates->format('Y-m-d'),
':dates2' => $dates->format('Y-m-d'),
':dates3' => $dates->format('Y-m-d'),
':dates4' => $dates->format('Y-m-d'),
':dates5' => $dates->format('Y-m-d'),
':dates6' => $dates->format('Y-m-d'),
':dates7' => $dates->format('Y-m-d'),
':dates8' => $dates->format('Y-m-d'),
':dates9' => $dates->format('Y-m-d'),
':dates10' => $dates->format('Y-m-d'),
':dates11' => $dates->format('Y-m-d'),
':id_tenant' => $id_tenant,
':id_tenant1' => $id_tenant
];
$result = $this->db->executePrepared($statement , $values);
$data[] = $result->fetchAll();
}
$this -> jsonReturnSuccess($data);
推荐答案
您必须考虑需要改进整个代码.首先，使用PHP的资源来进行一些改进.
You have to consider that your entire code needs to be improved. First of all, use the resources from PHP to get some improvement.
日期期限
创建介于一年的第一个和最后一个星期一之间的时间段.使用 DatePeriod :
To create the period between the first and the last monday of the year. Use DatePeriod:
$start = new \DateTime('first monday of this year');
$end = new \DateTime('first day of next year');//The last date is never reached, so if the year ends in a monday, you won't have any problem
$datePeriod = new \DatePeriod($start , new \DateInterval('P7D') , $end);
foreach($datePeriod as $date)
{
echo $period->format('Y-m-d');
}
与您的for循环相比，它非常快.
It's very fast compared to your for loop.
注意:如果使用array_push()将一个元素添加到数组中，则最好使用$ array [] =，因为那样就没有调用函数的开销.
Note: If you use array_push() to add one element to the array it's better to use $array[] = because in that way there is no overhead of calling a function.
如手册所述，如果要将元素添加到数组中，请使用$ array [].
As manual says, if it's to add an element into the array, use $array[].
准备好的声明
我发现的其他常见问题，请使用prepared statement.它可以用来使您的查询获得预编译"状态(简单的示例):
Other common problem which I found, use prepared statement. It can be used to your query get a "pre-compiled" state (simple example):
$query = 'SELECT * FROM table WHERE id = :id';
$statement = $pdo->prepare($query);
$idArray = [1 , 2 , 3 , 4 , 5 , /** very long array, as your date list **/ ];
foreach($idArray as $id)
{
$statement->execute(array(':id' => $id));
$result = $statement->fetchAll();
}
第N + 1个问题
另一种方法是关于N + 1问题.如果其他提示不足以提高速度，则可以使用本机函数( array_map ，array_walk，array_filter等... )来收集值并执行单个请求.
Another way is about the N+1 problem. If the others hints aren't enough to gain some speed, you can use native functions (array_map, array_walk, array_filter, etc...) to gather the values and do a single request.
看看: 什么是SELECT N + 1? https://secure.phabricator.com/book/phabcontrib/article/n_plus_one/
查询
最后，我需要有关您查询的更多信息.您正在使用许多mysql函数.这是我所得到的最后一个可能的提示.但是，正如您所说，查询执行速度很快.尝试删除这些功能，并检查脚本的执行是否得到了改善.
At last, I need more information about your query. You're using many mysql functions. It's the last plausible hint which I have. But, as you said, the query execution is fast. Try to take out those functions and check if the execution of script has been improved.
更新
首先，我认为您在MySQL函数中使用了太多的PHP变量.如果您只需要输入年和月(yyyy-mm)，请使用 DateTime :: format().
First of all, I think you're using so much PHP variable inside MySQL functions. If you have to take just the year and month (yyyy-mm), use DateTime::format().
$date->format('Y-m');//2017-02
手册上有很多例子.
正如我之前说的那样，准备好的语句是一种预编译"查询.您必须使用占位符(命名或位置)而不是变量来编写查询.上面的查询将是我的示例:
As I said before, prepared statement is a kind of "pre-compiled" query. You have to write your query using placeholders (named or positional) instead of variables. The query above will be my example:
$query = "SELECT *
FROM
mytable
INNER JOIN mysecondtable ON (mytable.id = mysecondtable.id_mytable)
WHERE
mytable.date BETWEEN :start AND :end
AND mytable.value >= :value;";
您已经有了foreach:
You already have the foreach:
$data = array();
$start = new DateTime('first monday of January 2016');
$end = new DateTime('last day of December 2016');
$datePeriod = new DatePeriod($start , new DateInterval('P7D') , $end);
foreach($datePeriod AS $dates) {
//your code
}
现在，您必须在foreach循环之外准备"查询:
Now, you have to "prepare" your query outside of your foreach loop:
$statement = $this->db->prepare($query);
foreach($datePeriod AS $dates) {
//your code
}
在您的foreach内部，只需使用占位符即可.
And inside your foreach, just have to use the placeholders.
foreach($datePeriod AS $dates) {
$values = [
'start' => $dates->format('Y-m-d'),
'end' => $dates->add(new DateInterval('P7D'))->format('Y-m-d'),//add 7 to reach a week
'value' => 10
];
$types = [
'start' => Column::BIND_PARAM_STR,
'end' => Column::BIND_PARAM_STR,
'value' => Column::BIND_PARAM_INT
]
//Phalcon PDO Adapter method
$result = $connection->executePrepared($statement , $values , $types);//The result is a PDOStatement object
$data[] = $result->fetchAll();
}
有了这些技巧，您可以大大提高脚本的执行时间.
With these tips, you can improve a lot the execution time of your script.
这篇关于更改查询SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
