php - MySQL - 如何从最小/最大值获取最小/最大日期

Question

我有一个问题，关于如何从多个列的所有最小值/最大值中获取所有日期以及如何优化它。我已经尝试让它合理运行很长时间了，但我几乎放弃了:-(

数据库存储了来自各种设备的大量温度。并以这种方式构建。

ID, User, DateTime, Temp1, Temp2, Temp3 and so on.

现在我想提取每个测量的 MIN 和 MAX 的所有日期和值(MIN Temp1 的日期、MAX Temp1 的日期，...)

获取最小值/最大值非常简单快捷。

SELECT
MIN(Modul_FremL) as Min_Temp1,
MAX(Modul_FremL) as Max_Temp1,
MIN(Modul_ReturL) as Min_Temp2,
MAX(Modul_ReturL) as Max_Temp2,
MIN(Modul_Gas) as Min_Temp3,
MAX(Modul_Gas) as Max_Temp3,
MIN(Modul_FB) as Min_Temp4,
MAX(Modul_FB) as Max_Temp4,
MIN(Modul_SB) as Min_Temp5,
MAX(Modul_SB) as Max_Temp5,
MIN(Temp_Kedel) as Min_Temp6,
MAX(Temp_Kedel) as Max_Temp6,
MIN(Temp_Central) as Min_Temp7,
MAX(Temp_Central) as Max_Temp7,
MIN(Temp_VVB) as Min_Temp8,
MAX(Temp_VVB) as Max_Temp8,
MIN(Temp_Ude) as Min_Temp9,
MAX(Temp_Ude) as Max_Temp9
FROM pillestat_data_1
LIMIT 1

要获取此数据，只需30万行以上的数据库，仅需0.2004秒即可获取全部数据。

但是因为我还需要最小/最大的日期时间，所以我尝试过这个。

SELECT DatoTid, MIN(Modul_FremL) as Temperatur FROM pillestat_data_1 WHERE Modul_FremL = (SELECT MIN(Modul_FremL) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MAX(Modul_FremL) as Temperatur FROM pillestat_data_1 WHERE Modul_FremL = (SELECT MAX(Modul_FremL) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MIN(Modul_ReturL) as Temperatur FROM pillestat_data_1 WHERE Modul_ReturL = (SELECT MIN(Modul_ReturL) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MAX(Modul_ReturL) as Temperatur FROM pillestat_data_1 WHERE Modul_ReturL = (SELECT MAX(Modul_ReturL) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MIN(Modul_Gas) as Temperatur FROM pillestat_data_1 WHERE Modul_Gas = (SELECT MIN(Modul_Gas) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MAX(Modul_Gas) as Temperatur FROM pillestat_data_1 WHERE Modul_Gas = (SELECT MAX(Modul_Gas) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MIN(Modul_FB) as Temperatur FROM pillestat_data_1 WHERE Modul_FB = (SELECT MIN(Modul_FB) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MAX(Modul_FB) as Temperatur FROM pillestat_data_1 WHERE Modul_FB = (SELECT MAX(Modul_FB) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MIN(Modul_SB) as Temperatur FROM pillestat_data_1 WHERE Modul_SB = (SELECT MIN(Modul_SB) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MAX(Modul_SB) as Temperatur FROM pillestat_data_1 WHERE Modul_SB = (SELECT MAX(Modul_SB) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MIN(Temp_Kedel) as Temperatur FROM pillestat_data_1 WHERE Temp_Kedel = (SELECT MIN(Temp_Kedel) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MAX(Temp_Kedel) as Temperatur FROM pillestat_data_1 WHERE Temp_Kedel = (SELECT MAX(Temp_Kedel) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MIN(Temp_Central) as Temperatur FROM pillestat_data_1 WHERE Temp_Central = (SELECT MIN(Temp_Central) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MAX(Temp_Central) as Temperatur FROM pillestat_data_1 WHERE Temp_Central = (SELECT MAX(Temp_Central) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MIN(Temp_VVB) as Temperatur FROM pillestat_data_1 WHERE Temp_VVB = (SELECT MIN(Temp_VVB) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MAX(Temp_VVB) as Temperatur FROM pillestat_data_1 WHERE Temp_VVB = (SELECT MAX(Temp_VVB) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MIN(Temp_Ude) as Temperatur FROM pillestat_data_1 WHERE Temp_Ude = (SELECT MIN(Temp_Ude) FROM pillestat_data_1 LIMIT 1)
union all
SELECT DatoTid, MAX(Temp_Ude) as Temperatur FROM pillestat_data_1 WHERE Temp_Ude = (SELECT MAX(Temp_Ude) FROM pillestat_data_1 LIMIT 1)

实际上只有 18 个查询，但需要很多时间。300.000 行需要 1.9866 秒。

这 300,000 行只是一个小型数据库，预计可达约 1800 万行。

我也尝试过这个

SELECT
(SELECT DatoTid FROM pillestat_data_1 WHERE Modul_FremL = (SELECT MIN(Modul_FremL) FROM pillestat_data_1 )) AS Min_DT_Temp1, MIN(Modul_FremL) AS Temp_Min_Temp1,
(SELECT DatoTid FROM pillestat_data_1 WHERE Modul_FremL = (SELECT MAX(Modul_FremL) FROM pillestat_data_1 )) AS Max_DT_Temp1, MAX(Modul_FremL) AS Temp_Max_Temp1,
(SELECT DatoTid FROM pillestat_data_1 WHERE Modul_ReturL = (SELECT MIN(Modul_ReturL) FROM pillestat_data_1 )) AS Min_DT_Temp2, MIN(Modul_ReturL) AS Temp_Min_Temp2,
(SELECT DatoTid FROM pillestat_data_1 WHERE Modul_ReturL = (SELECT MAX(Modul_ReturL) FROM pillestat_data_1 )) AS Max_DT_Temp2, MAX(Modul_ReturL) AS Temp_Max_Temp2,
(SELECT DatoTid FROM pillestat_data_1 WHERE Modul_Gas = (SELECT MIN(Modul_Gas) FROM pillestat_data_1 )) AS Min_DT_Temp3, MIN(Modul_Gas) AS Temp_Min_Temp3,
(SELECT DatoTid FROM pillestat_data_1 WHERE Modul_Gas = (SELECT MAX(Modul_Gas) FROM pillestat_data_1 )) AS Max_DT_Temp3, MAX(Modul_Gas) AS Temp_Max_Temp3,
(SELECT DatoTid FROM pillestat_data_1 WHERE Modul_FB = (SELECT MIN(Modul_FB) FROM pillestat_data_1 )) AS Min_DT_Temp4, MIN(Modul_FB) AS Temp_Min_Temp4,
(SELECT DatoTid FROM pillestat_data_1 WHERE Modul_FB = (SELECT MAX(Modul_FB) FROM pillestat_data_1 )) AS Max_DT_Temp4, MAX(Modul_FB) AS Temp_Max_Temp4,
(SELECT DatoTid FROM pillestat_data_1 WHERE Modul_SB = (SELECT MIN(Modul_SB) FROM pillestat_data_1 )) AS Min_DT_Temp5, MIN(Modul_SB) AS Temp_Min_Temp5,
(SELECT DatoTid FROM pillestat_data_1 WHERE Modul_SB = (SELECT MAX(Modul_SB) FROM pillestat_data_1 )) AS Max_DT_Temp5, MAX(Modul_SB) AS Temp_Max_Temp5,
(SELECT DatoTid FROM pillestat_data_1 WHERE Temp_Kedel = (SELECT MIN(Temp_Kedel) FROM pillestat_data_1 )) AS Min_DT_Temp6, MIN(Temp_Kedel) AS Temp_Min_Temp6,
(SELECT DatoTid FROM pillestat_data_1 WHERE Temp_Kedel = (SELECT MAX(Temp_Kedel) FROM pillestat_data_1 )) AS Max_DT_Temp6, MAX(Temp_Kedel) AS Temp_Max_Temp6,
(SELECT DatoTid FROM pillestat_data_1 WHERE Temp_Central = (SELECT MIN(Temp_Central) FROM pillestat_data_1 )) AS Min_DT_Temp7, MIN(Temp_Central) AS Temp_Min_Temp7,
(SELECT DatoTid FROM pillestat_data_1 WHERE Temp_Central = (SELECT MAX(Temp_Central) FROM pillestat_data_1 )) AS Max_DT_Temp7, MAX(Temp_Central) AS Temp_Max_Temp7,
(SELECT DatoTid FROM pillestat_data_1 WHERE Temp_VVB = (SELECT MIN(Temp_VVB) FROM pillestat_data_1 )) AS Min_DT_Temp8, MIN(Temp_VVB) AS Temp_Min_Temp8,
(SELECT DatoTid FROM pillestat_data_1 WHERE Temp_VVB = (SELECT MAX(Temp_VVB) FROM pillestat_data_1 )) AS Max_DT_Temp8, MAX(Temp_VVB) AS Temp_Max_Temp8,
(SELECT DatoTid FROM pillestat_data_1 WHERE Temp_Ude = (SELECT MIN(Temp_Ude) FROM pillestat_data_1 )) AS Min_DT_Temp9, MIN(Temp_Ude) AS Temp_Min_Temp9,
(SELECT DatoTid FROM pillestat_data_1 WHERE Temp_Ude = (SELECT MAX(Temp_Ude) FROM pillestat_data_1 )) AS Max_DT_Temp9, MAX(Temp_Ude) AS Temp_Max_Temp9
FROM pillestat_data_1
LIMIT 1

但由于某种原因，它不适用于所有列。它适用于大约一半的列。

所以我希望这里有人可以帮助我优化我的代码。

//金

如果你想测试的话，已经制作了一个SQLFIDDLE。

http://sqlfiddle.com/#!9/2b1c8f/16

Answer 1

规范化的表格设计是

devices table
-------------
id
name
...


temperatures table
------------------
id
user_id
datetime
device_id
temp

外键会自动建立索引。如果您进行依赖于日期的选择，还要为 datetime 列添加索引。

然后获取所有设备的最小和最大 tmp

select d.name, min(t.temp) as minTemp, max(t.temp) as maxTemp
from temperratures t
join devices d on d.id = t.device_id
group by d.name

即使您有数百万条记录，也应该在几毫秒内返回结果。