Блог

В настоящее время у меня есть запрос, который выдает среднюю и максимальную скорость дня в качестве запроса, который я хочу оптимизировать, вот так

 
SELECT V_VEHICLEID, V_LICENSENO, 
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 1 AND WP_VEHICLEID = V_VEHICLEID) AS AVG1,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 2 AND WP_VEHICLEID = V_VEHICLEID) AS AVG2,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 3 AND WP_VEHICLEID = V_VEHICLEID) AS AVG3,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 4 AND WP_VEHICLEID = V_VEHICLEID) AS AVG4,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 5 AND WP_VEHICLEID = V_VEHICLEID) AS AVG5,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 6 AND WP_VEHICLEID = V_VEHICLEID) AS AVG6,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 7 AND WP_VEHICLEID = V_VEHICLEID) AS AVG7,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 8 AND WP_VEHICLEID = V_VEHICLEID) AS AVG8,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 9 AND WP_VEHICLEID = V_VEHICLEID) AS AVG9,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 10 AND WP_VEHICLEID = V_VEHICLEID) AS AVG10,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 11 AND WP_VEHICLEID = V_VEHICLEID) AS AVG11,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 12 AND WP_VEHICLEID = V_VEHICLEID) AS AVG12,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 13 AND WP_VEHICLEID = V_VEHICLEID) AS AVG13,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 14 AND WP_VEHICLEID = V_VEHICLEID) AS AVG14,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 15 AND WP_VEHICLEID = V_VEHICLEID) AS AVG15,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 16 AND WP_VEHICLEID = V_VEHICLEID) AS AVG16,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 17 AND WP_VEHICLEID = V_VEHICLEID) AS AVG17,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 18 AND WP_VEHICLEID = V_VEHICLEID) AS AVG18,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 19 AND WP_VEHICLEID = V_VEHICLEID) AS AVG19,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 20 AND WP_VEHICLEID = V_VEHICLEID) AS AVG20,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 21 AND WP_VEHICLEID = V_VEHICLEID) AS AVG21,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 22 AND WP_VEHICLEID = V_VEHICLEID) AS AVG22,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 23 AND WP_VEHICLEID = V_VEHICLEID) AS AVG23,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 24 AND WP_VEHICLEID = V_VEHICLEID) AS AVG24,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 25 AND WP_VEHICLEID = V_VEHICLEID) AS AVG25,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 26 AND WP_VEHICLEID = V_VEHICLEID) AS AVG26,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 27 AND WP_VEHICLEID = V_VEHICLEID) AS AVG27,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 28 AND WP_VEHICLEID = V_VEHICLEID) AS AVG28,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 29 AND WP_VEHICLEID = V_VEHICLEID) AS AVG29,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 30 AND WP_VEHICLEID = V_VEHICLEID) AS AVG30,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 31 AND WP_VEHICLEID = V_VEHICLEID) AS AVG31,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 1 AND WP_VEHICLEID = V_VEHICLEID) AS MAX1,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 2 AND WP_VEHICLEID = V_VEHICLEID) AS MAX2,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 3 AND WP_VEHICLEID = V_VEHICLEID) AS MAX3,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 4 AND WP_VEHICLEID = V_VEHICLEID) AS MAX4,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 5 AND WP_VEHICLEID = V_VEHICLEID) AS MAX5,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 6 AND WP_VEHICLEID = V_VEHICLEID) AS MAX6,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 7 AND WP_VEHICLEID = V_VEHICLEID) AS MAX7,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 8 AND WP_VEHICLEID = V_VEHICLEID) AS MAX8,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 9 AND WP_VEHICLEID = V_VEHICLEID) AS MAX9,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 10 AND WP_VEHICLEID = V_VEHICLEID) AS MAX10,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 11 AND WP_VEHICLEID = V_VEHICLEID) AS MAX11,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 12 AND WP_VEHICLEID = V_VEHICLEID) AS MAX12,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 13 AND WP_VEHICLEID = V_VEHICLEID) AS MAX13,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 14 AND WP_VEHICLEID = V_VEHICLEID) AS MAX14,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 15 AND WP_VEHICLEID = V_VEHICLEID) AS MAX15,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 16 AND WP_VEHICLEID = V_VEHICLEID) AS MAX16,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 17 AND WP_VEHICLEID = V_VEHICLEID) AS MAX17,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 18 AND WP_VEHICLEID = V_VEHICLEID) AS MAX18,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 19 AND WP_VEHICLEID = V_VEHICLEID) AS MAX19,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 20 AND WP_VEHICLEID = V_VEHICLEID) AS MAX20,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 21 AND WP_VEHICLEID = V_VEHICLEID) AS MAX21,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 22 AND WP_VEHICLEID = V_VEHICLEID) AS MAX22,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 23 AND WP_VEHICLEID = V_VEHICLEID) AS MAX23,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 24 AND WP_VEHICLEID = V_VEHICLEID) AS MAX24,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 25 AND WP_VEHICLEID = V_VEHICLEID) AS MAX25,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 26 AND WP_VEHICLEID = V_VEHICLEID) AS MAX26,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 27 AND WP_VEHICLEID = V_VEHICLEID) AS MAX27,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 28 AND WP_VEHICLEID = V_VEHICLEID) AS MAX28,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 29 AND WP_VEHICLEID = V_VEHICLEID) AS MAX29,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 30 AND WP_VEHICLEID = V_VEHICLEID) AS MAX30,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 31 AND WP_VEHICLEID = V_VEHICLEID) AS MAX31
FROM VEHICLES
WHERE V_USER = 'tc'
  

