如何用T-SQL找出ABC市场中占总销售额80%的品牌？

Let's break down how to solve this problem using T-SQL, including calculating cumulative sales percentages and identifying the brands that make up 80% of total sales (aligning with the Pareto principle).

Step 1: Clarify Core Goals #

We need to:

• Calculate cumulative sales percentages for each brand in the ABC market (sorted by highest sales first, since we're targeting top contributors)
• Isolate the set of brands that together account for 80% of the market's total sales

Step 2: Complete T-SQL Query #

Assuming your table is named BrandSales with columns matching your input data, here's a query that produces formatted results and filters the top 80% contributors:

-- CTE to compute base metrics: total market sales, cumulative sales, and raw percentage
WITH BrandSalesMetrics AS (
    SELECT 
        [Brand Name],
        Sales,
        Cost,
        Mkt,
        -- Total sales across all ABC market brands
        SUM(Sales) OVER(PARTITION BY Mkt) AS TotalMarketSales,
        -- Running total of sales, starting from the highest-selling brand
        SUM(Sales) OVER(
            PARTITION BY Mkt 
            ORDER BY Sales DESC 
            ROWS UNBOUNDED PRECEDING
        ) AS CumulativeSales,
        -- Rounded cumulative percentage of total sales
        ROUND(
            (SUM(Sales) OVER(
                PARTITION BY Mkt 
                ORDER BY Sales DESC 
                ROWS UNBOUNDED PRECEDING
            ) * 100.0 / SUM(Sales) OVER(PARTITION BY Mkt)), 
            0
        ) AS CumulativePercent
    FROM BrandSales
    WHERE Mkt = 'ABC' -- Filter to only the ABC market
),
-- CTE to flag brands that are part of the top 80% (including the first brand that crosses the threshold)
Top80Flagged AS (
    SELECT *,
        CASE 
            WHEN CumulativePercent <= 80 THEN 1
            -- Include the first brand that pushes cumulative sales over 80% (it completes the threshold)
            WHEN LAG(CumulativePercent) OVER(ORDER BY Sales DESC) < 80 THEN 1
            ELSE 0 
        END AS IsInTop80
    FROM BrandSalesMetrics
)
-- Final output with formatted percentage and filtered top 80% brands
SELECT 
    [Brand Name],
    Sales,
    Cost,
    Mkt,
    CONCAT(CAST(CumulativePercent AS INT), '%') AS Percentage
FROM Top80Flagged
WHERE IsInTop80 = 1
ORDER BY Sales DESC;

Step 3: Query Explanation #

1. BrandSalesMetrics CTE:

   • TotalMarketSales: Uses a window function to calculate the total sales for the entire ABC market, so every row has access to this value.
   • CumulativeSales: Computes a running total starting from the highest-selling brand, ensuring we prioritize top contributors first.
   • CumulativePercent: Converts the running total to a rounded percentage of the total market sales.

2. Top80Flagged CTE:

   • Uses the LAG function to check if the previous brand's cumulative percentage was below 80%. If so, the current brand (which pushes the total over 80%) is still included, as it's necessary to reach the 80% threshold.

3. Final SELECT:

   • Formats the percentage to match your desired output (e.g., "25%") and filters only brands that are part of the top 80% contributor group.

Example Output (For Your Sample Data) #

The total sales in the ABC market is 33330. The top 80% contributors would be:

These four brands together account for ~79.6% of total sales, which rounds to 80% as shown.

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