Sql: average of a dates

I have to write a query to calculate the average number days between the shopping for each customer(without using subqueries).

    create table data {
customer varchar(20) not null,
bought date not null,
primary key (customer,bought)
}

For example,

insert into data (customer,bought)
values (‘John Smith’, date ‘2011-02-01’),
(‘Alice Cooper’, date ‘2011-02-01’),
(‘Bob Baker’, date ‘2011-02-01’),
(‘John Smith’, date ‘2011-02-02’),
(‘Bob Baker’, date ‘2011-02-02’),
(‘Bob Baker’, date ‘2011-02-03’),
(‘Bob Baker’, date ‘2011-02-04’),
(‘Bob Baker’, date ‘2011-02-05’),
(‘Bob Baker’, date ‘2011-02-06’),
(‘Bob Baker’, date ‘2011-02-07’),
(‘John Smith’, date ‘2011-02-07’),
(‘Alice Cooper’, date ‘2011-02-08’);

should return that John Smith waited 1 day then 5 days, so his average is 3 days. Alice Cooper(!) waited 7 days so her average is 7. Bob Baker is a daily runner so his average is 1.

I have done something like this

select distinct customer, avg (bought) as average from data;

but it doesn't work.

Any help will be greatly appreciated.

---

PostgreSQL版本的链接答案

select customer, (max(bought) - min(bought)) / (count(bought)-1)
from data
group by customer;

---

您必须在时代秒内转换您的时间戳以使用avg聚合函数：

SELECT
    customer,
    timestamp without time zone '1970-01-01' + cast(
       avg(EXTRACT(EPOCH FROM bought::timestamp)
    )::text as interval) 
FROM data
GROUP BY customer;

---

You might want to use a group by statement

select 
   customer, 
   datediff(D, min(bought), max(bought)) / count(bought) as average
from data
group by customer

Whenever you have an aggregate function in the select list, you have to use grouping on the other fields that are not members of the aggregation.

This was tested on SQL Server and the syntax may differ to Postgresql, which I am not using.

链接地址: http://www.djcxy.com/p/5460.html

上一篇: OpenglES混合粒子，但不是背景

下一篇: Sql：一个日期的平均值