я уже пробовал этот запрос, но я хочу, чтобы таблица результатов (в html) выглядела как запрос выше

 
SELECT WP_VEHICLEID AS VEHICLE, V_LICENSENO AS REGNO, 
DATE(WP_DATETIME) AS SUM_DATE, 
ROUND(AVG(WP_SPEED),2) AS AVG_SPEED, 
ROUND(MAX(WP_SPEED),2) AS MAX_SPEED
FROM WAYPOINTS, VEHICLES
WHERE MONTH(WP_DATETIME) = 6
AND WP_ODOMETER  0
AND V_VEHICLEID = WP_VEHICLEID
AND V_USER = 'tc'
GROUP BY WP_VEHICLEID, DATE(WP_DATETIME)
ORDER BY WP_VEHICLEID, WP_DATETIME
  

Таблица результатов

 
========================================================
VEHICLE|     1     |     2     |     3     |     4     |
       -------------------------------------------------
       | AVG | MAX | AVG | MAX | AVG | MAX | AVG | MAX |
========================================================
CAR A  | 10  | 30  |  90 | 160 | .......................
CAR B  | 50  | 90  |  0  | 0   | .......................
CAR C  | 15  | 20  |  10 | 10  | .......................
========================================================
  

есть решение?

Попробуйте что-то вроде этого —

 SELECT
  v.V_VEHICLEID,

  ROUND(AVG(IF(DAY(WP_DATETIME) = 1, wp.WP_SPEED, NULL)), 2) AS AVG1,
  ROUND(AVG(IF(DAY(WP_DATETIME) = 2, wp.WP_SPEED, NULL)), 2) AS AVG2,
  ROUND(AVG(IF(DAY(WP_DATETIME) = 3, wp.WP_SPEED, NULL)), 2) AS AVG3,
  ...
  ROUND(MAX(IF(DAY(WP_DATETIME) = 1, wp.WP_SPEED, NULL)), 2) AS MAX1,
  ROUND(MAX(IF(DAY(WP_DATETIME) = 2, wp.WP_SPEED, NULL)), 2) AS MAX2,
  ROUND(MAX(IF(DAY(WP_DATETIME) = 3, wp.WP_SPEED, NULL)), 2) AS MAX3,
  ...
FROM VEHICLES v
  JOIN WAYPOINTS wp ON wp.WP_VEHICLEID = v.V_VEHICLEID
GROUP BY
  wp.WP_VEHICLEID
  

Вместо приведенного выше чудовищного запроса создайте крошечную таблицу с номерами 1-31, затем присоединитесь к ней и добавьте некоторую группу BY.

У вас не будет точно вашей таблицы, вместо этого у вас будет куча строк с ДАТОЙ, идентификатором транспортного СРЕДСТВА. Упорядочите его по дате, затем по идентификатору транспортного средства. Вам нужно будет выполнить цикл по нему, создавая свою таблицу.