I have the following table of data: Table name entity_values
| date_time | value |
|---|---|
| 2017-09-02 05:05:00 | 20 |
| 2017-09-02 05:10:00 | 20 |
| 2017-09-02 05:15:00 | 21 |
| 2017-09-02 05:20:00 | 21 |
| 2017-09-02 05:25:00 | 21 |
| 2017-09-02 05:30:00 | 22 |
I currently only want to see the changes so I build the following query:
SELECT date_time AS 'time',
value - LAG(value) over (order by date_time) as 'Kwh01-Energy (kWh)'
FROM entity_values;
This returns the following:
| date_time | value |
|---|---|
| 2017-09-02 05:00:00 | 0 |
| 2017-09-02 05:05:00 | 0 |
| 2017-09-02 05:10:00 | 1 |
| 2017-09-02 05:15:00 | 0 |
| 2017-09-02 05:20:00 | 0 |
| 2017-09-02 05:25:00 | 1 |
Since Window Functions aren't allowed in the WHERE clause like:
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;
Error Code: 3593. You cannot use the window function 'lag' in this context.'
Question: How do I filter out the 0 values and only get the "changed" values? Desired result:
| date_time | value |
|---|---|
| 2017-09-02 05:10:00 | 1 |
| 2017-09-02 05:25:00 | 1 |