如何实现admission表中按15分钟时间段分组统计公司入院记录？

实验用表结构及数据 #

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) 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);

注：原INSERT语句中的company_id为笔误，对应表字段应为company

需求 #

将某公司的入院记录按每15分钟为一个时间段分组统计，每个时间段需展示：

• 该组的起始日期（组内最早的记录时间）
• 该时间段内最后一条记录的日期（组内最晚的记录时间）
• 记录总数
• 公司ID

预期输出 #

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

尝试的SQL（未得到预期结果） #

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

解决方案 #

原SQL的问题在于自连接逻辑错误，导致生成大量重复分组，无法得到正确的聚合结果。以下是两种适配需求的PostgreSQL解决方案：

方案1：匹配预期输出的连续时间段分组 #

该方案基于记录的连续时间划分窗口，以上一个窗口结束时间作为下一个窗口的起点，完全贴合你的预期输出：

WITH ranked_records AS (
    SELECT 
        ad_date,
        company,
        -- 计算每条记录与前一条的时间差，超过15分钟则开启新分组
        SUM(CASE WHEN ad_date - LAG(ad_date, 1, ad_date) OVER (PARTITION BY company ORDER BY ad_date) > INTERVAL '15 minutes' THEN 1 ELSE 0 END) OVER (PARTITION BY company ORDER BY ad_date) AS group_id
    FROM admission
    WHERE company = 1 -- 指定目标公司
)
SELECT 
    MIN(ad_date) AS start_date,
    MAX(ad_date) AS next_15_min_date,
    COUNT(*) AS total_count,
    company
FROM ranked_records
GROUP BY company, group_id
ORDER BY start_date;

方案2：固定时间桶分组（可选） #

如果需要按整点/15分的固定时间间隔分组（比如20:45-21:00、21:00-21:15这类固定窗口），可以使用时间截断函数：

SELECT 
    DATE_TRUNC('minute', ad_date) - INTERVAL '1 minute' * ((EXTRACT(minute FROM ad_date)::int % 15)) AS start_date,
    MAX(ad_date) AS next_15_min_date,
    COUNT(*) AS total_count,
    company
FROM admission
WHERE company = 1
GROUP BY company, DATE_TRUNC('minute', ad_date) - INTERVAL '1 minute' * ((EXTRACT(minute FROM ad_date)::int % 15))
ORDER BY start_date;

内容的提问来源于stack exchange，提问作者Avinash Ware