下面是用于实验的表格和数据。
CREATE TABLE IF NOT EXISTS admission
(
id integer NOT NULL DEFAULT serial,
ad_date timestamp without time zone,
company integer,
CONSTRAINT admission_pkey PRIMARY KEY (id)
)
INSERT INTO admission (ad_date,company_id) VALUES
('2014-03-03 20:46:33',1),
('2014-03-03 20:49:13',1),
('2014-03-03 21:01:03',1),
('2014-03-03 21:01:06',1),
('2014-03-03 21:02:16',1),
('2014-03-03 21:02:22',1),
('2014-03-03 21:15:48',1),
('2014-03-03 21:16:19',1);
我公司需要在每15分钟前办理一次入场手续。
例如,低于预期输出
start_date next_15_min_date total_count company
2014-03-03 20:46:33.000 2014-03-03 21:01:06.000 4 1
2014-03-03 21:02:16.000 2014-03-03 21:16:19.000 4 1
我试过了
select t1.ad_date as start_date ,t2.ad_date as next_15_min_date , count(t2.ad_date),t2.company
from admission t1,admission t2
where t2.ad_date between t1.ad_date and t1.ad_date+interval '15 minute'
group by t1.ad_date,t2.ad_date,t2.company
order by t1.ad_date,t2.ad_date,t2.company
但不管用有人知道吗
谢谢
1条答案
按热度按时间rqenqsqc1#
让你的查询变得困难的是检测哪些记录必须用作每个范围的开始,哪些记录落在范围的中间。解决方案是
generate_series
,因为它只需要传递一个时间起点(也是一个终点,但它不是这里重要的)来生成你需要的所有范围。顺便说一句,我正在写两个解决方案,以不同的方式处理数据中的差距。在您提供的数据集上没有差异,但如果您将
('2014-03-03 21:43:19', 1), ('2014-03-03 21:48:00', 1)
添加到您的测试数据中,您将看到差异。我添加了2个helper列(在注解中),以使区别更加明显。请随意取消注解。
为了完全安全地使用
<=
与<
以及>=
与>
,我在下面的查询中使用了范围和<@
运算符。溶液1:从第一个时间戳开始的15分钟范围,没有重置(1次调用
generate_series
以获取所有时间戳范围)。溶液2:15分钟范围,每次检测到间隙时重置(如果
NOT EXISTS
检测到间隙,则可能多次调用generate_series
):注意:根据您的示例数据,这不是您所要求的,但为了完整,我必须说我会亲自对时间戳进行分组,以便在每个小时后的
0
,15
,30
和45
分钟开始范围。这可以通过将
MIN(ad_date)
转换为解决方案1中的类型date
来轻松实现,这将移动所有后续范围。