SAP HANA grouping sets 、rollup、cube、limit 实践

create column table .t1 ( id int primary key, customer varchar(5), year int, product
varchar(5), sales int );
insert into t1 values(1, 'C1', 2009, 'P1', 100);
insert into t1 values(2, 'C1', 2009, 'P2', 200);
insert into t1 values(3, 'C1', 2010, 'P1', 50);
insert into t1 values(4, 'C1', 2010, 'P2', 150);
insert into t1 values(5, 'C2', 2009, 'P1', 200);
insert into t1 values(6, 'C2', 2009, 'P2', 300);
insert into t1 values(7, 'C2', 2010, 'P1', 100);
insert into t1 values(8, 'C2', 2010, 'P2', 150);

select customer, year, product, sum(sales) from ""."T1" 
group by GROUPING SETS ((customer, year),(customer, product));

ROLLUP
select customer, year, sum(sales) from t1 group by ROLLUP(customer, year)

select customer, year, sum(sales) from t1 group by CUBE(customer, year);

select customer, year, sum(sales) from t1 group by CUBE best 1 (customer, year);

select customer, year, sum(sales) from t1 group by CUBE best 2 (customer, year);

select customer, year, sum(sales) from t1 group by CUBE best 3 (customer, year);

select customer, year, product, sum(sales) from "ZHAIMING"."T1"
group by GROUPING SETS LIMIT 2 ((customer, year),(customer, product))

 

select customer, year, product, sum(sales)
from t1 group by grouping sets LIMIT 2 WITH SUBTOTAL
( (customer, year), (product) );

select customer, year, sum(sales) from t1 group by CUBE limit 1 with balance (customer, year)

with total
select customer, year, product, sum(sales)
from t1 group by grouping sets LIMIT 2 WITH TOTAL
( (customer, year), (product) )

select customer, year, product, sum(sales), text_filter(customer),
text_filter(product) from t1 group by grouping sets TEXT_FILTER ‘*2’
( (customer, year), (product) )

fill up
select customer, year, product, sum(sales),
text_filter(customer), text_filter(product)
from t1 group by grouping sets TEXT_FILTER ‘*2’ FILL UP
( (customer, year), (product) )
与text_filter对照看，这里返回的结果集里没有过滤掉不符合条件的。

 这是hana版本不行吗，

select customer, year, product, sum(sales) from "T1" 
group by GROUPING SETS LIMIT 2 ((customer, year),(customer, product));

select customer, year, sum(sales) from T1  group by ROLLUP(customer, year);

select customer, year, sum(sales) from t1 group by CUBE  (customer, year);


select customer, year, sum(sales) from t1 group by CUBE best 1 (customer, year);

select customer, year, sum(sales) from t1 group by CUBE best 2 (customer, year);

select customer, year, sum(sales) from t1 group by CUBE best 3 (customer, year);


select customer, year, product, sum(sales) from t1 group by grouping sets LIMIT 2
( (customer, year), (product) );
 
 
select customer, year, product, sum(sales)
from t1 group by grouping sets LIMIT 2 WITH SUBTOTAL
( (customer, year), (product) );

select customer, year, sum(sales) from t1 group by CUBE  with balance (customer, year)


select customer, year, product, sum(sales)
from t1 group by grouping sets  WITH TOTAL
( (customer, year), (product) )

select customer, year, product, sum(sales), text_filter(customer),
text_filter(product) from t1 group by grouping sets TEXT_FILTER '*2'
( (customer, year), (product) )


select customer, year, product, sum(sales),
text_filter(customer), text_filter(product)
from t1 group by grouping sets TEXT_FILTER '*2' FILL UP
( (customer, year), (product) )


select customer, year, product, sum(sales),text_filter(customer), text_filter(product)
from t1 group by grouping sets TEXT_FILTER '*2' FILL UP SORT MATCHS TO TOP ( (customer, year), (product) )

参考:https://my.oschina.net/corleone/blog/131576