SQL Server solves the number of recent sales records that represent a specified percentage of total sales

Looking at the blog of the Big V in the garden, I found that the text was a description of the problem as described in the title.

The specific question describes my explanation through the blog of the Hermit: Note: If the above interception is infringing, please also inform the author, thank you again. When I see this problem, I think of the solution that is related to the cumulative rollup provided by the window function. To prepare the test data, the T-SQL code is as follows:

1 IF object_id(N'dbo. SalesData'N'U') is  not NULL2 BEGIN3     DROP TABLEdbo. SalesData;4 END5 GO6  7 CREATE TABLEdbo. SalesData8 (9Product_NameCHAR( +) not NULL,--Product NameTenSale_amountFLOAT  not NULL 　　  --Sales Amount One ); A GO -   - INSERT  intodbo. SalesData (Product_Name, Sale_amount) the SELECT 'Productnamea',13000 UNION  All - SELECT 'Productnamea',12000 UNION  All - SELECT 'Productnamea',9000 UNION  All - SELECT 'Productnameb',167000 UNION  All + SELECT 'Productnameb',137000 UNION  All - SELECT 'Productnameb',107000 UNION  All + SELECT 'Productnamec',78000 UNION  All A SELECT 'Productnamec',12000; at GO

Execute the following T-SQL code:

1 SELECT Product_Name, Sale_amount 2  from dbo. SalesData; 3 GO

The results are as follows:

Note: The above test data is also from the Hermit's blog post, I have made adjustments on the basis. The T-SQL code for a solution based on SQL Server 2005 and later is as follows:

1 --Solutions for SQL Server 2005 and later using subqueries2 SELECT *3  from (4     SELECTT.product_name, T.sale_amount, T.rownum, T.sale_totalamount, T.sale_accumulateamount5,100.0 *T.sale_accumulateamount/T.sale_totalamount assale_accumulatepercent6      from (7         SELECTT.product_name, T.sale_amount, Row_number () Over(ORDER  byT.sale_amountDESC) asrownum, T2. Sale_totalamount8,(SELECT SUM(Sale_amount) fromDbo. SalesDataWHERESale_amount>=T.sale_amount) asSale_accumulateamount9          fromDbo. SalesData asTTen          CrossAPPLY (SELECT SUM(Sale_amount) asSale_totalamount fromDbo. SalesData) asT2 One) asT A) asT - WHERET.sale_accumulatepercent<=  the; - GO the  

The following query results are executed:

The T-SQL code for a solution based on SQL Server 2012 and later is as follows:

1 --Solutions for SQL Server 2012 and above using enhanced window functions2 SELECTT.product_name, T.sale_amount, T.rownum, T.sale_totalamount, T.sale_accumulateamount, T.Sale_AccumulatePercent 3  from (4     SELECTT.product_name, T.sale_amount, T.rownum, T.sale_totalamount, T.sale_accumulateamount5,100.0 *T.sale_accumulateamount/T.sale_totalamount asSale_accumulatepercent/*percentage of cumulative sales in total sales*/6      from (7         SELECTProduct_Name, Sale_amount, Row_number () Over(ORDER  bySale_amountDESC) asRowNum/*row number row number, with uncertainty, window sort sentence column is not unique*/8,SUM(Sale_amount) Over() asSale_totalamount/*Total Sales*/9,SUM(Sale_amount) Over(ORDER  bySale_amountDESCROWS unbounded preceding) asSale_accumulateamount/*cumulative sales, using the new window frame words of SQL Server 2012 window functions, also equivalent to ROWS between unbounded preceding and current ROW*/Ten          fromdbo. SalesData One) asT A) asT - WHERET.sale_accumulatepercent<=  -; - GO

The following query results are executed:

Bo friends have other solutions, please kindly enlighten me, thank you very much.

SQL Server solves the number of recent sales records that represent a specified percentage of total sales