mysql - 根据给定条件选择MYSQL datetime取值范围

Question

我有一个包含日期时间列的 mysql 表。

即

表结构

CREATE TABLE IF NOT EXISTS powerpro (
  `record_no` int(11) NOT NULL AUTO_INCREMENT,
  `date_time` datetime DEFAULT NULL,
  `ph1_active_power` float DEFAULT NULL,
   PRIMARY KEY (`record_no`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=47525 ;

示例数据

+-----------+---------------------+-------------+
| record_no |      Date_Time      |   Phase 1   |
+-----------+---------------------+-------------+
|         1 | 2014-12-01 00:00:00 | 73.07668203 |
|         2 | 2014-12-01 00:01:00 | 73.02925975 |
|         3 | 2014-12-01 00:02:00 | 72.98180225 |
|         4 | 2014-12-01 00:03:00 | 72.93430956 |
|         5 | 2014-12-01 00:04:00 | 72.88678169 |
|         6 | 2014-12-01 00:05:00 | 72.83921867 |
|         7 | 2014-12-01 00:06:00 | 72.79162052 |
|         8 | 2014-12-01 00:07:00 | 72.74398728 |
|         9 | 2014-12-01 00:08:00 | 72.69631894 |
|        10 | 2014-12-01 00:09:00 | 72.64861556 |
|        11 | 2014-12-01 00:10:00 | 72.60087713 |
|        12 | 2014-12-01 00:11:00 | 72.5531037  |
|        13 | 2014-12-01 00:12:00 | 72.50529527 |
|        14 | 2014-12-01 00:13:00 | 72.45745188 |
|        15 | 2014-12-01 00:14:00 | 72.40957355 |
|        16 | 2014-12-01 00:15:00 | 72.3616603  |
|        17 | 2014-12-01 00:16:00 | 72.31371215 |
|        18 | 2014-12-01 00:17:00 | 72.26572912 |
|        19 | 2014-12-01 00:18:00 | 72.21771125 |
|        20 | 2014-12-01 00:19:00 | 72.16965855 |
|        21 | 2014-12-01 00:20:00 | 72.12157105 |
|        22 | 2014-12-01 00:21:00 | 72.07344876 |
|        23 | 2014-12-01 00:22:00 | 72.02529172 |
|        24 | 2014-12-01 00:23:00 | 71.97709994 |
|        25 | 2014-12-01 00:24:00 | 71.92887345 |
+-----------+---------------------+-------------+

我正在构建一个 MYSQL 查询，通过使用数据组 03 分钟范围内的 UNIX_TIMESTAMP(date_time) 值返回一组行的平均值。并且需要创建一个临时表，将字段作为时间、p1 和avg_key。然后想通过avg_key对选中的记录进行分组

到目前为止，我的查询如下所示:

 SELECT UNIX_TIMESTAMP(date_time) AS time, 
ph1_active_power AS p1 

    CASE powerpro.UNIX_TIMESTAMP(date_time)
            WHEN <condition> THEN avg_key = 1
            WHEN ........... THEN avg_key = 1
            WHEN ............THEN avg_key = 2
            WHEN............ THEN avg_key = 2

        END AS avg_key
    FROM
        powerpro

临时表的输出可能是这样的:

+------------+---------+---------+
|    time    |   p1    | avg_key |
+------------+---------+---------+
| 1417372200 | 73.0767 |       1 |
| 1417372260 | 73.0293 |       1 |
| 1417372320 | 72.9818 |       1 |
| 1417372380 | 72.9343 |       2 |
| 1417372440 | 72.8868 |       2 |
| 1417372500 | 72.8392 |       2 |
| 1417372560 | 72.7916 |       3 |
| 1417372620 | 72.744  |       3 |
| 1417372680 | 72.6963 |       3 |
+------------+---------+---------+

然后所需的输出(平均):

+------------+--------------+--------------+
| time(avg)  |   p1(avg)    | avg_key(avg) |
+------------+--------------+--------------+
| 1417372240 | 73.029266667 |            1 |
| 1417372260 | 72.886766667 |            2 |
| 1417372620 | 72.743966667 |            3 |
+------------+--------------+--------------+

任何人都可以帮助我构建正确的脚本

Answer 1

你不能简单地做这样的事情吗？

SELECT 
  AVG(time) AS avg_time, 
  AVG(p1) AS avg_p1, 
  avg_key 
FROM (
  SELECT 
    UNIX_TIMESTAMP(date_time) AS time, 
    ph1_active_power AS p1, 
    FLOOR(RAND() * 3) + 1 AS avg_key  -- note: insert conditions here! 
  FROM powerpro
) data 
GROUP BY 
  avg_key;

这消除了首先创建临时表的需要，但可以使用“from 子句中的子查询”从源值计算结果。请注意，对所提到的条件没有任何要求，因此我的查询使用 RAND() 为 avg_key 生成随机值。您可以在那里插入任意表达式。