postgresql - 如何使用不在当前范围内的 lead() 值进行查询

Question

当 lead() 值不在当前行的范围内时，我在查询时遇到问题，范围边缘的行返回 null lead() 值。

假设我有一个简单的表来跟踪连续的计数器

create table anytable
( wseller integer  NOT NULL,
  wday    date     NOT NULL,
  wshift  smallint NOT NULL,
  wconter numeric(9,1) )

具有以下值

wseller      wday wshift wcounter
      1 2016-11-30     1    100.5
      1 2017-01-03     1    102.5
      1 2017-01-25     2    103.2
      1 2017-02-05     2    106.1
      2 2015-05-05     2    81.1
      2 2017-01-01     1    92.1
      2 2017-01-01     2    93.1
      3 2016-12-01     1    45.2
      3 2017-01-05     1    50.1

并想要当年的净单位数

wseller       wday wshift units
      1 2017-01-03      1     2
      1 2017-01-25      2   0.7
      1 2017-02-05      2   2.9
      2 2017-01-01      1    11
      2 2017-01-01      2     1
      3 2017-01-05      1   4.9

如果我用

seletc wseller, wday, wshift, wcounter-lead(wcounter) over (partition by wseller order by  wseller, wday desc, wshift desc) 
from anytable
where wday>='2017-01-01' 

按分区给出第一个 wseller 的空值。我在大型 CTE 中使用此查询。

我做错了什么？

Answer 1

窗口函数的范围考虑了 WHERE 子句中的条件。将条件移动到外部查询:

select *
from (
    select 
        wseller, wday, wshift, 
        wcounter- lead(wcounter) over (partition by wseller order by wday desc, wshift desc)
    from anytable
    ) s
where wday >= '2017-01-01'
order by wseller, wday, wshift

 wseller |    wday    | wshift | ?column? 
---------+------------+--------+----------
       1 | 2017-01-03 |      1 |      2.0
       1 | 2017-01-25 |      2 |      0.7
       1 | 2017-02-05 |      2 |      2.9
       2 | 2017-01-01 |      1 |     11.0
       2 | 2017-01-01 |      2 |      1.0
       3 | 2017-01-05 |      1 |      4.9
(6 rows)