Lag() function with WHERE clause

I have this sql function that works properly:

SELECT out_hum ,
 (out_hum - LAG(out_hum, 1) OVER (ORDER BY id)) As dif
FROM excel_table

But I want to select all the out_hum when the diferrence (dif) is equal to 0 or grater than a value. When I type this code I get an error…

SELECT out_hum ,
 (out_hum - LAG(out_hum, 1) OVER (ORDER BY id)) As dif
FROM excel_table  WHERE dif=0

How I can solve this?

Answer

The where clause cannot access aliases to expressions defined in the select clause (because, basically, the former is processed before the latter). On top of that, there is a special restriction to window functions, which can not appear in the where clause of a query (they are allowed only in the select an order by clauses).

A typical solution is to use a derived table, such as a subquery:

select *
from (
    select out_hum, out_hum - lag(out_hum) over (order by id) as dif
    from excel_table
) t
where dif = 0

Notes:

• parenthesis around the substraction are not necessary

• 1 is the default value of the second argument of lag(), so there is no need to specify it