Limiting results
These questions test your knowledge of the Over Command.
Read on to Learn about Over
Basic Over
Over is useful in limiting or tweaking the data returned from aggregate functions
Think of over as like a running total. For instance in our runners table, we have a query that selects name and average weight of all the runners seen so far when ordered by name. Here average_weight is recomputed on each 'step' of the SQL output
select name, weight, avg(weight) over (order by name) from runners order by name limit 3
name | weight | avg_weight |
---|---|---|
andy | 50 | 50 |
bob | 100 | 75 |
cedric | 50 | 63 |
Partition by
Partition by allows us to further subdivide the preceding Over Command
Here avg_weight is recomputed on every 'step' of the SQL like above BUT it is reset when the partition by field changes.
In this case we partition by country (think of it as grouping by country). There is a different aggregate function for each country
select name, weight, country, avg(weight) over (partition by country order by name) from runners order by name
name | weight | country | avg_weight |
---|---|---|---|
andy | 50 | UK | 50 |
bob | 100 | UK | 75 |
cedric | 50 | France | 50 |
dave | 70 | Germany | 70 |
eric | 70 | France | 60 |
Preceding and Following
Preceding and Following allow us to perform aggregate functions on the rows just before and after the current row.
Here we will list the weight and the minimum weight of each runner and their neighbours just before and after them.
min(andy) = min(50, 100) min(bob) = min(50, 100, 50) min(cedric) = min(100, 50, 70) min(dave) = min(50, 70, 70) min(eric) = min(70, 70)
select name, weight, min(weight) over (order by name ROWS between 1 preceding and 1 following) from runners order by name
name | weight | min |
---|---|---|
andy | 50 | 50 |
bob | 100 | 50 |
cedric | 50 | 50 |
dave | 70 | 50 |
eric | 70 | 70 |