How to achieve multi level aggregation in SAP HANA ?

Multi-level aggregation can be achieved using grouping sets. The advantage of this
approach is that multiple levels of grouping can be computed in a single SQL statement.

SELECT publisher, name, year, SUM(price)
FROM :it_publishers, :it_books
WHERE publisher=pub_id AND crcy=:currency
GROUP BY GROUPING SETS ((publisher, name, year), (year))

To retrieve the different levels of aggregation the client typically has to examine the result 
repeatedly, e.g. by filtering by NULL on the grouping attributes.

In the special case of multi-level aggregations, SQLScript can exploit results at a finer grouping for 
computing coarser aggregations and return the different granularities of groups in distinct table 
variables. This could save the client the effort of reexamining the query result. Consider the above 
multi-level aggregation expressed in SQLScript.

books_ppy = SELECT publisher, name, year, SUM(price)
            FROM :it_publishers, :it_books
WHERE publisher = pub_id AND crcy = :currency
GROUP BY publisher, name, year;
books_py = SELECT year, SUM(price)
           FROM :books_ppy
GROUP BY year;

Tags: SAP HANA multi level aggregation, multi level aggregation, query plan, calculation engine plan, sqlscript, hana database query, SQLScript compiler,
Share this article :

Post a Comment

Copyright © 2011. SAP HANA TUTORIALS FREE - S/4 HANA - All Rights Reserved