Wählen Sie MySQL nur aus, wenn sich die Gleitkommazahl ändert
P粉158473780
P粉158473780 2023-09-04 00:01:04
0
1
642
<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>
P粉158473780
P粉158473780

Antworte allen(1)
P粉373990857

感谢 @Andrew

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
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage