SQL Server cube operator using the detailed

Source: Internet
Author: User
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.



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.