I have this table that I need to compare the values from the current hour with the previous hour for each ID.
| id | value | time |
|---|---|---|
| A | 1 | 3pm |
| B | 2 | 3pm |
| C | 3 | 3pm |
| A | 3 | 2pm |
| B | 2 | 2pm |
| C | 1 | 2pm |
| A | 3 | 1pm |
| B | 2 | 1pm |
| C | 2 | 1pm |
If the values from the current hour and the previous hour is different, the 'got_changes' table will have 'yes' value and 'no' if similar. If there's no previous hour data, it will just have 'yes' for 'got_changes'.
| id | value | time | got_changes |
|---|---|---|---|
| A | 1 | 3pm | yes |
| B | 2 | 3pm | no |
| C | 3 | 3pm | yes |
| A | 3 | 2pm | no |
| B | 2 | 2pm | no |
| C | 1 | 2pm | yes |
| A | 3 | 1pm | yes |
| B | 2 | 1pm | yes |
| C | 2 | 1pm | yes |
I couldn't think of a good logic to do this. Any help would be very much appreciated.
CodePudding user response：
Use LAG() window function in a CASE expression to check the previous value of each id:
SELECT *,
CASE WHEN value = LAG(value) OVER (PARTITION BY id ORDER BY time) THEN 'no' ELSE 'yes' END got_changes
FROM tablename
ORDER BY time DESC, id;
See the demo.