оператор выбора для средних значений на основе разных диапазонов дат в одном запросе MySQL
В основном я пытаюсь сделать диаграмму с этими данными. Я могу поместить свой запрос в цикл while в PHP, чтобы получить каждое среднее значение, но я бы предпочел, чтобы это было сделано с одним запросом, создающим одну таблицу результатов.
<?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, который я могу использовать для получения желаемой таблицы результатов за один раз. Любая помощь будет принята с благодарностью.
Решение
У вас есть данные о каждом отдельном дне в диапазоне? Если это так, это немного сложная операция соединения, но очень выполнимая.
Вы можете получить нужные диапазоны дат следующим образом:
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 ценности.
Другие решения
Если вы создаете таблицу календаря и заполняете ее диапазоном значений дат, например,
CREATE TABLE cal (dt DATE NOT NULL PRIMARY KEY) ;
INSERT INTO cal VALUES ('2015-04-01'),('2015-04-02'),('2015-04-03'), ... ;
Вы можете использовать это как источник строки в запросе, подобном следующему:
SELECT cal.dt
, ( -- correlated subquery references value returned from cal
SELECT ROUND(SUM(n.OutCount)/30)
FROM inventory n
JOIN item t
ON t.itemcode = n.itemcode
WHERE t.subcategory = 'foo'
AND n.TrDateTime >= cal.dt + INTERVAL -28 DAY
AND n.TrDateTime < cal.dt + INTERVAL 1 DAY
AND n.transactiontype LIKE 'OUT_%'
) AS `average`
FROM cal
WHERE cal.dt >= '2015-04-01'
AND cal.dt < '2015-05-01'
ORDER BY cal.dt
Не обязательно создавать cal календарный стол. Мы могли бы использовать встроенное представление и дать ему псевдоним cal, Например, в приведенном выше запросе мы могли бы заменить эту строку:
FROM cal
с этим:
FROM ( SELECT DATE('2015-04-01') AS dt
UNION ALL SELECT DATE('2015-04-02')
UNION ALL SELECT DATE('2015-04-03')
UNION ALL SELECT DATE('2015-04-04')
UNION ALL SELECT DATE('2015-04-05')
) cal
Или, если у вас есть источник строк, который может дать вам непрерывный ряд целых чисел, начиная с нуля до t, вы можете создать значения даты, например, из базовой даты.
FROM ( SELECT '2014-04-01' + INTERVAL i.n DAY
FROM source_of_integers i
WHERE i.n >= 0
AND i.n < 31
ORDER BY i.n
) cal
Некоторые заметки:
Исходный запрос показывает внешний (LEFT), но предикат равенства в предложении WHERE отрицает «внешность» соединения, это эквивалентно внутреннему соединению.
Некоторые ссылки на столбцы в запросе не определены. Рекомендуется квалифицировать все ссылки на столбцы, тогда читатель сможет понять, какие столбцы идут из каких таблиц, не требуя, чтобы читатель был знаком с тем, какие столбцы в каких таблицах. Это также защищает оператор от разрыва в будущем (с ошибкой «неоднозначный столбец»), когда столбец с таким же именем добавляется в другую таблицу, на которую есть ссылка в запросе.)
СЛЕДОВАТЬ ЗА
Лично для ограниченного числа значений даты я бы использовал встроенное представление, которое не ссылается на таблицу. Я хотел бы, чтобы код PHP генерировал этот запрос для меня.
С начальной датой, скажем, «2015-04-10», я бы взял это значение даты и отформатировал бы его в запросе, эквивалентно так:
$cal = "SELECT DATE('2015-04-10') AS dt" ;
Затем я бы крутил цикл и увеличивал значение даты на 1 день. Каждый раз в цикле я бы добавил $cal При выборе следующей даты чистый эффект от прохождения цикла три раза будет эквивалентен выполнению этого:
$cal .= " UNION ALL SELECT DATE('2015-04-11')";
$cal .= " UNION ALL SELECT DATE('2015-04-12')";
$cal .= " UNION ALL SELECT DATE('2015-04-13')";
В качестве менее привлекательной альтернативы мы могли бы продолжать повторять одно и то же значение начальной даты и просто увеличивать целочисленное значение, и MySQL будет делать для нас математику даты.
$cal .= " UNION ALL SELECT '2015-04-10' + INTERVAL 1 DAY";
$cal .= " UNION ALL SELECT '2015-04-10' + INTERVAL 2 DAY";
$cal .= " UNION ALL SELECT '2015-04-10' + INTERVAL 3 DAY";
Тогда я бы просто сдвинул $cal запрос в тексте SQL как запрос в виде встроенного представления. Что-то вроде этого:
$sql = "SELECT cal.dt
, ( SELECT IFNULL(ROUND(SUM
,0) AS average_
FROM ( " . $cal . " ) cal
LEFT
JOIN item ON ... ";
Во всяком случае, такой подход я бы взял, если бы это было для ограничено число значений даты (пара дюжин или около того), и если я собирался запускать этот запрос только изредка, не ударяя сервер базы данных этим запросом повторно, для каждого запроса.) Если я собирался отбить сервер, я создать и поддерживать настоящий cal таблицы, а не материальные затраты на материализацию производной таблицы при каждом запросе.
Предложения @OllieJones и @ spencer7593 требовали, чтобы «транзакция» выполнялась каждый день, чтобы использовать SELECT DISTINCT DATE(trdatetime), вам нужно было создать другую таблицу, или вам нужно было создать производную таблицу.
SELECT DISTINCT DATE(trdatetime) не было выбора для меня, потому что у меня не было сделок на каждый день.
Гибридный пример PHP и MySQL, предложенный @ spencer7593, очень хорошо сгенерирует производную таблицу. В конце концов, для получения результата статической версии потребовалось около 1,8 секунд. Проблема в том, что вам понадобится дополнительный PHP для генерации этого … (см. Ответ @ spencer7593)
SELECT cal.dt
, ( -- correlated subquery references value returned from cal
SELECT ROUND(SUM(n.OutCount)/30)
FROM inventory n
JOIN item t
ON t.itemcode = n.itemcode
WHERE t.subcategory = 'foo'
AND n.TrDateTime >= cal.dt + INTERVAL -28 DAY
AND n.TrDateTime < cal.dt + INTERVAL 1 DAY
AND n.transactiontype LIKE 'OUT_%'
) AS `average`
FROM ( SELECT DATE('2015-04-01') AS dt
UNION ALL SELECT DATE('2015-04-02')
UNION ALL SELECT DATE('2015-04-03')
UNION ALL SELECT DATE('2015-04-04')
UNION ALL SELECT DATE('2015-04-05')
UNION ALL SELECT DATE('2015-04-06')
etc...
) cal
WHERE cal.dt >= '2015-04-01'
AND cal.dt < '2015-05-01'
ORDER BY cal.dt
Я попытался использовать еще один из ответов @ spencer7593. Я создал таблицу «источник целых чисел» с номерами 0-31, как он предложил. Этот метод занял чуть более 1,8 секунд.
SELECT cal.sd, cal.ed
, ( -- correlated subquery references value returned from cal
SELECT ROUND(SUM(n.OutCount)/30)
FROM inventory n
JOIN item t
ON t.itemcode = n.itemcode
WHERE t.subcategory = 'foobar'
AND n.TrDateTime >= cal.ed + INTERVAL -30 DAY
AND n.TrDateTime < cal.ed + INTERVAL 1 DAY
AND n.transactiontype LIKE 'OUT_%'
) AS `average`
FROM ( SELECT (CURDATE() + INTERVAL -30 DAY) + INTERVAL i.n DAY as `ed`, (((CURDATE() + INTERVAL -30 DAY) + INTERVAL i.n DAY) + INTERVAL - 30 DAY) as `sd`
FROM source_of_integers i
WHERE i.n >= 0
AND i.n < 31
ORDER BY i.n
) cal
WHERE cal.ed >= CURDATE() + INTERVAL -29 DAY
AND cal.ed <= CURDATE()
ORDER BY cal.ed;
Вам нужен источник строк для этих дат, на самом деле не существует способа обойти это. В конце концов я сделал таблицу вызовов.
CREATE TABLE cal (
dt DATE NOT NULL PRIMARY KEY
);
CREATE TABLE ints ( i tinyint );
INSERT INTO ints VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
INSERT INTO cal (dt)
SELECT DATE('2010-01-01') + INTERVAL a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i DAY
FROM ints a JOIN ints b JOIN ints c JOIN ints d JOIN ints e
WHERE (a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i) <= 3651
ORDER BY 1;
А затем запустил слегка измененную версию ответа @ spencer7593 на него ..
SELECT cal.dt
, ( -- correlated subquery references value returned from cal
SELECT ROUND(SUM(n.OutCount)/30)
FROM inventory n
JOIN item t
ON t.itemcode = n.itemcode
WHERE t.subcategory = 'foo'
AND n.TrDateTime >= cal.dt + INTERVAL -28 DAY
AND n.TrDateTime < cal.dt + INTERVAL 1 DAY
AND n.transactiontype LIKE 'OUT_%'
) AS `average`
FROM cal
WHERE cal.dt >= CURDATE() + INTERVAL -30 DAY
AND cal.dt < CURDATE()
ORDER BY cal.dt;
На мой взгляд, я считаю, что это самый чистый (без PHP) и самый эффективный ответ.
Вот как я проиндексировал таблицу инвентаризации, чтобы существенно ее ускорить:
ALTER TABLE inventory ADD KEY (ItemCode, TrDateTime, TransactionType);
Спасибо @OllieJones и @ spencer7593 за всю вашу помощь!