获取过去7天的平均值 [英] Get average of last 7 days

问题描述

我正在解决一个问题，其中我具有一系列日期的值.我想通过对表中的行进行平均，然后将date列重新分配为相对于过去7天，来合并这些行.我缺乏SQL经验，可能会有所帮助.感谢您的关注！！

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

例如 7行，包含日期和值.

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

结果行

UniqueId    Date      AvgValue
........    ....      ........
  a       2014-03-26   2

首先，我什至不确定这是否可行.我正在尝试使用此数据来解决问题.我以为可能会使用带有分区的框架窗口将日期转换为具有平均结果的日期，但是我不确定如何在SQL中这么说.

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.

推荐答案

上午以样本为例

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)

选项A:-使用PostgreSQL特定功能WITH

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 

> SQLFIDDLE DEMO

选项B:-在PostgreSQL和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 

> SQLFDDLE DEMO

这篇关于获取过去7天的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！