Shulou(Shulou.com)06/01 Report--

This article shows you how to do "other" grouping of big data reports. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Grouping is a very common operation in SQL, which divides a set into several subsets according to some rules, often accompanied by further aggregate calculation of the subsets.

In the report, we usually call this grouping "equivalent grouping". It is also easy to achieve this kind of equivalent grouping, just do group () according to the specified field, and then do the aggregation operation for the grouping. Figure 1 is as follows:

With regard to each expression for which you can see the corresponding settings in the equivalent grouping diagram, there is no explanation here, but let's focus on the following

Other groups

For example:

Sometimes, in addition to the above equivalent grouping, we also want to achieve partial grouping (also known as incomplete grouping, that is, not all the records are used for grouping, only the grouping items that users care about are retained, and the rest of the groups are grouped into one group).

In most cases, SQL can also set up temporary tables for such calculations through subqueries or stored procedures, but it will be more troublesome.

Let's take a look at how to achieve incomplete grouping:

It is required to keep the Miaosheng of the original grouping report (figure 1 above) as a full supplier group, and all other groups are merged into one group called "other".

The design interface is modified as follows:

Specific treatment:

On the basis of the original grouping report (figure 1 above), two new lines are added for other groups to present statistics

A5 cell expression adds filtering and sorting, the expression is: = ds1.group (supplier ID, supplier ID==3 or supplier ID==4; supplier ID:1)

Add lines 7-8 to merge A7-A8 cells with the expression: other

The B7 expression is: = ds1.group (category ID, supplier IDs 3 and supplier IDs 4; category ID:1), leaving only the supplier as Miaosheng and complete

The G8 expression is: = sum (E7 calculate G7 Magi B7 {}), subtotal for other groups

Other cell expressions and settings in the new line can directly copy the settings of C5-G5 cells.

Web preview effect:

It can be seen that the dry report is very clever in dealing with this problem. Only by using its built-in functions select and group to set the filter condition parameters, we can filter out the irrelevant records when selecting or grouping, and only group some records, thus achieving the effect of incomplete grouping statistics.

After that, the same data set can be repeatedly grouped or selected and summarized, so that very complex grouping reports can be realized.

The above content is how to group big data report into "other" groups. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.