基于客户订单数及单订单商品数的客户排名SQL实现问询

Got it, let's refine your query to implement the multi-metric ranking you need. The core goal here is to rank customers first by their average number of items per order (your key metric derived from the two counts), then use the raw order count and item count as tiebreakers if needed.

Step 1: Finalize the CTE & Calculate the Key Metric #

Your existing CTE already captures the two foundational counts. We'll add the average items per order calculation (making sure to avoid integer division) and then apply a ranking function that uses multiple metrics.

Here's the complete query:

WITH CTE AS (
    SELECT 
        o.CustomerId,
        COUNT(DISTINCT o.OrderId) AS OrderCount,
        COUNT(oi.OrderItemId) AS OrderItemCount
    FROM OrderItem oi
    INNER JOIN [Order] o ON o.OrderId = oi.OrderId
    WHERE o.CategoryId = 52 -- Filter for website sales
    GROUP BY o.CustomerId
)
SELECT 
    cust.Code,
    cust.DisplayTitle,
    CTE.OrderCount,
    CTE.OrderItemCount,
    -- Calculate average items per order (avoid integer division with *1.0)
    ROUND(CTE.OrderItemCount * 1.0 / CTE.OrderCount, 2) AS AvgItemsPerOrder,
    -- Multi-metric ranking: first by average items per order (descending), then order count (descending)
    RANK() OVER (
        ORDER BY 
            CTE.OrderItemCount * 1.0 / CTE.OrderCount DESC,
            CTE.OrderCount DESC
    ) AS CustomerRank
FROM CTE
INNER JOIN Customer cust ON CTE.CustomerId = cust.CustomerId
-- Optional: Add ORDER BY CustomerRank to get results in ranked order
ORDER BY CustomerRank;

Key Details Explained: #

• Avoiding Integer Division: Multiplying OrderItemCount by 1.0 converts the value to a decimal, ensuring we get a precise average (e.g., 5 items across 2 orders becomes 2.5 instead of 2).
• Ranking Logic: The RANK() function uses two metrics to break ties:
  1. Primary metric: Average items per order (sorted descending to prioritize customers who buy more items per order)
  2. Secondary metric: Total order count (sorted descending to rank customers with the same average higher if they have more orders)
  • If you prefer no gaps in ranking (e.g., two customers tied for 1st place both get rank 1, next gets 2 instead of 3), replace RANK() with DENSE_RANK().
• Optional Adjustments: You could swap the secondary metric to OrderItemCount DESC if you want to prioritize total items sold over number of orders for ties.

Example Output: #

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