Examining nearby rows
Question:
The cats would like to see the average of the weight of them, the cat just after them and the cat just before them. The first and last cats are content to have an average weight of consisting of 2 cats not 3.
Return: name, weight, average_weight
Order by: weight
Show Table
Cats:
| name | varchar |
| breed | varchar |
| weight | float |
| color | varchar |
| age | int |
Show Desired Output
Desired output:
| name | weight | average_weight |
| Tigger | 3.80 | 4.00 |
| Molly | 4.20 | 4.17 |
| Ashes | 4.50 | 4.50 |
| Charlie | 4.80 | 4.73 |
| Smudge | 4.90 | 4.90 |
| Felix | 5.00 | 5.00 |
| Puss | 5.10 | 5.17 |
| Millie | 5.40 | 5.33 |
| Alfie | 5.50 | 5.53 |
| Misty | 5.70 | 5.77 |
| Oscar | 6.10 | 5.97 |
| Smokey | 6.10 | 6.10 |
Query Window:
Correct output but can you use 'precedingfollowing'?
Show Answer
Answer:
select name, weight,
avg(weight) over (order by weight ROWS between 1 preceding and 1 following) as average_weight
from cats order by weight