1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
/* postgres can not */
use plato;
$data = (select cast(key as uint32) as age, cast(subkey as uint32) as region, value as name from Input);
--insert into Output
select
prefix,
region,
region + 2 as region_2,
avg(age) as avg_age,
sum(age) as sum_age,
sum(avg(age)) over w1 as sum_by_avg_age,
lag(region) over w1 as prev_region,
some(prefix) over w1 as prefix_list,
lag(prefix) over w1 as prev_prefix_list,
lag(some(name)) over w1 as prev_region_list,
'test'
from $data
group by region, SUBSTRING(name,0,1) as prefix
window w1 as (partition by region order by age desc) -- use unaggregaded age in order by in win specification
order by region, avg_age desc
;
|
