GBase 8a mpp cluster 当前支持的一些olap开窗函数

-- 创建数据表
use test;
drop table if exists t3;
CREATE TABLE t3(i int, j int);
INSERT INTO t3 VALUES(2,1),(2,3),(2,3),(2,5),(3,2),(3,2),(3,2),(3,4),(3,1),(3,5);
-- 窗口函数查询
SELECT *,
RANK() OVER(PARTITION BY i order by j desc) AS rank,
DENSE_RANK()OVER(PARTITION BY i order by j desc) AS dense_rank ,
ROW_NUMBER() OVER(PARTITION BY i order by j desc) AS row_number,
SUM(i) OVER(PARTITION BY i ORDER BY j DESC),
AVG(j) OVER(PARTITION BY i ORDER BY j DESC),
COUNT(j) OVER(PARTITION BY i ORDER BY j DESC),
LEAD(i,1,4) OVER(order by j) as lead,
Lag(i,1,0) OVER(order by j) as lag,
PERCENT_RANK() OVER(PARTITION BY i order by j desc) AS PERCENT_RANK,
ROUND((cume_dist() over(partition by j order by i)),2) as cume_dist,
ntile(2) over(partition by j order by i) as ntile,
first_value(i) over(partition by j order by  i) as first_value,
last_value(i) over(partition by j order by  i) as last_value,
nth_value(i,2) over(partition by j order by i) as nth_value,
min(i) over (partition by j) as min_over ,
max(i)  over (partition by j) as max_over 
FROM t3;

select *,ROUND((cume_dist() over(partition by j order by i)),2) AS cume_dist from t3;

-- 
CREATE TABLE t4 (
 "id" int(11) ,
 "name" varchar(20) ,
 "sex" varchar(1) ,
 "did" varchar(1) );
insert into t4 values (1,'tom',1,2),(2,'bob',1,1),(3,'james',1,3),(4,'cate',2,1),(5,'jack',1,1),(6,'candy',2,1),(7,'kay',2,4),(8,'jerry',2,1),(9,'jacksion',1,1);
select sex as 性别 , count(*) as 总数 from t4 group by rollup(sex);
select sex as 性别 , did as 部门 , count(*) as 总数 from t4 group by grouping sets(did,sex);
select sex as 性别 , did as 部门 , count(*) as 总数 from t4 group by cube (did,sex);
select sex as 性别 , did as 部门 , grouping(sex),grouping(did),count(*) from t4 group by grouping sets(did,sex);