postgresql 在同一表格中显示日期范围的单行

f45qwnt8  于 2023-03-22  发布在  PostgreSQL
关注(0)|答案(1)|浏览(110)

下面是用于实验的表格和数据。

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

但不管用有人知道吗
谢谢

rqenqsqc

rqenqsqc1#

让你的查询变得困难的是检测哪些记录必须用作每个范围的开始,哪些记录落在范围的中间。解决方案是generate_series,因为它只需要传递一个时间起点(也是一个终点,但它不是这里重要的)来生成你需要的所有范围。
顺便说一句,我正在写两个解决方案,以不同的方式处理数据中的差距。在您提供的数据集上没有差异,但如果您将('2014-03-03 21:43:19', 1), ('2014-03-03 21:48:00', 1)添加到您的测试数据中,您将看到差异。
我添加了2个helper列(在注解中),以使区别更加明显。请随意取消注解。
为了完全安全地使用<=<以及>=>,我在下面的查询中使用了范围和<@运算符。

溶液1:从第一个时间戳开始的15分钟范围,没有重置(1次调用generate_series以获取所有时间戳范围)。

WITH T(start_date, company) AS (
    SELECT generate_series (MIN(ad_date), MAX(ad_date), interval '15 minute'), company
    FROM admission
    GROUP BY company
) 
SELECT MIN(a.ad_date), MAX(a.ad_date), count(*), a.company
/*, t.start_date as start_date_range, t.start_date + interval '15 minute' as end_date_range*/
FROM admission a
JOIN T ON a.company = t.company and a.ad_date <@ tsrange(t.start_date, t.start_date + interval '15 minute', '[)')
GROUP BY a.company, t.start_date

溶液2:15分钟范围,每次检测到间隙时重置(如果NOT EXISTS检测到间隙,则可能多次调用generate_series):

WITH T(start_date, company) AS (
    SELECT generate_series(ad_date, LEAD(ad_date - interval '15 minute', 1 , ad_date + interval '15 minute') OVER w, interval '15 minute'), company
    FROM admission a
    WHERE NOT EXISTS (
        SELECT
        FROM admission
        WHERE (ad_date < a.ad_date AND ad_date + interval '15 minute' >= a.ad_date)
    )
    WINDOW w AS (PARTITION BY company ORDER BY ad_date)
) 
SELECT MIN(a.ad_date), MAX(a.ad_date), count(*), a.company
/*, t.start_date as start_date_range, t.start_date + interval '15 minute' end_date_range*/
FROM admission a
JOIN T ON a.company = t.company and a.ad_date <@ tsrange(t.start_date, t.start_date + interval '15 minute', '[)')
GROUP BY a.company, t.start_date

注意:根据您的示例数据,这不是您所要求的,但为了完整,我必须说我会亲自对时间戳进行分组,以便在每个小时后的0153045分钟开始范围。
这可以通过将MIN(ad_date)转换为解决方案1中的类型date来轻松实现,这将移动所有后续范围。

WITH T(start_date, company) AS (
    SELECT generate_series (MIN(ad_date)::date, MAX(ad_date), interval '15 minute'), company
    FROM admission
    GROUP BY company
) 
SELECT MIN(a.ad_date), MAX(a.ad_date), count(*), a.company
/*, t.start_date as start_date_range, t.start_date + interval '15 minute' as end_date_range*/
FROM admission a
JOIN T ON a.company = t.company and a.ad_date <@ tsrange(t.start_date, t.start_date + interval '15 minute', '[)')
GROUP BY a.company, t.start_date

相关问题