Just a short note. Average is in this case
calculated as: (1+2+3+4)/4=2.5 Just like sum of the
values divided the number of rows in the query.
Above that one can group the rows in windowing.
function That would look like this .
Let's examine following query. It uses CASE
expression to divide the rows into groups with rows
between 0 and 2 and the rest. Then it uses windowing
functions to operate on subgroup of the rows in the
user_key ascending order.
select
user_key,
grp,
max(user_key) over (
partition by grp
order by user_key asc) wxuk,
min(user_key) over (
partition by grp
order by user_key asc) wmuk,
avg(user_key) over (
partition by grp
order by user_key asc) wauk,
sum(user_key) over (
partition by grp
order by user_key asc) wsuk
from (
select
user_key,
case
when user_key between 0 and 2
then 1
else 0
end grp
from users
) order by user_key asc;