Change sql query

Question

Change sql query

Navigation

#1 by (0 votes)

1

I have the following query to obtain an average of some data of the 52 weeks of the year in the following way:

$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";

}

I declare two variables initially that are $firstdate what is the starting date and $lastDate which is the final date.

Then I make a for to go through the two dates and keep in an array the dates of the Mondays of each week.

Then I go through that new array to get the data I need week by week.

Note: Within the query the variables $dates[$i] are the dates of the mondays of each week.

In short, 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.

I have already indexed the tables in mysql, I improve a little but not enough, the query is not really heavy, it takes on average 0.60 seconds per cycle.

I would like to know if there is a possibility to eliminate 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.

mysql sql php rendimiento

asked by Fabian Sierra 03.02.2017 в 20:42

source

1 answer

Firebase Custom Auth - signInWithCustomToken creates the user without Email Fancybox, how to redirect after a Submit

score 0 · Accepted Answer

Procedure creation (is the example of how to create the procedure is not yet finished):

DELIMITER $$

DROP PROCEDURE IF EXISTS 'promedio' $$
    CREATE PROCEDURE 'promedio'(in dateStart timestamp,in dateFinal timestamp,in idTenant int)
    BEGIN
    DECLARE cont int default 0;
    DECLARE dateProcesada timestamp;
    /*primer lunes*/
    SET dateProcesada= DATE_ADD(dateStart, INTERVAL - WEEKDAY(dateStart) DAY);
    /*tendrias que verificar bien el tipo de datos para las columnas de la tabla temporal*/
    CREATE TEMPORARY TABLE promedio_temp (
      'product' int,
      'Week' varchar(10),
      'PlantSowing' bigint,
      'production' int,
      PRIMARY KEY(product)
      );
     WHILE dateProcesada  < dateFinal DO
    /*misma consulta la idea es crear 
     una tabla temporal para sw_sowing de forma tal q el max se calcule en este punto y devolver los datos en esa temporal*/
    INSERT INTO promedio_temp('product','Week','PlantSowing','production')
     SELECT pr_products.product,
    CONCAT(YEAR(dateProcesada),'-',LPAD(WEEK(dateProcesada),2,'0')) AS Week,
    SUM(IF(sw_sowing.type = 'SW', sw_sowing.quantity,0)) AS PlantSowing,
    SUM(IF(ROUND(DATEDIFF(TIMESTAMPADD(DAY,(6-  WEEKDAY(dateProcesada)),dateProcesada), 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=idTenant
      AND sw_sowing.status = 100
      AND sw_sowing.date <= TIMESTAMPADD(DAY,(6-WEEKDAY(dateProcesada)),dateProcesada)
     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;

     /*sumar 7 dias para obtener proximo lunes*/
     SET dateProcesada= DATE_ADD(dateProcesada, INTERVAL 7 DAY);

    end while;

   SELECT 'product','Week','PlantSowing','production' FROM promedio_temp;

    END $$

DELIMITER ;

to call her use this:

$sql="call promedio('$firstDate',''$lastDate',:id_intent)";