加快对重叠日期范围的postgres读取窗口查询

Question

我有一个表格(简化(，其中包含如下的读数

meter_id     read_date        value
1            2017-01-01         10
1            2017-01-15         15
1            2017-02-05         20
1            2017-04-15         22
2            2016-12-14         120
2            2016-03-02         200

这张表包含数以百万计的读数。

我有一个类似的视图(或查询(

select  meter_id, read_date as start_read_date, value as start_value, 
CASE
WHEN lead(read_date) OVER read_wdw IS NULL THEN date_trunc('month'::text, read_date + '1 day'::interval) + '1 mon'::interval - '1 day'::interval) + '1 mon'::interval - '1 day'::interval
ELSE lead(.read_date) OVER read_wdw::date
END::date AS read_end_date,
lead(value) OVER read_wdw AS end_value,
from reads_table 

WINDOW read_wdw AS (PARTITION BY meter_id ORDER BY read_date);

我需要能够查询某个月内的日期。因此，起始日期、结束日期介于例如"2017-01-01"one_answers"2017-01-31"之间

因此，例如

select * from my_view where daterange(start_read_date,end_read_date, '[]') && daterange('2017-01-01', '2017-01-31', '[]) 

Which with the above table would return 
meter_id  start_read_date   start_value  end_read_date  end_value
1            2017-01-01         10        2017-01-15      15
1            2017-01-15         15        2017-02-05      20 
2            2016-12-14         120       2016-03-02      200

有没有一种方法可以在这个表上进行类似的查询，而不必首先构建整个视图来获得我想要的结果？

类似的东西(不起作用(

select  meter_id, read_date as start_read_date, value as start_value, 
CASE
WHEN lead(read_date) OVER read_wdw IS NULL THEN date_trunc('month'::text, read_date + '1 day'::interval) + '1 mon'::interval - '1 day'::interval) + '1 mon'::interval - '1 day'::interval
ELSE lead(.read_date) OVER read_wdw::date
END::date AS read_end_date,
lead(value) OVER read_wdw AS end_value,
from reads_table 
where read_date between '2017-01-01' and '2017-01-31'
or lead(read_date) over read_window between '2017-01-01' and '2017-01-31'
WINDOW read_wdw AS (PARTITION BY meter_id ORDER BY read_date);

Answer 1

实际上，将其包装在另一个select中似乎可以解决。。。

select * from (
select  meter_id, read_date as start_read_date, value as start_value, 
CASE
WHEN lead(read_date) OVER read_wdw IS NULL THEN date_trunc('month'::text, read_date + '1 day'::interval) + '1 mon'::interval - '1 day'::interval) + '1 mon'::interval - '1 day'::interval
ELSE lead(.read_date) OVER read_wdw::date
END::date AS read_end_date,
lead(value) OVER read_wdw AS end_value,
from reads_table 

WINDOW read_wdw AS (PARTITION BY meter_id ORDER BY read_date)
)sub 
where read_start_date between ...
or read_end_date between ...