Sql 查询以从时间范围列表中查找唯一时间段

当一天中有多组重叠时间时，让它工作

使用min()和max()的普通聚合不能解决这个问题.哪些行最终形成一组仅在after合并范围变得明显.

聚集功能range_agg()使任务变得简单得多.它与Postgres 14相加.现在，只需计算合并范围就非常简单:

SELECT unnest(range_agg(tsrange(starttime, endtime, '[]'))) AS merged_range
FROM   time_records;

要获得一组涉及的ID，我们需要做更多的工作.一种方法是连接回基础表，然后再次聚合(组现在由合并的范围标识):

SELECT employee_id, lower(merged) AS starttime, upper(merged) AS endtime
     , array_agg(t.id) AS ids
FROM  (
   SELECT employee_id, unnest(range_agg(tsrange(starttime, endtime, '[]'))) AS merged
   FROM   time_records
   GROUP  BY employee_id
   ) r
JOIN   time_records t USING (employee_id)
WHERE  r.merged @> t.starttime
GROUP  BY r.employee_id, r.merged
ORDER  BY r.employee_id, r.merged;

使用LATERAL个子查询的另一种方式:

SELECT r.employee_id, lower(r.merged) AS starttime, upper(r.merged) AS endtime, i.ids
FROM  (
   SELECT employee_id, unnest(range_agg(tsrange(starttime, endtime, '[]'))) AS merged
   FROM   time_records
   GROUP  BY employee_id
   ) r
CROSS  JOIN LATERAL (
   SELECT ARRAY (
      SELECT t.id
      FROM   time_records t
      WHERE  t.employee_id = r.employee_id
      AND    t.starttime <@ r.merged
      ORDER  BY t.id
      )
   ) i (ids)
ORDER  BY r.employee_id, r.merged;

fiddle个个

相关:

• Remove duplicates from array_agg, where elements are also arrays个

我不确定这两个查询是否也比下面的定制函数快，因为它只对整个表迭代一次.

Postgres 9.6

在使用过时的版本时，创建一个定制的集返回函数(一次):

CREATE OR REPLACE FUNCTION public.f_merge_ranges()
  RETURNS TABLE (
    employee_id int
  , starttime timestamp
  , endtime timestamp
  , ids int[]
  )
  LANGUAGE plpgsql AS
$func$
DECLARE
   r record;  -- current row
BEGIN
   FOR r IN 
      SELECT t.id, t.employee_id, t.starttime, t.endtime
      FROM   time_records t
      ORDER  BY t.employee_id, t.starttime, t.endtime DESC, t.id  -- better take longer range first
   LOOP
      IF r.employee_id = employee_id THEN  -- works for null in first iteration
         IF r.starttime > endtime THEN
            RETURN NEXT;
            starttime   := r.starttime;
            endtime     := r.endtime;
            ids         := ARRAY[r.id];
         ELSE
            ids         := ids || r.id;
            IF r.endtime > endtime THEN
               endtime  := r.endtime;
            END IF;
         END IF;
      ELSE
         IF employee_id IS NOT NULL THEN  -- catch first iteration
            RETURN NEXT;
         END IF;
         employee_id := r.employee_id;
         starttime   := r.starttime;
         endtime     := r.endtime;
         ids         := ARRAY[r.id];
      END IF;
   END LOOP;

   -- return last row (if any)
   IF FOUND THEN
      RETURN NEXT;
   END IF;
END
$func$;

电话:

SELECT * FROM public.f_merge_ranges();

fiddle个个

与上面的查询不同，ids中的数组是未排序的.如果你需要的话，你需要做得更多.

相关:

• Calculate Difference in Overlapping Time in PostgreSQL / SSRS个