MySQL平均每4行 [英] MySQL Average Every 4 Rows

问题描述

我有一个1000万以上的行表，如下所示:

I have a 10 million+ row table that looks like this:

    id | time_read  | value
    -----------------------
    9   1111111111   100
    9   1111111222   200
    9   1111111333   150
    9   1111111444   225

我要AVG每4行的值.如何在MySQL中做到这一点?

I want to AVG the value of every 4 rows. How do I do this in MySQL?

此外，对于我的问题，如果我想平均一个月中所有数据的每4行的值并返回表中每年每个月的总计，我该怎么做?例如，在我的第一个问题中使用同一张表，最后显示为:

Also, secondary to my question, how might I do this if I wanted to avg the value of every 4 rows for all the data in a month and return that total for each month of each year in the table? For example, using the same table in my first question, ending up with:

    2008 | 12 | 500000
    2009 | 01 | 450000
    2009 | 02 | 475000

edit:换句话说，按年-月分组的每个平均4行的总数.每个time_read相隔15分钟.

edit: In other words, the total of each average 4 rows grouped by year-month. Each time_read is 15 minutes apart.

我以前在做类似的事情，但是还不够准确.我需要平均每4行并求和，而不是将一个月中所有值的总和除以4.

I was doing something like this previously, but it wasn't accurate enough. I need to average every 4 rows and total that instead of taking the sum of all value's in a month and dividing by 4.

    SELECT DATE_FORMAT(FROM_UNIXTIME(time_read),'%Y %m') as tr,  
    DATE_FORMAT(FROM_UNIXTIME(time_read),'%Y') as year, 
    DATE_FORMAT(FROM_UNIXTIME(time_read),'%m') as month, SUM(value) as value 
    FROM table WHERE id = 9 
    GROUP BY tr

推荐答案

尝试此代码-

CREATE TABLE table1 (
  id INT(11) NOT NULL AUTO_INCREMENT,
  time_read INT(11) DEFAULT NULL,
  value INT(11) DEFAULT NULL,
  PRIMARY KEY (id)
);

INSERT INTO table1 VALUES 
  (1, 1312880400, 10),  -- 09.08.2011 12:00:00 -> 1 august
  (2, 1312880410, 20),  -- 09.08.2011 12:00:10 -> 2
  (3, 1312880420, 30),  -- 09.08.2011 12:00:20 -> 3
  (4, 1312880430, 40),  -- 09.08.2011 12:00:30 -> 4
  (5, 1312880440, 50),  -- 09.08.2011 12:00:40 -> 5
  (6, 1315558800, 60),  -- 09.09.2011 12:00:00 -> 1 september
  (7, 1315558810, 70);  -- 09.09.2011 12:00:10 -> 2

SELECT AVG(value) FROM (
  SELECT t1.*, COUNT(*) cnt FROM table1 t1
  LEFT JOIN table1 t2
    ON t2.time_read <= t1.time_read
      AND YEAR(FROM_UNIXTIME(t2.time_read)) = YEAR(FROM_UNIXTIME(t1.time_read))
      AND MONTH(FROM_UNIXTIME(t2.time_read)) = MONTH(FROM_UNIXTIME(t1.time_read))
  GROUP 
    BY time_read
) t
GROUP BY
  YEAR(FROM_UNIXTIME(time_read)), MONTH(FROM_UNIXTIME(time_read)), CEIL(cnt/4);

+------------+
| AVG(value) |
+------------+
|    25.0000 |
|    50.0000 |
|    65.0000 |
+------------+

它按月分组并且每月有4条记录.

It does group by month and 4 records in month.

这篇关于MySQL平均每4行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！