windowing.sql

select distinct on (id) id, value2 from t_pk order by id, value2 asc nulls first;

-- filter
explain analyze select region, sum(case when year = 2009 then production else 0 end) as p2009, sum(case when year = 2010 then production else 0 end) as p2010 from t_oil group by region;

explain analyze select region, sum(production) filter (where  year = 2009) as p2009, sum(production) filter (where  year = 2010) as p2010 from t_oil group by region;

-- array_agg
select string_agg(a::text, ':')::text from (select a from t_test limit 100) s;
select array_agg(a order by a desc)::text from (select a from t_test limit 10) s;
select array_agg(distinct a)::text from (select a from t_test limit 10) s;

-- ordered set aggregates
select g, mode() within group (order by v) from (select 1 as g, 1 as v union all select 1, 1 union all select 1, 0) s group by g;
select region, percentile_disc(0.5) within group (order by production) from t_oil group by region;

-- hypothetical aggregates
select region, percentile_disc(0.5) within group (order by production) from t_oil group by region;

-- range mode vs rows mode
select region, year, avg::int, sum(avg) over(order by year rows unbounded preceding)::int as rows_mode, sum(avg) over(order by year range unbounded preceding)::int as range_mode  from (select region, year, avg(production) from t_oil where year > 2007 group by region, year order by year) s order by year;
select id, value, sum(id) over(order by value rows between unbounded preceding and current row) from t_dup order by id;
select id, value, sum(id) over(order by value rows between unbounded preceding and current row) from t_dup order by id;

-- simple windowing. easily possible with normal sql also
select year, production / (max(production) over())::float*100 from t_oil where country = 'USA' order by year;
select year, production, production / (select max(production) from t_oil where country = 'USA')::float from t_oil where country = 'USA' order by year;

select
  distinct country,
  abs(last_value(production) over(partition by country order by year rows between unbounded preceding and unbounded following) - 
     first_value(production) over(partition by country order by year)) delta 
from
  t_oil
where 
  production is not null 
order by 1;

select year, production, avg(production) over(order by year rows between 2 preceding and current row)::int as moving_Avg from t_oil where country = 'USA' order by year;

select year, production, sum(production) over(order by year rows between unbounded preceding and current row)::int as moving_Avg from t_oil where country = 'USA' order by year;

select country, ntile(4) over(order by country) from (select distinct country from t_oil) t order by 1;