如何用T-SQL基于value2总和实现非等分四分位划分？

Hey there! Let's walk through solving this problem where we split sorted data into quartiles with roughly equal sums of value2.

Problem Breakdown #

We have a table with ID, value1, and value2 columns. The requirements are:

1. Sort the data by value1 in ascending order.
2. Split the sorted rows into 4 quartiles such that the total sum of value2 in each quartile is as close to equal as possible (target sum per quartile = total sum of value2 ÷ 4).

Sample Input Data #

Desired Approximate Quartile Result #

Your Existing T-SQL Code #

Your current implementation gets the job done! Here's how it looks formatted properly:

SELECT value1 
,value2 
,SUM(value2) OVER (ORDER BY value1 ) CumSum 
,CASE 
WHEN SUM(value2) OVER (ORDER BY value1 ) < (Select sum(value2) from table1)/4 Then 1 
WHEN SUM(value2) OVER (ORDER BY value1 ) < 2 * (Select sum(value2) from table1)/4 Then 2 
WHEN SUM(value2) OVER (ORDER BY value1 ) < 3 * (Select sum(value2) from table1)/4 Then 3 
Else 4 
End as Quartile 
FROM Table1

Optimized T-SQL Version #

The main improvement here is calculating the total sum of value2 only once (instead of repeating subqueries), which makes the code cleaner and more efficient:

WITH TotalValue2Sum AS (
    SELECT SUM(value2) AS total_sum
    FROM Table1
)
SELECT 
    ID,
    value1,
    value2,
    SUM(value2) OVER (ORDER BY value1, ID) AS CumSum, -- Add ID to break ties consistently
    CASE 
        WHEN SUM(value2) OVER (ORDER BY value1, ID) < total_sum / 4 THEN 1
        WHEN SUM(value2) OVER (ORDER BY value1, ID) < total_sum / 2 THEN 2
        WHEN SUM(value2) OVER (ORDER BY value1, ID) < 3 * total_sum / 4 THEN 3
        ELSE 4
    END AS Quartile
FROM Table1, TotalValue2Sum
ORDER BY value1, ID;

Key Notes #

• Adding ID to the ORDER BY clause ensures consistent sorting when multiple rows have the same value1, leading to predictable quartile assignments.
• The CTE (TotalValue2Sum) computes the total sum once, avoiding redundant subquery executions.
• This method uses cumulative sums to assign quartiles, which aligns perfectly with your goal of creating groups with approximately equal value2 totals.

内容的提问来源于stack exchange，提问作者elsolo21