合并postgres中的时间戳间隔

Question

我有以下数据，其中我跟踪两个不同设备上的活动：

CREATE TABLE input_data
(
device "varchar",
activity "varchar",
time_from time,
time_to time);
INSERT INTO input_data (device, activity, time_from, time_to) VALUES
('pc', 'netflix', '2020-01-01 12:30:00', '2020-01-01 14:00:00'),
('phone', 'call', '2020-01-01 12:58:40', '2020-01-01 13:05:00');

我想看看同时在两个设备上花费的时间间隔：

CREATE TABLE desired_result
(
pc_activity "varchar",
phone_activity "varchar",
time_from time,
time_to time);
INSERT INTO desired_result (pc_activity, phone_activity, time_from, time_to) VALUES
('netflix', 'NA', '2020-01-01 12:30:00', '2020-01-01 12:58:40'),
('netflix', 'call', '2020-01-01 12:58:40', '2020-01-01 13:05:00'),
('netflix', 'NA', '2020-01-01 13:05:00', '2020-01-01 14:00:00')

如何在Postgres中获得所需的结果？

Answer 1

可能有一个更有效的解决方案，但下面要实现desired_result:

/* collect all time stamp changes in one column */
WITH cte_raw_intervals AS (
SELECT time_from AS time_stamp from input_data
UNION
SELECT time_to AS time_stamp from input_data
order by time_stamp
),
/* create sorted table of all time stamp changes */
cte_sorted_intervals AS (
select *, 
ROW_NUMBER() OVER(ORDER BY time_stamp) AS INTERVAL_ID
from cte_raw_intervals),
/* calculate end time for each time stamp */
cte_intervals AS (
SELECT a.interval_id, a.time_stamp AS time_from ,b.time_stamp AS time_to
FROM cte_sorted_intervals AS a LEFT JOIN cte_sorted_intervals AS b 
ON a.INTERVAL_ID+1 = b.INTERVAL_ID
WHERE b.time_stamp IS NOT NULL),
/* attach each activity back to the new time stamp intervals */
cte_union_results AS 
(SELECT cte_intervals.*, input_data.device, input_data.activity from cte_intervals , input_data
WHERE cte_intervals.time_from >= input_data.time_from
AND cte_intervals.time_to <= input_data.time_to)
/* cross tab the union table results into the unique intervals */
SELECT 
b.activity as pc_activity, c.activity as phone_activity, 
a.time_from, b.time_to FROM cte_intervals as a 
LEFT JOIN cte_union_results as b
ON a.time_from = b.time_from AND b.device = 'pc'
LEFT JOIN cte_union_results as c
ON a.time_from = c.time_from AND c.device = 'phone'