Question

在邮政方面,由于收集的时间范围类似:

meter_id	device_id	start_at	end_at
meter1	device1	2020-01-02 10:30	2025-01-02 14:00
meter2	device1	2020-01-02 10:30	2020-01-02 11:30
meter3	device1	2020-01-02 10:30	2020-01-03 11:30

我想把范围推到:

a range for the whole days
a range for the hours / mins before the whole days
a range for the hours / mins after the whole days
if the range already fits into a day then just leave it alone

因此,上述结论如下:

meter_id	device_id	start_at	end_at	remark
meter1	device1	2020-01-02 10:30	2020-01-03 00:00	hours at start of 1st row
meter1	device1	2020-01-03 00:00	2025-01-02 00:00	whole days from 1st row
meter1	device1	2025-01-02 00:00	2025-01-02 14:00	hours at end of 1st row
meter2	device1	2020-01-02 10:30	2020-01-02 11:30	2nd row left alone
meter3	device1	2020-01-02 10:30	2020-01-03 00:00	hours at start of 3rd row
meter3	device1	2020-01-03 00:00	2020-01-03 11:30	hours at end of 3rd row

我撰写了一些作品,但很复杂,很简单。

www.un.org/Depts/DGACM/index_spanish.htm 是否有更简单的方式这样做?

Table and data:

CREATE TABLE IF NOT EXISTS metering_ranges (
    metering_point_id text NOT NULL,
    device_id text NOT NULL,
    start_at timestamp(0) with time zone NOT NULL,
    end_at timestamp(0) with time zone NOT NULL
);

INSERT INTO metering_ranges( metering_point_id, device_id,  start_at, end_at)
VALUES 
    ( meter1 ,  device1 ,  2020-01-02 10:30:00 ,  2025-01-02 14:00:00 ),
    ( meter2 ,  device1 ,  2020-01-02 10:30:00 ,  2020-01-02 11:30:00 ),
    ( meter3 ,  device1 ,  2020-01-02 10:30:00 ,  2020-01-03 11:30:00 );

Existing (complicated) solution

with 

ranges_with_whole_days as (
    SELECT
        metering_point_id,
        device_id,
        start_at,
        date_trunc( day , start_at) + interval  1 d  as start_at_next_whole_day,
        date_trunc( day , end_at) as end_at_whole_day,
        end_at
    FROM
        metering_ranges
),

ranges as (
    SELECT
        metering_point_id,
        device_id,
        start_at,
        CASE
            WHEN start_at_next_whole_day <= end_at_whole_day THEN start_at_next_whole_day ELSE NULL
        END as start_at_next_day,
        CASE 
            WHEN end_at_whole_day >= start_at_next_whole_day THEN end_at_whole_day ELSE NULL
        END as end_at_prev_day,
        end_at
    FROM
        ranges_with_whole_days
),

ranges_bucketed AS (
    -- get hours before whole day
    SELECT metering_point_id, device_id, start_at, start_at_next_day as end_at
    FROM ranges m
    WHERE start_at_next_day IS NOT NULL
    
    UNION
    
    -- get whole day period
    SELECT metering_point_id, device_id, start_at_next_day as start_at, end_at_prev_day as end_at
    FROM  ranges m
    WHERE start_at_next_day IS NOT NULL AND end_at_prev_day IS NOT NULL AND start_at_next_day != end_at_prev_day
    
    UNION
    
    -- get hours after whole day
    SELECT metering_point_id, device_id, end_at_prev_day as start_at, end_at
    FROM ranges m
    WHERE end_at_prev_day IS NOT NULL
    
    UNION
    
    -- get existing record if it fits within a day
    SELECT  metering_point_id,  device_id, start_at, end_at
    FROM ranges m
    WHERE start_at_next_day IS NULL AND end_at_prev_day IS NULL
) 

SELECT *
FROM ranges_bucketed
ORDER BY metering_point_id, device_id, start_at

Answer 1

在经过了细微的ker锁之后,会随之而来,希望分享,但可能是一个更为棘手的解决办法。

select *
from 
(
select metering_point_id, device_id, 
start_at, 
case when date(start_at)=date(end_at) then end_at else date(start_at+INTERVAL  1 day ) end end_at
from  metering_ranges
union
select metering_point_id, device_id, 
case when date(start_at)=date(end_at) then null else date(start_at+INTERVAL  1 day ) end, 
date(end_at)
from  metering_ranges
union
select metering_point_id, device_id, 
case when date(start_at)=date(end_at) then null else date(end_at) end, 
end_at
from  metering_ranges
) t 
where t.start_at is not null and t.start_at!=t.end_at
order by metering_point_id

Answer 2

