时间间隔后期

Question

我有一个包含ID（唯一），系统ID，时间戳和状态的表。 当状态1的意思是，系统不可用时，当0-可用时：

CREATE temp TABLE temp_data_test (
       id int8 NULL,
       system_id int8 NULL,
       time timestamptz NULL,
       status int4 NULL
);
 
INSERT INTO temp_data_test (id, system, time, status) VALUES
       (53,1,'2022-04-02 13:57:07.000',1),
       (54,1,'2022-04-02 14:10:26.000',0),
       (55,1,'2022-04-02 14:28:45.000',1),
       (56,1,'2022-04-02 14:32:19.000',0),
       (57,1,'2022-04-05 03:20:18.000',1),
       (58,3,'2022-04-05 03:21:18.000',1),
       (59,2,'2022-04-05 03:21:22.000',1),
       (60,2,'2022-04-06 02:27:15.000',0),
       (61,3,'2022-04-06 02:27:15.000',0),
       (62,1,'2022-04-06 02:28:17.000',0);

它的起作用，就像系统变得不可用时，我们会获得1，当可用时获得1-＆gt; 0。 当每个系统都无法看到每个系统时，我需要获得一个结果表。 对于此表结果，我应该

date          system                 available                             unavailable
2022-04-02      1      13:57:07+00:18:19+09:27:40 =23:43:06         23:59:59-23:43:06=..
2022-04-02      2                        24                                     0
2022-04-02      3                        24                                     0

2022-04-03      1                        24                                     0
2022-04-03      2                        24                                     0
2022-04-03      3                        24                                     0
...
2022-04-05      1                     03:20:18                     23:59:59-03:20:18=..
2022-04-05      3                     03:21:18                     23:59:59-03:21:18=..
2022-04-05      2                     03:21:22                     23:59:59-03:21:22=..

2022-04-06      1               23:59:59-02:28:17=..                       02:28:17
2022-04-06      3               23:59:59-02:27:15=..                       02:27:15
2022-04-06      2               23:59:59-02:27:15=..                       02:27:15

尝试使用过度划分和递归来进行此操作，但要获得的间隔比我需要更多。

Answer 1

一个示例查询，用于计算间隔并使用表结构显示时间：

with d_test as (
    select 
        row_number() over (order by system_id, time) as r_num, 
        "time"::date as "onlydate",
        "time", 
        system_id, 
        status
    from temp_data_test
    order by system_id, time 
)
select avi.*, unavi."unavialible", unavi."unavialible_hours" from (
    select 
        d1.system_id, 
        d1.onlydate,
        sum(d2.time - d1.time) as "avialible",  
        (extract(day from sum(d2.time - d1.time) )*24 + extract(hour from sum(d2.time - d1.time)))::text || ' hours' as "avialible_hours"
    from d_test d1 
    inner join d_test d2 on d1.r_num+1 = d2.r_num and d1.system_id = d2.system_id 
    where d1.status = 1 
    group by d1.system_id, d1.onlydate
    order by d1.system_id 
) avi 
left join (
    select 
        d1.system_id, 
        d1.onlydate,
        sum(d2.time - d1.time) as "unavialible",  
        (extract(day from sum(d2.time - d1.time) )*24 + extract(hour from sum(d2.time - d1.time)))::text || ' hours' as "unavialible_hours"
    from d_test d1 
    inner join d_test d2 on d1.r_num+1 = d2.r_num and d1.system_id = d2.system_id 
    where d1.status = 0 
    group by d1.system_id, d1.onlydate
    order by d1.system_id
) unavi on avi.system_id = unavi.system_id and avi.onlydate = unavi.onlydate 

此查询的结果：

System_id	byse_id	avialible	avialible_hours	novialible	unavialible_hours
1	2022-04-04-02	002 002	0时	00:16:53	我编写了 小时
1	2022-04-05	23:07:59	23小时
2	2022-04-05	23:05:53	23小时
3	2022-04-05	1天02:05:57	26小时