SQL Server按复杂分组查询

Question

在SQL Server中，假设我们有一个SALES_HISTORY表，如下所示:

CustomerNo  PurchaseDate    ProductId
    1         20120411         12
    1         20120330         13
    2         20120312         14
    3         20120222         16
    3         20120109         16

…并且每个客户的每次购买都有许多记录…

如何编写合适的查询来查找:

对于每个客户，

• 找到他在MOST买的产品，
• 查找该产品占他购买的所有产品的百分比。

结果表必须有如下列:

CustomerNo, 
MostPurchasedProductId, 
MostPurchasedProductPercentage

Answer 1

假设使用SQL Server 2005+，您可以执行以下操作:

;WITH CTE AS
(
    SELECT *, 
           COUNT(*) OVER(PARTITION BY CustomerNo, ProductId) TotalProduct,
           COUNT(*) OVER(PARTITION BY CustomerNo) Total
    FROM YourTable
), CTE2 AS
(
    SELECT *,
           RN = ROW_NUMBER() OVER(PARTITION BY CustomerNo 
                                  ORDER BY TotalProduct DESC)
    FROM CTE
)
SELECT CustomerNo, 
       ProductId MostPurchasedProductId, 
       CAST(TotalProduct AS NUMERIC(16,2))/Total*100 MostPurchasedProductPercent
FROM CTE2
WHERE RN = 1

你仍然需要处理当你有一个以上的产品作为购买最多的一个。下面是一个sqlfiddle和一个演示供您尝试。

Answer 2

可以做得更漂亮，但它可以工作:

with cte   as(
select CustomerNo, ProductId, count(1) as c
from SALES_HISTORY
group by CustomerNo, ProductId)
select CustomerNo, ProductId as MostPurchasedProductId, (t.c * 1.0)/(select sum(c) from cte t2 where t.CustomerNo = t2.CustomerNo) as MostPurchasedProductPercentage
from cte t
where c = (select max(c) from cte t2 where t.CustomerNo = t2.CustomerNo)

SQL小提琴