php - 一个 MySQL 查询中基于不同日期范围的平均值选择语句

Question

基本上，我试图用这些数据制作图表。我可以将我的查询放入 PHP 的 while 循环中以获得每个平均值，但我更希望这是通过生成一个结果表的一个查询来完成的。

<?php 

date_default_timezone_set('America/Los_Angeles');

include('../connect.php');

$subcategory = 'T-Shirts';

$date = date('Y-m-d', strtotime('-29 days'));
$today = date("Y-m-d");

$subcategory = mysqli_real_escape_string($conp, $subcategory);

echo "<table border=\"1\">";
echo "<tr>";
echo "<th>date</th>";
echo "<th>average</th>";
echo "</tr>";

while (strtotime($date) <= strtotime($today)) {

    $from_date = date ("Y-m-d", strtotime("-29 day", strtotime($date)));

    $query = $conp->query("SELECT ROUND(SUM(OutCount)/30) AS 'average' FROM inventory
    LEFT JOIN item
    ON inventory.itemcode = item.itemcode
    WHERE item.subcategory = '$subcategory'
    AND TrDateTime BETWEEN '$from_date' AND '$date' AND transactiontype like 'OUT_%'"); 

    if($query->num_rows){       
        while($row = mysqli_fetch_array($query, MYSQL_ASSOC)){                      
            if(!empty($row['average'])){
                $average = $row['average'];
            }else{
                $average = "N/A";
            }
        }                       
        mysqli_free_result($query);                             
    }else{
        $average = "N/A";
    }

    $date = date ("Y-m-d", strtotime("+1 day", strtotime($date)));

    echo "<tr>";
    echo "<td>" . $date . "</td>";
    echo "<td>" . $average . "</td>";
    echo "</tr>";
}

echo "</table>";

?>

我获取过去 30 天(包括今天)的所有日期以及该日期之前 29 天范围内的平均销售额。

+------------+----------+  
| date       | average  |  
+------------+----------+  
| 2015-04-09 | 222      |  
| 2015-04-10 | 225      |  
| 2015-04-11 | 219      |  
| ...        | ...      |  
+------------+----------+  

我能够通过这种方式获得我需要的一切，但在这种情况下它运行 29 个查询，而 MySQL 会快得多。我开始想出一个 MySQL 过程，但我不确定当我尝试用 PHP 调用它时它的效果如何。

DELIMITER //
    CREATE PROCEDURE average_daily_sales()
    BEGIN

        SET @today = CURDATE();
        SET @date_var = CURDATE() - INTERVAL 29 DAY;
        SET @from_date = @date_var - INTERVAL 29 DAY;
        SET @to_date = @from_date + INTERVAL 29 DAY;

        label1: WHILE @date_var < @today DO

            SELECT      DATE_FORMAT(trdatetime, '%Y-%m-%d') as 'date', ROUND(SUM(OutCount)/30) AS 'average'
            FROM        inventory
            LEFT JOIN   item
            ON          inventory.itemcode = item.itemcode
            WHERE       item.subcategory = 'T-Shirts'
            AND         trdatetime BETWEEN @from_date - INTERVAL 29 DAY AND @to_date
            AND         transactiontype like 'OUT_%';

            SET @date_var = @date_var + INTERVAL 1 DAY;

        END WHILE label1;    

    END; //
DELIMITER ;

最终，我更喜欢常规的 MySQL 语句，我可以使用它一次性生成所需的结果表。任何帮助将不胜感激。

Answer 1

您是否有范围内每个不同日期的数据？如果是这样，这是一个稍微复杂的连接操作，但非常可行。

您可以通过以下方式获取您需要的日期范围:

        SELECT DISTINCT
               DATE(trdatetime)- INTERVAL 30 DAY AS startdate,
               DATE(trdatetime)                  AS enddateplus1
          FROM inventory
         WHERE trdatetime >= NOW() - INTERVAL 31 DAY

调试此查询。查看以确保获得所需的每个日期范围。

然后您可以像这样将其加入您的业务查询

  SELECT dates.startdate, 
         ROUND(SUM(OutCount)/30) AS 'average'
   FROM (
        SELECT DISTINCT
               DATE(trdatetime)- INTERVAL 30 DAY AS startdate,
               DATE(trdatetime)                  AS enddateplus1
          FROM inventory
         WHERE trdatetime >= NOW() - INTERVAL 31 DAY
        ) dates
   LEFT JOIN inventory  ON i.trdatetime >= dates.startdate
                       AND i.trdatetime <  dates.enddateplus1 
   LEFT JOIN  item ON  i.itemcode = item.itemcode
  WHERE item.subcategory = 'T-Shirts'
    AND transactiontype like 'OUT_%'
  GROUP BY dates.startdate

如果您的库存数据稀疏，也就是说，您没有所有日期的交易，那么您的日期查询将丢失一些行。

有一种方法可以填补那些缺失的行。但这是 s 的痛苦。阅读本文以获取更多信息。 http://www.plumislandmedia.net/mysql/filling-missing-data-sequences-cardinal-integers/

请注意，BETWEEN 对于过滤 DATETIME 或 TIMESTAMP 值确实效果很差。