MySQL 过去几天均值 当天,获取过去7天的平均值

I'm attacking a problem, where I have a value for a a range of dates. I would like to consolidate the rows in my table by averaging them and reassigning the date column to be relative to the last 7 days. My SQL experience is lacking and could use some help. Thanks for giving this a look!!

E.g.

7 rows with dates and values.

UniqueId Date Value

........ .... .....

a 2014-03-20 2

a 2014-03-21 2

a 2014-03-22 3

a 2014-03-23 5

a 2014-03-24 1

a 2014-03-25 0

a 2014-03-26 1

Resulting row

UniqueId Date AvgValue

........ .... ........

a 2014-03-26 2

First off I am not even sure this is possible. I'm am trying to attack a problem with this data at hand. I thought maybe using a framing window with a partition to roll the dates into one date with the averaged result, but am not exactly sure how to say that in SQL.

解决方案

Am taking following as sample

CREATE TABLE some_data1 (unique_id text, date date, value integer);

INSERT INTO some_data1 (unique_id, date, value) VALUES

( 'a', '2014-03-20', 2),

( 'a', '2014-03-21', 2),

( 'a', '2014-03-22', 3),

( 'a', '2014-03-23', 5),

( 'a', '2014-03-24', 1),

( 'a', '2014-03-25', 0),

( 'a', '2014-03-26', 1),

( 'b', '2014-03-01', 1),

( 'b', '2014-03-02', 1),

( 'b', '2014-03-03', 1),

( 'b', '2014-03-04', 1),

( 'b', '2014-03-05', 1),

( 'b', '2014-03-06', 1),

( 'b', '2014-03-07', 1)

OPTION A : - Using PostgreSQL Specific Function WITH

with cte as (

select unique_id

,max(date) date

from some_data1

group by unique_id

)

select max(sd.unique_id),max(sd.date),avg(sd.value)

from some_data1 sd inner join cte using(unique_id)

where sd.date <=cte.date

group by cte.unique_id

limit 7

OPTION B : - To work in PostgreSQL and MySQL

select max(sd.unique_id)

,max(sd.date)

,avg(sd.value)

from (

select unique_id

,max(date) date

from some_data1

group by unique_id

) cte inner join some_data1 sd using(unique_id)

where sd.date <=cte.date

group by cte.unique_id

limit 7