SQL返回2行从1

Question

我不确定这是否可能或可能有效，但这是我想做的。

我有一个包含以下列的表:日期和值

Date                  Value
06/09/2022 11:30PM    110
06/10/2022 12:45AM    100
06/10/2022 1:30AM     300

我想做一个选择查询，当一个值跨越2天之间的12:00 AM和12:59AM它返回2个数据点(不修改底层数据)，其中每天的值是由多少进入这个窗口的时间戳的小时加权。

在这个例子中，我希望我的查询返回:

Date                  Value
06/09/2022 11:30PM    110
06/09/2022 11:59PM    25
06/10/2022 12:59AM    75
06/10/2022 1:30AM     300

我的问题是:

1. 是否可以编写函数或使用SQL语句从1返回2行而不修改底层数据?

2. 是否有一种方法可以在Postgres与应用程序中实现这一点?

感谢

Answer 1

从表中获得更多行的一种方法是使用union将多个结果连接起来:

create table d (date timestamp, value float);
insert into d values ('2022/06/09 11:30PM',110),('2022/06/10 12:45AM',100),('2022/06/10 1:30AM',300);
select date as "Date", value as "Value" from d where date::time >= '1:00' 
union all
select date_trunc('day',date)+'0:59',
value * extract (epoch from date::time)/3600
from d where date::time < '1:00' 
union all
select date_trunc('day',date)-'0:01'::interval,
value * (3600-extract (epoch from date::time))/3600
from d where date::time < '1:00'
order by 1;

结果:

Date         | Value 
---------------------+-------
2022-06-09 23:30:00 |   110
2022-06-09 23:59:00 |    25
2022-06-10 00:59:00 |    75
2022-06-10 01:30:00 |   300

但是你想把它作为一个函数?我可以在这里使用过程代码，但我已经把它作为查询了，所以我就使用它。

create function mangle(inout "Date" timestamp,inout "Value" float)
returns setof record language sql as $$
select "Date", "Value" where "Date"::time >= '1:00' 
union all
select date_trunc('day',"Date")+'0:59',
"Value" * extract (epoch from "Date"::time)/3600
where "Date"::time < '1:00' 
union all
select date_trunc('day',"Date")-'0:01'::interval,
"Value" * (3600-extract (epoch from "Date"::time))/3600
where "Date"::time < '1:00'
order by 1;
$$;
select (mangle(date,value)).* from d;

正如你所看到的，混合大小写标识符很快就会变得混乱，所以我建议你坚持使用小写，除非你绝对需要它们。

Answer 2

如果您从未在午夜前的一天23:59看到条目，则将其分解如下:

找到要分割的值并计算出份额:

with to_be_split as (
select date, value, 
extract(minute from date)/60 as today_share,
(60 - extract(minute from date))/60 as yesterday_share 
from my_table
where extract(hour from date) = 0
), 

执行横向交叉连接到values伪表以创建额外的行:

splits as (
select sub.date, round(tbs.value * sub.share) as value
from to_be_split tbs
cross join lateral (
values (date, today_share),
(date_trunc('day', date) - interval '1 minute', yesterday_share)
) as sub(date, share)
)

将结果与不需要拆分的记录合并:

select date, value
from splits
union all
select date, value
from my_table
where extract(hour from date) != 0
order by date;

db<此处小提琴>