Mysql查询获取温度趋势
Mysql查询获取温度趋势
提问于 2013-04-02 19:09:48
我有一个非常大的表，其中我每1分钟记录一次温度，我想查询的是一个趋势；类似于所选时间段(小时或15分钟；取决于查询)的百分比增加或减少。
我的表(示例)如下所示
ID time temp
119950 2013-03-27 07:56:05 27.25
119951 2013-03-27 07:57:05 27.50
119952 2013-03-27 07:58:05 27.60
119953 2013-03-27 07:59:05 27.80
119954 2013-03-27 08:00:05 27.70
119955 2013-03-27 08:01:05 27.50
119956 2013-03-27 08:02:05 27.25
119957 2013-03-27 08:03:05 27.10
119958 2013-03-27 08:04:05 26.9
119959 2013-03-27 08:05:05 27.1
119960 2013-03-27 08:06:05 27.25
119961 2013-03-27 08:07:05 27.6我相信趋势可以计算如下(根据link)，但如果你有更好的方法，请纠正我；取每行之间的差值，然后相加，再除以计数。因此，对于上面的表格，我们得到
Diff
+0.25
+0.10
+0.20
-0.10
-0.20
-0.25
-0.15
-0.20
+0.20
+0.15
+0.35过去11分钟的每分钟趋势是diff/11的和，这就是过去11分钟的每分钟0.063C。
谁能帮我得到百分比趋势每小时过去3个小时。以及1小时内每分钟的趋势？
回答 2
Stack Overflow用户
回答已采纳
发布于 2013-04-02 20:57:52
CREATE TABLE temperature_log
(ID INT NOT NULL,dt DATETIME NOT NULL, temperature DECIMAL(5,2) NOT NULL);
INSERT INTO temperature_log VALUES
(119950 ,'2013-03-27 07:56:05',27.25),
(119951 ,'2013-03-27 07:57:05', 27.50),
(119952 ,'2013-03-27 07:58:05', 27.60),
(119953 ,'2013-03-27 07:59:05', 27.80),
(119954 ,'2013-03-27 08:00:05', 27.70),
(119955 ,'2013-03-27 08:01:05', 27.50),
(119956 ,'2013-03-27 08:02:05', 27.25),
(119957 ,'2013-03-27 08:03:05', 27.10),
(119958 ,'2013-03-27 08:04:05', 26.9),
(119959 ,'2013-03-27 08:05:05', 27.1),
(119960 ,'2013-03-27 08:06:05', 27.25),
(119961 ,'2013-03-27 08:07:05', 27.6);
SELECT x.*
, x.temperature - y.temperature diff
, COUNT(*) cnt
,(x.temperature-y.temperature)/COUNT(*) trend
FROM temperature_log x
JOIN temperature_log y
ON y.id < x.id
GROUP
BY x.id;
+--------+---------------------+-------------+-------+-----+-----------+
| ID | dt | temperature | diff | cnt | trend |
+--------+---------------------+-------------+-------+-----+-----------+
| 119951 | 2013-03-27 07:57:05 | 27.50 | 0.25 | 1 | 0.250000 |
| 119952 | 2013-03-27 07:58:05 | 27.60 | 0.35 | 2 | 0.175000 |
| 119953 | 2013-03-27 07:59:05 | 27.80 | 0.55 | 3 | 0.183333 |
| 119954 | 2013-03-27 08:00:05 | 27.70 | 0.45 | 4 | 0.112500 |
| 119955 | 2013-03-27 08:01:05 | 27.50 | 0.25 | 5 | 0.050000 |
| 119956 | 2013-03-27 08:02:05 | 27.25 | 0.00 | 6 | 0.000000 |
| 119957 | 2013-03-27 08:03:05 | 27.10 | -0.15 | 7 | -0.021429 |
| 119958 | 2013-03-27 08:04:05 | 26.90 | -0.35 | 8 | -0.043750 |
| 119959 | 2013-03-27 08:05:05 | 27.10 | -0.15 | 9 | -0.016667 |
| 119960 | 2013-03-27 08:06:05 | 27.25 | 0.00 | 10 | 0.000000 |
| 119961 | 2013-03-27 08:07:05 | 27.60 | 0.35 | 11 | 0.031818 |
+--------+---------------------+-------------+-------+-----+-----------+顺便说一下，如果你对每小时的平均结果感兴趣，你可以这样做...
SELECT DATE_FORMAT(x.dt,'%Y-%m-%d %h:00:00')
, AVG(x.temperature) avg_temp
FROM temperature_log x
GROUP
BY DATE_FORMAT(x.dt,'%Y-%m-%d %h:00:00');Stack Overflow用户
发布于 2019-12-17 17:12:54
我知道这是一个古老的话题，但如果我能和你分享我的经验。也许这对下一个人很有用:)
我有一个很大的表，上面有我的所有设备(100+)的温度，我所有的设备每5秒推送一次温度(一个设备有6个视区，我可以得到每个区域的温度)。
所以这张桌子很大。对我来说，之前的响应对于大量数据来说效率不高。看看我做了什么：
这是我的大表的模式：
CREATE TABLE `histozone` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`camera_id` INT(11) NULL DEFAULT NULL,
`Date` DATE NOT NULL,
`Time` TIME NOT NULL,
`ZoneId` INT(11) NOT NULL,
`AverageTemperature` INT(11) NOT NULL,
`MinimumTemperature` INT(11) NOT NULL,
`MaximumTemperature` INT(11) NOT NULL,
PRIMARY KEY (`id`),
INDEX `IDX_19E8F664B47685CD` (`camera_id`),
INDEX `datetime` (`camera_id`, `Date`, `Time`),
);如您所见，我为每一行提供了Date和Time。
- 在我的代码中使用内存引擎为每个设备创建一个临时表。我限制临时表的持续时间(日期和时间)。
DROP TEMPORARY TABLE IF EXISTS histoZoneMaxTempCamera{$cameraId};
CREATE TEMPORARY TABLE histoZoneMaxTempCamera{$cameraId} (
`id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
`originalid` INT(11) NOT NULL,
`date` DATE NOT NULL,
`time` TIME NOT NULL,
`zoneid` INT(11) NOT NULL,
`maximumtemperature` INT(11) NOT NULL,
INDEX (`maximumtemperature`)
) ENGINE=MEMORY;
INSERT INTO histoZoneMaxTempCamera{$cameraId} (`originalid`, `date`, `time`, `zoneid`, `maximumtemperature`)
SELECT
h.id,
h.Date,
h.Time,
h.ZoneId,
h.MaximumTemperature
FROM histozone h
INNER JOIN (
SELECT
hz.camera_id,
MAX(hz.MaximumTemperature) AS MaximumTemperature,
hz.Date,
hz.Time
FROM histozone hz
WHERE hz.camera_id = '{$cameraId}'
AND hz.Date >= '{$date}'
AND hz.Time >= '{$time}'
GROUP BY hz.Date, hz.Time
) histozoneMaxTemp
ON h.Date = histozoneMaxTemp.Date
AND h.Time = histozoneMaxTemp.Time
AND h.MaximumTemperature = histozoneMaxTemp.MaximumTemperature
WHERE h.camera_id = histozoneMaxTemp.camera_id
ORDER BY h.Date ASC, h.Time ASC;- 和最后一步，是获取数据、趋势等。我，我只想要温度起伏的点。在同一水平上有很多点的图表是不有趣的。
SELECT
a.*
FROM (
SELECT
x.id AS xid
, x.Date AS `Date`
, x.Time AS `Time`
, x.maximumtemperature AS maximumtemperature
, y.maximumtemperature AS previousmaximumtemperature
, x.maximumtemperature - y.maximumtemperature diff
,(x.maximumtemperature-y.maximumtemperature)/MAX(x.id) trend
FROM histoZoneMaxTempCamera{$cameraId} x
LEFT JOIN histoZoneMaxTempCamera{$cameraId} y
ON y.id = (x.id - 1)
GROUP BY x.id
) a
WHERE a.trend <> (
SELECT b.trend
FROM (
SELECT
x.id AS xid
,(x.maximumtemperature-y.maximumtemperature)/MAX(x.id) trend
FROM histoZoneMaxTempCamera{$cameraId} x
LEFT JOIN histoZoneMaxTempCamera{$cameraId} y
ON y.id = (x.id - 1)
GROUP BY x.id
) b
WHERE b.xid = a.xid - 1
) OR a.xid = 1
;这是完美的工作，而且即使在非常大的起跑台上也是超级快的。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/15762585
复制相关文章
相似问题
腾讯云开发者
