假设我有这个架构:
假设我有这个模式:
Assuming I have this schema:
假设我有这个模式:
Assuming I have this schema:
create table rental (
id integer,
rental_date timestamp,
customer_id smallint,
return_date timestamp,
);
运行这个查询返回了奇怪的结果:
select customer_id, avg(return_date - rental_date) as "avg"
from rental
group by customer_id
order by "avg" DESC
它显示:
customer_id|avg_rent_duration |
-----------|----------------------|
315| 6 days 14:13:22.5|
187|5 days 34:58:38.571428|
321|5 days 32:56:32.727273|
539|5 days 31:39:57.272727|
436| 5 days 31:09:46|
532|5 days 30:59:34.838709|
427| 5 days 29:27:05|
555|5 days 26:48:35.294118|
...
599 rows
为什么会有像5天34:58:38
、5天32:56:32
等这样的数值?我以为一天只有24小时,也许我错了。
编辑
演示在这里:http://sqlfiddle.com/#!17/caa7a/1/0
样本数据:
insert into rental (rental_date, customer_id, return_date)
values
('2007-01-02 13:10:06', 1, '2007-01-03 01:01:01'),
('2007-01-02 01:01:01', 1, '2007-01-09 15:10:06'),
('2007-01-10 22:10:06', 1, '2007-01-11 01:01:01'),
('2007-01-30 01:01:01', 1, '2007-02-03 22:10:06');
justify_interval()
函数来规范化区间。 - user330315