<p>I have the following data table:</p> <em>Table name entity_values</em></p> <table class="s-table"> <thead> <tr> <th>Date Time</th> <th>value</th> </tr> </thead> <tbody> <tr> <td>2017-09-02 05:05:00</td> <td>20</td> </tr> <tr> <td>2017-09-02 05:10:00</td> <td>20</td> </tr> <tr> <td>2017-09-02 05:15:00</td> <td>21</td> </tr> <tr> <td>2017-09-02 05:20:00</td> <td>21</td> </tr> <tr> <td>2017-09-02 05:25:00</td> <td>21</td> </tr> <tr> <td>2017-09-02 05:30:00</td> <td>22</td> </tr> </tbody> </table> <p>I currently only want to see changes, so I constructed the following query: </p> <pre class="brush:php;toolbar:false;">SELECT date_time AS 'time', value - LAG(value) over (order by date_time) as 'Kwh01-Energy (kWh)' FROM entity_values;</pre> <p>This will return the following results: </p> <table class="s-table"> <thead> <tr> <th>Date Time</th> <th>value</th> </tr> </thead> <tbody> <tr> <td>2017-09-02 05:00:00</td> <td>0</td> </tr> <tr> <td>2017-09-02 05:05:00</td> <td>0</td> </tr> <tr> <td>2017-09-02 05:10:00</td> <td>1</td> </tr> <tr> <td>2017-09-02 05:15:00</td> <td>0</td> </tr> <tr> <td>2017-09-02 05:20:00</td> <td>0</td> </tr> <tr> <td>2017-09-02 05:25:00</td> <td>1</td> </tr> </tbody> </table> <p>Since the window function <em> does not allow </em> to be used in the WHERE clause, such as: </p> <pre class="brush:php;toolbar:false;">SELECT date_time AS 'time', value - LAG(value) over (order by date_time) as 'Kwh01-Energy (kWh)' FROM entity_values WHERE (value - LAG(value) over (order by date_time)) > 0;</pre> <blockquote> <p>Error code: 3593. You cannot use the window function "lag" in this context.</p> </blockquote> <p><strong>Question</strong>: How to filter out 0 values ​​and only get the "changed" values? Desired result: </p> <table class="s-table"> <thead> <tr> <th>Date Time</th> <th>value</th> </tr> </thead> <tbody> <tr> <td>2017-09-02 05:10:00</td> <td>1</td> </tr> <tr> <td>2017-09-02 05:25:00</td> <td>1</td> </tr> </tbody> </table></p>

SELECT t.* from (
SELECT 
date_time,
value - LAG(value) over (order by date_time) as changed_value
FROM entity_values 
) as t 
WHERE changed_value > 0