日期范围可在准备时间序列(通过<代码>union all)的<密码>上填入原表格。合并条件具体规定有效幅度:

select distinct r.meter_id, r.device_id, coalesce(t.a, r.start_at), coalesce(t.b, r.end_at)
from metering_ranges r left join lateral (
   select r.meter_id id, r.start_at a, date(r.start_at) + make_interval(days => (r.end_at != date(r.end_at))::int) b
   union all
   select r.meter_id id, date(r.start_at) + make_interval(days => (r.end_at != date(r.end_at))::int) a, date(r.end_at) b
   union all
   select r.meter_id id, date(r.end_at) a, r.end_at
) t on greatest(t.a, t.b) <= r.end_at and t.a < t.b and t.a >= r.start_at 
order by r.meter_id

https://dbfiddle.uk/wOogrm06”rel=“nofollow noreferer”>。

Answer 3

假设数据类型<代码> 时stamp (根据你的抽样数据,而不是与表格定义相矛盾的) 你们必须做更多的工作。见:

Ignoring time zones altogether in Rails and PostgreSQL

轨道更简单:

WITH cte AS (
   SELECT *
        , CASE WHEN start_at::time =  0:0  THEN start_at
               ELSE date_trunc( day , start_at) + interval  1 day  END AS start_day
        , date_trunc( day , end_at) AS end_day
   FROM   metering_ranges
   )
-- nothing to split
SELECT metering_point_id, device_id, start_at, end_at
FROM   cte
WHERE  end_day <= start_day
-- split core days
UNION ALL
SELECT metering_point_id, device_id, start_day, end_day
FROM   cte
WHERE  end_day > start_day
-- add leading range
UNION ALL
SELECT metering_point_id, device_id, start_at, start_day
FROM   cte
WHERE  end_day > start_day
AND    start_at::time <>  0:0 
-- add trailing range
UNION ALL
SELECT metering_point_id, device_id, end_day, end_at
FROM   cte
WHERE  end_day > start_day
AND    end_at::time <>  0:0 
ORDER  BY 1,2,3;

https://dbfiddle.uk/3hWci9OZ”rel=“nofollow noreferer”>fiddle

Use UNION ALL, not UNION. Faster.
Also, you can append ORDER BY to a UNION query once at the end. That applies to the whole result set.

Variant with multirange type & operators

要求<>Postgres 14或之后。我们可以使用新的<条码>任何多程——任何多程——<条码>,任何多程/<条码>操作者,

弥补多距离的差异。

然后,我们可以<条码>。

WITH cte AS (
   SELECT *
        , CASE WHEN start_at::time =  0:0  THEN start_at
               ELSE date_trunc( day , start_at) + interval  1 day  END AS start_day
        , date_trunc( day , end_at) AS end_day
   FROM   metering_ranges
   )
-- nothing to split
SELECT metering_point_id, device_id, start_at, end_at
FROM   cte
WHERE  end_day <= start_day

-- nested full days to split
UNION ALL
SELECT metering_point_id, device_id, x.*
FROM   cte
CROSS  JOIN LATERAL (
   VALUES (start_day, end_day)
   UNION ALL
   SELECT lower(rest), upper(rest)
   FROM   unnest(tsrange(start_at, end_at)::tsmultirange
               - tsrange(start_day, end_day)::tsmultirange) rest
   ) x
WHERE  end_day > start_day
ORDER  BY 1,2,3;

甚至更短的时间,没有CTE和。 LEFT JOIN LATERAL:

SELECT c.metering_point_id, c.device_id
     , COALESCE(x.start_at, c.start_at) AS start_at
     , COALESCE(x.end_at, c.end_at) AS end_at
FROM  (
   SELECT *
        , CASE WHEN start_at::time =  0:0  THEN start_at
               ELSE date_trunc( day , start_at) + interval  1 day  END AS start_day
        , date_trunc( day , end_at) AS end_day
   FROM   metering_ranges
   ) c
LEFT   JOIN  LATERAL (
   VALUES (start_day, end_day)
   UNION ALL
   SELECT lower(rest), upper(rest)
   FROM   unnest(tsrange(start_at, end_at)::tsmultirange
               - tsrange(start_day, end_day)::tsmultirange) rest
   WHERE  c.end_day > c.start_day
   ) x(start_at, end_at) ON c.end_day > c.start_day
ORDER  BY 1,2,3;

https://dbfiddle.uk/3hWci9OZ”rel=“nofollow noreferer”>fiddle

但是,建筑、计算和随之而来的多种安排增加了相当大的间接费用。不能确定这种先进程度能够与更“精良的力量”问题竞争。

Table and data:

Existing (complicated) solution

Variant with multirange type & operators

友情链接