Sql: Average of 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 version of the linked answer

select customer, (max(bought) - min(bought)) / (count(bought)-1)
from data
group by customer;
Hello, buddy!稿源:Hello, buddy! (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Sql: Average of dates

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录