1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
/* postgres can not */
/* syntax version 1 */
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
region,
name,
sum(age) over w1 as sum,
min(age) over w1 as min,
max(age) over w1 as max,
count(age) over w1 as count,
count(*) over w1 as count_all,
count_if(age>20) over w1 as count_if,
some(age) over w1 as some,
bit_and(age) over w1 as bit_and,
bit_or(age) over w1 as bit_or,
bit_xor(age) over w1 as bit_xor,
bool_and(age>20) over w1 as bool_and,
bool_or(age>20) over w1 as bool_or,
avg(age) over w1 as avg,
aggr_list(age) over w1 as `list`,
min_by(age, name) over w1 as min_by,
max_by(age, name) over w1 as max_by,
nanvl(variance(age) over w1, -999.0) as variance,
nanvl(stddev(age) over w1, -999.0) as stddev,
nanvl(populationvariance(age) over w1, -999.0) as popvar,
nanvl(stddevpopulation(age) over w1, -999.0) as popstddev,
histogram(age) over w1 as hist,
median(age) over w1 as median,
percentile(age, 0.9) over w1 as perc90,
aggregate_by(age, aggregation_factory("count")) over w1 as aggby
from $data
window w1 as (partition by region order by name desc)
order by region, name desc
;
|
