<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)) > 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