我有一个查询,其中我将照明开关的时间平整地打开,同时将打开(开始)时间和关闭(结束)时间联系在一起。
我想知道打开了多少个电灯开关,有人知道我该怎么办?
查询示例:
Select Flat, ID_light, start, end, 1 as ON
from lights
I / O的示例:
输入数据:
Flat Id Light Start End On
1 1 01/01/2021 00:00:15 01/01/2021 00:59:00 1
1 2 01/01/2021 00:00:15 01/01/2021 01:59:00 1
2 1 01/01/2021 00:00:15 01/01/2021 00:01:15 1
2 1 01/01/2021 00:02:00 01/01/2021 01:00:00 1
2 2 01/01/2021 00:00:00 01/01/2021 08:00:00 1
文字输出:
Flat Start End Lights On
1 01/01/2021 00:00:15 01/01/2021 00:59:00 2
1 01/01/2021 00:59:00 01/01/2021 01:59:00 1
2 01/01/2021 00:00:00 01/01/2021 00:00:15 1
2 01/01/2021 00:00:15 01/01/2021 00:01:15 2
2 01/01/2021 00:01:15 01/01/2021 00:02:00 1
2 01/01/2021 00:02:00 01/01/2021 01:00:00 2
2 01/01/2021 01:00:00 01/01/2021 08:00:00 1
该range
数据类型简化了这一点。
获取所有单位和时间:
with times as (
select flat, start as ts from lights
union
select flat, end_ from lights
),
使用lead()
以下命令为每个间隔构造行:
all_ivals as (
select flat, ts as start,
lead(ts) over (partition by flat
order by ts) as end_
from times
)
连接回原始表并使用范围重叠&&
运算符进行聚合:
select a.flat, a.start, a.end_, count(*) as lights_on
from all_ivals a
join lights l
on tsrange(l.start, l.end_, '[)') && tsrange(a.start, a.end_, '[)')
and l.flat = a.flat
where a.end_ is not null
group by a.flat, a.start, a.end_
order by a.flat, a.start
;
结果:
flat start end_ lights_on
1 2021-01-01 00:00:15 2021-01-01 00:59:00 2
1 2021-01-01 00:59:00 2021-01-01 01:59:00 1
2 2021-01-01 00:00:00 2021-01-01 00:00:15 1
2 2021-01-01 00:00:15 2021-01-01 00:01:15 2
2 2021-01-01 00:01:15 2021-01-01 00:02:00 1
2 2021-01-01 00:02:00 2021-01-01 01:00:00 2
2 2021-01-01 01:00:00 2021-01-01 08:00:00 1
7 rows
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句