postgresql 在SQL(Postgres)中查找传感器同时属于多个标签时的SCD-2时间范围重叠

anauzrmj 于 2023-06-05 发布在 PostgreSQL

关注(0)|答案(1)|浏览(395)

我有一个PostgreSQL表，它使用SCD-2方法表示具有不同标签的不同传感器的时间限制关联。表格结构如下：

CREATE TABLE SensorLabel (
    sensor_id INT,
    label_id INT,
    start_time TIMESTAMPTZ,
    end_time TIMESTAMPTZ
);

该表中的每一行表示传感器（sensor_id）在特定时间段[start_time到end_time]（因此，分别为包含性、排他性）。
现在我有一个问题，我需要找到一组标签的所有时间范围重叠。也就是说，我有一组标签{label 1，label 2，...，labelN}，我想找到传感器同时与这些标签中的每一个相关联的所有时间范围。
请注意，每个标签关联的时间范围可能不同，因此这些范围的交集可能会分解为更小的时间范围。我只想返回传感器与所有给定标签关联的时间范围。
我可以解决说一个静态的2标签或3标签，但有问题 Package 在一个通用的SQL，解决了一个变量N标签

EDIT：部分输入输出示例

sensor|label|from|to
1|1|2021-01-01|2021-10-01
1|2|2020-12-01|2021-05-01
1|2|2021-07-01|2021-09-01
1|3|2021-03-01|2021-06-01
1|3|2021-08-01|2021-12-01

输出：即：标签1、2、3重叠的时间范围：

sensor|from|to
1|2021-03-01|2021-05-01
1|2021-08-01|2021-09-01

postgresql

来源：https://stackoverflow.com/questions/76401731/find-scd-2-timerange-overlaps-when-a-sensor-belongs-to-multiple-labels-simultane

1条答案

按热度按时间

mkh04yzy1#

我唯一能做的就是用一个函数：

CREATE TABLE sensorlabel (
    sensor_id INT,
    label_id INT,
    start_time TIMESTAMPTZ,
    end_time TIMESTAMPTZ
);

insert into sensorlabel values
(1,1,'2021-01-01','2021-10-01'),
(1,2,'2020-12-01','2021-05-01'),
(1,2,'2021-07-01','2021-09-01'),
(1,3,'2021-03-01','2021-06-01'),
(1,3,'2021-08-01','2021-12-01');

CREATE OR REPLACE FUNCTION public.tsrange_fnc(l_ids integer[], s_id integer)
 RETURNS SETOF tstzrange
 LANGUAGE plpgsql
AS $function$
DECLARE

    _tsrange tstzrange;
    _prevrange tstzrange := NULL;
    _testrange tstzrange;

BEGIN
for _tsrange in select
        tstzrange(start_time, end_time)
    from
        sensorlabel
    where
        label_id = ANY(l_ids)
    and
        sensor_id = s_id
    order by
        start_time  LOOP
    IF _prevrange IS NULL THEN
        _prevrange = _tsrange;
    ELSE
        _testrange = _tsrange * _prevrange;
        IF _testrange = 'empty'::tstzrange THEN
            RETURN NEXT _prevrange;
            _prevrange = _tsrange;
        ELSE
           _prevrange =  _tsrange * _prevrange;
        END IF;
    END IF;

END LOOP;
            RETURN NEXT _prevrange;
END;
$function$



select tsrange_fnc(ARRAY[1, 2, 3], 1);
                      tsrange_fnc                      
-------------------------------------------------------
 ["03/01/2021 00:00:00 PST","05/01/2021 00:00:00 PDT")
 ["08/01/2021 00:00:00 PDT","09/01/2021 00:00:00 PDT")

 select tsrange_fnc(ARRAY[1, 2], 1);
                      tsrange_fnc                      
-------------------------------------------------------
 ["01/01/2021 00:00:00 PST","05/01/2021 00:00:00 PDT")
 ["07/01/2021 00:00:00 PDT","09/01/2021 00:00:00 PDT")

select tsrange_fnc(ARRAY[2, 3], 1);
                      tsrange_fnc                      
-------------------------------------------------------
 ["03/01/2021 00:00:00 PST","05/01/2021 00:00:00 PDT")
 ["08/01/2021 00:00:00 PDT","09/01/2021 00:00:00 PDT")

赞(0）回复(0）举报 2023-06-05

我来回答

postgresql 在SQL(Postgres)中查找传感器同时属于多个标签时的SCD-2时间范围重叠

1条答案

相关问题

热门标签

最新问答