gpt4 book ai didi

php - 在一个表中,查看某个 ID 是否有一天中每一分钟的行 - 如何优化此查询?

转载 作者:行者123 更新时间:2023-11-29 05:20:16 25 4
gpt4 key购买 nike

我正在尝试为每个 ID 显示一个表,以查看是否有可用数据。在这种情况下,它是测量值。如果没有数据,则意味着记录器没有正常工作。

我目前有两个表:datatimesdata 包含iddatetimesensor_idvaluetimes 包含idvalue

Times 由 00:0000:0100:02 等组成,一直到 23 :59

我有这个问题:

SELECT t.`value`, d.`sensor_id`, COUNT(t.`value`) as `numrows`
FROM `data` d
RIGHT JOIN `times` t
ON d.`datetime` LIKE CONCAT('% ', t.`value`, '%')
WHERE d.`datetime` LIKE '%$2014-11-05%'
AND d.`sensor_id` IN(1,2,3,4,5,999)
GROUP BY d.`sensor_id`, t.`value`
ORDER BY d.`sensor_id` ASC, t.`id` ASC

while($s = $select->fetch_assoc()) {
$checkArray[$s['sensor_id']][$s['value']] = $s['numrows'];
}

foreach($checkArray as $key => $arr) {
echo 'Sensor: ' . $key;
for($i = 0; $i <= 23; $i++) {
for($j = 0; $j <= 59; $j++) {
$time = strlen($i) == 1 ? '0' . $i : '' . $i;
$time .= ':';
$time .= strlen($j) == 1 ? '0' .$j : '' . $j;

if(isset($arr[$time]) && $arr[$time] >= 1) { //See if has at least one row
echo 'YES DATA FOR ' . $time . '<br>';
}
}
}
}

当然,我把这个排序在一个表中,结果是这样的:

Result

仅对于传感器 1、2、3、4 和 5,加载时间超过 5.5 秒。我不知道如何进一步优化它。我已经在查询的列上放置了索引,但我想不出其他任何东西。

我的 SHOW CREATE TABLE 用于 data:

CREATE TABLE `data` (
`id` int(13) NOT NULL AUTO_INCREMENT,
`sensor_id` int(13) NOT NULL,
`datetime` datetime NOT NULL,
`value` float NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `sensor_id_2` (`sensor_id`,`datetime`,`value`),
KEY `sensor_id` (`sensor_id`),
KEY `value` (`value`),
KEY `datetime` (`datetime`),
KEY `sensor_id_3` (`sensor_id`),
KEY `datetime_2` (`datetime`)
) ENGINE=InnoDB AUTO_INCREMENT=103921 DEFAULT CHARSET=utf8

最佳答案

您的查询将遇到困难,因为它试图在带有前导通配符的 LIKE 上进行连接。没有索引对此有用。

进一步检查 WHERE 子句中 datetime 的值,再次使用前导通配符,这将防止在该检查中使用索引。此外,您正在检查 RIGHT JOINed 表上的值,有效地将其呈现为 INNER JOIN。

我会尝试这样的事情:-

SELECT sub0.aDateTime, sub1.sensor_id, COUNT(d.datetime)
FROM
(
SELECT DATE_ADD('2014-11-05 00:00:00', INTERVAL a.Mnt + b.Mnt * 10 + c.Mnt * 100 + d.Mnt * 1000 MINUTE) AS aDateTime,
DATE_ADD('2014-11-05 00:00:59', INTERVAL a.Mnt + b.Mnt * 10 + c.Mnt * 100 + d.Mnt * 1000 MINUTE) AS aDateTimeEnd
FROM (SELECT 0 AS Mnt UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
CROSS JOIN (SELECT 0 AS Mnt UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
CROSS JOIN (SELECT 0 AS Mnt UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c
CROSS JOIN (SELECT 0 AS Mnt UNION SELECT 1) d
WHERE (a.Mnt + b.Mnt * 10 + c.Mnt * 100 + d.Mnt * 1000) < 1440
) sub0
CROSS JOIN
(SELECT 1 AS sensor_id UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 999) sub1
LEFT OUTER JOIN data d
ON d.datetime BETWEEN sub0.aDateTime AND sub0.aDateTimeEnd
AND d.sensor_id = sub1.sensor_id
GROUP BY sub1.sensor_id, sub0.aDateTime
ORDER BY sub1.sensor_id ASC, sub0.aDateTime ASC

这将生成从 0 到 1439 的数字范围(即一天中的分钟数)并将其添加到您感兴趣的一天的开始。这就是为一天中的每一分钟生成一行(返回的 2 个日期/时间值是一分钟的第一秒和最后一秒)。然后它根据 date_time 将数据左连接到该数据。

如果你只有一个数字 1 到 1439 的表并将其与数据连接以保存子查询,这可以很容易地改进(它仍然需要一个函数来计算日期,但仍然可以节省一些时间).

关于php - 在一个表中,查看某个 ID 是否有一天中每一分钟的行 - 如何优化此查询?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/26799507/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com