server| detailed
Cube operator
To use the cube, you first need to understand the group by
In fact, the difference between cube and rollup is not very large, but there is a difference between the possible combinations of creating and summarizing groupings based on the GROUP BY clause,
The cube will return more possible combinations. If there are n columns in the GROUP BY clause or if there are n expressions,
SQL Server returns a possible combination of 2 n-1 power on the result set.
Attention:
When using the cube operator, you can have a maximum of 10 group expressions
The ALL keyword cannot be used in the cube
Example:
In database statistics, we often query the following:
such as an order database, we need to know the quantity of each order, the quantity of each order, the quantity of a product of all orders, the aggregate information of all the product orders. It is convenient to use the cube at this time. Of course, do not need so much information or just want to know a specific product, a specific order, a certain time relationship (before, after, between) and so on specific information, only in the Where can be limited
Let me give you an example, in all cases:
A product order is documented in a database table:
Now a total of three products (1,2,3), has been under two orders (1,2)
SQL statement:
Select Productid,orderid SUM (quantity) as total from order GROUP by Productid,orderid with CUBE
ORDER BY Productid,orderid
Get results after running:
ProductID OrderID Total
NULL 95 all orders all products total
Null 1 30 Order 1 All product quantity
NULL 2 65 order 2 All product quantity
1 NULL 15 All Order Products 1 Total
1 1 5 Order 1 Product 1 Quantity
1 2 10 order 2 Product 1 Quantity
2 NULL 35 all Order Products 2 Total
2 1 10 Order 1 Product 2 Quantity
2 2 25 order 2 product 2 Quantity
3 NULL 45 All Order Products 3 Total
3 1 15 Order 1 Product 3 Quantity
3 2 30 order 2 Product 3 Quantity
If you have a certain understanding of SQL Server Group by aggregation, you can understand the use and function of the cube operator. In fact, in the practical use of the cube is still very good, we often have some data on the database to help us better grasp the situation
I think E-commerce can be better to achieve a reasonable allocation of resources, to minimize inventory, only a better grasp of the production, sales data of the specific circumstances, in order to achieve a reasonable allocation of resources. Hope that after the enterprise like Dell, do not like Changhong and other color TV manufacturers.