<p>我有以下数据表：</p> <em>表名 entity_values</em></p> <table class="s-table"> <thead> <tr> <th>日期时间</th> <th>值</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>我目前只想看到变化，所以我构建了以下查询：</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>这将返回以下结果：</p> <table class="s-table"> <thead> <tr> <th>日期时间</th> <th>值</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>由于窗口函数<em>不允许</em>在WHERE子句中使用，如：</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)) &gt; 0;</pre> <blockquote> <p>错误代码：3593。您不能在此上下文中使用窗口函数“lag”。</p> </blockquote> <p><strong>问题</strong>：如何过滤掉0值，只获取“变化”的值？ 期望的结果：</p> <table class="s-table"> <thead> <tr> <th>日期时间</th> <th>值</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