MySQL查询获取温度趋势 [英] Mysql query to get trend of temperature

问题描述

我有一张很大的桌子，每1分钟记录一次温度，我想查询的是一个趋势；诸如每个选定时间段(一个小时或15分钟；取决于查询)的百分比增加或百分比减少

I have a very large table where I have temperatures getting logged every 1 mins, what I would like to query is a trend; something like a percentage increase or percentage decrease per selected period ( hour or 15mins; depending on the query)

我的表看起来(示例)如下

my table looks (example) like the following

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

我认为可以按如下方式计算趋势(按照链接)，但是如果您有更好的方法，请指正； 取每行之间的差，然后相加然后除以计数.因此，对于上表，我们得到

I believe a trend can be calculated as follow (as per link), but correct me if you have a better way; take the difference between each row then add then up and divide by count. so for the table above we get

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.

The trend per minute for last 11 minutes is sum of diff/11. which gives 0.063C per minute for last 11minutes.

有人可以帮我获取最近3小时每小时的百分比趋势吗?和每分钟1小时的趋势?

Can someone please help me get percentage trend per hour for last 3 hours. and trend per minute for 1 hour?

推荐答案

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 |
+--------+---------------------+-------------+-------+-----+-----------+

顺便说一句，如果您有兴趣每小时获取平均结果，则可以执行以下操作...

Incidentally, if you're interested in getting average results per hour, you could do something like this...

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');

这篇关于MySQL查询获取温度趋势的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！