获取特定时间范围和特定条目类型的值的平均值

问题描述：

I have a table like this:

ID | Type | Value | Timestamp
--------------------------------
 1  | AAA  | 0.5   | day 1 hour 1
 2  | BBB  | 1.5   | day 1 hour 1
 3  | CCC  | 1.8   | day 1 hour 1
.....
11  | AAA  | 0.6   | day 1 hour 2
12  | BBB  | 1.4   | day 1 hour 2
13  | CCC  | 1.5   | day 1 hour 2
.....


ID := Int, PK, AI, and so on
Type := String/Varchar
Value := Double
Timestamp := Unix Timestamp (Int), could be easily changed to date type

What I want is now an average for specific timeframes. For example:

I want all AAA and all BBB with an average of "Value" for day 1 till day 3. Every day has 24 entries per type over several days.

Expected result would be

Type | Average | Timestamp/Date
-------------------------------
AAA  | 0.5242  | Day 1
AAA  | 0.5442  | Day 2
AAA  | 0.5913  | Day 3
BBB  | 1.4228  | Day 1
BBB  | 1.6924  | Day 2
BBB  | 1.3018  | Day 3

I'm not sure if this is possible just with mysql. Maybe it's more efficient to do it with PHP?

我有一个这样的表： p>

  ID | 输入| 价值| 时间戳
 -------------------------------- 
 1 |  AAA |  0.5 | 第1天1小时1 
 2 |  BBB |  1.5 | 第1天1小时1 
 3 |  CCC |  1.8 | 第1天1 
 ..... 
11 |  AAA |  0.6 | 第1天2小时
12 |  BBB |  1.4 | 第1天2小时
13 |  CCC |  1.5 | 第1天2小时
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 nID：= Int，PK，AI等
类型：=字符串/ Varchar 
值：= Double 
Timestamp：= Unix时间戳（Int）， 可以轻松更改为日期类型
  code>  pre> 
 
 我想要的是现在特定时间范围的平均值。 例如： p> 
 
 
我希望所有AAA和所有BBB在第1天到第3天平均具有“值”。
每天在每天有24个条目。 p  > 
 
 
预期结果为 p> 
 
 
  Type | 平均值| 时间戳/日期
 ------------------------------- 
AAA |  0.5242 | 第1天
AAA |  0.5442 | 第2天
AAA |  0.5913 | 第3天
BBB |  1.4228 | 第1天
BBB |  1.6924 | 第2天
BBB |  1.3018 | 第3天
  code>  pre> 
 
 我不确定这是否可以使用mysql。 也许使用PHP更有效率？ p> 
  div>

答

Does this produce the result you require?

# DROP TABLE IF EXISTS likeThis;

CREATE TABLE likeThis (id INT UNSIGNED, `type` CHAR(255), `value` DECIMAL(2,1), `timestamp` CHAR(255));

INSERT INTO likeThis VALUES
    (1, 'AAA', 0.5, 'day 1 hour 1'),
    (2, 'BBB', 1.5, 'day 1 hour 1'),
    (3, 'CCC', 1.8, 'day 1 hour 1'),
    (11, 'AAA', 0.6, 'day 1 hour 2'),
    (12, 'BBB', 1.4, 'day 1 hour 2'),
    (13, 'CCC', 1.5, 'day 1 hour 2');

SELECT * FROM likeThis;

SELECT type, AVG(`value`) average, LEFT(`timestamp`, INSTR(`timestamp`, 'hour') - 2) timestampDate FROM likeThis GROUP BY `type`, LEFT(`timestamp`, INSTR(`timestamp`, 'hour') - 2);

Let me know if not,

Regarding how to adapt the query if the date is stored in differing field types, here are some example queries:

# How to aggregate by day on a DATETIME field type timestamp
SELECT type, AVG(`value`) average, DATE_FORMAT(`timestamp`, '%Y%m%d') timestampDate FROM likeThis GROUP BY `type`, DATE_FORMAT(`timestamp`, '%Y%m%d');

# How to aggregate by day on a unix timestamp (stored as INT) field type timestamp
SELECT type, AVG(`value`) average, `timestamp` - `timestamp` % 86400 timestampDate FROM likeThis GROUP BY `type`, `timestamp` - `timestamp` % 86400;

Thanks,

James

获取特定时间范围和特定条目类型的值的平均值

相关推荐